PHPExecl导出数据遇到复杂查询+几万条数据的情况浏览器就容易卡死，下面分享一下解决思路和过程：
先说解决思路：
1、优化查询、数据分批写入文件
2、先将表格下载到服务器，浏览器轮询服务器，表格生成完成再下载
3、表格使用队列导出
上代码
浏览器端：
1、点击导出按钮，请求exportUser接口，将文件加入队列，返回唯一key
2、使用key轮询请求getURL接口，文件生成成功则返回文件路径
3、下载文件
1 <div class="form-group col-sm-1" > 2 <a class="btn btn-block btn-success btn-bg importExcel" href="javascript:void(0);">导出</a> 3 </div> 4 5 6 <script src="{{ asset("/js/layer/layer.js")}}"></script> 7 8 $(‘.importExcel‘).on(‘click‘, function () { 9 var data = { 10 ‘status‘: $("#status_val option:selected").val(), 11 ‘start_time‘: $("input[name=‘start_time‘]").val(), 12 ‘user_name‘: $("input[name=‘user_name‘]").val(), 13 ‘end_time‘: $("input[name=‘end_time‘]").val() 14 }; 15 16 $.post(‘/admin/user/exportUser‘, data, function (res) { 17 if (res.code == 200) { 18 var params={ 19 ‘key‘:res.data 20 } 21 var ii = layer.load(); 22 var aaa = setInterval(() => { 23 $.post(‘/admin/user/getURL‘, params, function (reslut) { 24 var url=reslut.data.url 25 if(url){ 26 clearInterval(aaa) 27 layer.close(ii); 28 window.open(url); 29 } 30 }) 31 }, 1000) 32 } 33 }); 34 35 })
服务器端：
1、exportUser接口
use App\Jobs\exportList;
/** * 导出列表 */ public function exportUser(Request $request) { $query = $request->all(); $str=str_random(10).time(); Redis::set($str,‘‘); $arr=[ ‘query‘=>$query, ‘type‘=>1, ‘key‘=>$str ]; $this->dispatch(new exportList($arr)); return success($str); }
2、生成队列任务
<?php namespace App\Jobs; use Illuminate\Bus\Queueable; use Illuminate\Queue\SerializesModels; use Illuminate\Queue\InteractsWithQueue; use Illuminate\Contracts\Queue\ShouldQueue; use Illuminate\Foundation\Bus\Dispatchable; use App\Services\UserService; use Illuminate\Support\Facades\Log; class exportList implements ShouldQueue { use Dispatchable, InteractsWithQueue, Queueable, SerializesModels; protected $data; /** * Create a new job instance. * * @return void */ public function __construct($data) { $this->data = $data; } /** * Execute the job. 处理列队数据 * * @return void */ public function handle(UserService,$userService) { if($this->data[‘type‘]==1){ //用户导出 $userService->exportUser($this->data[‘key‘],$this->data[‘query‘]);
} elseif($this->data[‘type‘]==2){ //订单导出 xxxxxx
}
}
}
3、分批导出，其中getExportList方法取数据，newExportList渲染数据并导出
/** * 批量导出 * * @param [type] $key * @param [type] $query * @return void */ public function exportUser($key,$query) { $i = 0; $file_name = rand(1000, 9999); while (true) { $list = $this->getExportList($query, $i); //分页取数据 if(is_null($list)){ $list=[]; } $res=$this->newExportList($key,$list, $file_name, ($i * env(‘EXPORT_NUM‘)) + 1); if (count($list) < env(‘EXPORT_NUM‘)) { return $res; } $i += 1; } }
4、getExportList方法，取数据
/**
* 获取导出的数据
* @param $params
* @return mixed
*/
public function getExportList($query, $page)
{
$list = $this->UserRepositories->getListAll($query, $page);
if (!$list->isEmpty()) {
$list = $list->toArray();
$data = [];
foreach ($list as $key => $value) {
$data[$key][‘A‘] = $value[‘user_name‘];
$data[$key][‘B‘] = "\t" . $value[‘phone‘];
$data[$key][‘C‘] = $value[‘created_at‘];
}
return $data;
}
return [];
}
5、newExportList 渲染数据方法
/** * 导出数据 * @param $list */ public function newExportList($key,$list, $file_name, $total) { $arr = [‘A‘ => ‘用户名称‘, ‘B‘ => ‘手机号‘, ‘C‘ => ‘注册时间‘]; if ($total == 1) { array_unshift($list, $arr); //插入表头 } else { $total += 1; } $title = date(‘Y-m-d‘, time()) . ‘_‘ . $file_name . ‘.csv‘; $width = array(‘A‘ => 45, ‘B‘ => 35, ‘C‘ => 35);//列宽 $res=exportExcelForURL($title, $list, $width, $total); if($res){ Redis::set($key,$res); Redis::EXPIRE($key,600); } return [‘code‘ => 200,‘url‘=>$res]; }
6、exportExcelForURL 导出表格方法
/**
* 导出excel 分批保存
* @param $title
* @param $list
* @param $width
*/
function exportExcelForURL($title, $list, $width, $total = 0)
{
$file_name = env(‘EXPORT_DIR‘) . ‘/‘ . $title;
$name=config(‘config.EXCEL_ROUTE‘). $title;
if (!file_exists($file_name)) {
$myfile = fopen($file_name, "w");
fclose($myfile);
}
$excel = \Excel::load($file_name);
$sheet = $excel->getSheet(0);
foreach ($list as $row_key => $row) {
foreach ($row as $key => $value) {
\Log::info($key . ($row_key + $total));
$res = $sheet->setCellValue($key . ($row_key + $total), $value);
}
}
$excel->store(‘csv‘, env(‘EXPORT_DIR‘));
if (count($list) < env(‘EXPORT_NUM‘)) {
return $name;
}
return false;
}
7、获取下载链接
/**
* 获取下载链接
*
* @param Request $request
* @return void
*/
public function getURL(Request $request)
{
$key=$request->key;
$data[‘url‘]=Redis::get($key);
return success($data);
}
8、开启队列（推荐配置Supervisor）
php artisan queue:work --tries=3