PHP基于xlswriter支持无限表头层级Excel导出

本章介绍基于PHP扩展xlswriter的Vtiful\Kernel\Excel类可以支持无限层级的复杂表头导出！
废了九牛二虎之力，终于把这个功能类写完了…后续会持续更新优化

准备xlswriter扩展
windows系统：
到PECL网站下载符合自己本地PHP环境的ddl文件下载地址，并复制到PHP的扩展目录ext文件夹下，修改php.ini文件，加上这行

 

extension=xlswriter

打开phpinfo()验证扩展是否安装成功

Linux系统：
 

pecl install xlswriter

 php配置文件添加
 

extension = xlswriter.so

重启php nginx

composer下载phpoffice/phpexcel

因为有用到单元格相关函数，所以需要执行下列命令

composer require phpoffice/phpexcel 1.8

封装导出类文件（重点来了）

• 支持多层表头
• 支持多Sheet
• 支持过滤选项
• 支持单元格格式
• 支持单元格公式
• 支持表头加粗
• 支持表头斜体
• 支持冻结表头
• 支持插入图片
• 支持表头居中
• 支持锁定保护
• 支持数据合并
• 支持数据背景颜色

<?php
use PHPExcel_Cell;

class MultiFloorXlsWriterService
{
    // 默认宽度
    private $defaultWidth = 16;
    // 默认高度
    private $defaultHeight = 15;
    // 默认导出格式
    private $exportType = '.xlsx';
    // 表头最大层级
    private $maxHeight  = 1;
    // 文件名
    private $fileName = null;
    // 默认公式行距离数据间隔
    private $defaultFormulaTop = 2;
    // 数据占用截至行
    private $maxDataLine = 2;
    // 默认的单元格格式，常规
    private $defaultCellFormat = 'general';
    // 支持的单元格格式，可扩充
    private $allowCellFormat = [
        'general' => \PHPExcel_Style_NumberFormat::FORMAT_GENERAL,
        'text' => \PHPExcel_Style_NumberFormat::FORMAT_TEXT,
    ];
    // 支持的单元列操作-数据合并
    const CELL_ACT_MERGE = 'merge';
    // 支持的单元列操作-背景颜色
    const CELL_ACT_BACKGROUND = 'background';
    // 数据合并开始标识
    const ACT_MERGE_START = 'start';
    // 数据合并结束标识
    const ACT_MERGE_END = 'end';
    private $allowCellActs = [
        self::CELL_ACT_MERGE,
        self::CELL_ACT_BACKGROUND,
    ];
    // 单元格操作集合
    private $cellActs = [];

    private $xlsObj;
    private $fileObject;
    private $format;
    private $boldIStyle;
    private $colManage;
    private $lastColumnCode;

    public function __construct()
    {
        // 文件默认输出地址
        $path = base_path().'/storage/logs';
        $config = [
            'path' => $path
        ];

        $this->xlsObj = (new \Vtiful\Kernel\Excel($config));
    }

