# 说明
之前关于 后台渲染导出 Excel 虽然使用 PHP_XLSXWriter 轻量封装
导出 excel，不用担心大数据量内存溢出，但是 nginx 超时，依旧影响导出大数据。
# 解决思路
因为 FastAdmin 基于 TP5, 所以使用 thinkphp-queue 队列，具体可以看文档怎么使用。
导出的数据超过一定量时，把导出数据需要的参数加入到队列中，让用户过段时间自行下载
执行队列任务（根据传递的参数，查询需要导出的数据，然后导出 excel 到本地）
# 加入队列
主要代码
注意：FastAdmin 后台控制器 中 list ($where, $sort, $order, $offset, $limit) = $this->buildparams ();
返回的 $where 是一个匿名对象，无法直接传递给队列，也不能直接序列化
要么像下面代码中一样，把 where 条件取出来，处理成数组
要么试着使用 super_closure 可以序列化匿名
public function export() | |
{ | |
// 只展示主要代码 | |
$job = 'AsyncExport'; // 执行队列的类名 | |
$queue = 'asyncexport'; // 队列名 | |
//TODO 获取匿名对象中的 static 重组 where | |
$reflection = new \ReflectionFunction($where); | |
$whereParams = $reflection->getStaticVariables()['where']; | |
$where2 = []; | |
foreach ($whereParams as $v){ | |
$where2[$v[0]] = [$v[1],$v[2]]; | |
} | |
$data = [ | |
$columns_arr, | |
$columns, | |
$where2, | |
$whereIds, | |
$map, | |
$sort, | |
$order, | |
get_class($this->model), | |
$this->auth->id, | |
]; | |
$isPushed = \think\Queue::push($job,$data,$queue); // 加入队列 | |
if (!$isPushed){ | |
$this->error(__('export queue Error'),''); | |
} | |
$this->success(__('export queue Success,Please go to the export list to download,The larger the amount of data, the longer the waiting time'),''); | |
} |
public function export() | |
{ | |
if ($this->request->isPost()) { | |
set_time_limit(0); | |
ini_set("memory_limit", "256M"); | |
$search = $this->request->post('search'); | |
$ids = $this->request->post('ids'); | |
$filter = $this->request->post('filter'); | |
$op = $this->request->post('op'); | |
$sort = $this->request->post('sort'); | |
$order = $this->request->post('order'); | |
$columns = $this->request->post('columns'); | |
$searchList = $this->request->post('searchList'); | |
$searchList = json_decode($searchList,true); | |
$whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]]; | |
$this->request->get(['search' => $search, 'ids' => $ids, 'filter' => urldecode($filter), 'op' => urldecode($op), 'sort' => $sort, 'order' => $order]); | |
list($where, $sort, $order, $offset, $limit) = $this->buildparams(); | |
//$columns 是得到的字段，可以在这里写上自己的逻辑，比如删除或添加其他要写的字段 | |
$columns_arr = $new_columns_arr = explode(',',$columns); | |
$key = array_search('serviceFee',$columns_arr); | |
if ($key){ | |
$new_columns_arr[$key] = "(platformFee + agentFee)/100 AS serviceFee"; | |
$columns = implode(',',$new_columns_arr); | |
} | |
$count = $this->model | |
->where($where) | |
->where($whereIds) | |
->where($map) | |
->count(); | |
// 超过一定量就加入到队列中 | |
if ($count > 10000){ | |
$job = 'AsyncExport'; | |
$queue = 'asyncexport'; | |
//TODO 获取匿名对象中的 static 重组 where | |
$reflection = new \ReflectionFunction($where); | |
$whereParams = $reflection->getStaticVariables()['where']; | |
$where2 = []; | |
foreach ($whereParams as $v){ | |
$where2[$v[0]] = [$v[1],$v[2]]; | |
} | |
$data = [ | |
$columns_arr, | |
$columns, | |
$where2, | |
$whereIds, | |
$map, | |
$sort, | |
$order, | |
get_class($this->model), | |
$this->auth->id, | |
]; | |
$isPushed = \think\Queue::push($job,$data,$queue); | |
if (!$isPushed){ | |
$this->error(__('export queue Error'),''); | |
} | |
$this->success(__('export queue Success,Please go to the export list to download,The larger the amount of data, the longer the waiting time'),''); | |
} | |
$title = date("YmdHis"); | |
$fileName = $title . '.xlsx'; | |
$writer = new XLSXWriter(); | |
$sheet = 'Sheet1'; | |
// 处理标题数据，都设置为 string 类型 | |
$header = []; | |
foreach ($columns_arr as $value) { | |
$header[__($value)] = 'string'; // 把表头的数据全部设置为 string 类型 | |
} | |
$writer->writeSheetHeader($sheet, $header); | |
$this->model | |
->field($columns) | |
->where($where) | |
->where($whereIds) | |
->where($map) | |
->chunk(1000, function ($items) use (&$list, &$writer,&$columns_arr,&$searchList) { | |
$list = $items = collection($items)->toArray(); | |
foreach ($items as $index => $item) { | |
// 根据标题数据 title，按 title 的字段顺序把数据一条条加到 excel 中 | |
$sheet = 'Sheet1'; | |
$row = []; | |
foreach ($item as $field => $value) { | |
// 只导出传递的字段 过滤 createtime_text 等 modeld 中的附加字段 | |
if (!in_array($field,$columns_arr)) continue; | |
// 根据前端传递的 $searchList 处理状态等 | |
if (isset($searchList[$field])){ | |
$value = $searchList[$field][$value] ?? $value; | |
} | |
$row[$field] = $value; | |
} | |
$writer->writeSheetRow($sheet, $row); | |
} | |
},$sort,$order); | |
// 设置 header，用于浏览器下载 | |
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($fileName).'"'); | |
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); | |
header('Content-Transfer-Encoding: binary'); | |
header('Cache-Control: must-revalidate'); | |
header('Pragma: public'); | |
$writer->writeToStdOut(); | |
exit(0); | |
} | |
} |
# 队列方法
<?php | |
namespace app\job; | |
use app\common\library\XLSXWriter; | |
use fast\Random; | |
use think\Exception; | |
use think\queue\Job; | |
class AsyncExport{ | |
// 只展示主要代码，省略部分 具体查看 think-queue 的具体使用 | |
/** | |
* 根据消息中的数据进行实际的业务处理... | |
*/ | |
private function doJob($data) | |
{ | |
// 整理传递到队列的参数 | |
list($columns_arr,$columns,$where,$whereIds,$map,$sort,$order,$model,$admin_id) = $data; | |
//$title = date("YmdHis"); | |
$title = Random::alnum(16); | |
$fileName = $title . '.xlsx'; | |
$writer = new XLSXWriter(); | |
$sheet = 'Sheet1'; | |
// 处理标题数据，都设置为 string 类型 | |
$header = []; | |
foreach ($columns_arr as $value) { | |
$header[__($value)] = 'string'; // 把表头的数据全部设置为 string 类型 | |
} | |
$writer->writeSheetHeader($sheet, $header); | |
model($model) | |
->field($columns) | |
->where($where) | |
->where($whereIds) | |
->where($map) | |
->chunk(1000, function ($items) use (&$list, &$writer,&$columns_arr,&$searchList) { | |
$list = $items = collection($items)->toArray(); | |
foreach ($items as $index => $item) { | |
// 根据标题数据 title，按 title 的字段顺序把数据一条条加到 excel 中 | |
$sheet = 'Sheet1'; | |
$row = []; | |
foreach ($item as $field => $value) { | |
// 只导出传递的字段 过滤 createtime_text 等 modeld 中的附加字段 | |
if (!in_array($field,$columns_arr)) continue; | |
// 根据前端传递的 $searchList 处理状态等 | |
if (isset($searchList[$field])){ | |
$value = $searchList[$field][$value] ?? $value; | |
} | |
$row[$field] = $value; | |
} | |
$writer->writeSheetRow($sheet, $row); | |
} | |
},$sort,$order); | |
$output_dir = ROOT_PATH . 'public' . DS . 'export' . DS . $fileName; | |
$writer->writeToFile($output_dir); | |
$insertData = [ | |
'filename' => $fileName, | |
'filesize' => filesize($output_dir), | |
'url' => DS . 'export' . DS . $fileName, | |
'mimetype' => 'xlsx', | |
'model' => $model, | |
'admin_id' => $admin_id, | |
'createtime' => time(), | |
]; | |
//excel 导出后保存在服务器 这里把导出的 excel 基本信息和路径保存在表中 给用户展示 方便下载 | |
model('app\admin\model\ExportList')->insert($insertData); | |
return true; | |
} | |
} |
# 保存导出记录
导出表 记录导出的 excel
admin_id 用来区分是谁下载的，只给他显示自己下载的
CREATE TABLE `t_export_list` ( | |
`id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID', | |
`filename` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '文件名', | |
`url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '文件路径', | |
`filesize` int(10) NULL DEFAULT NULL COMMENT '文件大小', | |
`mimetype` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'mime类型', | |
`admin_id` int(10) NULL DEFAULT NULL COMMENT '管理员ID', | |
`model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '导出model', | |
`createtime` int(10) NULL DEFAULT NULL COMMENT '创建时间', | |
PRIMARY KEY (`id`) USING BTREE, | |
INDEX `admin_id`(`admin_id`) USING BTREE | |
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact; |
FastAdmin 对应导出列表的控制器
<?php | |
namespace app\admin\controller\general; | |
use app\common\controller\Backend; | |
/** | |
* | |
* | |
* @icon fa fa-circle-o | |
*/ | |
class Exportlist extends Backend | |
{ | |
protected $dataLimit = true; // 开启后台默认 根据 admin_id 做数据限制 | |
} |
# 效果展示
