如何高效批量导出PHP中的大量Excel数据？

本文共计1921个文字，预计阅读时间需要8分钟。

在日常生活中或特定场合，我们可能会需要大量导出Excel数据。为此，我们将推出优化过程，希望对有需求的同学们起到一定的帮助。使用PHP在Laravel或其他框架内批量生成Excel数据。

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

先看优化后效果：

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

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 （异步生成数据 ）

思路分析：

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

代码实现：

前端代码： jquery + Bootstrap

# 进度条样式，可在 bootstrap 找到 <section class="panel" id="export_loading_box"> <p class="panel-body m-b-10" style="display: none"> <p class="text-muted"> 数据导出中 ..... </p> <p class="progress progress-striped active"> <p class="progress-bar progress-bar-info" role="progressbar" aria-valuemin="0" aria-valuemax="100" style="width: 0%"> 0% </p> </p> </p> </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 = '<p class="alert alert-success fade in">' + ' <button data-dismiss="alert" class="close close-sm" type="button">' + ' <i class="fa fa-times"></i>' + ' </button>' + ' <strong>导出成功</strong> 数据下载中' + ' </p>'; } $('#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(); //直接渲染至 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();

总结

• 优点： 代码流程简单，开发难度低

• 缺点： 前端体验差， （ 数据单进程获取，效率低） 下载等待耗时长 ）

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

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

推荐学习：php视频教程

以上就是php如何大批量导出excel数据的详细内容，更多请关注自由互联其它相关文章！