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
• 支持过滤选项
• 支持单元格格式
• 支持单元格公式
• 支持表头加粗
• 支持表头斜体
• 支持冻结表头
• 支持插入图片
• 支持表头居中
• 支持锁定保护
• 支持数据合并
• 支持数据背景颜色

 \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));    }        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()));    }        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();    }        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);    }        public function addSheet(string $sheetName)    {        $this->fileObject->addSheet($sheetName);    }        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']);        }    }        public function reBuildFormulaTop(int $top)    {        $this->defaultFormulaTop = $top;    }        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);    }        public function setFreezeHeader()    {        $this->fileObject->freezePanes($this->maxHeight, 0);    }        public function setFilter()    {        $this->fileObject->autoFilter("A1:{$this->lastColumnCode}");    }        public function setBoldHeader()    {        $this->boldIStyle = $this->format->bold()->toResource();        $this->fileObject->setRow("A1:{$this->lastColumnCode}", $this->defaultHeight, $this->boldIStyle);    }        public function setItalicHeader()    {        $this->boldIStyle = $this->format->italic()->toResource();        $this->fileObject->setRow("A1:{$this->lastColumnCode}", $this->defaultHeight, $this->boldIStyle);    }    public function setAlignCenterHeader()    {        $this->boldIStyle = $this->format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource();    }    public function setFileProtection($password = null)    {        $this->fileObject->protection($password);    }        public function output()    {        return $this->fileObject->output();    }        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();    }        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;    }        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 = [];        }    }       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;    }        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博客

来源地址：https://blog.csdn.net/dreamboycs/article/details/130886639