PHPExcel 中文使用手册详解
发布:smiling 来源: PHP粉丝网 添加日期:2018-09-14 11:01:57 浏览: 评论:0
PHPExcel 是用来操作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');
- //phpfensi.com
- // 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() 清除缓冲区,避免乱码.
Tags: PHPExcel PHPExcel手册
相关文章
- ·php的phpExcel类生成excel时列超过26大于Z时如何解决(2015-04-04)
- ·PHPExcel导出时列过大问题解决办法(2015-04-13)
- ·phpexcel读取excel表格时间的例子(2015-05-09)
- ·PHPExcel导出excel表格的应用(2016-07-27)
- ·php怎么读取excel?php中使用PHPExcel读写excel(xls)文件的方法(2018-07-20)
- ·PHPExcel导入Excel文件并对其日期单元格处理的代码示例(2019-12-25)
- ·利用phpExcel实现Excel数据的导入导出(全步骤详细解析)(2020-07-06)
- ·phpExcel中文帮助手册之常用功能指南(2021-04-03)
- ·php中使用PHPExcel读写excel(xls)文件的方法(2021-04-11)
- ·使用PHPExcel操作Excel用法实例分析(2021-05-18)
- ·基于ThinkPHP+uploadify+upload+PHPExcel 无刷新导入数据(2021-06-18)
- ·PHP使用PHPexcel导入导出数据的方法(2021-06-26)
- ·PHPExcel笔记, mpdf导出(2021-07-31)
- ·使用phpexcel类实现excel导入mysql数据库功能(实例代码)(2021-08-03)
- ·PHPExcel 修改已存在Excel的方法(2021-09-16)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)