首页 > 编程语言 >PHPExcel 中文使用手册详解

PHPExcel 中文使用手册详解

时间:2023-04-03 17:48:20浏览次数:36  
标签:style objPHPExcel header getstyle 使用手册 PHPExcel getActiveSheet 详解

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 /**  *  * execl数据导出  * 应用场景:订单导出  * @param string $title 模型名(如Member),用于导出生成文件名的前缀  * @param array $cellName 表头及字段名  * @param array $data 导出的表数据  *  * 特殊处理:合并单元格需要先对数据进行处理  */ function exportOrderExcel($title,$cellName,$data) {        //引入核心文件     vendor("PHPExcel.PHPExcel");     $objPHPExcel new \PHPExcel();     //定义配置     $topNumber = 2;//表头有几行占用     $xlsTitle = iconv('utf-8''gb2312'$title);//文件名称     $fileName $title.date('_YmdHis');//文件名称     $cellKey array(             'A','B','C','D','E','F','G','H','I','J','K','L','M',             'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',             'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',             'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'     );           //写在处理的前面(了解表格基本知识,已测试) //     $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//所有单元格(行)默认高度 //     $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);//所有单元格(列)默认宽度 //     $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);//设置行高度 //     $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);//设置列宽度 //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);//设置文字大小 //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//设置是否加粗 //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);// 设置文字颜色 //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字居左(HORIZONTAL_LEFT,默认值)中(HORIZONTAL_CENTER)右(HORIZONTAL_RIGHT) //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中 //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);//设置填充颜色 //     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF7F24');//设置填充颜色           //处理表头标题     $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellKey[count($cellName)-1].'1');//合并单元格(如果要拆分单元格是需要先合并再拆分的,否则程序会报错)     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','订单信息');     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);     $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);     $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);           //处理表头     foreach ($cellName as $k=>$v)     {         $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber$v[1]);//设置表头数据         $objPHPExcel->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口         $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗         $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中         if($v[3] > 0)//大于0表示需要设置宽度         {             $objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth($v[3]);//设置列宽度         }     }     //处理数据     foreach ($data as $k=>$v)     {         foreach ($cellName as $k1=>$v1)         {             $objPHPExcel->getActiveSheet()->setCellValue($cellKey[$k1].($k+1+$topNumber), $v[$v1[0]]);             if($v['end'] > 0)             {                 if($v1[2] == 1)//这里表示合并单元格                 {                     $objPHPExcel->getActiveSheet()->mergeCells($cellKey[$k1].$v['start'].':'.$cellKey[$k1].$v['end']);                     $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].$v['start'])->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);                 }             }             if($v1[4] != "" && in_array($v1[4], array("LEFT","CENTER","RIGHT")))             {                 $v1[4] = eval('return PHPExcel_Style_Alignment::HORIZONTAL_'.$v1[4].';');                 //这里也可以直接传常量定义的值,即left,center,right;小写的strtolower                 $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].($k+1+$topNumber))->getAlignment()->setHorizontal($v1[4]);             }         }     }     //导出execl     header('pragma:public');     header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');     header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel'Excel5');     $objWriter->save('php://output');     exit; }

  实现如下:

PHPExcel是国外人开发的一个php处理excel插件了,这个插件没有中文的说明了,只有英文说明了,今天我们就一起来看看关于PHPExcel 中文使用手册的例子,具体的如下所示。
HPExcel 是用来操作Office Excel 文档的一个PHP类库,它基于微软的Openxml标准和PHP语言。可以使用它来读取、写入不同格式的电子表格。整体来说是一个不错的Excel操作类。
下面是这个类的一些操作说明

复制代码
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
创建一个excel
$objPHPExcel = new PHPExcel();
// 输出Excel表格到浏览器下载
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="abc.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('php://output');
——————————————————————————————————————–
设置excel的属性:
创建人
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
标题
$objPHPExcel->getProperties()->settitle("Office 2007 XLSX Test Document");
题目
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
描述
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
关键字
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
种类
$objPHPExcel->getProperties()->setCategory("Test result file");
——————————————————————————————————————–
 
设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
设置sheet的name
$objPHPExcel->getActiveSheet()->settitle('Simple');
设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 12);
$objPHPExcel->getActiveSheet()->setCellValue('A3', true);
$objPHPExcel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
$objPHPExcel->getActiveSheet()->setCellValue('B8', '=MIN(B2:C5)');
合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');
分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');
冻结窗口
$objPHPExcel->getActiveSheet()->freezePane('A2');
保护cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');
设置格式
// Set cell number formats
echo date('H:i:s') . " Set cell number formats\n";
$objPHPExcel->getActiveSheet()->getstyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicatestyle( $objPHPExcel->getActiveSheet()->getstyle('E4'), 'E5:E13' );
设置宽width
// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
// 设置单元格高度
// 所有单元格默认高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
// 第一行的默认高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
设置font
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setUnderline(PHPExcel_style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->getColor()->setARGB(PHPExcel_style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getstyle('E1')->getFont()->getColor()->setARGB(PHPExcel_style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getstyle('D13')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getstyle('E13')->getFont()->setBold(true);
设置align
$objPHPExcel->getActiveSheet()->getstyle('D11')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getstyle('D12')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getstyle('D13')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getstyle('A18')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_JUSTIFY);
//垂直居中
$objPHPExcel->getActiveSheet()->getstyle('A18')->getAlignment()->setVertical(PHPExcel_style_Alignment::VERTICAL_CENTER);
设置column的border
$objPHPExcel->getActiveSheet()->getstyle('A4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getstyle('B4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getstyle('C4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getstyle('D4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getstyle('E4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
设置border的color
$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getstyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getstyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getstyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
设置填充颜色
$objPHPExcel->getActiveSheet()->getstyle('A1')->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getstyle('A1')->getFill()->getStartColor()->setARGB('FF808080');
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getstyle('B1')->getFill()->getStartColor()->setARGB('FF808080');
加图片
/*实例化插入图片类*/
$objDrawing = new PHPExcel_Worksheet_Drawing();
/*设置图片路径 切记:只能是本地图片*/
$objDrawing->setPath($img_val);
/*设置图片高度*/
$objDrawing->setWidth(200);
$img_height[] = $objDrawing->getHeight();
/*设置图片要插入的单元格*/
$objDrawing->setCoordinates($img_k[$j].$i);
/*设置图片所在单元格的格式*/
$objDrawing->setOffsetX(10);
$objDrawing->setOffsetY(10);
$objDrawing->setRotation(0);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(50);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
导出Excel表格例子
<?php
$datas = array(
array('王城', '男', '18', '1997-03-13', '18948348924'),
array('李飞虹', '男', '21', '1994-06-13', '159481838924'),
array('王芸', '女', '18', '1997-03-13', '18648313924'),
array('郭瑞', '男', '17', '1998-04-13', '15543248924'),
array('李晓霞', '女', '19', '1996-06-13', '18748348924'),
);
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
// 创建一个excel
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Phpmarker")->setLastModifiedBy("Phpmarker")->settitle("Phpmarker")->setSubject("Phpmarker")->setDescription("Phpmarker")->setKeywords("Phpmarker")->setCategory("Phpmarker");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', '名字')->setCellValue('B1', '性别')->setCellValue('C1', '年龄')->setCellValue('D1', '出生日期')->setCellValue('E1', '电话号码');
 
// Rename worksheet
$objPHPExcel->getActiveSheet()->settitle('Phpmarker-' . date('Y-m-d'));
 
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
$objPHPExcel->getActiveSheet()->freezePane('A2');
$i = 2;
foreach($datas as $data){
$objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $data[0])->getstyle('A'.$i)->getAlignment()->setHorizontal(PHPExcel_style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $data[1]);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $data[2]);
$objPHPExcel->getActiveSheet()->setCellValueExplicit('D'. $i, $data[3],PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getstyle('D' . $i)->getNumberFormat()->setFormatCode("@");
 
// 设置文本格式
$objPHPExcel->getActiveSheet()->setCellValueExplicit('E'. $i, $data[4],PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getstyle('E' . $i)->getAlignment()->setWrapText(true);
$i ;
}
$objActSheet = $objPHPExcel->getActiveSheet();
 
// 设置CELL填充颜色
$cell_fill = array(
'A1',
'B1',
'C1',
'D1',
'E1',
);
foreach($cell_fill as $cell_fill_val){
$cellstyle = $objActSheet->getstyle($cell_fill_val);
// background
// $cellstyle->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID)->getStartColor()->setARGB('fafa00');
// set align
$cellstyle->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_style_Alignment::VERTICAL_CENTER);
// font
$cellstyle->getFont()->setSize(12)->setBold(true);
// border
$cellstyle->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
$cellstyle->getBorders()->getBottom()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
$cellstyle->getBorders()->getLeft()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
$cellstyle->getBorders()->getRight()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB('FFFF0000');
}
 
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
 
$objActSheet->getColumnDimension('A')->setWidth(18.5);
$objActSheet->getColumnDimension('B')->setWidth(23.5);
$objActSheet->getColumnDimension('C')->setWidth(12);
$objActSheet->getColumnDimension('D')->setWidth(12);
$objActSheet->getColumnDimension('E')->setWidth(12);
 
$filename = '2015030423';
ob_end_clean();//清除缓冲区,避免乱码 
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save('php://output');
?>
复制代码

到处中文时,会出现乱码,记得在Header 之前加 ob_end_clean() 清除缓冲区,避免乱码

标签:style,objPHPExcel,header,getstyle,使用手册,PHPExcel,getActiveSheet,详解
From: https://www.cnblogs.com/yzl042349/p/17283776.html

相关文章

  • ipmitool详解
    1、ipmitool(IPMITool)是一个开源的命令行实用程序,用于与基于IPMI(IntelligentPlatformManagementInterface)协议的远程管理控制器(RMC)通信。IPMI是一种硬件标准,它定义了一组接口和协议,用于管理和监控服务器等计算机系统。通过IPMI,管理员可以使用远程控制台、Web界面或命令行接口来......
  • 详解语音处理检测技术中的热点——端点检测、降噪和压缩 | 硬创公开课
    作为一种人机交互的手段,语音的端点检测在解放人类双手方面意义重大。同时,工作环境存在着各种各样的背景噪声,这些噪声会严重降低语音的质量从而影响语音应用的效果,比如会降低识别率。未经压缩的语音数据,网络交互应用中的网络流量偏大,从而降低语音应用的成功率。因此,音频的端点检测......
  • k8s教程(Volume篇)-PVC详解
    文章目录01引言02PVC详解2.1参数配置2.1.1资源请求(Resources)2.1.2访问模式(AccessModes)2.1.3存储卷模式(VolumeModes)2.1.4PV选择条件(Selector)2.1.5存储类别(Class)03Pod使用PVC3.1举例3.1.1举例:默认模式(Filesystem)3.1.2举例:存储卷模式为块设备(Block)04文末01......
  • mysql/mariadb配置详解
    环境说明mariadb版本:mariadb-10.4配置文件参数解读[mysqld]#服务端基本配置port=3306#监听端口user=mysqlpid-file=/var/run/mysqld/mysqld.pid#pid所在的目录socket=/var/run/mysqld/mysqld.sock#为mysql客户端程序和服务器之间的本地通......
  • js数组详解(四):排序API
    1.排序:   自定义排序:冒泡  排序API:arr.sort();    大问题:默认将所有元素转为字符串再按字符串排列         只能对字符串类型的元素正确排序    解决:自定义比较规则:      比较器函数:专门比较任意两值大小的......
  • ResultSet详解
    ResultSet类型ResultSet对象的类型主要体现在两个方面:(1)游标可操作的方式。(2)ResultSet对象的修改对数据库的影响。后者称为ResultSet对象的敏感性。ResultSet有3种不同的类型,分别说明如下。(1)TYPE_FORWARD_ONLY​ 这种类型的ResultSet不可滚动,游标只能向前移动,从第一行到最后......
  • Vue3【Axios网络请求(GET、POST 、并发请求、全局配置 )】(八)-全面详解(学习总结---从入
    ......
  • Vue3【Transition(效果、CSS 过渡、使用animation、TransitionGroup、 KeepAlive、Tele
    ......
  • 原码、反码、补码和移码详解
    计算入门原码:正数是其二进制本身;负数是符号位为1,数值部分取X绝对值的二进制。反码:正数的反码和原码相同;负数是符号位为1,其它位是原码取反。补码:正数的补码和原码,反码相同;负数是符号位为1,其它位是原码取反,未位加1。(或者说负数的补码是其绝对值反码未位加1)移码:将符号位取反......
  • Java 序列化详解
    XML和JSON是两种经常在网络使用的数据表示格式,这里我们介绍如何使用Java读写XML和JSON。 一、XML概述1、XML简介我们都知道对象是不能在网络中直接传输的,不过还有补救的办法。XML(ExtensibleMarkupLanguage)可扩展标记语言,本身就被设计用来存储数据,任何一个对象都可以用XML来描......