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拓展
二、封装导出类文件（重点来了）<?phpnamespace 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进行大数据的导入导出？（详解）的详细内容。