aoding9 / laravel-xlswriter-export
laravel导出excel(基于xlswriter)
Requires
- php: >=7.1.0
- ext-xlswriter: *
- laravel/framework: ~5.5|~6.0|~7.0|~8.0|~9.0|~10.0
- viest/php-ext-xlswriter-ide-helper: dev-master
README
laravel扩展:xlswriter导出
之前用了laravel-excel做数据导出,很耗内存速度也慢,数据量大的时候内存占用容易达到php上限,或者响应超时,换成xlswriter这个扩展来做。
由于直接导出的表格不太美观,经常需要进行合并单元格和自定义表格样式等操作,我对此进行了一些封装,使用更加方便直观。
导出时间和内存占用情况
以下测试使用了扩展中的DemoAoding9\Laravel\Xlswriter\Export\Demo\AreaExport
导出areas地区表,有4列,使用分页查询,包括了数据查询的时间。
chunkSize=2000,导出1万条
chunkSize=50000 导出50万条
效果示例
导出类简单示例
自定义组装数据集合(2种方法)
复杂合并及指定单元格样式
安装
首先根据xlswriter文档安装扩展,windows可以下载对应php版本的dll文件,linux可以源码编译安装,或者pecl安装
官方文档:https://xlswriter-docs.viest.me/
修改php.ini后,在phpinfo中确认是否安装成功,然后进行下一步
composer require aoding9/laravel-xlswriter-export
若国内composer镜像安装失败,请设置官方源
composer config repo.packagist composer https://packagist.org
官方源下载慢,国内镜像偶尔出问题可能导致安装失败,也可以把以下代码添加到composer.json,直接从github安装
如果无法访问github,可以将url改为gitee:https://gitee.com/aoding9/laravel-xlswriter-export
{ "repositories": [ { "type": "vcs", "url": "https://github.com/aoding9/laravel-xlswriter-export" } ] }
配置
在导出类中定义BaseExport的相关属性进行配置,或者在make之后调用相关属性的set方法
使用
1.定义导出类
简单导出
使用预定义的格式进行导出,最少只需定义表头和数据到列的关联,即可导出一个比较美观的表格。
以用户导出为例,首先创建一个UserExport导出类,继承Aoding9\Laravel\Xlswriter\Export\BaseExport
基类,一般放在app\Exports目录下
$header
中,column是列名,按abcd顺序排列,仅作为标识不参与实际导出,列很多时方便一眼看出列名,防止写错位,觉得麻烦不写也可以,width是列宽,name是填充的表头文本。
若要合并表头,需定义最细分的列以指明每一列的宽度,合并列在另外的方法中去处理。
/** @var \App\Models\User $row */
告诉编辑器$row可能是User模型,输入$row->
弹出模型的属性提示,需要配合barryvdh/laravel-ide-helper
扩展生成_ide_helper_models.php
文件,方便开发,可用可不用
<?php namespace Aoding9\Laravel\Xlswriter\Export\Demo; use Aoding9\Laravel\Xlswriter\Export\BaseExport; class UserExport extends BaseExport { public $header = [ ['column' => 'a', 'width' => 8, 'name' => '序号'], ['column' => 'b', 'width' => 8, 'name' => 'id'], ['column' => 'c', 'width' => 10, 'name' => '姓名'], ['column' => 'd', 'width' => 10, 'name' => '性别'], ['column' => 'e', 'width' => 20, 'name' => '注册时间'], ]; public $fileName = '用户导出表'; // 导出的文件名 public $tableTitle = '用户导出表'; // 第一行标题 // 将模型字段与表头关联 public function eachRow($row) { /** @var \App\Models\User $row */ return [ $this->index, $row->id, \Faker\Factory::create('zh_CN')->name, random_int(0, 1) ? '男' : '女', $row->created_at->toDateTimeString(), ]; } }
使用自定义的数组或集合
如果不希望使用查询构造器获取数据,比如从接口获取数据,有2种方式使用自己定义的数据集合。
注意: 如果数据是普通数组或集合,而非ORM模型集合,那么eachRow中不能直接用
$row->id
获取数据,应该使用$row['id']
方式1、将集合或数组传给构造函数,弊端是需要传入全部数据,无法分块;好处是写法简单,数据在外部定义,适合数据量小的导出
$data = [ ['id' => 1, 'name' => '小白', 'created_at' => now()->toDateString()], ['id' => 2, 'name' => '小红', 'created_at' => now()->toDateString()], ]; // $data = User::get()->toArray(); \Aoding9\Laravel\Xlswriter\Export\Demo\UserExportFromCollection::make($data)->export(); \Aoding9\Laravel\Xlswriter\Export\Demo\AreaExportFromCollection::make(\App\Models\Area::query()->limit(500000)->get())->export();
不使用分页获取,直接导50万条数据的集合,因为要一次保存全部数据,所以内存占用极高
方式2、构造函数传参留空,在导出类中重写buildData方法,分页返回集合,适合数据量大的情况
\Aoding9\Laravel\Xlswriter\Export\Demo\UserExportFromCollection::make()->export();
<?php namespace Aoding9\Laravel\Xlswriter\Export\Demo; use Aoding9\Laravel\Xlswriter\Export\BaseExport; class UserExportFromCollection extends BaseExport { public $header = [ ['column' => 'a', 'width' => 8, 'name' => '序号'], ['column' => 'b', 'width' => 8, 'name' => 'id'], ['column' => 'c', 'width' => 10, 'name' => '姓名'], ['column' => 'd', 'width' => 10, 'name' => '性别'], ['column' => 'e', 'width' => 20, 'name' => '注册时间'], ]; public $fileName = '用户导出表'; // 导出的文件名 public $tableTitle = '用户导出表'; // 第一行标题 // 将模型字段与表头关联 public function eachRow($row) { return [ $this->index, $row['id'], $row['name'], random_int(0, 1) ? '男' : '女', $row['created_at'], ]; } // 方法2 可以分块获取数据 public function buildData(?int $page = null, ?int $perPage = null) { return collect([ ['id' => 1, 'name' => '小白', 'created_at' => now()->toDateString()], ['id' => 2, 'name' => '小红', 'created_at' => now()->toDateString()], ]); } }
复杂合并单元格,指定单元格样式
在每个分块插入之前,每行的数据会被绑定一个index值,在每行插入后,会回调afterInsertEachRowInEachChunk()
,在其中可以使用getCurrentLine
获取当前行数,使用
getRowByIndex()
获取分块中index对应的rowData
setHeaderData()
设置表头数据,重写可修改预定义的表头、标题等
$this->excel
是xlswriter的Excel实例,可以使用$this->excel->mergeCells
合并单元格,此时可以指定自定义样式,样式设置方法请参考官方文档。
afterInsertData()
是所有数据插入完成后的回调,默认在其中调用了mergeCellsAfterInsertData
方法,合并标题,合并表头,或者对整个表格进行最后修改。可以用于整个的纵向合并,如A1:A100
,还可以设置打印纸张大小方向,设置文档密码保护等,参考xlswriter文档即可。
insertCellHandle()
是插入单元格数据的处理方法,重写后可实现设置特定单元格的样式,或者对特定单元格插入公式、图片等
getCellName()
可以根据传入的行数和列数,返回单元格名称,配合insertCellHandle,可判断当前写入的单元格
<?php namespace Aoding9\Laravel\Xlswriter\Export\Demo; use Aoding9\Laravel\Xlswriter\Export\BaseExport; use Illuminate\Support\Carbon; use Vtiful\Kernel\Format; class UserMergeExport extends BaseExport { public $header = [ ['column' => 'a', 'width' => 10, 'name' => '序号'], ['column' => 'b', 'width' => 10, 'name' => 'id'], ['column' => 'c', 'width' => 10, 'name' => '姓名'], ['column' => 'd', 'width' => 10, 'name' => '性别'], ['column' => 'e', 'width' => 20, 'name' => '注册时间'], ]; public function getGender() { return random_int(0, 1) ? '男' : '女'; } // 处理每行的模型,使其对应到表头 public function eachRow($row) { return [ $this->index, // 自增序号,绑定在模型中 $row->id, \Faker\Factory::create('zh_CN')->name, $this->getGender(), $row->created_at, ]; } public $fileName = '用户导出表'; // 导出的文件名 public $tableTitle = '用户导出表'; // 第一行标题 public $useFreezePanes = false; // 是否冻结表头 public $fontFamily = '宋体'; public $rowHeight = 30; // 行高 public $titleRowHeight = 40; // 首行大标题行高 public $headerRowHeight = 50; // 表头行高 public $useGlobalStyle=false; // 是否用全局默认样式代替列默认样式(为ture时,数据末尾行下方没有边框,但是速度会慢一点点) /** * @Desc 在分块数据插入每行后回调(到下一个分块,则上一分块被销毁) * @param $row */ public function afterInsertEachRowInEachChunk($row) { // 奇数行进行合并,且不合并到有效数据行之外 if ($this->index % 2 === 1 && $this->getCurrentLine() < $this->completed + $this->startDataRow) { // 定义纵向合并范围,范围形如"B1:B2" $range1 = "B" . $this->getCurrentLine() . ":B" . ($this->getCurrentLine() + 1); $nextRow = $this->getRowInChunkByIndex($this->index + 1); $ids = $row->id . '---' . ($nextRow ? $nextRow->id : null); // mergeCells(范围, 数据, 样式) ,通过第三个参数可以设置合并单元格的字体颜色等 $this->excel->mergeCells($range1, $ids, $this->getSpecialStyle()); // 横向合并,形如"C3:D3" $range2 = "C" . $this->getCurrentLine() . ":D" . $this->getCurrentLine(); $nameAndGender = $row->name . "---" . $this->getGender(); $this->excel->mergeCells($range2, $nameAndGender); } } public function setHeaderData() { parent::setHeaderData(); // 把表头放到第三行,第二行留空用于合并 $this->headerData->put(2, $this->headerData->get(1)); $this->headerData->put(1, []); return $this; } /** * @Desc 插入数据完成后进行合并 * @return array[] */ public function mergeCellsAfterInsertData() { // range是合并范围,$this->end是末尾的列名字母,formatHandle指定合并单元格的样式 return [ ['range' => "A1:{$this->end}1", 'value' => $this->getTableTitle(), 'formatHandle' => $this->titleStyle], ['range' => "A2:A3", 'value' => '序号', 'formatHandle' => $this->getSpecialStyle()], ['range' => "B2:B3", 'value' => 'id', 'formatHandle' => $this->headerStyle], ['range' => "C2:E2", 'value' => '基本资料', 'formatHandle' => $this->getSpecialStyle()], ]; } public $specialStyle; /** * 定义个特别的表格样式 * @return resource */ public function getSpecialStyle() { return $this->specialStyle ?: $this->specialStyle = (new Format($this->fileHandle)) ->background(Format::COLOR_YELLOW) ->fontSize(10) ->border(Format::BORDER_THIN) ->italic() ->font('微软雅黑') ->align(Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER) ->wrap() ->toResource(); } // public $specialStyle2; // public function getSpecialStyle2() {} /** * @Desc 重写插入单元格数据的处理方法,可单独设置某个单元格的样式 * @param int $currentLine 单元格行数 * @param int $column 单元格列数 * @param mixed $data 插入的数据 * @param string|null $format 数据格式化 * @param resource|null $formatHandle 表格样式 * @return \Vtiful\Kernel\Excel */ public function insertCellHandle($currentLine, $column, $data, $format, $formatHandle) { // if($this->getCellName($currentLine,$column)==='A4'){ ... } // 根据单元格名称判断 // 筛选出E列,且日期秒数为偶数的单元格 if ($this->getColumn($column) === 'E' && $data instanceof Carbon) { if ($data->second % 2 === 0) { // 设置为上面定义好的样式(黄色背景,斜体,微软雅黑,水平垂直居中等) $formatHandle = $this->getSpecialStyle(); } $data = $data->toDateTimeString(); } return $this->excel->insertText($currentLine, $column, $data, $format, $formatHandle); } }
2、在控制器中使用
public function exportModels() { // 定义查询构造器,设置查询条件,如果有关联关系,使用with预加载以优化查询 $query=\App\Models\User::query(); // 将查询构造器传入构造函数,然后调用export即可触发下载 \Aoding9\Laravel\Xlswriter\Export\Demo\UserExport::make($query)->export(); // 合并单元格的demo \Aoding9\Laravel\Xlswriter\Export\Demo\UserMergeExport::make($query)->export(); // 用数据集合或数组 // 方式1:如果给构造函数传数组或集合,必须把数据全部传入 $data = [ ['id' => 1, 'name' => '小白', 'created_at' => now()->toDateString()], ['id' => 2, 'name' => '小红', 'created_at' => now()->toDateString()], ]; // $data = \App\Models\User::get()->toArray(); \Aoding9\Laravel\Xlswriter\Export\Demo\UserExportFromCollection::make($data)->export(); // 方式2:无需传参给构造函数,但需要重写buildData方法,分块返回数据 \Aoding9\Laravel\Xlswriter\Export\Demo\UserExportByCollection::make()->export(); // 地区导出的demo // 用于调试模式查看运行耗时,包含数据查询耗费的时间 $time =microtime(true); // 用查询构造器 $query=\App\Models\Area::where('parent_code',0); // 查父级为0的地区,即查省份 \Aoding9\Laravel\Xlswriter\Export\Demo\AreaExport::make($query,$time)->export(); // 用数组或集合 // 数据量大时占用很高,需要修改内存上限,不推荐 ini_set('memory_limit', '2048M'); set_time_limit(0); $data =\App\Models\Area::query()->limit(500000)->get(); \Aoding9\Laravel\Xlswriter\Export\Demo\AreaExportFromCollection::make($data,$time)->export(); }
3、通过swoole使用
由于swoole中不能调用exit()
方法,需要在控制器中直接return下载响应
为此,需要在导出类中将$useSwoole
属性设为true,然后在控制器中return导出类的export()返回值
// UserExport public $useSwoole = true; // UserController return UserExport::make()->export();
其他
合并单元格的范围请使用大写字母,小写字母会报错。
如果eachRow中需要调用关联模型,请使用with预加载以优化查询。
仓库中包含几个导出类的demo以供参考
方法属性补充介绍
$max
设置最大导出数据量,默认50万
useFreezePanes()
是否启用表格冻结功能
freezePanes()
设置表格冻结的行列
getColumn()
传入列数得到对应字母
getColumnIndexByName()
根据字母列名得到列数
store()
保存到文件,export里面主要是这个方法
shouldDelete()
设置下载后是否删除文件
export()
导出一条龙,保存文件->下载->下载后删除文件
$startDataRow
数据开始的行数
$currentLine
当前插入数据行,第一行为0,excel显示的行数需要再此基础上+1
getCurrentLine()
返回$currentLine+1
$index
数据行的序号,不包括表头
getIndex()
返回$index
getRowInChunkByIndex()
根据序号获取rowData,分块时会被销毁
$chunkData
分块数据
$max和setMax()
设置最大导出数据量
setUseTitle()
是否使用标题行(插入第一行的合并标题)
$chunkSize和setChunkSize()
设置每个分块的数据量
$debug和setDebug()
用dump()输出每个分块导出后的内存占用和耗费时间
$completed
已插入的数据量,用来计算导出的进度
$dataSourceType
根据构造函数的第一个参数,设置数据源类型,传入查询构造器Builder则为query类型,传入数组和集合为collection类型,其他情况为other类型
initDataSource()
初始化数据源,重写以扩展自己的类型
buildData()
根据数据源类型,执行对应的方法获取数据
buildDataFromQuery() buildDataFromCollection() buildDataFromOther()
重写后可以实现你自己的数据获取方法
chunk()
分块处理方法
$this->excel->mergeCells()
合并单元格
$headerLen
表头长度 count($this->getHeader())
$end
获取最后一列的字母 $this->getColumn($this->headerLen - 1)
$useSwoole
是否使用了swoole
更多方法详见BaseExport,注释非常详细
有什么建议或者问题,欢迎留言讨论
版本更新
- v1.2.1 (2023-6-30)
- 数据源新增other类型,非query/array/collection则均为other
- 新增buildDataFromOther,当数据源为other类型时,buildData会调用它,重写以返回自定义数据集合
- 构造函数现在数据源默认为null,即other类型。
- $useGlobalStyle现在默认为true,使用全局默认样式代替列默认样式,效果是数据末尾行之后不再有边框。
- v1.2.2 (2023-9-16)
- download时调用
$this->useSwoole()
判断是否使用了swoole,如果使用了,将返回下载响应,代替默认的exit()
- download时调用