thinkphp6 + redis实现大数据导出excel超时或内存溢出问题解决方案

redis下载安装（window版本）
参考地址：https://blog.csdn.net/Ci1693840306/article/details/144214215

php安装redis扩展
参考链接：https://blog.csdn.net/jianchenn/article/details/106144313

解决思路：（分批处理，最后合并）
业务逻辑：本项目由于涉及到多张数据表，导出业务逻辑为：先查询主表，查询出数据后通过foreach遍历数据，并在遍历循环中根据与主表关联的字段查询另外几张表对应数据。
解决方案：
后端：

1. 先将字典表、需要在循环中查询的所有数据表存储到redis中（如果数据过多，可以将其分为多个方法）
2. 将导出数据接口中的数据进行分页，根据页码数导出相应的数据条数，并存放至临时excel文件中。等待全部执行完毕后将这些临时文件合并成一个excel文件，并返回。

前端：

1. 首先请求字典等数据表存入redis的接口
2. 请求导出数据接口，每次传入当前需导出数据的页码数，在没有全部完成之前页数++，直到完成后执行下载文件操作

PHP-Xlswriter扩展安装：
官网：https://xlswriter-docs.viest.me/zh-cn/an-zhuang/windows

在thinkphp6项目中打开config/cache.php，加上redis配置参数：

<?php// +----------------------------------------------------------------------
// | 缓存设置
// +----------------------------------------------------------------------return [// 默认缓存驱动'default' => env('cache.driver', 'file'),// 缓存连接方式配置'stores'  => ['file' => [// 驱动方式'type'       => 'File',// 缓存保存目录'path'       => '',// 缓存前缀'prefix'     => '',// 缓存有效期 0表示永久缓存'expire'     => 0,// 缓存标签前缀'tag_prefix' => 'tag:',// 序列化机制 例如 ['serialize', 'unserialize']'serialize'  => [],],// 更多的缓存连接'redis' => ['type'   => 'redis',// 缓存主机'host'       => '127.0.0.1',// 缓存端口'port'     => '6379',// 缓存密码'password'     => '',// 缓存数据库'select'   => 0,// 缓存有效期 0表示永久缓存'timeout'   => 0,// 缓存前缀'prefix'   => '']],];

后端路由route/app.php

Route::post('export_data/:code/:id', 'Basicinfo/export_data'); // 根据指定条件和字段导出数据
Route::post('export_save_redis/:code/:id', 'Basicinfo/export_save_redis'); // 导出之前将部分数据存入redis
Route::post('export_save_redis2/:code/:id', 'Basicinfo/export_save_redis2'); // 导出之前将部分数据存入redis

控制器 BasicinfoController.php

<?php
namespace app\controller;
use app\service\BasicinfoService;
class BasicinfoController {public function export_save_redis(){$data = input('post.');$service = new BasicinfoService;$res = $service->export_basicinfo_save_redis($data);return show(true, '', $res);}public function export_save_redis2(){$data = input('post.');$service = new BasicinfoService;$res = $service->export_basicinfo_save_redis_person($data);return show(true, '', $res);}public function export_data(){$data = input('post.');$service = new BasicinfoService;$res = $service->export_data($data);if($res['state']){if(file_exists($res['file'])){return show(true, '', $res['file']);}else{return show(false, '文件导出失败');}}else{return show(false, '', $res);}}
}

BasicinfoService.php

