FastAdmin 個人記錄 - thinkphp-queue匯出Excel

說明#

之前關於後台渲染導出 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' . $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 做數據限制
}

效果展示#

7-1