/**
* to output excel
* @param array $data ['data'=>[],'header'=>'','cell'=>[]]
* @param string $name filename
* @param string $type 'Excel5' or 'Excel2007' or 'PDF'
*/
public function outExcel($data, $name, $type = 'Excel5')
{
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
ini_set('date.timezone', 'PRC');
if (PHP_SAPI == 'cli') {
die('This example should only be run from a Web Browser');
}
require_once __DIR__ . '/extensions/PHPExcel.php';
$objPHPExcel = new \PHPExcel();
//设置文件属性
if ($type == 'PDF') {
$objPHPExcel->getProperties()->setCreator("miaojiaju")->setLastModifiedBy("miaojiaju")->setTitle("PDF Test Document")->setSubject("PDF Test Document")->setDescription("Test document for PDF, generated using PHP classes.")->setKeywords("pdf php")->setCategory("Test result file");
} else {
$objPHPExcel->getProperties()->setCreator("miaojiaju")->setLastModifiedBy("miaojiaju")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('测试记录');
//获取实际表头下标数组array $cell_key
$cell_key = isset($data['data'][0]) ? array_keys($data['data'][0]) : [];
//根据实际表头下标数组长度$len生成有效单元格列数（字母）数组array $index（A,B，C,...,Z,AA,AB,...）
$len = count($cell_key);
$index = EasyHelpers::excelCell($len);
//$cell_key最大下标$max_index
$max_index = $len - 1;
foreach ($index as $v_i) {
//设置单元格宽度(自动)
$objPHPExcel->getActiveSheet()->getColumnDimension($v_i)->setAutoSize(true);
// $objPHPExcel->getActiveSheet()->getColumnDimension($v_i)->setWidth(30);
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle($v_i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置垂直居中
$objPHPExcel->getActiveSheet()->getStyle($v_i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
}
//设置单元格宽度（手动）
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
//合并第一行单元格作为标题行
$objPHPExcel->getActiveSheet()->mergeCells('A1:' . $index[$max_index] . '1');
//设置单元格样式
$objPHPExcel->getActiveSheet()->getStyle('A2:' . $index[$max_index] . '2')->getFont()->setName('黑体');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A2:' . $index[$max_index] . '2')->getFont()->setBold(true);
// 添加标题行数据
$cellvalue = $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $data['header'] . ' （时间:' . date('Y-m-d H:i:s') . ')');
//添加表头数据
foreach ($data['cell'] as $k_c => $v_c) {
$cellvalue->setCellValue($index[$k_c] . '2', $v_c);
}
//添加表格数据
foreach ($data['data'] as $k_d => $v_d) {
//each line each cell
foreach ($cell_key as $k_ck => $v_ck) {
$objPHPExcel->getActiveSheet()->setCellValue($index[$k_ck] . ($k_d + 3), $v_d[$v_ck]);
}
}
//加图片
// $objDrawing = new \PHPExcel_Worksheet_Drawing();
// $objDrawing->setName('Photo');
// $objDrawing->setDescription('Photo');
// $objDrawing->setPath(\Yii::$app->basePath.'/web/images/balance/ABC.png');
//// $objDrawing->setHeight(170);
//// $objDrawing->setWidth(120);
// //图片坐标
// $objDrawing->setCoordinates('A10');
// $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
ob_end_clean();
if ($type == 'Excel5') {
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
header('Content-Disposition: attachment;filename="' . $name . '(' . date('Ymd-His') . ').xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
} elseif ($type == 'Excel2007') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
header('Content-Disposition: attachment;filename="' . $name . '(' . date('Ymd-His') . ').xlsx"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
} elseif ($type == 'PDF') {
$rendererName = \PHPExcel_Settings::PDF_RENDERER_TCPDF;
$rendererLibrary = 'tcPDF';
$rendererLibraryPath = __DIR__ . '/extensions/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');
}
header('Content-Type: application/pdf;charset=UTF-8');
header('Content-Disposition: attachment;filename="' . $name . '(' . date('Ymd-His') . ').pdf"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('php://output');
} else {
die;
}
}
