php如何导出复杂列的excel?

Question

php如何导出复杂列的excel，复杂表头之前的搞过，但是下面的列如何合并单元格呀？

Answer 1

//前台处理
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;

Answer 2

可以了解下xlswriter这个扩展

xlswriter是一个 PHP C 扩展，可用于在 Excel 2007+ XLSX 文件中读取数据，插入多个工作表，写入文本、数字、公式、日期、图表、图片和超链接。

合并单元格

Answer 3

如果 excel 结构是固定的话，就只是数据会变化，可以先做一个没有数据的 excel 文件模板，每次导出时载入这个空数据 excel 文件模板，然后就是填充数据导出了，这样就不用费劲去组织表格结构了。。。