PHPExcel 中文使用手册详解

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

复制代码
下面是这个类的一些操作说明

  2. include ‘PHPExcel.php’;
  3. include ‘PHPExcel/Writer/Excel2007.php’;
  4. //或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的
  5. 创建一个excel
  6. $objPHPExcel = new PHPExcel();
  7. // 输出Excel表格到浏览器下载
  8. header(‘Content-Type: application/vnd.ms-excel’);
  9. header(‘Content-Disposition: attachment;filename="abc.xls"’);
  10. header(‘Cache-Control: max-age=0’);
  11. // If you’re serving to IE 9, then the following may be needed
  12. header(‘Cache-Control: max-age=1’);
  13. // If you’re serving to IE over SSL, then the following may be needed
  14. header(‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’); // Date in the past
  15. header(‘Last-Modified: ‘ . gmdate(‘D, d M Y H:i:s’) . ‘ GMT’); // always modified
  16. header(‘Cache-Control: cache, must-revalidate’); // HTTP/1.1
  17. header(‘Pragma: public’); // HTTP/1.0
  18. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
  19. $objWriter->save(‘php://output’);
  20. ——————————————————————————————————————–
  21. 设置excel的属性：
  22. 创建人
  23. $objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
  24. 最后修改人
  25. $objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
  26. 标题
  27. $objPHPExcel->getProperties()->settitle("Office 2007 XLSX Test Document");
  28. 题目
  29. $objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
  30. 描述
  31. $objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
  32. 关键字
  33. $objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
  34. 种类
  35. $objPHPExcel->getProperties()->setCategory("Test result file");
  36. ——————————————————————————————————————–
  38. 设置当前的sheet
  39. $objPHPExcel->setActiveSheetIndex(0);
  40. 设置sheet的name
  41. $objPHPExcel->getActiveSheet()->settitle(‘Simple’);
  42. 设置单元格的值
  43. $objPHPExcel->getActiveSheet()->setCellValue(‘A1’, ‘String’);
  44. $objPHPExcel->getActiveSheet()->setCellValue(‘A2’, 12);
  45. $objPHPExcel->getActiveSheet()->setCellValue(‘A3’, true);
  46. $objPHPExcel->getActiveSheet()->setCellValue(‘C5’, ‘=SUM(C2:C4)’);
  47. $objPHPExcel->getActiveSheet()->setCellValue(‘B8’, ‘=MIN(B2:C5)’);
  48. 合并单元格
  49. $objPHPExcel->getActiveSheet()->mergeCells(‘A18:E22’);
  50. 分离单元格
  51. $objPHPExcel->getActiveSheet()->unmergeCells(‘A28:B28’);
  52. 冻结窗口
  53. $objPHPExcel->getActiveSheet()->freezePane(‘A2’);
  54. 保护cell
  55. $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
  56. $objPHPExcel->getActiveSheet()->protectCells(‘A3:E13’, ‘PHPExcel’);
  57. 设置格式
  58. // Set cell number formats
  59. echo date(‘H:i:s’) . " Set cell number formats\n";
  60. $objPHPExcel->getActiveSheet()->getstyle(‘E4’)->getNumberFormat()->setFormatCode(PHPExcel_style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
  61. $objPHPExcel->getActiveSheet()->duplicatestyle( $objPHPExcel->getActiveSheet()->getstyle(‘E4’), ‘E5:E13’ );
  62. 设置宽width
  63. // Set column widths
  64. $objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setAutoSize(true);
  65. $objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(12);
  66. // 设置单元格高度
  67. // 所有单元格默认高度
  68. $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
  69. // 第一行的默认高度
  70. $objPHPExcel->getActiveSheet()->getRowDimension(‘1’)->setRowHeight(30);
  71. 设置font
  72. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFont()->setName(‘Candara’);
  73. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFont()->setSize(20);
  74. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFont()->setBold(true);
  75. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFont()->setUnderline(PHPExcel_style_Font::UNDERLINE_SINGLE);
  76. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFont()->getColor()->setARGB(PHPExcel_style_Color::COLOR_WHITE);
  77. $objPHPExcel->getActiveSheet()->getstyle(‘E1’)->getFont()->getColor()->setARGB(PHPExcel_style_Color::COLOR_WHITE);
  78. $objPHPExcel->getActiveSheet()->getstyle(‘D13’)->getFont()->setBold(true);
  79. $objPHPExcel->getActiveSheet()->getstyle(‘E13’)->getFont()->setBold(true);
  80. 设置align
  81. $objPHPExcel->getActiveSheet()->getstyle(‘D11’)->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);
  82. $objPHPExcel->getActiveSheet()->getstyle(‘D12’)->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);
  83. $objPHPExcel->getActiveSheet()->getstyle(‘D13’)->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);
  84. $objPHPExcel->getActiveSheet()->getstyle(‘A18’)->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_JUSTIFY);
  85. //垂直居中
  86. $objPHPExcel->getActiveSheet()->getstyle(‘A18’)->getAlignment()->setVertical(PHPExcel_style_Alignment::VERTICAL_CENTER);
  87. 设置column的border
  88. $objPHPExcel->getActiveSheet()->getstyle(‘A4’)->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
  89. $objPHPExcel->getActiveSheet()->getstyle(‘B4’)->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
  90. $objPHPExcel->getActiveSheet()->getstyle(‘C4’)->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
  91. $objPHPExcel->getActiveSheet()->getstyle(‘D4’)->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
  92. $objPHPExcel->getActiveSheet()->getstyle(‘E4’)->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);
  93. 设置border的color
  94. $objPHPExcel->getActiveSheet()->getstyle(‘D13’)->getBorders()->getLeft()->getColor()->setARGB(‘FF993300’);
  95. $objPHPExcel->getActiveSheet()->getstyle(‘D13’)->getBorders()->getTop()->getColor()->setARGB(‘FF993300’);
  96. $objPHPExcel->getActiveSheet()->getstyle(‘D13’)->getBorders()->getBottom()->getColor()->setARGB(‘FF993300’);
  97. $objPHPExcel->getActiveSheet()->getstyle(‘E13’)->getBorders()->getTop()->getColor()->setARGB(‘FF993300’);
  98. $objPHPExcel->getActiveSheet()->getstyle(‘E13’)->getBorders()->getBottom()->getColor()->setARGB(‘FF993300’);
  99. $objPHPExcel->getActiveSheet()->getstyle(‘E13’)->getBorders()->getRight()->getColor()->setARGB(‘FF993300’);
  100. 设置填充颜色
  101. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID);
  102. $objPHPExcel->getActiveSheet()->getstyle(‘A1’)->getFill()->getStartColor()->setARGB(‘FF808080’);
  103. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID);
  104. $objPHPExcel->getActiveSheet()->getstyle(‘B1’)->getFill()->getStartColor()->setARGB(‘FF808080’);
  105. 加图片
  106. /*实例化插入图片类*/
  107. $objDrawing = new PHPExcel_Worksheet_Drawing();
  108. /*设置图片路径 切记：只能是本地图片*/
  109. $objDrawing->setPath($img_val);
  110. /*设置图片高度*/
  111. $objDrawing->setWidth(200);
  112. $img_height[] = $objDrawing->getHeight();
  113. /*设置图片要插入的单元格*/
  114. $objDrawing->setCoordinates($img_k[$j].$i);
  115. /*设置图片所在单元格的格式*/
  116. $objDrawing->setOffsetX(10);
  117. $objDrawing->setOffsetY(10);
  118. $objDrawing->setRotation(0);
  119. $objDrawing->getShadow()->setVisible(true);
  120. $objDrawing->getShadow()->setDirection(50);
  121. $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
  122. 导出Excel表格例子
  123. <?php
  124. $datas = array(
  125. array(‘王城’, ‘男’, ’18’, ‘1997-03-13’, ‘18948348924’),
  126. array(‘李飞虹’, ‘男’, ’21’, ‘1994-06-13’, ‘159481838924’),
  127. array(‘王芸’, ‘女’, ’18’, ‘1997-03-13’, ‘18648313924’),
  128. array(‘郭瑞’, ‘男’, ’17’, ‘1998-04-13’, ‘15543248924’),
  129. array(‘李晓霞’, ‘女’, ’19’, ‘1996-06-13’, ‘18748348924’),
  130. );
  131. include ‘PHPExcel.php’;
  132. include ‘PHPExcel/Writer/Excel2007.php’;
  133. //或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的
  134. // 创建一个excel
  135. $objPHPExcel = new PHPExcel();
  136. // Set document properties
  137. $objPHPExcel->getProperties()->setCreator("Phpmarker")->setLastModifiedBy("Phpmarker")->settitle("Phpmarker")->setSubject("Phpmarker")->setDescription("Phpmarker")->setKeywords("Phpmarker")->setCategory("Phpmarker");
  138. $objPHPExcel->setActiveSheetIndex(0)->setCellValue(‘A1’, ‘名字’)->setCellValue(‘B1’, ‘性别’)->setCellValue(‘C1’, ‘年龄’)->setCellValue(‘D1’, ‘出生日期’)->setCellValue(‘E1’, ‘电话号码’);
  140. // Rename worksheet
  141. $objPHPExcel->getActiveSheet()->settitle(‘Phpmarker-‘ . date(‘Y-m-d’));
  143. // Set active sheet index to the first sheet, so Excel opens this as the first sheet
  144. $objPHPExcel->setActiveSheetIndex(0);
  145. $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
  146. $objPHPExcel->getActiveSheet()->freezePane(‘A2’);
  147. $i = 2;
  148. foreach($datas as $data){
  149. $objPHPExcel->getActiveSheet()->setCellValue(‘A’ . $i, $data[0])->getstyle(‘A’.$i)->getAlignment()->setHorizontal(PHPExcel_style_Alignment::VERTICAL_CENTER);
  150. $objPHPExcel->getActiveSheet()->setCellValue(‘B’ . $i, $data[1]);
  151. $objPHPExcel->getActiveSheet()->setCellValue(‘C’ . $i, $data[2]);
  152. $objPHPExcel->getActiveSheet()->setCellValueExplicit(‘D’. $i, $data[3],PHPExcel_Cell_DataType::TYPE_STRING);
  153. $objPHPExcel->getActiveSheet()->getstyle(‘D’ . $i)->getNumberFormat()->setFormatCode("@");
  155. // 设置文本格式
  156. $objPHPExcel->getActiveSheet()->setCellValueExplicit(‘E’. $i, $data[4],PHPExcel_Cell_DataType::TYPE_STRING);
  157. $objPHPExcel->getActiveSheet()->getstyle(‘E’ . $i)->getAlignment()->setWrapText(true);
  158. $i ;
  159. }
  160. $objActSheet = $objPHPExcel->getActiveSheet();
  162. // 设置CELL填充颜色
  163. $cell_fill = array(
  164. ‘A1’,
  165. ‘B1’,
  166. ‘C1’,
  167. ‘D1’,
  168. ‘E1’,
  169. );
  170. foreach($cell_fill as $cell_fill_val){
  171. $cellstyle = $objActSheet->getstyle($cell_fill_val);
  172. // background
  173. // $cellstyle->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID)->getStartColor()->setARGB(‘fafa00’);
  174. // set align
  175. $cellstyle->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_LEFT)->setVertical(PHPExcel_style_Alignment::VERTICAL_CENTER);
  176. // font
  177. $cellstyle->getFont()->setSize(12)->setBold(true);
  178. // border
  179. $cellstyle->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB(‘FFFF0000’);
  180. $cellstyle->getBorders()->getBottom()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB(‘FFFF0000’);
  181. $cellstyle->getBorders()->getLeft()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB(‘FFFF0000’);
  182. $cellstyle->getBorders()->getRight()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN)->getColor()->setARGB(‘FFFF0000’);
  183. }
  185. $objPHPExcel->getActiveSheet()->getRowDimension(‘1’)->setRowHeight(30);
  187. $objActSheet->getColumnDimension(‘A’)->setWidth(18.5);
  188. $objActSheet->getColumnDimension(‘B’)->setWidth(23.5);
  189. $objActSheet->getColumnDimension(‘C’)->setWidth(12);
  190. $objActSheet->getColumnDimension(‘D’)->setWidth(12);
  191. $objActSheet->getColumnDimension(‘E’)->setWidth(12);
  193. $filename = ‘2015030423’;
  194. ob_end_clean();//清除缓冲区,避免乱码
  195. header(‘Content-Type: application/vnd.ms-excel’);
  196. header(‘Content-Disposition: attachment;filename="’.$filename.’.xls"’);
  197. header(‘Cache-Control: max-age=0’);
  198. // If you’re serving to IE 9, then the following may be needed
  199. header(‘Cache-Control: max-age=1’);
  200. // If you’re serving to IE over SSL, then the following may be needed
  201. header(‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’); // Date in the past
  202. header(‘Last-Modified: ‘ . gmdate(‘D, d M Y H:i:s’) . ‘ GMT’); // always modified
  203. header(‘Cache-Control: cache, must-revalidate’); // HTTP/1.1
  204. header(‘Pragma: public’); // HTTP/1.0
  205. $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
  206. $objWriter->save(‘php://output’);

复制代码记得在Header 之前加 ob_end_clean() 清除缓冲区，避免乱码

发表回复