我们最近需要对系统加一个报表导出的功能,可以通过POI直接导出,导出后的excel文件需要支持在office里面修改数据后图表也会自动变换。 在phpexcel中我们可以使用模板进行填充,不用对复杂模板进行样式设置,先看看模板吧:
这个一个电商销售SKU的模板,当然了,模板还有表头,表尾,这个只是一部分,我们只需要填充数据,最终生成excel文件即可。
直接上代码吧,表头,表尾略掉,这部分的代码如下:
//合并边框样式
$styleArray = array(
'borders' => array(
'bottom' => array(
'style' => \PHPExcel_Style_Border::BORDER_NONE,
),
'top' => array(
'style' => \PHPExcel_Style_Border::BORDER_NONE,
),
));
$styleArray2 = array(
'borders' => array(
'top' => array(
'style' => \PHPExcel_Style_Border::BORDER_NONE,
),
));
$styleArray3 = array(
'borders' => array(
'bottom' => array(
'style' => \PHPExcel_Style_Border::BORDER_NONE,
),
));
$styleArray4 = array(
'borders' => array(
'top' => array(
'style' => \PHPExcel_Style_Border::BORDER_NONE,
),
'bottom' => array(
'style' => \PHPExcel_Style_Border::BORDER_THIN,
),
));
$styleArray5 = array(
'borders' => array(
'top' => array(
'style' => \PHPExcel_Style_Border::BORDER_THIN,
),
'bottom' => array(
'style' => \PHPExcel_Style_Border::BORDER_NONE,
),
));
$baseRow = 21; //第21行开始插入
$line = 0;
foreach($body as $index=>$dataRow){
if($index == 0) {
$line = $index;
$row= $baseRow + $index + 1; //$row是循环操作行的行号
} else {
$row= $baseRow + $line + 1; //$row是循环操作行的行号
}
$objPHPExcel->getActiveSheet()->insertNewRowBefore($row, 3);
//对应的列都附上数据和编号
$objPHPExcel->getActiveSheet()->getStyle( 'A'.(22+$line))->applyFromArray($styleArray5);
$objPHPExcel->getActiveSheet()->getStyle( 'A'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'A'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->setCellValue( 'A'.(22+$line), $dataRow['sort']."\t");
$objPHPExcel->getActiveSheet()->setCellValue( 'A'.(23+$line), $dataRow['sort']."\t");
$objPHPExcel->getActiveSheet()->setCellValue( 'A'.(24+$line), "\t"."0");
$objPHPExcel->getActiveSheet()->mergeCells( 'B'.(22+$line).':'.'H'.(22+$line));
//行设置样式-商品编码
$objPHPExcel->getActiveSheet()->getStyle( 'B'.(22+$line).':'.'H'.(22+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'B'.(23+$line).':'.'H'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'B'.(24+$line).':'.'H'.(24+$line))->applyFromArray($styleArray2);
$objPHPExcel->getActiveSheet()->setCellValue( 'B'.(22+$line), "\t".$dataRow['hs_code']);
$objPHPExcel->getActiveSheet()->mergeCells( 'B'.(23+$line).':'.'H'.(23+$line));
$objPHPExcel->getActiveSheet()->setCellValue( 'B'.(23+$line), "\t".$dataRow['hs_ciq_code']);
$objPHPExcel->getActiveSheet()->mergeCells( 'B'.(24+$line).':'.'H'.(24+$line));
$objPHPExcel->getActiveSheet()->mergeCells( 'I'.(22+$line).':'.'O'.(22+$line));
$objPHPExcel->getActiveSheet()->setCellValue( 'I'.(22+$line), "\t".$dataRow['goods_name']);
//合并行列
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(8,(23+$line),14,(24+$line));
$objPHPExcel->getActiveSheet()->getStyle('I'.(23+$line))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('I'.(23+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//行设置样式-商品名称及规格型号
$objPHPExcel->getActiveSheet()->getStyle( 'I'.(22+$line).':'.'O'.(22+$line))->applyFromArray($styleArray5);
$objPHPExcel->getActiveSheet()->getStyle( 'I'.(23+$line).':'.'O'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'I'.(24+$line).':'.'O'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->setCellValue( 'I'.(23+$line), $dataRow['goodBrand']);
$objPHPExcel->getActiveSheet()->getRowDimension((23+$line))->setRowHeight(40);
$objPHPExcel->getActiveSheet()->mergeCells( 'P'.(22+$line).':'.'R'.(22+$line));
//行设置样式-数量及单位
$objPHPExcel->getActiveSheet()->getStyle( 'P'.(22+$line).':'.'S'.(22+$line))->applyFromArray($styleArray5);
$objPHPExcel->getActiveSheet()->getStyle( 'P'.(23+$line).':'.'S'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'P'.(24+$line).':'.'S'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->setCellValue( 'P'.(22+$line), $dataRow['num1Total']."\t");
$objPHPExcel->getActiveSheet()->mergeCells( 'P'.(23+$line).':'.'R'.(23+$line));
$objPHPExcel->getActiveSheet()->setCellValue( 'P'.(23+$line), "\t".$dataRow['num2Total']);
$objPHPExcel->getActiveSheet()->mergeCells( 'P'.(24+$line).':'.'R'.(24+$line));
$objPHPExcel->getActiveSheet()->setCellValue( 'P'.(24+$line), "\t".$dataRow['num']);
$objPHPExcel->getActiveSheet()->setCellValue( 'S'.(22+$line), "\t".$dataRow['unit1']);
$objPHPExcel->getActiveSheet()->setCellValue( 'S'.(23+$line), $dataRow['unit2']);
$objPHPExcel->getActiveSheet()->setCellValue( 'S'.(24+$line), $dataRow['unit']);
//行设置样式-空行
$objPHPExcel->getActiveSheet()->getStyle( 'T'.(22+$line).':'.'Z'.(22+$line))->applyFromArray($styleArray5);
$objPHPExcel->getActiveSheet()->getStyle( 'T'.(23+$line).':'.'Z'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'T'.(24+$line).':'.'Z'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->getStyle('AA'.(22+$line).':'.'AS'.(22+$line))->applyFromArray($styleArray5);
$objPHPExcel->getActiveSheet()->getStyle('AA'.(23+$line).':'.'AS'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle('AA'.(24+$line).':'.'AS'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->setCellValue( 'AA'.(22+$line), $dataRow['price']."\t");
$objPHPExcel->getActiveSheet()->setCellValue( 'AA'.(23+$line), "\t".$dataRow['totalPrice']);
$objPHPExcel->getActiveSheet()->setCellValue( 'AA'.(24+$line), $head['curruncy']."\t");
//合并行列
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(28,(22+$line),32,(24+$line));
$objPHPExcel->getActiveSheet()->getStyle('AC'.(22+$line))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('AC'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//行设置样式-原产国(地区)
$objPHPExcel->getActiveSheet()->getStyle( 'AC'.(22+$line).':'.'AG'.(22+$line))->applyFromArray($styleArray5);
$objPHPExcel->getActiveSheet()->getStyle( 'AC'.(23+$line).':'.'AG'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'AC'.(24+$line).':'.'AG'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->setCellValue( 'AC'.(22+$line), "\t".$dataRow['country_origin']);
//合并行列
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(33,(22+$line),38,(24+$line));
$objPHPExcel->getActiveSheet()->getStyle('AH'.(22+$line))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('AH'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//行设置样式-最终目的国(地区)
$objPHPExcel->getActiveSheet()->getStyle( 'AH'.(22+$line).':'.'AM'.(22+$line))->applyFromArray($styleArray3);
$objPHPExcel->getActiveSheet()->getStyle( 'AH'.(23+$line).':'.'AM'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'AH'.(24+$line).':'.'AM'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->setCellValue( 'AH'.(22+$line), $dataRow['country_destination']);
//合并行列
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(40,(22+$line),42,(24+$line));
//行设置样式-境内货源地
$objPHPExcel->getActiveSheet()->getStyle( 'AO'.(22+$line).':'.'AQ'.(22+$line))->applyFromArray($styleArray3);
$objPHPExcel->getActiveSheet()->getStyle( 'AO'.(23+$line).':'.'AQ'.(23+$line))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getStyle( 'AO'.(24+$line).':'.'AQ'.(24+$line))->applyFromArray($styleArray4);
$objPHPExcel->getActiveSheet()->getStyle('AO'.(22+$line))->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('AO'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue( 'AO'.(22+$line), $dataRow['district_code']);
//行设置样式-征免
//合并行列
$objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow(44,(22+$line),44,(24+$line));
$objPHPExcel->getActiveSheet()->getStyle('AS'.(22+$line))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->setCellValue( 'AS'.(22+$line), $dataRow['tax_method']);
$line = $line+3;
}
最终生成的模板部分如下: