PHP 基于XLSXWriter的多级表头Excel导出

最近公司业务有一些需要导出多级表头的业务需求，百度之后，没有很好的应用demo，然后接触了XLSXWriter，就想着一劳永逸，写个工具类，示例只有三级，但是不止于三级

应用举例

比如说我们需要这样一个表头

1.XLSXWriter应用分析

根据XLSXWriter工具类的示例

我们需要插入三行数据作为表头

然后再合并掉对应的单元格

2.工具类编写

调试的注释啥的，都在里面，有兴趣的自己可以看一下。需要放在和xlsxwriter.class.php同一目录下使用

<?php
/**
 * Created By
 * 模块说明:
 * User: helloc
 * Datetime: 2020/1/17 11:07
 */
require_once  __DIR__.DIRECTORY_SEPARATOR.'xlsxwriter.class.php';

class XLSXWriterMultiHeaderTool
{
    private $headerData =[];

    private $headerWidthSize = 0;
    private $headerHeightSize = 0;


    private $colDefalutFormat = 'string';// 列默认数据格式
    private $colDefalutWidth = '10';// 列默认宽度

    // 设置表头需要要参数 从根节点中取
    //$writer->writeSheetHeader($sheet1, $header, $col_options = ['suppress_row'=>true,'widths'=>$headerWidth] )
    private $header = [];
    private $headerWidth =[];

    // 数据字段
    private $dataFields = [];

    // 标题行默认样式
    private $headerDefalutStyle = [
        'halign'=>'center',//水平居中
        'valign'=>'center',//竖直居中
        'font-style'=>'bold',
        'font-size'=>12,
        'border'=>'top,bottom,left,right'
        //'fill'=>'#fff'// 背景色
        // 边框有点问题不能用
        // 高度也不行 用font-size撑开
    ];
    // 标题行处理数组
    /**
    $headerRow = array(
    array('content' =>['Merge Cells Example','','','',''],'style'=>[['halign'=>'center'],[],[],[],[]]),
    array('content' =>['姓名', '吃饭', '', '考勤', ''],'style'=>[['halign'=>'center','valign'=>'center'],['halign'=>'center'],[],['halign'=>'center'],[]]),
    array('content' =>['', '上午', '下午', '上午', '下午'],'style'=>[[],['halign'=>'center'],[],[],[]]),
    );
     */
    private $headerRow =[
        0=>['content'=>[],'style'=>[]],
    ];
    private $headerMargin =[];

    // 数据行
    private $dataRow = [

    ];
    // 现在够用了，先不做了
    private $dataStyle = [];



    public function __construct()
    {
    }

    public function __get($name)
    {
        if (isset($this->$name)) {
            return $this->$name;
        } else {
            return null;
        }
        // TODO: Implement __get() method.
    }

    /**
     * 设置表头
     * @param array $head
     */
    public function setHeader($header = [])
    {
        $this->headerData = $header;
        // 设置表头尺寸
        $this->setHeaderSize($this->headerData);
        // 生成rows 和 margin 设置样式
        $this->dealHeader($this->headerData);
    }


    public function setData($data = []){
        foreach ($data as $d){
            $row = [];
            foreach ($this->dataFields as $field){
                $value = isset($d[$field])?$d[$field]:'';
                $row[] = $value;
            }
            $this->dataRow[] = $row;
        }
//        dump($this->dataFields);
//        dump($this->dataRow);
        dump($data);exit;
//        exit;
    }


    /**
     * 导出
     * @param string $filename
     * @param string $sheet
     */
    public function writeToStdOut($filename='test',$sheet='Sheet1'){
        //$header = ['字段','标题','宽度','类型']
        $filename .= '.xls';
        $writer = new \XLSXWriter();
        $writer->writeSheetHeader($sheet, $this->header, $col_options = ['suppress_row'=>true,'widths'=>$this->headerWidth] );
        foreach($this->headerRow as $row)
//            $row['style']['height']=30;
            $writer->writeSheetRow($sheet, $row['content'],$row['style']);
        foreach ($this->headerMargin as $margin){
            $writer->markMergedCell($sheet, $margin['startRow'], $margin['startCol'], $margin['endRow'], $margin['endCol']);
        }
        foreach($this->dataRow as $data)
            $writer->writeSheetRow($sheet, $data);
        header('Content-disposition: attachment; filename="' . \XLSXWriter::sanitize_filename($filename));
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        $writer->writeToStdOut();
    }


