PHP基于xlswriter支持无限表头层级Excel导出
本文介绍基于PHP扩展xlswriter的Vtiful\Kernel\Excel类可以支持无限层级的复杂表头导出！
一、准备xlswriter扩展
1、windows系统：
到PECL网站下载符合自己本地PHP环境的ddl文件下载地址：https://pecl.php.net/package/xlswriter，并复制到PHP的扩展目录ext文件夹下，修改php.ini文件，
加上这行
extension=xlswriter
打开phpinfo()验证扩展是否安装成功
2、Linux系统：
pecl install xlswriter
php配置文件添加
extension = xlswriter.so
重启php nginx
二、composer下载phpoffice/phpexcel
因为有用到单元格相关函数，所以需要执行下列命令
composer require phpoffice/phpexcel 1.8
三、封装导出类文件（重点来了）
<?php use PHPExcel_Cell; 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().'/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 * @param bool $filter * @throws Exception */ 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); } /** * 填充文件数据 * @param array $data */ public function setData(array $data) { foreach ($data as $row => $datum) { foreach ($datum as $column => $value) { $this->fileObject->insertText($row + $this->maxHeight, $column, $value); } } } /** * 添加Sheet * @param string $sheetName */ public function addSheet(string $sheetName) { $this->fileObject->addSheet($sheetName); } /** * 保存文件至服务器 */ public function output() { return $this->fileObject->output(); } /** * 输出到浏览器 * @param $filePath * @param $fileName * @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; // 组装单元格需要的各种信息 $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; } /** * 完善单元格合并信息 * @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; } /** * 合并单元格 * @param $colManage * @param $filter */ private function queryMergeColumn($colManage, $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}"); } } /** * 获取单元格列标识 * @param $num * @return string */ private function getColumn($num) { return PHPExcel_Cell::stringFromColumnIndex($num); } }
四、使用示例
代码如下：
$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', ], ]; // 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); // 输出到浏览器
导出效果图：
