PHP 使用 PHPExcel 库生成 Excel 文件

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

调用代码示例：

$php_excel = new PHPExcel();
$properties = $php_excel->getProperties();
$properties->setCreator("www.1024i.com");
$properties->setLastModifiedBy("www.loubarnes.com");
$properties->setTitle("PHP 生成 Excel");
$properties->setSubject("PHP 生成 Excel");
$properties->setDescription(\'PHP 生成 Excel\');
$properties->setKeywords("PHP 生成 Excel");
$properties->setCategory("PHP 生成 Excel");
$php_excel->setActiveSheetIndex(0);
$active_sheet = $php_excel->getActiveSheet();
$active_sheet->setTitle(\'用户\');
// 自动调节大小
$active_sheet->getColumnDimension(\'A\')->setWidth(8);
$active_sheet->getColumnDimension(\'B\')->setWidth(12);
$active_sheet->getColumnDimension(\'C\')->setWidth(8);
$active_sheet->getColumnDimension(\'D\')->setWidth(8);
$active_sheet->getColumnDimension(\'E\')->setWidth(24);
$active_sheet->getColumnDimension(\'F\')->setWidth(60);
$active_sheet->setCellValue(\'A1\', \'PHP 生成 Excel 示例\' );
$active_sheet->mergeCells(\'A1:F1\'); // 合并表头单元格
$active_sheet->getRowDimension(1)->setRowHeight(30); // 设置表头1高度
$style = array(
\'font\' => array(
\'size\' => 20
),
\'alignment\' => array(
\'horizontal\' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
\'borders\' => array(
\'bottom\' => array(
\'style\' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$active_sheet->getStyle(\'A1:F1\')->applyFromArray($style); // 设置表头1样式
$active_sheet->getRowDimension(2)->setRowHeight(30); // 设置表头2高度
// 设置表头2名称
$active_sheet->setCellValue(\'A2\', \'编号\');
$active_sheet->setCellValue(\'B2\', \'名称\');
$active_sheet->setCellValue(\'C2\', \'性别\');
$active_sheet->setCellValue(\'D2\', \'年龄\');
$active_sheet->setCellValue(\'E2\', \'出生日期\');
$active_sheet->setCellValue(\'F2\', \'备注\');
// 表头(编号, 名称, 性别, 出生日期)样式
$style = array(
\'font\' => array(
\'bold\' => true
),
\'alignment\' => array(
\'horizontal\' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
\'borders\' => array(
\'bottom\' => array(
\'style\' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$active_sheet->getStyle(\'A2:E2\')->applyFromArray($style);
// 表头(备注)样式
$style = array(
\'font\' => array(
\'bold\' => true
),
\'borders\' => array(
\'bottom\' => array(
\'style\' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$active_sheet->getStyle(\'F2\')->applyFromArray($style);
// 内容样式
$style = array(
\'alignment\' => array(
\'horizontal\' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
)
);
$active_sheet->getStyle(\'A:E\')->applyFromArray($style);
$ids = post::_ints(\'id\', array());
$notes = post::_strings(\'note\', array());
$i = 3;
if(count($ids))
{
foreach($ids as $id)
{
$note = $notes[$i-3];
foreach($this->data as $data)
{
if($data[\'id\']==$id)
{
$active_sheet->setCellValue(\'A\'.$i, $id );
$active_sheet->setCellValue(\'B\'.$i, $data[\'name\'] );
$active_sheet->setCellValue(\'C\'.$i, $data[\'male\'] );
$active_sheet->setCellValue(\'D\'.$i, $data[\'age\'] );
$active_sheet->setCellValue(\'E\'.$i, $data[\'birth_date\'] );
$active_sheet->setCellValue(\'F\'.$i, $note );
break;
}
}
$i++;
}
}
header(\'Content-Type: application/vnd.ms-excel\');
header(\'Content-Disposition: attachment; filename="用户.xls"\');
$writer = PHPExcel_IOFactory::createWriter($php_excel, \'Excel5\');
$writer->save(\'php://output\');

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

// Change these values to select the Rendering library that you wish to use
// and its directory location on your server
//$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
$rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
//$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;
//$rendererLibrary = \'tcPDF5.9\';
$rendererLibrary = \'mPDF5.4\';
//$rendererLibrary = \'domPDF0.6.0beta3\';
$rendererLibraryPath = dirname(__FILE__).\'/../../../libraries/PDF/\' . $rendererLibrary;
// ..........
if (!PHPExcel_Settings::setPdfRenderer(
$rendererName,
$rendererLibraryPath
)) {
die(
\'NOTICE: Please set the $rendererName and $rendererLibraryPath values\' .
\'<br />\' .
\'at the top of this script as appropriate for your directory structure\'
);
}
// Redirect output to a client’s web browser (PDF)
header(\'Content-Type: application/pdf\');
header(\'Content-Disposition: attachment;filename="01simple.pdf"\');
header(\'Cache-Control: max-age=0\');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, \'PDF\');
$objWriter->save(\'php://output\');

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

//$rendererName = PHPExcel_Settings::PDF_RENDERER_TCPDF;
$rendererName = PHPExcel_Settings::PDF_RENDERER_MPDF;
//$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 中文乱码问题，解决方法如下：

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

秒客网

PHP 使用 PHPExcel 库生成 Excel 文件

相关文章