logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

PHPExcel 中文使用手册详解

 黄三岁大爱人生 2021-04-20

实现代码

  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.     );
  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');//设置填充颜色
  40.     //处理表头标题
  41.     $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellKey[count($cellName)-1].'1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
  42.     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','订单信息');
  43.     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
  44.     $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
  45.     $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  46.     $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  48.     //处理表头
  49.     foreach ($cellName as $k=>$v)
  50.     {
  51.         $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber, $v[1]);//设置表头数据
  52.         $objPHPExcel->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口
  53.         $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗
  54.         $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
  55.         if($v[3] > 0)//大于0表示需要设置宽度
  56.         {
  57.             $objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth($v[3]);//设置列宽度
  58.         }
  59.     }
  60.     //处理数据
  61.     foreach ($data as $k=>$v)
  62.     {
  63.         foreach ($cellName as $k1=>$v1)
  64.         {
  65.             $objPHPExcel->getActiveSheet()->setCellValue($cellKey[$k1].($k+1+$topNumber), $v[$v1[0]]);
  66.             if($v['end'] > 0)
  67.             {
  68.                 if($v1[2] == 1)//这里表示合并单元格
  69.                 {
  70.                     $objPHPExcel->getActiveSheet()->mergeCells($cellKey[$k1].$v['start'].':'.$cellKey[$k1].$v['end']);
  71.                     $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].$v['start'])->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
  72.                 }
  73.             }
  74.             if($v1[4] != "" && in_array($v1[4], array("LEFT","CENTER","RIGHT")))
  75.             {
  76.                 $v1[4] = eval('return PHPExcel_Style_Alignment::HORIZONTAL_'.$v1[4].';');
  77.                 //这里也可以直接传常量定义的值，即left,center,right；小写的strtolower
  78.                 $objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].($k+1+$topNumber))->getAlignment()->setHorizontal($v1[4]);
  79.             }
  80.         }
  81.     }
  82.     //导出execl
  83.     header('pragma:public');
  84.     header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
  85.     header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
  86.     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  87.     $objWriter->save('php://output');
  88.     exit;
  89. }

实现效果：

以下从网上找的，很有用，都经过本人亲测的！拿下来做为自己收藏

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

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

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

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： 黄三岁大爱人生 > 《网页》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多