    /**
     *  生成rows 和 margin 设置样式
     * @param $headerData
     */
    private function dealHeader($headerData){
//        $widthSize = $this->headerWidthSize;
//        $heightSize = $this->headerHeightSize;
//        echo "宽度：{$widthSize},高度：{$heightSize}";
//        $headerData = [
//            [
//                'title' => 'test1',
//                'marginCol' => '1',
//                'style'=>[
//                    'halign'=>'left','valign'=>'center'
//                ]
//            ],
//            [
//                'title' => 'test2',
//                'marginCol' => '3',
//                'children'=>[
//                    [
//                        'title'=>'上午',
//                        'field'=>'workam'
//                    ],
//                    [
//                        'title'=>'上午',
//                        'field'=>'workpm'
//                    ],
//                ]
//            ],
//            [
//                'title' => 'test3',
//                'marginCol' => '1',
//            ],
//
//        ];

        $this->recursionSetHeaderRowsAndMargin($headerData,0,0,$this->headerDefalutStyle);
//        dump($this->headerMargin);
//        dump($this->headerRow);exit;
//        exit;
    }

    /**
     * 递归设置表头行数据，和合并表格数据源
     * @param $headerData
     * @param $row
     * @param $startCol
     * // 换个思路，填坑赋值
     */
    private function recursionSetHeaderRowsAndMargin($headerData,$row,$col,$defStyle=[]) {
//        $widthSize = $this->headerWidthSize;
        $heightSize = $this->headerHeightSize;

        $startRow = $row;
        $startCol = $col;
//        echo "开始行{$startRow},开始列{$startCol}<br />";


//        $writer->markMergedCell($sheet1, $start_row=0, $start_col=0, $end_row=0, $end_col=4);
//        $writer->markMergedCell($sheet1, $start_row=1, $start_col=0, $end_row=2, $end_col=0);// 姓名
//        $writer->markMergedCell($sheet1, $start_row=1, $start_col=1, $end_row=1, $end_col=2);// 吃饭
//        $writer->markMergedCell($sheet1, $start_row=1, $start_col=3, $end_row=1, $end_col=4);// 考勤
        $content = [];//标题
        $style = [];//样式

        foreach ($headerData as $single){
//            dump($single);exit;
            $marginCol = isset($single['marginCol'])?$single['marginCol']:1;
            // 合并
            // 是否有子节点
            $hasChildren = (isset($single['children']) && !empty($single['children'])) ? true:false;
            // 有子节点合并一行，没有子节点直接把剩下行都算上
            if($hasChildren){
                $marginRow = 1;
            }else{
                // 一共3行 当前第1行需要合并1，2两行
                $marginRow =$heightSize-$startRow;
            }
//            if($startRow ==1 && $startCol ==0){
//            }


            $style = isset($single['style'])?array_merge($defStyle,$single['style']):$defStyle;
            // 如果不是合并一行一列就加入合并单元格的数组,同时每个合并的单元格都加样式(主要是方便边框)
            if($marginCol !=1 || $marginRow != 1){
                //0+2-1=1 合并了0，1两个单元格 end是1
                $endCol = $startCol+$marginCol-1;
                // 当前第1行需要合并2两行,目标单元格行号1+2-1
                $endRow = $startRow+$marginRow-1;
//                if($endRow == -1){
//                    dump($marginRow);
//                    dump($startRow);exit;
//                }
                $this->headerMargin[] = [
                    'startRow'=>$startRow,
                    'startCol'=>$startCol,
                    'endRow'=>$endRow,
                    'endCol'=>$endCol
                ];

                if(isset($style['border'])){
                    $s = ['border' => $style['border']];
                    for ($r = 0;$r<$marginRow;$r++){
                        $row = $startRow+ $r;
                        for($c = 0;$c<$marginCol;$c++){
                            $col = $startCol+$c;
                            $this->setHeaderRowCell($row,$col,'style',$s);
                        }
                    }
                }

            }
            $this->setHeaderRowCell($startRow,$startCol,'style',$style);


            $content = isset($single['title'])?$single['title']:'';
            $this->setHeaderRowCell($startRow,$startCol,'content',$content);




            //处理子节点
            if($hasChildren){
                $this->recursionSetHeaderRowsAndMargin($single['children'],$startRow+1,$startCol,$style);
            }
            $startCol +=$marginCol;


//            $marginRow = isset($single['marginCol'])?$single['marginCol']:1;
//            $startRow = $row;
//            $startCol = $col;
//            dump($single);exit;
        }
//        dump($content);
//        dump($style);exit;
//        $this->headerRow[$row]['content'] =$content;
//        $this->headerRow[$row]['style'] = $style;


    }

    //
    //
    /**
     * 设置表头单元格尺寸
     * 同事设置列的数据类型和宽度
     * @param $header
     */
    private function setHeaderSize(&$headerData)
    {

        list($w, $h) = $this->recursionCalSize($headerData);
        $this->headerWidthSize = $w;
        $this->headerHeightSize = $h;
        $this->setHeaderRow($w,$h);


    }

