本文介绍基于PHP扩展xlswriter的Vtiful\Kernel\Excel类可以支持无限层级的复杂表头导出！后续也可能会持续更新优化
![]()
一、准备xlswriter扩展
1、windows系统：
到PECL网站下载符合自己本地PHP环境的ddl文件下载地址：https://pecl.php.net/package/xlswriter，并复制到PHP的扩展目录ext文件夹下，修改php.ini文件，
加上这行
extension=xlswriter2、Linux系统：
使用命令安装
pecl install xlswriterphp配置文件添加
extension = xlswriter.so重启:php nginx 查看PHP安装xlswriter拓展
![]()
二、封装导出类文件（重点来了）
<?php
namespace App\Services;use Vtiful\Kernel\Excel;class MultiFloorXlsWriterService{ // 默认宽度 private $defaultWidth = 16; // 默认导出格式 private $exportType = '.xlsx'; // 表头最大层级 private $maxHeight = 1; // 文件名 private $fileName = null; private $xlsObj; private $fileObject; private $format; public function __construct() { // 文件默认输出地址
$path = base_path().'/public/uploads/excel';
$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, bool $filter = false) { if (empty($header)) { throw new \Exception('表头数据不能为空'); } if (is_null($this->fileName)) {
self::setFileName(time()); } // 获取单元格合并需要的信息
$colManage = self::setHeaderNeedManage($header); // 完善单元格合并信息
$colManage = self::completeColMerge($colManage); // 合并单元格
self::queryMergeColumn($colManage, $filter); } public function setData(array $data) { foreach ($data as $row => $datum) { foreach ($datum as $column => $value) {
$this->fileObject->insertText($row + $this->maxHeight, $column, $value); } } } public function addSheet(string $sheetName) {
$this->fileObject->addSheet($sheetName); } public function output() { return $this->fileObject->output(); } public function excelDownload(string $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(array $header,int $col = 1,int &$cursor = 0,array &$colManage = [], $parent = null,array $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; // 组装单元格需要的各种信息
$colManage[$column] = [ 'title' => $head['title'], // 标题 'cursor' => $cursor, // 游标 'cursorEnd' => $cursor, // 结束游标 'height' => $col, // 高度 'width' => $this->defaultWidth, // 宽度 'mergeStart' => $column, // 合并开始标识 'hMergeEnd' => $column, // 横向合并结束标识 'zMergeEnd' => $column, // 纵向合并结束标识 'parent' => $parent, // 父级标识 'parentList' => $parentList, // 父级集合 ]; if (isset($head['children']) && !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(array $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(array $colManage,bool $filter) { foreach ($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; } // 设置列单元格样式
$toColumnStart = self::getColumn($value['cursor']);
$toColumnEnd = self::getColumn($value['cursorEnd']);
$this->fileObject->setColumn("{$toColumnStart}:{$toColumnEnd}", $value['width']); } // 是否开启过滤选项 if ($filter) { // 获取最后的单元格标识
$filterEndColumn = self::getColumn(end($colManage)['cursorEnd']) . $this->maxHeight;
$this->fileObject->autoFilter("A1:{$filterEndColumn}"); } } private function getColumn(int $num) { return Excel::stringFromColumnIndex($num); }}三、使用示例
代码如下
public function export() {
$header = [ [ 'title' => '一级表头1', 'children' => [ [ 'title' => '二级表头1', ], [ 'title' => '二级表头2', ], [ 'title' => '二级表头3', ], ] ], [ 'title' => '一级表头2' ], [ 'title' => '一级表头3', 'children' => [ [ 'title' => '二级表头1', 'children' => [ [ 'title' => '三级表头1', ], [ 'title' => '三级表头2', ], ] ], [ 'title' => '二级表头2', ], [ 'title' => '二级表头3', 'children' => [ [ 'title' => '三级表头1', 'children' => [ [ 'title' => '四级表头1', 'children' => [ [ 'title' => '五级表头1' ], [ 'title' => '五级表头2' ] ] ], [ 'title' => '四级表头2' ] ] ], [ 'title' => '三级表头2', ], ] ] ] ], [ 'title' => '一级表头4', ], [ 'title' => '一级表头5', ], ];
$data= []; // header头规则 title表示列标题，children表示子列，没有子列children可不写或为空 for ($i = 0; $i < 100; $i++) {
$data[] = [ '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', '这是第'. $i .'行测试', ]; }
$fileName = '很厉害的文件导出类';
$xlsWriterServer = new MultiFloorXlsWriterService();
$xlsWriterServer->setFileName($fileName, '这是Sheet1别名');
$xlsWriterServer->setHeader($header, true);
$xlsWriterServer->setData($data);
$xlsWriterServer->addSheet('这是Sheet2别名');
$xlsWriterServer->setHeader($header); //这里可以使用新的header
$xlsWriterServer->setData($data); // 这里也可以根据新的header定义数据格式
$filePath = $xlsWriterServer->output(); // 保存到服务器
$xlsWriterServer->excelDownload($filePath); // 输出到浏览器 }导出效果
