【黄啊码】PHP配合xlswriter实现无限表头层级Excel导出
【摘要】
本文介绍基于PHP扩展xlswriter的Vtiful\Kernel\Excel类可以支持无限层级的复杂表头导出！后续也可能会持续更新优化
一、准备xlswriter扩展
1、windows系统：
到PECL网站下载符合自己本地PHP环境的ddl文件下载地址：https://pecl.php.net/package/xlswrit...
本文介绍基于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); // 输出到浏览器
导出效果图：
文章来源: markwcm.blog.csdn.net，作者：黄啊码，版权归原作者所有，如需转载，请联系作者。
原文链接：markwcm.blog.csdn.net/article/details/124706484
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）