PHP如何使用xlswriter进行大数据的导入导出?下面本篇文章给大家介绍一下PHP大数据xlswriter导入导出(最优数据化)的方法,希望对大家有所帮助!
本文介绍基于PHP扩展xlswriter的Vtiful/Kernel/Excel类可以支持无限层级的复杂表头导出!后续也可能会持续更新优化
一、准备xlswriter扩展
1、windows系统:
到PECL网站下载符合自己本地PHP环境的ddl文件下载地址:https://pecl.php.net/package/xlswriter,并复制到PHP的扩展目录ext文件夹下,修改php.ini文件,
加上这行
extension=xlswriter
2、Linux系统:
使用命令安装
pecl install xlswriter
php配置文件添加
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; /** * MultiFloorXlsWriterService constructor. * @throws /App/Exceptions/ApiException */ public function __construct() { // 文件默认输出地址 $path = base_path().'/public/uploads/excel'; $config = [ 'path' => $path ]; $this->xlsObj = (new /Vtiful/Kernel/Excel($config)); } /** * 设置文件名 * @param string $fileName * @param string $sheetName * @author LWW */ 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 * @author LWW */ 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 * @author LWW */ 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 * @author LWW */ public function addSheet(string $sheetName) { $this->fileObject->addSheet($sheetName); } /** * 保存文件至服务器 * @return mixed * @author LWW */ public function output() { return $this->fileObject->output(); } /** * 输出到浏览器 * @param string $filePath * @throws /Exception * @author LWW */ 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(); } /** * 组装单元格合并需要的信息 * @param array $header * @param int $col * @param int $cursor * @param array $colManage * @param null $parent * @param array $parentList * @return array * @throws /Exception * @author LWW */ 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; } /** * 完善单元格合并信息 * @param array $colManage * @return mixed * @author LWW */ 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; } /** * 合并单元格 * @param array $colManage * @param bool $filter * @author LWW */ 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}"); } } /** * 获取单元格列标识 * @param int $num * @return string * @author LWW */ private function getColumn(int $num) { return Excel::stringFromColumnIndex($num); } }
三、使用示例
代码如下
/** * 导出测试 * @author LWW */ 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); // 输出到浏览器 }
导出效果
推荐学习:《PHP视频教程》
以上就是PHP如何使用xlswriter进行大数据的导入导出?(详解)的详细内容,更多请关注php中文网其它相关文章!
声明:本文转载于:learnku,如有侵犯,请联系admin@php.cn删除
- 上一篇:PHP8.2将有哪些改动(性能改进、新特性)!
- 下一篇:没有了