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' . 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 做数据限制
}

效果展示#

7-1