    /**
     * 设置文件名
     * @param string $fileName 文件名
     * @param string $sheetName 第一个sheet名
     */
    public function setFileName(string $fileName = '', string $sheetName = 'Sheet1')
    {
        $fileName = empty($fileName) ? (string)time() : $fileName;
        $fileName .= $this->exportType;

        $this->fileName = $fileName;

        $this->fileObject = $this->xlsObj->fileName($fileName, $sheetName);
        $this->format     = (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
    }

    /**
     * 设置表头
     * @param array $header
     * @throws \Exception
     */
    public function setHeader(array $header)
    {
        if (empty($header)) {
            throw new \Exception('表头数据不能为空');
        }

        if (is_null($this->fileName)) {
            self::setFileName(time());
        }

        // 获取单元格合并需要的信息
        $colManage = self::setHeaderNeedManage($header);

        // 完善单元格合并信息
        $this->colManage = self::completeColMerge($colManage);

        // 设置最后单元格标识
        $this->lastColumnCode = self::getColumn(end($this->colManage)['cursorEnd']) . $this->maxHeight;

        // 合并单元格
        self::queryMergeColumn();
    }

    /**
     * 填充文件数据
     * @param array $data
     * @throws \Exception
     */
    public function setData(array $data)
    {
        // 起始行
        $indexRow = $this->maxHeight + 1;
        // 起始列
        $indexCol = 0;
        foreach ($data as $row => $datum) {
            foreach ($datum as $column => $value) {
                // 列值为数组，说明有额外操作
                if (is_array($value)) {
                    $val = $value[0];
                    $act = $value[1];
                    $pos = self::getColumn($indexCol) . $indexRow;
                    // 有效行为
                    $availableActs = array_intersect($this->allowCellActs, array_keys($act));
                    foreach ($availableActs as $availableAct) {
                        switch ($availableAct) {
                            case self::CELL_ACT_MERGE:
                                // 数据合并
                                $this->cellActs[$indexCol][self::CELL_ACT_MERGE][$act[$availableAct]] = $pos;
                                $this->cellActs[$indexCol][self::CELL_ACT_MERGE]['val'] = $val;
                                break;
                            case self::CELL_ACT_BACKGROUND:
                                // 背景颜色
                                $this->cellActs[$indexCol][self::CELL_ACT_BACKGROUND][] = [
                                    'row'    => $row,
                                    'column' => $column,
                                    'color'  => $act[$availableAct],
                                    'val'    => $val
                                ];
                                break;
                            default:
                                throw new \Exception('不支持的单元格操作['. $availableAct .']');
                        }
                    }
                } else {
                    $this->fileObject->insertText($row + $this->maxHeight, $column, $value);
                }
                $indexCol++;
            }
            $indexRow++;
            $indexCol = 0;
        }
        // 执行单元格操作
        self::queryCellActs();
        $this->maxDataLine = $this->maxHeight + count($data);
    }

    /**
     * 添加Sheet
     * @param string $sheetName
     */
    public function addSheet(string $sheetName)
    {
        $this->fileObject->addSheet($sheetName);
    }

    /**
     * 设置公式
     * {start}：数据开始行 {end}：数据结束行
     * col_title：公式标题所在列标识，从0开始
     * title：公式标题
     * col_formula：公式结果所在列标识
     * formula：公式内容
     * @param array $formulas
     * @throws \Exception
     */
    public function setFormula(array $formulas)
    {
        if (empty($formulas)) {
            throw new \Exception('公式格式错误');
        }

        $line = $this->maxDataLine + $this->defaultFormulaTop;

        foreach ($formulas as $formula) {
            if (isset($formula['col_title']) && isset($formula['title'])) {
                $this->fileObject->insertText($line, $formula['col_title'], $formula['title']);
            }

            if (!isset($formula['col_formula']) || !isset($formula['formula']) || empty($formula['formula'])) {
                throw new \Exception('公式格式错误');
            }

            $formula['formula'] = str_ireplace('{start}', $this->maxHeight + 1, $formula['formula']);
            $formula['formula'] = str_ireplace('{end}', $this->maxDataLine, $formula['formula']);

            $this->fileObject->insertFormula($line, $formula['col_formula'], $formula['formula']);
        }
    }

    /**
     * 设置公式行距离数据间隔
     * @param $top
     */
    public function reBuildFormulaTop(int $top)
    {
        $this->defaultFormulaTop = $top;
    }

    /**
     * 插入本地图片
     * @param int $row
     * @param int $column
     * @param string $localImagePath
     * @param float|int $widthScale
     * @param float|int $heightScale
     * @throws \Exception
     */
    public function setImage(int $row, int $column, string $localImagePath, float $widthScale = 1, float $heightScale = 1)
    {
        if (!file_exists($localImagePath)) {
            throw new \Exception("未检测到图片{$localImagePath}");
        }
        $this->fileObject->insertImage($row, $column, $localImagePath, $widthScale, $heightScale);
    }

    /**
     * 冻结表头（需放到setHeader后调用）
     */
    public function setFreezeHeader()
    {
        $this->fileObject->freezePanes($this->maxHeight, 0);
    }

    /**
     * 开启过滤选项（需放到setHeader后调用）
     */
    public function setFilter()
    {
        $this->fileObject->autoFilter("A1:{$this->lastColumnCode}");
    }

    /**
     * 设置表头加粗（需放到setHeader后调用）
     */
    public function setBoldHeader()
    {
        $this->boldIStyle = $this->format->bold()->toResource();
        $this->fileObject->setRow("A1:{$this->lastColumnCode}", $this->defaultHeight, $this->boldIStyle);
    }

    /**
     * 设置表头斜体（需放到setHeader后调用）
     */
    public function setItalicHeader()
    {
        $this->boldIStyle = $this->format->italic()->toResource();
        $this->fileObject->setRow("A1:{$this->lastColumnCode}", $this->defaultHeight, $this->boldIStyle);
    }

	/**
     * 设置表头水平居中对齐（需放到setHeader后调用）
     */
    public function setAlignCenterHeader()
    {
        $this->boldIStyle = $this->format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource();
    }

	/**
     * 文件密码保护
     * @param $password
     */
    public function setFileProtection($password = null)
    {
        $this->fileObject->protection($password);
    }

    /**
     * 保存文件至服务器
     */
    public function output()
    {
        return $this->fileObject->output();
    }

    /**
     * 输出到浏览器
     * @param $filePath
     * @throws \Exception
     */
    public function excelDownload($filePath)
    {
        $fileName = $this->fileName;
        $userBrowser = $_SERVER['HTTP_USER_AGENT'];
        if( preg_match('/MSIE/i', $userBrowser)) {
            $fileName = urlencode($fileName);
        } else {
            $fileName = iconv('UTF-8', 'GBK//IGNORE', $fileName);
        }

        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Disposition: attachment;filename="' . $fileName . '"');
        header('Content-Length: ' . filesize($filePath));
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Cache-Control: max-age=0');
        header('Pragma: public');

        if (ob_get_contents()) {
            ob_clean();
        }

        flush();

        if (copy($filePath, 'php://output') === false) {
            throw new \Exception($filePath. '地址出问题了');
        }

        // 删除本地文件
        @unlink($filePath);

        exit();
    }

    /**
     * 组装单元格合并需要的信息
     * @param $header
     * @param int $cursor
     * @param int $col
     * @param array $colManage
     * @param $parentList
     * @param $parent
     * @throws \Exception
     * @return array
     */
    private function setHeaderNeedManage($header, $col = 1, &$cursor = 0, &$colManage = [], $parent = null, $parentList = [])
    {
        foreach ($header as $head) {
            if (empty($head['title'])) {
                throw new \Exception('表头数据格式有误');
            }

            if (is_null($parent)) {
                // 循环初始化
                $parentList = [];
                $col = 1;
            } else {
                // 递归进入，高度和父级集合通过相同父级条件从已有数组中获取，避免递归增加与实际数据不符
                foreach ($colManage as $value) {
                    if ($value['parent'] == $parent) {
                        $parentList = $value['parentList'];
                        $col = $value['height'];
                        break;
                    }
                }
            }

            // 单元格标识
            $column = $this->getColumn($cursor) . $col;

            // 单元格格式
            $format = $this->allowCellFormat[$this->defaultCellFormat];
            if (!empty($head['format'])) {
                if (!isset($this->allowCellFormat[$head['format']])) {
                    throw new \Exception("不支持的单元格格式{$head['format']}");
                }
                $format = $this->allowCellFormat[$head['format']];
            }

            // 组装单元格需要的各种信息
            $colManage[$column] = [
                'title'      => $head['title'],      // 标题
                'cursor'     => $cursor,             // 游标
                'cursorEnd'  => $cursor,             // 结束游标
                'height'     => $col,                // 高度
                'width'      => $this->defaultWidth, // 宽度
                'format'     => $format,             // 单元格格式
                'mergeStart' => $column,             // 合并开始标识
                'hMergeEnd'  => $column,             // 横向合并结束标识
                'zMergeEnd'  => $column,             // 纵向合并结束标识
                'parent'     => $parent,             // 父级标识
                'parentList' => $parentList,         // 父级集合
            ];

            if (!empty($head['children']) && is_array($head['children'])) {
                // 有下级，高度加一
                $col += 1;
                // 当前标识加入父级集合
                $parentList[] = $column;

                $this->setHeaderNeedManage($head['children'], $col, $cursor,$colManage, $column, $parentList);
            } else {
                // 没有下级，游标加一
                $cursor += 1;
            }
        }

        return $colManage;
    }

    /**
     * 完善单元格合并信息
     * @param $colManage
     * @return array
     */
    private function completeColMerge($colManage)
    {
        $this->maxHeight = max(array_column($colManage, 'height'));
        $parentManage    = array_column($colManage, 'parent');

        foreach ($colManage as $index => $value) {
            // 设置横向合并结束范围：存在父级集合，把所有父级的横向合并结束范围设置为当前单元格
            if (!is_null($value['parent']) && !empty($value['parentList'])) {
                foreach ($value['parentList'] as $parent) {
                    $colManage[$parent]['hMergeEnd'] = self::getColumn($value['cursor']) . $colManage[$parent]['height'];
                    $colManage[$parent]['cursorEnd'] = $value['cursor'];
                }
            }

            // 设置纵向合并结束范围：当前高度小于最大高度 且 不存在以当前单元格标识作为父级的项
            $checkChildren = array_search($index, $parentManage);
            if ($value['height'] < $this->maxHeight && !$checkChildren) {
                $colManage[$index]['zMergeEnd'] = self::getColumn($value['cursor']) . $this->maxHeight;
            }
        }

        return $colManage;
    }

    /**
     * 合并单元格
     */
    private function queryMergeColumn()
    {
        foreach ($this->colManage as $value) {
            $this->fileObject->mergeCells("{$value['mergeStart']}:{$value['zMergeEnd']}", $value['title']);
            $this->fileObject->mergeCells("{$value['mergeStart']}:{$value['hMergeEnd']}", $value['title']);

            // 设置单元格需要的宽度
            if ($value['cursor'] != $value['cursorEnd']) {
                $value['width'] = ($value['cursorEnd'] - $value['cursor'] + 1) * $this->defaultWidth;
            }

            // 设置单元格格式
            $formatCell =  (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
            $boldStyle  = $formatCell->number($value['format'])->toResource();

            // 设置列单元格样式
            $toColumnStart = self::getColumn($value['cursor']);
            $toColumnEnd   = self::getColumn($value['cursorEnd']);
            $this->fileObject->setColumn("{$toColumnStart}:{$toColumnEnd}", $value['width'], $boldStyle);
        }
    }

    /**
     * 执行单元格操作
     */
    private function queryCellActs()
    {
        if (!empty($this->cellActs)) {
            foreach ($this->cellActs as $actNote) {
                $tmpActStyle = (new \Vtiful\Kernel\Format($this->fileObject->getHandle()));
                // 背景颜色
                if (isset($actNote[self::CELL_ACT_BACKGROUND])) {
                    foreach ($actNote[self::CELL_ACT_BACKGROUND] as $item) {
                        // 支持颜色常量
                        $tmpActStyle->background($this->backgroundConst($item['color']));
                        $this->fileObject->insertText($item['row'] + $this->maxHeight, $item['column'], $item['val'], '', $tmpActStyle->toResource());
                    }
                }
                // 数据合并
                if (isset($actNote[self::CELL_ACT_MERGE])) {
                    if (!empty($actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_START]) && !empty($actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_END])) {
                        // 合并样式：水平左对齐，垂直居中对齐
                        $tmpActStyle->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_LEFT, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER);
                        $this->fileObject->mergeCells(
                            "{$actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_START]}:{$actNote[self::CELL_ACT_MERGE][self::ACT_MERGE_END]}",
                            $actNote[self::CELL_ACT_MERGE]['val'],
                            $tmpActStyle->toResource()
                        );
                    }
                }
            }
            $this->cellActs = [];
        }
    }

   /**
     * 颜色常量转换
     * @param $color
     * @return mixed
     */
    private function backgroundConst($color)
    {
        $const = [
            'black'   => \Vtiful\Kernel\Format::COLOR_BLACK,   // 黑色
            'blue'    => \Vtiful\Kernel\Format::COLOR_BLUE,    // 蓝色
            'brown'   => \Vtiful\Kernel\Format::COLOR_BROWN,   // 棕色
            'cyan'    => \Vtiful\Kernel\Format::COLOR_CYAN,    // 青色
            'gray'    => \Vtiful\Kernel\Format::COLOR_GRAY,    // 灰色
            'green'   => \Vtiful\Kernel\Format::COLOR_GREEN,   // 绿色
            'lime'    => \Vtiful\Kernel\Format::COLOR_LIME,    // 石灰
            'magenta' => \Vtiful\Kernel\Format::COLOR_MAGENTA, // 洋红
            'navy'    => \Vtiful\Kernel\Format::COLOR_NAVY,    // 深蓝
            'orange'  => \Vtiful\Kernel\Format::COLOR_ORANGE,  // 橙色
            'pink'    => \Vtiful\Kernel\Format::COLOR_PINK,    // 粉红
            'purple'  => \Vtiful\Kernel\Format::COLOR_PURPLE,  // 紫色
            'red'     => \Vtiful\Kernel\Format::COLOR_RED,     // 红色
            'silver'  => \Vtiful\Kernel\Format::COLOR_SILVER,  // 银色
            'white'   => \Vtiful\Kernel\Format::COLOR_WHITE,   // 白色
            'yellow'  => \Vtiful\Kernel\Format::COLOR_YELLOW,  // 黄色
        ];

        return $const[$color] ?? $color;
    }

    /**
     * 获取单元格列标识
     * @param $num
     * @return string
     */
    private function getColumn($num)
    {
        return PHPExcel_Cell::stringFromColumnIndex($num);
    }

}