<?php
namespace app\service;// 引入其他文件...class BasicinfoService{public function export_save_redis($data){$mapper = new BasicinfoMapper;$mapper -> export_save_redis($data['search']??[]);return true;}// 由于第此数据表数据量过多，导致保存失败，单独处理public function export_save_redis2($data){$mapper = new BasicinfoMapper;$mapper -> export_save_redis2($data['search']??[]);return true;}public function export_data($data){$result_data = [];$data['page'] = isset($data['page']) ? $data['page'] : 1;// 获取所有要查询的字段和名称$header_arr = [];$fields_arr = [];foreach($data['export_data'] as $key=>$val){array_push($header_arr, $val['label']);array_push($fields_arr, $val['field']);}// 文件存储目录$public = app()->getRootPath().'public/';$path = 'uploads/export_data/';$file_name = !empty($data['file_name']) ? $data['file_name'] : 'basicinfo_'.rand(99999, 99999999);$result_data['file_name'] = $file_name;$fileName = $file_name.'_'.$data['page'].'.xlsx';if(!file_exists($path)){mkdir($path, 0777);}$excel_config = ['path' => $public.$path // xlsx文件保存路径];$excel  = new \Vtiful\Kernel\Excel($excel_config);$fileObject = $excel->fileName($fileName, 'sheet1');$mapper = new BasicinfoMapper;$page_size = 5000; // 每次查询的数据条数// 在第1页时需要查询总数量，并获取总页数if($data['page'] == 1){// 获取总数$count = $mapper->get_count_basicinfo($data['search']??[]);$loop_num = ceil($count/$page_size);$data['total_page'] = $loop_num; // 设置总页数$result_data['total_page'] = $loop_num; // 返回总页数}else{$result_data['total_page'] = $data['total_page'];}$loop_page = 2; // 每页执行n次循环$loop_num = $loop_page;// 检测当前页数 * 循环数量 >= 总页数后if(intval($data['page'] * $loop_page) >= intval($data['total_page'])){if(intval($data['page'] * $loop_page) == intval($data['total_page'])){$loop_num = 1;}else{$loop_num = ($data['page'] * $loop_num) - $data['total_page'];}}// 当前循环完成后最大id$max_id = !empty($data['max_id']) ? $data['max_id'] : 0;for($l=0; $l<$loop_num; $l++){$list = $mapper->export_list_basicinfo($data['search']??[], $fields_arr, $max_id, $page_size);if($list){$max_id = $list[count($list)-1]['organ_id']; // 重置最大id// var_dump($max_id);$content = [];$i=0;foreach($list as &$val){// 处理转化数据值// ...$result = [];foreach ($fields_arr as $key) {if (isset($val_arr[$key])) {$result[$key] = $val[$key];}else{$result[$key] = '';}}$content[$i] = array_values($result);$i++;unset($val_arr);unset($val);unset($result);}// 将数据写入xls文件if(count($content) > 0){$fileObject->data($content)->output();unset($content);}}  unset($list);}$result_data['max_id'] = $max_id;// 检测如果为最后一页，则将文件合并后返回if(intval($data['page'] * $loop_page) >= intval($data['total_page'])){$result_data['state'] = true;// 处理合并文件$res = $this->merge_export_file($file_name, $data['page'], $header_arr);// $file_dir = 'uploads/export_data/'.$fileName;$result_data['file'] = $res;}else{$result_data['state'] = false;$result_data['file'] = '';}return $result_data;}// 处理合并文件public function merge_export_file($fileName, $page, $header_arr){$public = app()->getRootPath().'public/';$path = 'uploads/export_data/';$excel_config = ['path' => $public.$path // xlsx文件保存路径];$excel  = new \Vtiful\Kernel\Excel($excel_config);$res_file = $fileName . '_all.xlsx';$fileObject = $excel->fileName($res_file, 'sheet1');// // 设置样式$fileHandle = $fileObject->getHandle();$format = new \Vtiful\Kernel\Format($fileHandle);$alignStyle = $format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER_ACROSS)->toResource();$boldStyle = $format// ->bold() // 加粗// ->wrap() // 文本换行// ->background(0xFFB6C1) // 设置背景颜色 颜色常量和16进制数->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER) // 文本居中->toResource();// // fileName 会自动创建一个工作表，你可以自定义该工作表名称，工作表名称为可选参数$fileObject->header($header_arr)// ->data($content)->defaultFormat($alignStyle)->setRow('A1', 30, $boldStyle)->setRow('A2:A9999', 20) // 行高// ->setColumn('A:A', '10')->setColumn('A:CZ', 30)->output();$file_arr = [];for($i=1; $i<=$page; $i++){$nowFile = $fileName.'_'.$i.'.xlsx';array_push($file_arr, $nowFile);if(file_exists($path.$nowFile)){$data = $excel->openFile($nowFile)->openSheet()->getSheetData();$fileObject->data($data)->output();}}// 回收资源$fileObject -> close();// 删除临时文件foreach($file_arr as $file){if(file_exists($path.$file)){unlink($path.$file);}}return $path.$res_file;}
}

BasicinfoMapper.php

