使用PHPExcel导出数据
- 最近要求做增加客流数据等导出为Excel的功能，phpExcel包功能强大，根据实际需求，我只学习了简单的功能。
安装PHPExcel
在composer.json中添加：
"require": {
"phpoffice/phpexcel": "1.8.1"
},
安装完毕，在vendor中出现一个phpoffice包。
使用
$objPHPExcel = new \PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()
->setCellValue("A1", '门店名称')
->setCellValue("B1", '店长')
->setCellValue("A2", '我的门店')
->setCellValue("B2", 'www');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="store.xls"');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
- 增加header头，则会直接下载一个Excel表格到本地，exce列为大写的A,B...,行为数字1,2,3...，不断地拓展。
如果觉得一个个的麻烦可以使用一个php数组导入：
$objPHPExcel = new \PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$dataArray = array(
array('111' => "2016", '222' => "1000", '33aa' => "1000", 200),
array("2010", "Q2", "www", 100),
array("2010", "Q2", "www", 100),
array("2010", "Q2", "www", 100),
array("2010", "Q2", "www", 100),
);
// 或者hash也是可以的，只是插入的Excel的时候，过滤掉key
// $dataArray = array(
// '11'=> array('111' => "2116", '222' => "1000", '33aa' => "1000", 200),
// '22'=> array("2010", "Q2", "www", 100),
// array("2010", "Q2", "www", 100),
// array("2010", "Q2", "www", 100),
// array("2010", "Q2", "www", 100),
// );
$objPHPExcel->getActiveSheet()->fromArray($dataArray, null, 'A1');
$objPHPExcel->getActiveSheet()
->setCellValue("E2", '=SUM(A1:C1）/3');
-
可以将数组导入，关联数组也可以，只是将值导入，同时可以计算结果导入对应的表格。
-
单元格计算，例如：setCellValue("E2", '=SUM(A1:C1）/3')，试讲A1,B1,C1相加除以3所得结果放入E2表格。
-
输出，通过header头，下载Excel文件
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');
// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997$hourValueGMT'); // 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 = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
$objPHPExcel->disconnectWorksheets();
其他的有用的方法：
- 获取行列等
$Column = $objPHPExcel->getActiveSheet()
->getCell('B2')->getColumn();
$Row = $objPHPExcel->getActiveSheet()
->getCell('B2')->getRow();
$Value = $objPHPExcel->getActiveSheet()
->getCell('B2')->getValue();
以上分别可以得到相应的列，行，和表格对应的值，但如果单元格里面放的公式，则返回时公式，而非结果，如上，获取E2则返回结果SUM(A1:C1）/3。
- 合并单元格
$objPHPExcel->getActiveSheet()->mergeCells("A1:A3"); //将A列的1,2，3行合并为一行
- 增加样式
$objPHPExcel->getActiveSheet()->getStyle( 'A1:' . $lineIndex . '1')->getFont()->setBold(true); //加粗
$objPHPExcel->getActiveSheet()->getStyle( 'A1:' . $lineIndex . '1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID); // 背景填充方式
$objPHPExcel->getActiveSheet()->getStyle( 'A1:' . $lineIndex . '1')->getFill()->getStartColor()->setARGB('B0C4DE'); //加颜色
- 数组方式
* <code>
* $objPHPExcel->getActiveSheet()->getStyle('B2')->applyFromArray(
* array(
* 'font' => array(
* 'name' => 'Arial',
* 'bold' => true,
* 'italic' => false,
* 'underline' => PHPExcel_Style_Font::UNDERLINE_DOUBLE,
* 'strike' => false,
* 'color' => array(
* 'rgb' => '808080'
* )
* ),
* 'borders' => array(
* 'bottom' => array(
* 'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
* 'color' => array(
* 'rgb' => '808080'
* )
* ),
* 'top' => array(
* 'style' => PHPExcel_Style_Border::BORDER_DASHDOT,
* 'color' => array(
* 'rgb' => '808080'
* )
* )
* ),
* 'quotePrefix' => true
* )
假如列大于Z，可以通过这个方法转化为对应AA，AB等，从0开始。
private function stringFromColumnIndex($pColumnIndex = 0)
{
static $_indexCache = array();
if (!isset($_indexCache[$pColumnIndex])) {
if ($pColumnIndex < 26) {
$_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);
} elseif ($pColumnIndex < 702) {
$_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) . chr(65 + $pColumnIndex % 26);
} else {
$_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) . chr(65 + ((($pColumnIndex - 26) % 676) / 26)) . chr(65 + $pColumnIndex % 26);
}
}
return $_indexCache[$pColumnIndex];
}
foreach ($mergeArray as $singleMergeArray) {
$objPHPExcel->getActiveSheet()->mergeCells("A$singleMergeArray[0]:A$singleMergeArray[1]");
$objPHPExcel->getActiveSheet()->getStyle("A$singleMergeArray[0]:A$singleMergeArray[1]")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
if (($singleMergeArray[1] - $singleMergeArray[0]) > 2) {
$lastMergeIndex = $singleMergeArray[1] - 2;
$objPHPExcel->getActiveSheet()->mergeCells("D$singleMergeArray[0]:D$lastMergeIndex");
$objPHPExcel->getActiveSheet()->getStyle("D$singleMergeArray[0]:D$lastMergeIndex")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
}
