PHP 使用 PHPExcel 库生成 Excel 文件

时间:2024-03-07 22:00:03

PHPExcel 是用来操作Office Excel 文档的一个PHP类库,它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格,如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML等等

 

调用代码示例:

  1. $php_excel = new PHPExcel();
  2.  
  3. $properties = $php_excel->getProperties();
  4. $properties->setCreator("www.1024i.com");
  5. $properties->setLastModifiedBy("www.loubarnes.com");
  6. $properties->setTitle("PHP 生成 Excel");
  7. $properties->setSubject("PHP 生成 Excel");
  8. $properties->setDescription(\'PHP 生成 Excel\');
  9. $properties->setKeywords("PHP 生成 Excel");
  10. $properties->setCategory("PHP 生成 Excel");
  11.  
  12. $php_excel->setActiveSheetIndex(0);
  13. $active_sheet = $php_excel->getActiveSheet();
  14.  
  15. $active_sheet->setTitle(\'用户\');
  16.  
  17. // 自动调节大小
  18. $active_sheet->getColumnDimension(\'A\')->setWidth(8);
  19. $active_sheet->getColumnDimension(\'B\')->setWidth(12);
  20. $active_sheet->getColumnDimension(\'C\')->setWidth(8);
  21. $active_sheet->getColumnDimension(\'D\')->setWidth(8);
  22. $active_sheet->getColumnDimension(\'E\')->setWidth(24);
  23. $active_sheet->getColumnDimension(\'F\')->setWidth(60);
  24.  
  25. $active_sheet->setCellValue(\'A1\', \'PHP 生成 Excel 示例\' );
  26. $active_sheet->mergeCells(\'A1:F1\'); // 合并表头单元格
  27. $active_sheet->getRowDimension(1)->setRowHeight(30); // 设置表头1高度
  28. $style = array(
  29. \'font\' => array(
  30. \'size\' => 20
  31. ),
  32. \'alignment\' => array(
  33. \'horizontal\' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  34. ),
  35. \'borders\' => array(
  36. \'bottom\' => array(
  37. \'style\' => PHPExcel_Style_Border::BORDER_THIN
  38. )
  39. )
  40. );
  41. $active_sheet->getStyle(\'A1:F1\')->applyFromArray($style); // 设置表头1样式
  42.  
  43.  
  44.  
  45. $active_sheet->getRowDimension(2)->setRowHeight(30); // 设置表头2高度
  46. // 设置表头2名称
  47. $active_sheet->setCellValue(\'A2\', \'编号\');
  48. $active_sheet->setCellValue(\'B2\', \'名称\');
  49. $active_sheet->setCellValue(\'C2\', \'性别\');
  50. $active_sheet->setCellValue(\'D2\', \'年龄\');
  51. $active_sheet->setCellValue(\'E2\', \'出生日期\');
  52. $active_sheet->setCellValue(\'F2\', \'备注\');
  53.  
  54.  
  55.  
  56. // 表头(编号, 名称, 性别, 出生日期)样式
  57. $style = array(
  58. \'font\' => array(
  59. \'bold\' => true
  60. ),
  61. \'alignment\' => array(
  62. \'horizontal\' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  63. ),
  64. \'borders\' => array(
  65. \'bottom\' => array(
  66. \'style\' => PHPExcel_Style_Border::BORDER_THIN
  67. )
  68. )
  69. );
  70. $active_sheet->getStyle(\'A2:E2\')->applyFromArray($style);
  71.  
  72. // 表头(备注)样式
  73. $style = array(
  74. \'font\' => array(
  75. \'bold\' => true
  76. ),
  77. \'borders\' => array(
  78. \'bottom\' => array(
  79. \'style\' => PHPExcel_Style_Border::BORDER_THIN
  80. )
  81. )
  82. );
  83. $active_sheet->getStyle(\'F2\')->applyFromArray($style);
  84.  
  85. // 内容样式
  86. $style = array(
  87. \'alignment\' => array(
  88. \'horizontal\' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  89. )
  90. );
  91. $active_sheet->getStyle(\'A:E\')->applyFromArray($style);
  92.  
  93.  
  94.  
  95. $ids = post::_ints(\'id\', array());
  96. $notes = post::_strings(\'note\', array());
  97.  
  98. $i = 3;
  99.  
  100. if(count($ids))
  101. {
  102. foreach($ids as $id)
  103. {
  104. $note = $notes[$i-3];
  105. foreach($this->data as $data)
  106. {
  107. if($data[\'id\']==$id)
  108. {
  109. $active_sheet->setCellValue(\'A\'.$i, $id );
  110. $active_sheet->setCellValue(\'B\'.$i, $data[\'name\'] );
  111. $active_sheet->setCellValue(\'C\'.$i, $data[\'male\'] );
  112. $active_sheet->setCellValue(\'D\'.$i, $data[\'age\'] );
  113. $active_sheet->setCellValue(\'E\'.$i, $data[\'birth_date\'] );
  114. $active_sheet->setCellValue(\'F\'.$i, $note );
  115. break;
  116. }
  117. }
  118. $i++;
  119. }
  120. }
  121.  
  122. header(\'Content-Type: application/vnd.ms-excel\');
  123. header(\'Content-Disposition: attachment; filename="用户.xls"\');
  124. $writer = PHPExcel_IOFactory::createWriter($php_excel, \'Excel5\');
  125. $writer->save(\'php://output\');

 

官方示例文档中有输出为 PDF 的示例程序:

  1. // Change these values to select the Rendering library that you wish to use
  2. // and its directory location on your server
  3. //$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
  4. $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
  5. //$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;
  6. //$rendererLibrary = \'tcPDF5.9\';
  7. $rendererLibrary = \'mPDF5.4\';
  8. //$rendererLibrary = \'domPDF0.6.0beta3\';
  9. $rendererLibraryPath = dirname(__FILE__).\'/../../../libraries/PDF/\' . $rendererLibrary;
  10.  
  11.  
  12. // ..........
  13.  
  14.  
  15. if (!PHPExcel_Settings::setPdfRenderer(
  16. $rendererName,
  17. $rendererLibraryPath
  18. )) {
  19. die(
  20. \'NOTICE: Please set the $rendererName and $rendererLibraryPath values\' .
  21. \'<br />\' .
  22. \'at the top of this script as appropriate for your directory structure\'
  23. );
  24. }
  25.  
  26.  
  27. // Redirect output to a client’s web browser (PDF)
  28. header(\'Content-Type: application/pdf\');
  29. header(\'Content-Disposition: attachment;filename="01simple.pdf"\');
  30. header(\'Cache-Control: max-age=0\');
  31.  
  32. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, \'PDF\');
  33. $objWriter->save(\'php://output\');

 使用这段代码时需要引入PHP 版本的 PDF 库,支持三个版本的:

  1. //$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
  2. $rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
  3. //$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;

即 TCPDF, MPDF,DOMPDF,官方网址分别是:

http://www.tcpdf.org/ 
http://www.mpdf1.com/mpdf/ 
https://github.com/dompdf/dompdf

推荐使用TCPDF,下载后复制到项目中,然后代码中 $rendererLibraryPath 改为对应的路径,然后就可以正常输出 PDF 文档了。

对于网上很多用户反映的 PDF 中文乱码问题,解决方法如下:

    1. 所有程序及文档全部使用 UTF-8 编码
    2. 在 tcpdf_autoconfig.php 中设置中文字库。