【php】php laravel 大批量导出 excel

• Z时代
• 2024-01-10
• 分类：技术分享

[2020-09-27 09:21:13] local.ERROR: Allowed memory size of 536870912 bytes exhausted (tried to allocate 8192 bytes) {"userId":1,"exception":"[object] (Symfony\\Component\\Debug\\Exception\\FatalErrorException(code: 1): Allowed memory size of 536870912 bytes exhausted (tried to allocate 8192 bytes) at /Users/****/WebRoot/ValeSite/****/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Concerns/HasAttributes.php:879)

$list = Good::with(['good_standard', 'good_standard.default_picture',  'good_standard.brand'])
......
->selectRaw('goods.*')~~~~
->get();
#内存溢出点 1, 该 orm 返回数据量为 3.5w 行数据
...... ~~~~
$list = $this->goodsRepository->batchGetFullGoodsScope($list);
foreach ($list as $item) {
$cell = [];
.....
//没条数组共 30 个元素
$cellData[] = $cell;
}
# 内存溢出点 2 ，生成需要的数据，3w + 条数据时，内存消耗大概在 110M +
.....
Excel::create(...)
#内存溢出点 3 ， Maatwebsite/Laravel-Excel库大批量生成也会内存溢出
# 和直观的代码处理流，该代码在小数据量时无问题

# 进度条样式，可在 bootstrap 找到
<section class="panel" id="export_loading_box">
<div class="panel-body m-b-10">
<p class="text-muted">
数据导出中 .....
</p>
<div class="progress progress-striped active">
<div class="progress-bar progress-bar-info"
role="progressbar"
aria-valuemin="0"
aria-valuemax="100"
style="width: 0%">
0%
</div>
</div>
</div>
</section>

$(function () {
$('.down-list').click(function () {
let formName = $(this).attr('data-form')
let data = $('#' + formName).serialize();
OE.params.url = $(this).attr('data-url');
OE.handle(data);
});
})
//商品导出 JS 控件
let OE = window.OE || {}
OE = {
params: {
ifRun: 0,
url: '',
cachePre: '',
},
# 1. 前端 ajax 发送 excel导出请求
handle: function (formData) {
if (OE.params.ifRun) {
return false;
}
OE.params.ifRun = 1;
OE.rateShow(100, 0);
$('#export_loading_box').find('.panel-body').show();
$.getJSON(OE.params.url, formData + '&run=1', function (data) {
if (200 == data.status) {
OE.params.cachePre =  data.cachePre;
//请求成功， 渲染进度条
OE.init();
} else {
OE.showAlert(false, data.msg);
}
})
},
# 4. ajax 轮询渲染进度条
init: function () {
let t = setInterval(function () {
$.getJSON(OE.params.url, "get_run=1&cache_pre="+OE.params.cachePre, function (data) {
OE.rateShow(data.total, data.run);
if (200 == data.status && data.total == data.run) {
clearInterval(t);
OE.params.ifRun = 0;
OE.showAlert(true);
//跳转下载 excel
window.location.href = OE.params.url+'?cache_pre='+OE.params.cachePre;
return;
}
});
}, 2000);
},
showAlert: function (success, msg) {
if (success) {
html = '<div class="alert alert-success fade in">' +
'       <button data-dismiss="alert" class="close close-sm" type="button">' +
'           <i class="fa fa-times"></i>' +
'       </button>' +
'       <span>导出成功</span> 数据下载中' +
' </div>';
}
$('#export_loading_box').append(html);
$('#export_loading_box').find('.panel-body').hide();
},
# 进度条计算
rateShow: function (total, run) {
let width = ((run / total) * 100).toFixed(0);
$('#export_loading_box').find('.progress-bar').css('width', width + '%');
$('#export_loading_box').find('.progress-bar').text(width + '% ');
}
}

// 前端第一次请求触发代码实现
$listOrm = self::getGoodOrm();
//求总，初始化 job 数据
$total = $listOrm->count();
for ($page = 0; $page <= ($totalPage - 1); $page++) {
//创建子队列
CreateExportGoodData::dispatch($requestData, $page, $authAdmin->id, $cachePre)
->onQueue(self::JOB_QUEUE);
}

self::$requestData = $requestData;
$listOrm = self::getGoodOrm();
$list = $listOrm->offset($page * self::PAGE_NUM)
->limit(self::PAGE_NUM)
->orderByDesc('goods.id')
->get();
//数据清洗过程
......
//执行次数递增 1
Redis::incr(self::GE_RUN_KEY . $cachePre . ':' . $adminId);
//清洗后的数据压入 redis 列表
Redis::lpush(self::GE_DATA_KEY . $cachePre . ':' . $adminId, serialize($data));

$total = Redis::get(GoodsExportRepository::GE_TOTAL_KEY. $cachePre. ':'. $authAdmin->id);
$run = Redis::get(GoodsExportRepository::GE_RUN_KEY. $cachePre. ':'. $authAdmin->id);
if ($request->input('get_run')) {
//前端 ajax 轮询获取同步已运行队列数
return ['status' => 200, 'total' => $total, 'run' => $run];
}

$fileName = "商品导出" . date('Y-m-d');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
header('Cache-Control: max-age=0');
//开启预输出流
$fp = fopen('php://output', 'a');
//输出商品列表数据
while (true) {
//核心1 从 redis 列表里依次取数据
$data = Redis::rpop(self::GE_DATA_KEY . $cachePre . ':' . $adminId);
if (!$data) {
// redis 列表数据为空，结束 while 循环
break;
}
//核心2
ob_flush(); //取出 $fb 输出流 存入 buffer 内数据
flush();    //直接渲染至 http 数据流至浏览器
$data = unserialize($data);
foreach ($data as $row) {
foreach ($row as $key => $value) {
if (is_string($value)) {
$row[$key] = iconv('utf-8', 'gbk//IGNORE', $value);
}
}
fputcsv($fp, $row);
}
}
fclose($fp);
//必须 exit 阻止框架继续输出
exit();

set_time_limit(0)
//直接输出头部声明
$fileName = "商品导出" . date('Y-m-d');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
header('Cache-Control: max-age=0');
//开启输出流
$fp = fopen('php://output', 'a');
// while 循环取数据
$page = 0;
while (true) {
$listOrm = self::getGoodOrm();
$list = $listOrm->offset($page * self::PAGE_NUM)
->limit(self::PAGE_NUM)
->orderByDesc('goods.id')
->get();
if ($list->isEmpty()) {
//无数据时退出 while 循环
break;
}
//数据清洗
$data = .....
//直接将清洗后的 $data 数据写入输出流
foreach ($data as $row) {
foreach ($row as $key => $value) {
if (is_string($value)) {
$row[$key] = iconv('utf-8', 'gbk//IGNORE', $value);
}
}
fputcsv($fp, $row);
}
//输出至浏览器
ob_flush();
flush();
}
fclose($fp);
exit();

前言

用 php 在 laravel 或其他框架内大批量生成 excel 数据，受 php 的语言特性一直是个难点；

项目后台有导出几 w 条数据生成 excel 的功能，刚好前同事的方法直接报内存溢出错误，
所以将这次的优化过程发布出来，希望对有需要的同学启到一定的帮助

先看优化后效果：

1. 异步生成数据且真实进度条反馈进度

2.进度完成，前端 js 跳转到下载地址

3.生成 csv 文件代替 excel ， 3.5w 条数据文件大小 8M

原代码报错信息

[2020-09-27 09:21:13] local.ERROR: Allowed memory size of 536870912 bytes exhausted (tried to allocate 8192 bytes) {"userId":1,"exception":"[object] (Symfony\\Component\\Debug\\Exception\\FatalErrorException(code: 1): Allowed memory size of 536870912 bytes exhausted (tried to allocate 8192 bytes) at /Users/****/WebRoot/ValeSite/****/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Concerns/HasAttributes.php:879)

原代码逻辑

$list = Good::with(['good_standard', 'good_standard.default_picture',  'good_standard.brand'])
......
->selectRaw('goods.*')~~~~
->get();
#内存溢出点 1, 该 orm 返回数据量为 3.5w 行数据
...... ~~~~
$list = $this->goodsRepository->batchGetFullGoodsScope($list);
foreach ($list as $item) {
$cell = [];
.....
//没条数组共 30 个元素
$cellData[] = $cell;
}
# 内存溢出点 2 ，生成需要的数据，3w + 条数据时，内存消耗大概在 110M +
.....
Excel::create(...)
#内存溢出点 3 ， Maatwebsite/Laravel-Excel库大批量生成也会内存溢出
# 和直观的代码处理流，该代码在小数据量时无问题

解决思路分析

1. orm 取数据优化 (mysql)
2. 对已获取的 orm 数据二次处理后 , 数据存储优化
3. 导出 excel 时， 导出优化

后续所有的代码功能都是围绕该 3 个方向来处理

方案 1 （异步生成数据 ）

思路分析：

1. 前端 ajax 发送 excel 导出请求 ->
2. 后端结束请求且计算数据总条数， 按一定倍数拆分成多次 job 生成数据 ->
3. 后端多个进程异步执行 job 队列任务，按批次生成数据 （每次执行计数一次，数据写入 redis） ->

4.前端 ajax 轮询获取总次数和当前已执行次数 （计算出进度条 ） ->
5.前端获 ajax 轮询结果总次数 = 已执行次数 ~~~~（进度100%），跳转到下载地址 ->
6.后端 redis 取数据，渲染生成 csv 文件（下载完成）

代码实现：

前端代码： jquery + Bootstrap

# 进度条样式，可在 bootstrap 找到
<section class="panel" id="export_loading_box">
<div class="panel-body m-b-10">
<p class="text-muted">
数据导出中 .....
</p>
<div class="progress progress-striped active">
<div class="progress-bar progress-bar-info"
role="progressbar"
aria-valuemin="0"
aria-valuemax="100"
style="width: 0%">
0%
</div>
</div>
</div>
</section>

$(function () {
$('.down-list').click(function () {
let formName = $(this).attr('data-form')
let data = $('#' + formName).serialize();
OE.params.url = $(this).attr('data-url');
OE.handle(data);
});
})
//商品导出 JS 控件
let OE = window.OE || {}
OE = {
params: {
ifRun: 0,
url: '',
cachePre: '',
},
# 1. 前端 ajax 发送 excel导出请求
handle: function (formData) {
if (OE.params.ifRun) {
return false;
}
OE.params.ifRun = 1;
OE.rateShow(100, 0);
$('#export_loading_box').find('.panel-body').show();
$.getJSON(OE.params.url, formData + '&run=1', function (data) {
if (200 == data.status) {
OE.params.cachePre =  data.cachePre;
//请求成功， 渲染进度条
OE.init();
} else {
OE.showAlert(false, data.msg);
}
})
},
# 4. ajax 轮询渲染进度条
init: function () {
let t = setInterval(function () {
$.getJSON(OE.params.url, "get_run=1&cache_pre="+OE.params.cachePre, function (data) {
OE.rateShow(data.total, data.run);
if (200 == data.status && data.total == data.run) {
clearInterval(t);
OE.params.ifRun = 0;
OE.showAlert(true);
//跳转下载 excel
window.location.href = OE.params.url+'?cache_pre='+OE.params.cachePre;
return;
}
});
}, 2000);
},
showAlert: function (success, msg) {
if (success) {
html = '<div class="alert alert-success fade in">' +
'       <button data-dismiss="alert" class="close close-sm" type="button">' +
'           <i class="fa fa-times"></i>' +
'       </button>' +
'       <span>导出成功</span> 数据下载中' +
' </div>';
}
$('#export_loading_box').append(html);
$('#export_loading_box').find('.panel-body').hide();
},
# 进度条计算
rateShow: function (total, run) {
let width = ((run / total) * 100).toFixed(0);
$('#export_loading_box').find('.progress-bar').css('width', width + '%');
$('#export_loading_box').find('.progress-bar').text(width + '% ');
}
}

后端代码 ：

2.后端总入口

// 前端第一次请求触发代码实现
$listOrm = self::getGoodOrm();
//求总，初始化 job 数据
$total = $listOrm->count();
for ($page = 0; $page <= ($totalPage - 1); $page++) {
//创建子队列
CreateExportGoodData::dispatch($requestData, $page, $authAdmin->id, $cachePre)
->onQueue(self::JOB_QUEUE);
}

3.后端异步子队列执行任务 （和前端无关）

self::$requestData = $requestData;
$listOrm = self::getGoodOrm();
$list = $listOrm->offset($page * self::PAGE_NUM)
->limit(self::PAGE_NUM)
->orderByDesc('goods.id')
->get();
//数据清洗过程
......
//执行次数递增 1
Redis::incr(self::GE_RUN_KEY . $cachePre . ':' . $adminId);
//清洗后的数据压入 redis 列表
Redis::lpush(self::GE_DATA_KEY . $cachePre . ':' . $adminId, serialize($data));

4.后端实现前端 ajax 轮询执行进度反馈代码实现

$total = Redis::get(GoodsExportRepository::GE_TOTAL_KEY. $cachePre. ':'. $authAdmin->id);
$run = Redis::get(GoodsExportRepository::GE_RUN_KEY. $cachePre. ':'. $authAdmin->id);
if ($request->input('get_run')) {
//前端 ajax 轮询获取同步已运行队列数
return ['status' => 200, 'total' => $total, 'run' => $run];
}

6.后端实现前端 excel 下载代码实现

$fileName = "商品导出" . date('Y-m-d');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
header('Cache-Control: max-age=0');
//开启预输出流
$fp = fopen('php://output', 'a');
//输出商品列表数据
while (true) {
//核心1 从 redis 列表里依次取数据
$data = Redis::rpop(self::GE_DATA_KEY . $cachePre . ':' . $adminId);
if (!$data) {
// redis 列表数据为空，结束 while 循环
break;
}
//核心2
ob_flush(); //取出 $fb 输出流 存入 buffer 内数据
flush();    //直接渲染至 http 数据流至浏览器
$data = unserialize($data);
foreach ($data as $row) {
foreach ($row as $key => $value) {
if (is_string($value)) {
$row[$key] = iconv('utf-8', 'gbk//IGNORE', $value);
}
}
fputcsv($fp, $row);
}
}
fclose($fp);
//必须 exit 阻止框架继续输出
exit();

至此，异步导出 excel 已完成

总结：

1. 后端队列任务生产数据录入 redis list
2. 前端 ajax 轮询获取执行情况
3. 前端 获取后端已将所有 队列任务执行， 跳转至下载地址
4. 下载地址取 redis 内数据，渲染成 csv 文件

优点：

1. 异步多队列进程执行，效率高
2. 前端可实时获取执行进度， 用户体验好

缺点：

1. ajax 轮询占用正常用户请求资源，该方案只适合后台实现
2. 代码复杂， 施工人员需一定的 laravel 队列知识和 前端知识储备；
   对自己把握不足的同学可直接看第二种解决方案

方案 2 （同步生成数据 ）

思路分析：

1. 设置 php 脚本时间 set_time_limit(0);
2. orm 依次获取数据，对获取的数据直接清洗后直接写入 输出流， 输出至浏览器

代码实现：

set_time_limit(0)
//直接输出头部声明
$fileName = "商品导出" . date('Y-m-d');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
header('Cache-Control: max-age=0');
//开启输出流
$fp = fopen('php://output', 'a');
// while 循环取数据
$page = 0;
while (true) {
$listOrm = self::getGoodOrm();
$list = $listOrm->offset($page * self::PAGE_NUM)
->limit(self::PAGE_NUM)
->orderByDesc('goods.id')
->get();
if ($list->isEmpty()) {
//无数据时退出 while 循环
break;
}
//数据清洗
$data = .....
//直接将清洗后的 $data 数据写入输出流
foreach ($data as $row) {
foreach ($row as $key => $value) {
if (is_string($value)) {
$row[$key] = iconv('utf-8', 'gbk//IGNORE', $value);
}
}
fputcsv($fp, $row);
}
//输出至浏览器
ob_flush();
flush();
}
fclose($fp);
exit();

总结

1. 优点： 代码流程简单，开发难度低
2. 缺点： 前端体验差， （ 数据单进程获取，效率低） 下载等待耗时长 ）

不管是异步还是同步， 实际思路都是分页获取数据， 对分页获取的数据进行处理； 都有用到核心方法：
fopen('php://output', 'a') ,
ob_flush(),
flush();

上述三方法是实现大数据导出的关键

结束 ~~~~

以上是 【php】php laravel 大批量导出 excel 的全部内容， 来源链接： utcz.com/a/107482.html