本章介绍基于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
