Backend Rendering
Queue Export
Description#
Previously, regarding Backend Rendering Export Excel, although using PHP_XLSXWriter for lightweight encapsulation to export Excel, there was no need to worry about memory overflow with large data volumes, but nginx timeout still affected the export of large data.
Solution Approach#
Since FastAdmin is based on TP5, it uses thinkphp-queue for queuing, and you can refer to the documentation for specific usage.
When the exported data exceeds a certain amount, the parameters needed for the export data are added to the queue, allowing users to download it after some time. The queue task is executed (querying the data to be exported based on the passed parameters, and then exporting the Excel to local).
Adding to Queue#
Main Code
Note: In the FastAdmin backend controller, list($where, $sort, $order, $offset, $limit) = $this->buildparams(); The returned $where is an anonymous object and cannot be directly passed to the queue, nor can it be directly serialized. You can either extract the where conditions as shown in the code below and process them into an array, or try using super_closure to serialize the anonymous function.
public function export()
{
// Only show main code
$job = 'AsyncExport'; // Class name for executing the queue
$queue = 'asyncexport'; // Queue name
//TODO Get static from anonymous object to reconstruct 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); // Add to 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 is the obtained fields, you can write your own logic here, such as deleting or adding other fields to write
$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 it exceeds a certain amount, add to the queue
if ($count > 10000){
$job = 'AsyncExport';
$queue = 'asyncexport';
//TODO Get static from anonymous object to reconstruct 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';
// Process header data, set all to string type
$header = [];
foreach ($columns_arr as $value) {
$header[__($value)] = 'string'; // Set all header data to string type
}
$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) {
// Add data to excel one by one based on the title data title, in the order of title fields
$sheet = 'Sheet1';
$row = [];
foreach ($item as $field => $value) {
// Only export the passed fields, filter out additional fields like createtime_text in model
if (!in_array($field, $columns_arr)) continue;
// Process status etc. based on the $searchList passed from the front end
if (isset($searchList[$field])){
$value = $searchList[$field][$value] ?? $value;
}
$row[$field] = $value;
}
$writer->writeSheetRow($sheet, $row);
}
}, $sort, $order);
// Set header for browser download
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);
}
}
Queue Method#
<?php
namespace app\job;
use app\common\library\XLSXWriter;
use fast\Random;
use think\Exception;
use think\queue\Job;
class AsyncExport{
// Only show main code, omitted parts, see specific usage of think-queue
/**
* Perform actual business processing based on the data in the message...
*/
private function doJob($data)
{
// Organize the parameters passed to the queue
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';
// Process header data, set all to string type
$header = [];
foreach ($columns_arr as $value) {
$header[__($value)] = 'string'; // Set all header data to string type
}
$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) {
// Add data to excel one by one based on the title data title, in the order of title fields
$sheet = 'Sheet1';
$row = [];
foreach ($item as $field => $value) {
// Only export the passed fields, filter out additional fields like createtime_text in model
if (!in_array($field, $columns_arr)) continue;
// Process status etc. based on the $searchList passed from the front end
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(),
];
// After exporting the excel, save it on the server, here save the basic information and path of the exported excel in the table for user display, making it easy to download
model('app\admin\model\ExportList')->insert($insertData);
return true;
}
}
Save Export Records#
Export Table Records the exported excel
admin_id is used to distinguish who downloaded it, only showing their own downloads
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 'File Name',
`url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'File Path',
`filesize` int(10) NULL DEFAULT NULL COMMENT 'File Size',
`mimetype` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'MIME Type',
`admin_id` int(10) NULL DEFAULT NULL COMMENT 'Admin ID',
`model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Export Model',
`createtime` int(10) NULL DEFAULT NULL COMMENT 'Creation Time',
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 Corresponding Export List Controller
<?php
namespace app\admin\controller\general;
use app\common\controller\Backend;
/**
*
*
* @icon fa fa-circle-o
*/
class Exportlist extends Backend
{
protected $dataLimit = true; // Enable backend default data limit based on admin_id
}
Effect Display#