使用示例

header头规则 title表示列标题，children表示子列，没有子列children可不写或为空

$header = [
            [
                'title' => '一级表头1',
                'children' => [
                    [
                        'title' => '二级表头1',
                    ],
                    [
                        'title' => '二级表头2',
                    ],
                    [
                        'title' => '二级表头3',
                    ],
                ]
            ],
            [
                'title' => '公式测试',
            ],
            [
                'title' => '一级表头3',
                'children' => [
                    [
                        'title' => '二级表头1',
                        'children' => [
                            [
                                'title' => '三级表头1',
                            ],
                            [
                                'title' => '三级表头2',
                            ],
                        ]
                    ],
                    [
                        'title' => '公式测试',
                    ],
                    [
                        'title' => '二级表头3',
                        'children' => [
                            [
                                'title' => '三级表头1',
                                'children' => [
                                    [
                                        'title' => '四级表头1',
                                        'children' => [
                                            [
                                                'title' => '五级表头1',
                                            ],
                                            [
                                                'title' => '五级表头2',
                                            ]
                                        ]
                                    ],
                                    [
                                        'title' => '四级表头2'
                                    ]
                                ]
                            ],
                            [
                                'title' => '三级表头2',
                            ],
                        ]
                    ]
                ]
            ],
            [
                'title' => '一级表头4',
                'format' => 'text',
            ],
            [
                'title' => '一级表头5',
                'format' => 'text',
            ],
        ];

        $data = [];
        for ($i = 0; $i < 35; $i++) {
            // 数据合并
            $merge = '这是第'. $i .'行测试';
            if ($i == 10) {
                $merge = [
                    '数据合并测试',
                    ['merge' => 'start']
                ];
            }
            if ($i == 30) {
                $merge = [
                    '数据合并测试',
                    ['merge' => 'end']
                ];
            }

            // 数据合并+背景颜色
            $megBack = '这是第'. $i .'行测试';
            if ($i == 0) {
                $megBack = [
                    '数据合并+背景颜色测试',
                    ['merge' => 'start']
                ];
            }
            if ($i == 20) {
                $megBack = [
                    '数据合并+背景颜色',
                    ['merge' => 'end', 'background' => 'brown']
                ];
            }

            // 单个单元格背景颜色
            $testBack = '这是第'. $i .'行测试';
            if ($i == 10) {
                $testBack = [
                    '单行单列背景颜色测试',
                    ['background' => 'orange']
                ];
            }

            // RGB十六进制颜色
            $rgbColor = '这是第'. $i .'行测试';
            if ($i == 15) {
                $rgbColor = [
                    'RGB十六进制颜色测试',
                    ['background' => 0xFDB8]
                ];
            }

            $data[] = [
                $merge,
                '这是第'. $i .'行测试',
                $megBack,
                rand(1, 99),
                $testBack,
                $rgbColor,
                rand(1, 10),
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
            ];
        }

        // 公式测试，计算列的总和、平均
        $formulas_test = [
            [
                'col_title' => 2,
                'title' => '总计',
                'col_formula' => 3,
                'formula' => '=SUM(D{start}:D{end})'
            ],
            [
                'col_title' => 5,
                'title' => '平均',
                'col_formula' => 6,
                'formula' => '=AVERAGE(G{start}:G{end})'
            ],
        ];
        // 公式测试，计算列相乘
        $formulas_test_two = [
            [
                'col_title' => 5,
                'title' => '总计',
                'col_formula' => 6,
                'formula' => '=SUM(G{start}:G{end})'
            ]
        ];

        $data_two = [];
        for ($i = 0; $i < 30; $i++) {
            // 数据合并
            $merge = '这是第'. $i .'行测试';
            if ($i == 5) {
                $merge = [
                    '数据合并测试',
                    ['merge' => 'start']
                ];
            }
            if ($i == 10) {
                $merge = [
                    '数据合并测试',
                    ['merge' => 'end']
                ];
            }

            // 数据合并+背景颜色
            $megBack = '这是第'. $i .'行测试';
            if ($i == 0) {
                $megBack = [
                    '数据合并+背景颜色测试',
                    ['merge' => 'start']
                ];
            }
            if ($i == 10) {
                $megBack = [
                    '数据合并+背景颜色',
                    ['merge' => 'end', 'background' => 'brown']
                ];
            }

            // RGB十六进制颜色
            $rgbColor = '这是第'. $i .'行测试';
            if ($i == 15) {
                $rgbColor = [
                    'RGB十六进制颜色测试2',
                    ['background' => 0xFDB8]
                ];
            }

            $data_two[] = [
                $merge,
                '这是第'. $i .'行测试',
                $megBack,
                'test',
                'test2',
                $rgbColor,
                'test',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                $merge,
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                $megBack,
            ];
        }

        $data_three = [];
        for ($i = 0; $i < 100; $i++) {
            $data_three[] = [
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
                '这是第'. $i .'行测试',
            ];
        }

        try {
            $fileName = '很厉害的文件导出类';
            $xlsWriterServer = new MultiFloorXlsWriterService();
            $xlsWriterServer->setFileName($fileName, '这是Sheet1别名');
            $xlsWriterServer->setHeader($header);
            $xlsWriterServer->setBoldHeader(); // 设置表头加粗
            $xlsWriterServer->setItalicHeader(); // 设置表头斜体
            $xlsWriterServer->setFilter(); // 表头开启过滤选项
            $xlsWriterServer->setFreezeHeader(); // 冻结表头
            $xlsWriterServer->setAlignCenterHeader(); // 设置表头水平居中
            $xlsWriterServer->setFileProtection('testpwd'); // 设置文件解除锁定保护密码
            $xlsWriterServer->setData($data);

            $xlsWriterServer->setFormula($formulas_test); // 设置公式
            $xlsWriterServer->reBuildFormulaTop(3);  // 设置公式行距离数据行的间隔（默认2），这里使第二个公式数组在第一个公式下面
            $xlsWriterServer->setFormula($formulas_test_two);

            $xlsWriterServer->addSheet('这是Sheet2别名');
            $xlsWriterServer->setHeader($header); //这里可以使用新的header
            $xlsWriterServer->setFreezeHeader(); // 冻结表头
            $xlsWriterServer->setData($data_two); // 这里也可以根据新的header定义数据格式

            $xlsWriterServer->addSheet('这是Sheet3别名');
            $xlsWriterServer->setHeader($header); //这里可以使用新的header
            $xlsWriterServer->setFreezeHeader(); // 冻结表头
            $xlsWriterServer->setData($data_three); // 这里也可以根据新的header定义数据格式

            $filePath = $xlsWriterServer->output(); // 保存到服务器
            $xlsWriterServer->excelDownload($filePath); // 输出到浏览器
        } catch (\Exception $e) {
            exit($e->getMessage());
        }

导出效果图：

原：PHP基于xlswriter支持无限表头层级Excel导出_php xlswriter_叶先生i的博客-CSDN博客