How to export excel data in large batches with php-PHP Problem-php.cn

在平时生活或其他时候，我们可能会需要大批量导出excel数据，所以将这次的优化过程发布出来，希望对有需要的同学启到一定的帮助。

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

先看优化后效果：

How to export excel data in large batches with php

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

How to export excel data in large batches with php

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

How to export excel data in large batches with php

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)

Copy after login

原代码逻辑

$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库大批量生成也会内存溢出 # 和直观的代码处理流，该代码在小数据量时无问题

Copy after login

解决思路分析

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

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

方案 1 （异步生成数据）

思路分析：

前端 ajax 发送 excel 导出请求 ->后端结束请求且计算数据总条数，按一定倍数拆分成多次 job 生成数据 ->后端多个进程异步执行 job 队列任务，按批次生成数据（每次执行计数一次，数据写入 redis） ->前端 ajax 轮询获取总次数和当前已执行次数（计算出进度条） ->前端获 ajax 轮询结果总次数 = 已执行次数 ~~~~（进度100%），跳转到下载地址 ->后端 redis 取数据，渲染生成 csv 文件（下载完成）

代码实现：

前端代码： jquery + Bootstrap

# 进度条样式，可在 bootstrap 找到   
 数据导出中 ..... 
  
 0%

Copy after login

$(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 = '' + ' ' + ' 导出成功 数据下载中' + ' 
'; } $('#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 + '% '); } }

Copy after login

后端代码：

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); }

Copy after login

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));

Copy after login

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]; }

Copy after login

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();

Copy after login

至此，异步导出 excel 已完成

总结：

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

优点：

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

缺点：

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

方案 2 （同步生成数据）

思路分析：

设置 php 脚本时间 set_time_limit(0);
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();

Copy after login

总结

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

不管是异步还是同步，实际思路都是分页获取数据，对分页获取的数据进行处理；都有用到核心方法：

fopen('php://output', 'a') , ob_flush(), flush();

Copy after login

推荐学习：php视频教程

The above is the detailed content of How to export excel data in large batches with php. For more information, please follow other related articles on the PHP Chinese website!