<?php
namespace app\mapper;
use think\facade\Db;
use app\model\Basicinfo;
use think\facade\Cache;
class BasicinfoMapper
{public function export_save_redis($search){$where = '';$out_time = 600; // 缓存时间秒$batch_size = 2000;  // 每批次处理的数量$redis = Cache::store('redis')->handler();$redis->flushDb(); // 清空redis缓存$sql1= 'select main_id,field1,field2,... from table1  where 1=1 '.$where;$list1 = Db::query($sql1);if(!empty($list1)){// 建立管道$pipe1 = $redis->pipeline();$batches = array_chunk($list1, $batch_size); // 将数据分批foreach ($batches as $batch) {foreach ($batch as $v) {$pipe1->hMSet('table1_'.$v['main_id'], $v);$pipe1->expire('table1_'.$v['main_id'], $out_time); // 设置过期时间}$pipe1->exec(); // 执行当前批次$pipe1 = $redis->pipeline(); // 重新初始化管道}}unset($list1);$sql2= 'select main_id,field1,field2,... from table2  where 1=1 '.$where;$list2 = Db::query($sql2);if(!empty($list2)){$pipe2 = $redis->pipeline();$batches2 = array_chunk($list2, $batch_size); // 将数据分批foreach ($batches2 as $batch) {foreach ($batch as $v) {$pipe2->hMSet('table2_'.$v['main_id'], $v);$pipe2->expire('table2_'.$v['main_id'], $out_time); // 设置过期时间}$pipe2->exec(); // 执行当前批次$pipe2 = $redis->pipeline(); // 重新初始化管道}}unset($list2);}public function export_save_redis2($search){$where = '';$out_time = 600; // 缓存时间秒$batch_size = 2000;  // 每批次处理的数量$redis = Cache::store('redis')->handler();// $redis->flushDb(); // 清空redis缓存// $temp = '';$sql = 'select main_id,field1,field2,... from table3 where type in (1,3) '.$where;$list = Db::query($sql);if(!empty($list)){$pipe = $redis->pipeline();$batches = array_chunk($list, $batch_size); // 将数据分批foreach ($batches as $batch) {foreach ($batch as $v) {$key = $v['type'] == 1 ? 'table3_1_'.$v['main_id'] : 'table3_3_'.$v['main_id'];$pipe->hMSet($key, $v);$pipe->expire($key, $out_time);}$pipe->exec(); // 执行当前批次$pipe = $redis->pipeline(); // 重新初始化管道}}unset($list);}public function export_list_basicinfo($search, $fields=[], $max_id=0, $limit=1000){$pFields = [];$dFields = [];$cFields = [];$fields = array_reduce($fields, function($carry, $field) use (&$pFields,&$dFields,&$cFields) {if (substr($field, 0, 6) === 'table1_') {$dFields[] = $field;} else if (substr($field, 0, 7) === 'table2_') {// $carry[] = 'n.' . $field;$cFields[] = $field;} else if (substr($field, 0, 6) === 'table3') {$pFields[] = $field;} else {$carry[] =  $field;}return $carry;}, []);$fields = implode(',', $fields);$where = '1 = 1';// 其他条件...$sql = 'select main_id,'.$fields." from basicinfo   where ".$where.' and main_id > '.$max_id.' order by main_id limit '.$limit;$redis = Cache::store('redis')->handler();$list = Db::query($sql);if(!$list){ return []; }foreach ($list as $k => $v){$fd = [];if(count($dFields) > 0){$p_info = $redis->hGetAll('table1_'. $v['main_id']);if(!empty($p_info)){$fd = $p_info;}}if(in_array('organ', $dFields)){$list[$k]['organ'] = !empty($fd) ? $fd['organ']:'';}$fc = [];if(count($cFields) > 0){if (!empty($redis->hGetAll('table2_'. $v['main_id']))) {$fc = $redis->hGetAll('table2_'. $v['main_id']);}}if(in_array('party', $cFields)){$list[$k]['party'] = !empty($fc) ? $fc['party'] : 0;}$jbr = [];$fr = [];if(count($pFields) > 0){$table31_info = $redis->hGetAll('table3_1_'. $v['organ_idno']);$table33_info = $redis->hGetAll('table3_3_'. $v['organ_idno']);if(!empty($table31_info)){$jbr = $table31_info;}if(!empty($table33_info)){$fr = $table33_info;}}if(in_array('name', $pFields)){$list[$k]['name'] = !empty($jbr) ? $jbr['name']:'';}}return $list;
}
}

前端：

<template><el-button type="primary" style="margin-top: 30px;" @click="clickExportData">导出</el-button>
</template>
<script>
import downloadFile from '@/plugins/downloadFile';
export default {data() {return {percentage:0, //进度条的占比progressShow: false, // 是否显示进度条弹出层dowPage: 1}},methods: {clickExportData(){const data = {};// 获取被选中的字段let result = [];data['export_data'] = result;data['search'] = {};this.progressShow = true;this.percentage = 0;this.$http.post("export_save_redis/"+this.code+'/'+this.user_id, data).then(res1 => {// console.log(res1);this.$http.post("export_save_redis2/"+this.code+'/'+this.user_id, data).then(res2 => {this.exportData(data);})});},// 导出数据async exportData(data){data.page = this.dowPage;let url = "export_data/"+this.code+'/'+this.user_id;const {data:res} = await this.$http.post(url, data);console.log(res);if(res.state){console.log('进行文件下载')var xls_url = this.$request_url + res.content;// 进行下载文件操作// ...// downloadFile.getProgress(xls_url, '下载文件_'+new Date().getTime(), this, this.percentage);}else{if(res.content){this.dowPage ++;data.file_name = res.content.file_name;data.total_page = res.content.total_page;data.max_id = res.content.max_id;this.exportData(data);}else{this.progressShow = false;this.$message.error('数据导出失败，请稍后重试！');}}},}
}
</script>