php 导出复杂表头excel表头格式错乱？

Question

php 导出复杂表头表头格式错乱

$Head = array(
            array('value' => $year."年省直单位申购车辆申请呈批表",'col' => 19,'row' => 3,
                'children' =>
                    array(
                        array('value' => $date,'col' => 4,'row' => 1,
                            'children' =>
                                array(
                                    array('value' => '序号','col' => 1,'row' => 3),
                                    array('value' => '单位','col' => 1,'row' => 3,'width' => 25),
                                    array('value' => '机关有关情况','col' => 6,'row' => 1,
                                        'children' =>
                                            array(
                                                array('value' => '规格','col' => 1,'row' => 2),
                                                array('value' => '性质','col' => 1,'row' => 2),
                                                array('value' => '经费供应渠道','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '领导职数','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '人员编制','col' => 1,'row' => 2,'width' => 25,),
                                                array('value' => '离退休干部数','col' => 1,'row' => 2,'width' => 25)
                                            )
                                    ),
                                    array('value' => '现有车辆情况','col' => 3,'row' => 1,
                                        'children' =>
                                            array(
                                                array('value' => '编制车数','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '实有车数','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '应报废车辆数','col' => 1,'row' => 2,'width' => 25)
                                            )
                                    ),
                                    array('value' => '申购车辆情况','col' => 7,'row' => 1,
                                        'children' =>
                                            array(
                                                array('value' => '厂牌型号','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '种类','col' => 1,'row' => 2),
                                                array('value' => '排量或座位数','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '数量','col' => 1,'row' => 2),
                                                array('value' => '单价','col' => 1,'row' => 2),
                                                array('value' => '经费合计','col' => 1,'row' => 2,'width' => 25),
                                                array('value' => '经费来源','col' => 1,'row' => 2,'width' => 25)
                                            )
                                    ),
                                    array('value' => '备注','col' => 1,'row' => 3)
                                )
                        ),
                        array('value' => '','col' => 13),
                        array('value' => '金额单位：万元','col' => 2),
                )
            )
    );
$arr = array();
$data = array();
while ($row = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)) {
    $data['rownum'] = $row['rownum'];
    $data['companyname'] = $row['companyname'];
    $data['unitspec'] = $row['unitspec'];
    $data['unitprop'] = $row['unitprop'];
    $data['fundspychannels'] = $row['fundspychannels'];
    $data['officepostnum'] = $row['officepostnum'];
    $data['staffnum'] = $row['staffnum'];
    $data['retirednum'] = $row['retirednum'];
    $data['quotacar'] = $row['quotacar'];
    $data['availcar'] = $row['availcar'];
    $data['scrapcar'] = $row['scrapcar'];
    $data['brand'] = $row['brand'];
    $data['cartype'] = $row['cartype'];
    $data['output'] = $row['output'];
    $data['num'] = $row['num'];
    $data['oneprice'] = $row['oneprice'];
    $data['price'] = $row["num"]*$row["oneprice"];
    $data['afundfrom'] = $row['afundfrom'];
    $data['remark'] = $row['remark'];
    array_push($arr,$data);
}

$phpExcelCore = new PHPExcelCore();
$fn = $phpExcelCore::RecursionCreateExecl($Head,$arr);

好奇怪此处出现了几个空格，貌似是$head格式有点问题。

此处一行空白如何去掉?

最后一行加一个合计又如何加上去呢？

这两行怎么放到head头里面?

Answer 1

空格处理：合并单元格的代码需要改一下，网上的东西好多地方有坑的。。

整体的格式需要处理一下head：
$year = date('Y');

$title = array(
    array('value' => $year."年省直单位申购车辆申请呈批表",'col' => 19,'row' => 3,
        'children' =>
        array(
            array('value' => $date,'col' => 4,'row' => 1,
                'children' =>
                    array(
                        array('value' => '序号','col' => 1,'row' => 3),
                        array('value' => '单位','col' => 1,'row' => 3,'width' => 25),
                        array('value' => '机关有关情况','col' => 6,'row' => 1,
                            'children' =>
                                array(
                                    array('value' => '规格','col' => 1,'row' => 2),
                                    array('value' => '性质','col' => 1,'row' => 2),
                                    array('value' => '经费供应渠道','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '领导职数','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '人员编制','col' => 1,'row' => 2,'width' => 25,),
                                    array('value' => '离退休干部数','col' => 1,'row' => 2,'width' => 25)
                                )
                        ),
                        array('value' => '现有车辆情况','col' => 3,'row' => 1,
                            'children' =>
                                array(
                                    array('value' => '编制车数','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '实有车数','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '应报废车辆数','col' => 1,'row' => 2,'width' => 25)
                                )
                        ),
                        array('value' => '申购车辆情况','col' => 7,'row' => 1,
                            'children' =>
                                array(
                                    array('value' => '厂牌型号','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '种类','col' => 1,'row' => 2),
                                    array('value' => '排量或座位数','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '数量','col' => 1,'row' => 2),
                                    array('value' => '单价','col' => 1,'row' => 2),
                                    array('value' => '经费合计','col' => 1,'row' => 2,'width' => 25),
                                    array('value' => '经费来源','col' => 1,'row' => 2,'width' => 25)
                                )
                        ),
                        array('value' => '备注','col' => 1,'row' => 3)
                    )
            ),
            array('value' => '','col' => 13),
            array('value' => '金额单位：万元','col' => 2),
        )
    )
);

对于合计需要在data后面追加一行：

如果哪位大神能把第二行的两条竖线能去掉就更加完美了

Answer 2

简单画了一下表格。梳理一下。

如图所示：第一个椭圆处。好奇怪此处出现了几个空格，貌似是$head格式有点问题。,检查你合并相关表格处单元格列和行是否对应，如图所示，如果你不想要机关有关情况下面的三个单元格，应该 $excel->mergeCells('D2:F2'); 进行合并。

你的第三行看样子是数据插入起始行设置错误，应该设为 3.

最后一个问题合计：中间的空格，进行合并。我的思路是，先计算出你的数据总量：假设为 5 条，表头占 2 行，那么合计就是第 8 行。 $excel->mergeCells('B8:K8');,这样子中间的空格就没有了。

最后的效果

在合并单元格的时候，应该有边框线的设置，也应该要注意下。

建议在导出表格之前，先使用 office 工具手动填充数据，看下效果，这样在合并单元格的时候就会比较明了。

如有不对，请指正。