php如何导出复杂列的excel,复杂表头之前的搞过,但是下面的列如何合并单元格呀?
//前台处理
document.getElementById("a_export").onclick=function(){
$("#LoadingPark").show();
document.getElementById("does").value="exportPartyCarClassAnalyse";
document.frmAdd.action = cmdurl;
document.frmAdd.submit();
$("#LoadingPark").hide();
return false;
}
//后台处理
//引用类
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
//接收数据
$line1Total = $_POST['line1Total'];
$line1ShengJi = $_POST['line1ShengJi'];
$line1JiYao = $_POST['line1JiYao'];
$line1YingGi = $_POST['line1YingGi'];
$line1TeZhong = $_POST['line1TeZhong'];
$line1YeWu = $_POST['line1YeWu'];
$line1Other = $_POST['line1Other'];
$line1Remark = $_POST['line1Remark'];
$line2Total = $_POST['line2Total'];
$line2ShengJi = $_POST['line2ShengJi'];
$line2JiYao = $_POST['line2JiYao'];
$line2YingGi = $_POST['line2YingGi'];
$line2TeZhong = $_POST['line2TeZhong'];
$line2YeWu = $_POST['line2YeWu'];
$line2Other = $_POST['line2Other'];
$line2Remark = $_POST['line2Remark'];
$line3Total = $_POST['line3Total'];
$line3ShengJi = $_POST['line3ShengJi'];
$line3JiYao = $_POST['line3JiYao'];
$line3YingGi = $_POST['line3YingGi'];
$line3TeZhong = $_POST['line3TeZhong'];
$line3YeWu = $_POST['line3YeWu'];
$line3Other = $_POST['line3Other'];
$line3Remark = $_POST['line3Remark'];
$line4Total = $_POST['line4Total'];
$line4ShengJi = $_POST['line4ShengJi'];
$line4JiYao = $_POST['line4JiYao'];
$line4YingGi = $_POST['line4YingGi'];
$line4TeZhong = $_POST['line4TeZhong'];
$line4YeWu = $_POST['line4YeWu'];
$line4Other = $_POST['line4Other'];
$line4Remark = $_POST['line4Remark'];
$line5Total = $_POST['line5Total'];
$line5ShengJi = $_POST['line5ShengJi'];
$line5JiYao = $_POST['line5JiYao'];
$line5YingGi = $_POST['line5YingGi'];
$line5TeZhong = $_POST['line5TeZhong'];
$line5YeWu = $_POST['line5YeWu'];
$line5Other = $_POST['line5Other'];
$line5Remark = $_POST['line5Remark'];
$line6Total = $_POST['line6Total'];
$line6ShengJi = $_POST['line6ShengJi'];
$line6JiYao = $_POST['line6JiYao'];
$line6YingGi = $_POST['line6YingGi'];
$line6TeZhong = $_POST['line6TeZhong'];
$line6YeWu = $_POST['line6YeWu'];
$line6Other = $_POST['line6Other'];
$line6Remark = $_POST['line6Remark'];
$line7Total = $_POST['line7Total'];
$line7ShengJi = $_POST['line7ShengJi'];
$line7JiYao = $_POST['line7JiYao'];
$line7YingGi = $_POST['line7YingGi'];
$line7TeZhong = $_POST['line7TeZhong'];
$line7YeWu = $_POST['line7YeWu'];
$line7Other = $_POST['line7Other'];
$line7Remark = $_POST['line7Remark'];
$line8Total = $_POST['line8Total'];
$line8ShengJi = $_POST['line8ShengJi'];
$line8JiYao = $_POST['line8JiYao'];
$line8YingGi = $_POST['line8YingGi'];
$line8TeZhong = $_POST['line8TeZhong'];
$line8YeWu = $_POST['line8YeWu'];
$line8Other = $_POST['line8Other'];
$line8Remark = $_POST['line8Remark'];
$line9Total = $_POST['line9Total'];
$line9ShengJi = $_POST['line9ShengJi'];
$line9JiYao = $_POST['line9JiYao'];
$line9YingGi = $_POST['line9YingGi'];
$line9TeZhong = $_POST['line9TeZhong'];
$line9YeWu = $_POST['line9YeWu'];
$line9Other = $_POST['line9Other'];
$line9Remark = $_POST['line9Remark'];
$line10Total = $_POST['line10Total'];
$line10ShengJi = $_POST['line10ShengJi'];
$line10JiYao = $_POST['line10JiYao'];
$line10YingGi = $_POST['line10YingGi'];
$line10TeZhong = $_POST['line10TeZhong'];
$line10YeWu = $_POST['line10YeWu'];
$line10Other = $_POST['line10Other'];
$line10Remark = $_POST['line10Remark'];
$line11Total = $_POST['line11Total'];
$line11ShengJi = $_POST['line11ShengJi'];
$line11JiYao = $_POST['line11JiYao'];
$line11YingGi = $_POST['line11YingGi'];
$line11TeZhong = $_POST['line11TeZhong'];
$line11YeWu = $_POST['line11YeWu'];
$line11Other = $_POST['line11Other'];
$line11Remark = $_POST['line11Remark'];
$line12Total = $_POST['line12Total'];
$line12ShengJi = $_POST['line12ShengJi'];
$line12JiYao = $_POST['line12JiYao'];
$line12YingGi = $_POST['line12YingGi'];
$line12TeZhong = $_POST['line12TeZhong'];
$line12YeWu = $_POST['line12YeWu'];
$line12Other = $_POST['line12Other'];
$line12Remark = $_POST['line12Remark'];
$line13Total = $_POST['line13Total'];
$line13ShengJi = $_POST['line13ShengJi'];
$line13JiYao = $_POST['line13JiYao'];
$line13YingGi = $_POST['line13YingGi'];
$line13TeZhong = $_POST['line13TeZhong'];
$line13YeWu = $_POST['line13YeWu'];
$line13Other = $_POST['line13Other'];
$line13Remark = $_POST['line13Remark'];
$line14Total = $_POST['line14Total'];
$line14ShengJi = $_POST['line14ShengJi'];
$line14JiYao = $_POST['line14JiYao'];
$line14YingGi = $_POST['line14YingGi'];
$line14TeZhong = $_POST['line14TeZhong'];
$line14YeWu = $_POST['line14YeWu'];
$line14Other = $_POST['line14Other'];
$line14Remark = $_POST['line14Remark'];
$line15Total = $_POST['line15Total'];
$line15ShengJi = $_POST['line15ShengJi'];
$line15JiYao = $_POST['line15JiYao'];
$line15YingGi = $_POST['line15YingGi'];
$line15TeZhong = $_POST['line15TeZhong'];
$line15YeWu = $_POST['line15YeWu'];
$line15Other = $_POST['line15Other'];
$line15Remark = $_POST['line15Remark'];
$line16Total = $_POST['line16Total'];
$line16ShengJi = $_POST['line16ShengJi'];
$line16JiYao = $_POST['line16JiYao'];
$line16YingGi = $_POST['line16YingGi'];
$line16TeZhong = $_POST['line16TeZhong'];
$line16YeWu = $_POST['line16YeWu'];
$line16Other = $_POST['line16Other'];
$line16Remark = $_POST['line16Remark'];
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Huang")
->setLastModifiedBy("Huang")
->setTitle("数据EXCEL导出")
->setSubject("数据EXCEL导出")
->setDescription("备份数据")
->setKeywords("excel")
->setCategory("result file");
$objPHPExcel->createSheet();
$objectSheet=$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle("党政机关公务用车情况分类汇总表");
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置表头
$objectSheet->setCellValue('A1', '党政机关公务用车情况分类汇总表');
$objectSheet->mergeCells("A" . 1 .':'."J" . 1);
$objectSheet->setCellValue('A2', '统计项目');
$objectSheet->mergeCells("A" . 2 .':'."B" . 2);
$objectSheet->setCellValue('A3', '公务用车编制数');
$objectSheet->mergeCells("A" . 3 .':'."B" . 3);
$objectSheet->setCellValue('C2', '合计');
$objectSheet->setCellValue('D2', '省部级干部用车');
$objectSheet->setCellValue('E2', '机要通信用车');
$objectSheet->setCellValue('F2', '应急保障用车');
$objectSheet->setCellValue('G2', '特种专业技术用车');
$objectSheet->setCellValue('H2', '业务用车');
$objectSheet->setCellValue('I2', '其他公务用车');
$objectSheet->setCellValue('J2', '备注');
$objectSheet->setCellValue('A4', '配备车型情况 (辆)');
$objectSheet->mergeCells("A" . 4 .':'."A" . 11);
$objectSheet->setCellValue('B4', '合计');
$objectSheet->setCellValue('B5', '其中:新能源汽车');
$objectSheet->setCellValue('B6', '轿车');
$objectSheet->setCellValue('B7', '其他小型客车');
$objectSheet->setCellValue('B8', '其中:越野车');
$objectSheet->setCellValue('B9', '中型客车');
$objectSheet->setCellValue('B10', '大型客车');
$objectSheet->setCellValue('B11', '其他车型');
$objectSheet->setCellValue('A12', '全年运行费用支出情况 (万元)');
$objectSheet->mergeCells("A" . 12 .':'."A" . 18);
$objectSheet->setCellValue('B12', '合计');
$objectSheet->setCellValue('B13', '燃油费');
$objectSheet->setCellValue('B14', '充电费');
$objectSheet->setCellValue('B15', '保险费');
$objectSheet->setCellValue('B16', '维修保养费');
$objectSheet->setCellValue('B17', '过路过桥费');
$objectSheet->setCellValue('B18', '其他费用');
//放入数据
$objectSheet->setCellValue('C' . 3, $line1Total);
$objectSheet->setCellValue('D' . 3, $line1ShengJi);
$objectSheet->setCellValue('E' . 3, $line1JiYao);
$objectSheet->setCellValue('F' . 3, $line1YingGi);
$objectSheet->setCellValue('G' . 3, $line1TeZhong);
$objectSheet->setCellValue('H' . 3, $line1YeWu);
$objectSheet->setCellValue('I' . 3, $line1Other);
$objectSheet->setCellValue('J' . 3, $line1Remark);
$objectSheet->setCellValue('C' . 4, $line2Total);
$objectSheet->setCellValue('D' . 4, $line2ShengJi);
$objectSheet->setCellValue('E' . 4, $line2JiYao);
$objectSheet->setCellValue('F' . 4, $line2YingGi);
$objectSheet->setCellValue('G' . 4, $line2TeZhong);
$objectSheet->setCellValue('H' . 4, $line2YeWu);
$objectSheet->setCellValue('I' . 4, $line2Other);
$objectSheet->setCellValue('J' . 4, $line2Remark);
$objectSheet->setCellValue('C' . 5, $line3Total);
$objectSheet->setCellValue('D' . 5, $line3ShengJi);
$objectSheet->setCellValue('E' . 5, $line3JiYao);
$objectSheet->setCellValue('F' . 5, $line3YingGi);
$objectSheet->setCellValue('G' . 5, $line3TeZhong);
$objectSheet->setCellValue('H' . 5, $line3YeWu);
$objectSheet->setCellValue('I' . 5, $line3Other);
$objectSheet->setCellValue('J' . 5, $line3Remark);
$objectSheet->setCellValue('C' . 6, $line4Total);
$objectSheet->setCellValue('D' . 6, $line4ShengJi);
$objectSheet->setCellValue('E' . 6, $line4JiYao);
$objectSheet->setCellValue('F' . 6, $line4YingGi);
$objectSheet->setCellValue('G' . 6, $line4TeZhong);
$objectSheet->setCellValue('H' . 6, $line4YeWu);
$objectSheet->setCellValue('I' . 6, $line4Other);
$objectSheet->setCellValue('J' . 6, $line4Remark);
$objectSheet->setCellValue('C' . 7, $line5Total);
$objectSheet->setCellValue('D' . 7, $line5ShengJi);
$objectSheet->setCellValue('E' . 7, $line5JiYao);
$objectSheet->setCellValue('F' . 7, $line5YingGi);
$objectSheet->setCellValue('G' . 7, $line5TeZhong);
$objectSheet->setCellValue('H' . 7, $line5YeWu);
$objectSheet->setCellValue('I' . 7, $line5Other);
$objectSheet->setCellValue('J' . 7, $line5Remark);
$objectSheet->setCellValue('C' . 8, $line6Total);
$objectSheet->setCellValue('D' . 8, $line6ShengJi);
$objectSheet->setCellValue('E' . 8, $line6JiYao);
$objectSheet->setCellValue('F' . 8, $line6YingGi);
$objectSheet->setCellValue('G' . 8, $line6TeZhong);
$objectSheet->setCellValue('H' . 8, $line6YeWu);
$objectSheet->setCellValue('I' . 8, $line6Other);
$objectSheet->setCellValue('J' . 8, $line6Remark);
$objectSheet->setCellValue('C' . 9, $line7Total);
$objectSheet->setCellValue('D' . 9, $line7ShengJi);
$objectSheet->setCellValue('E' . 9, $line7JiYao);
$objectSheet->setCellValue('F' . 9, $line7YingGi);
$objectSheet->setCellValue('G' . 9, $line7TeZhong);
$objectSheet->setCellValue('H' . 9, $line7YeWu);
$objectSheet->setCellValue('I' . 9, $line7Other);
$objectSheet->setCellValue('J' . 9, $line7Remark);
$objectSheet->setCellValue('C' . 10, $line8Total);
$objectSheet->setCellValue('D' . 10, $line8ShengJi);
$objectSheet->setCellValue('E' . 10, $line8JiYao);
$objectSheet->setCellValue('F' . 10, $line8YingGi);
$objectSheet->setCellValue('G' . 10, $line8TeZhong);
$objectSheet->setCellValue('H' . 10, $line8YeWu);
$objectSheet->setCellValue('I' . 10, $line8Other);
$objectSheet->setCellValue('J' . 10, $line8Remark);
$objectSheet->setCellValue('C' . 11, $line9Total);
$objectSheet->setCellValue('D' . 11, $line9ShengJi);
$objectSheet->setCellValue('E' . 11, $line9JiYao);
$objectSheet->setCellValue('F' . 11, $line9YingGi);
$objectSheet->setCellValue('G' . 11, $line9TeZhong);
$objectSheet->setCellValue('H' . 11, $line9YeWu);
$objectSheet->setCellValue('I' . 11, $line9Other);
$objectSheet->setCellValue('J' . 11, $line9Remark);
$objectSheet->setCellValue('C' . 12, $line10Total);
$objectSheet->setCellValue('D' . 12, $line10ShengJi);
$objectSheet->setCellValue('E' . 12, $line10JiYao);
$objectSheet->setCellValue('F' . 12, $line10YingGi);
$objectSheet->setCellValue('G' . 12, $line10TeZhong);
$objectSheet->setCellValue('H' . 12, $line10YeWu);
$objectSheet->setCellValue('I' . 12, $line10Other);
$objectSheet->setCellValue('J' . 12, $line10Remark);
$objectSheet->setCellValue('C' . 13, $line11Total);
$objectSheet->setCellValue('D' . 13, $line11ShengJi);
$objectSheet->setCellValue('E' . 13, $line11JiYao);
$objectSheet->setCellValue('F' . 13, $line11YingGi);
$objectSheet->setCellValue('G' . 13, $line11TeZhong);
$objectSheet->setCellValue('H' . 13, $line11YeWu);
$objectSheet->setCellValue('I' . 13, $line11Other);
$objectSheet->setCellValue('J' . 13, $line11Remark);
$objectSheet->setCellValue('C' . 14, $line12Total);
$objectSheet->setCellValue('D' . 14, $line12ShengJi);
$objectSheet->setCellValue('E' . 14, $line12JiYao);
$objectSheet->setCellValue('F' . 14, $line12YingGi);
$objectSheet->setCellValue('G' . 14, $line12TeZhong);
$objectSheet->setCellValue('H' . 14, $line12YeWu);
$objectSheet->setCellValue('I' . 14, $line12Other);
$objectSheet->setCellValue('J' . 14, $line12Remark);
$objectSheet->setCellValue('C' . 15, $line13Total);
$objectSheet->setCellValue('D' . 15, $line13ShengJi);
$objectSheet->setCellValue('E' . 15, $line13JiYao);
$objectSheet->setCellValue('F' . 15, $line13YingGi);
$objectSheet->setCellValue('G' . 15, $line13TeZhong);
$objectSheet->setCellValue('H' . 15, $line13YeWu);
$objectSheet->setCellValue('I' . 15, $line13Other);
$objectSheet->setCellValue('J' . 15, $line13Remark);
$objectSheet->setCellValue('C' . 16, $line14Total);
$objectSheet->setCellValue('D' . 16, $line14ShengJi);
$objectSheet->setCellValue('E' . 16, $line14JiYao);
$objectSheet->setCellValue('F' . 16, $line14YingGi);
$objectSheet->setCellValue('G' . 16, $line14TeZhong);
$objectSheet->setCellValue('H' . 16, $line14YeWu);
$objectSheet->setCellValue('I' . 16, $line14Other);
$objectSheet->setCellValue('J' . 16, $line14Remark);
$objectSheet->setCellValue('C' . 17, $line15Total);
$objectSheet->setCellValue('D' . 17, $line15ShengJi);
$objectSheet->setCellValue('E' . 17, $line15JiYao);
$objectSheet->setCellValue('F' . 17, $line15YingGi);
$objectSheet->setCellValue('G' . 17, $line15TeZhong);
$objectSheet->setCellValue('H' . 17, $line15YeWu);
$objectSheet->setCellValue('I' . 17, $line15Other);
$objectSheet->setCellValue('J' . 17, $line15Remark);
$objectSheet->setCellValue('C' . 18, $line16Total);
$objectSheet->setCellValue('D' . 18, $line16ShengJi);
$objectSheet->setCellValue('E' . 18, $line16JiYao);
$objectSheet->setCellValue('F' . 18, $line16YingGi);
$objectSheet->setCellValue('G' . 18, $line16TeZhong);
$objectSheet->setCellValue('H' . 18, $line16YeWu);
$objectSheet->setCellValue('I' . 18, $line16Other);
$objectSheet->setCellValue('J' . 18, $line16Remark);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(22);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(22);
//清除缓冲区,避免乱码
ob_end_clean();
$file_name="党政机关公务用车情况分类汇总表.xls";
$ua = $_SERVER['HTTP_USER_AGENT'];
if(preg_match('/MSIE/',$ua)) {
$file_name = str_replace('+','%20',urlencode($file_name));
}
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename='.$file_name);
header('Cache-Control:max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save( 'php://output');
exit;
###
可以了解下xlswriter
这个扩展
xlswriter是一个 PHP C 扩展,可用于在 Excel 2007+ XLSX 文件中读取数据,插入多个工作表,写入文本、数字、公式、日期、图表、图片和超链接。
###如果 excel 结构是固定的话,就只是数据会变化,可以先做一个没有数据的 excel 文件模板,每次导出时载入这个空数据 excel 文件模板,然后就是填充数据导出了,这样就不用费劲去组织表格结构了。。。