    /**
     * 初始化表头行，占坑
     * @param $w
     * @param $h
     */
    private function setHeaderRow($w,$h){
        $data = [];
        $content = [];
        $style = [];
        while (true){
            $w--;
            $content[] = '';
            $style[] =[];
            if($w<=0) break;
        }

        while (true){
            $h--;
            $data[] = ['content'=>$content,'style'=>$style];

            if($h<=0)
                break;
        }

        $this->headerRow = $data;
        unset($content);
        unset($style);
        unset($data);

    }

    /**
     * 修改表头行的数据
     * @param $col
     * @param $row
     * @param $filed
     * @param $value
     */
    private function setHeaderRowCell($row,$col,$key,$value){

        if(isset($this->headerRow[$row][$key][$col])){
            $this->headerRow[$row][$key][$col] = $value;
        }else{
            return false;
        }
    }
    /**
     *
     * @param $headerData
     * @return array
     */
    private function recursionCalSize(&$headerData)
    {
        $w = 0; // 根节点++
        $h = 0; // 是子类高度的最大值
        $childHeightArr = [];
        foreach ($headerData as &$single) {
            $singleH = 1;
            // 没有子节点设置为空数组
//            $children = (isset($single['children']) && !empty($single['children'])) ? $single['children'] : [];

//            if ($children) {
            if((isset($single['children']) && !empty($single['children']))){
                list($cw, $ch) = $this->recursionCalSize($single['children'], $h);
//                dump($h);
//                dump($ch);
                $w += $cw;
                $singleH += $ch;
                $single['marginCol'] = $cw;
//                echo $single['title'].":".$h;

            } else {

                //设置表头需要要参数
                //字段类型
                $type = isset($single['format'])?$single['format']:$this->colDefalutFormat;
                // 字段宽度
                $width = isset($single['width'])?$single['width']:$this->colDefalutWidth;
                $this->header[] = $type;
                $this->headerWidth[] = $width;
                // data的键
                $field = isset($single['field'])?$single['field']:'';
                $this->dataFields[] = $field;

                $w ++;
                $singleH = 1;
            }
            $childHeightArr[] = $singleH;
//            $childWithArr[] = $singleW;

//            if ($single['title'] == '总表头') {
//                dump($childHeightArr);
//            }
        }
//        $w = array_sum($childWithArr);
        $h = max($childHeightArr);
        return [$w, $h];
    }
}

3.使用示例

<?php
/**
 * 表头配置为一个数组
 * 根节点参数
 * title  必填，表头的内容
 * field  根节点列的内容要显示的data字段，默认为空[则这一列不显示数据]
 * width  根节点的列宽,默认为10
 * format 根节点的列在excel中的显示类型，默认为string，其他的我没有测试，因为我实际的应用过程中，String就够用了，如果要使用其他类型，请参照xlsxwriter示例自行修改测试
 *      format和width的默认值可以再tool里面修改
 *      colDefalutFormat = 'string';// 列默认数据格式
 *      colDefalutWidth = '10';// 列默认宽度
 *
 * 一般节点的参数
 * title  必填，表头的内容
 * style=> 应用的样式，会应用到根节点
 *      默样式可以修改headerDefalutStyle的配置
 * children 必填,子节点
 */
$headerConfig = [
    [
        'title' => '总表头',
        'style'=> ['fill'=>'#33ccff'],
        'children' => [
            ['title' => '姓名', 'field' => 'name','format'=>'string','width'=>'40'],//根节点
//                    ['title' => '姓名', 'field' => 'name','width'=>'40'],//根节点
            // 一般节点
            [
                'title' => '吃饭',
                'children' => [
                    ['title' => '上午', 'field' => 'eatam'],
                    ['title' => '上午', 'field' => 'eatpm', 'width' => '40'],
                ]
            ],
            [
                'title' => '工作',
                'children' => [
                    ['title' => '上午', 'field' => 'workam'],
                    ['title' => '下午', 'field' => 'workpm'],
                ]
            ],
            [
                'title' => '喝水',
                'children' => [
                    ['title' => '上午','format'=>'string','width'=>'40'],
                    ['title' => '下午'],
                ]
            ],
        ]
    ]
];
$data = [
  ['name' => '张三', 'eatam' => '8:23', 'workam' => '9:23', 'workpm' => '9:23'],
['name' => '李四', 'eatam' => '8:24', 'workam' => '9:24', 'workpm' => '9:24'],
];

include_once("../extend/PHP_XLSXWriter-master/XLSXWriterMultiHeaderTool.php");
$tool = new \XLSXWriterMultiHeaderTool();
$tool->setHeader($headerConfig);
$tool->setData($data);
$filename = date('_YmdHis');
$tool->writeToStdOut($filename);

4.效果

因为data里面没有对应的字段，或者节点设置的时候没有指定的field，所以有几列是空的，表头的颜色是因为写了style的fill属性