积木报表Excel数据量大导出慢导不出问题、大量数据导不出问题优化方案和分析解决思路（优化前一万多导出失败，优化后支持百万级跨库表导出，已开源）

积木报表excel数据量大导出慢导不出问题、大量数据导不出问题优化方案和分析解决思路（优化前一万多导出失败，优化后支持百万级跨库表导出，已开源）

反馈官方进度

跟官方反馈这个方案后，官方直接优化了但是把原本这个免费的大数据导出做成了收费功能！绝了，我直接把我方案开源

反馈后看到后续的版本里有说升级了这个，于是我升级了包测试，直接这个功能需要企业版才给用了，寄，不如直接用下载中心模式得了，有空把这个下载中心的代码抽出来封装好开源放github ⁰
下载中心这个能力抽出来脱敏成开源项目已准备的差不多了，准备好后更新文章,帮忙点star支持开源
地址：https://github.com/humorchen/jimuexportdataextension

优化结果

原积木导出有两种导出，直接导出和大数据导出（大数据导出是做了优化去掉了一些样式之类的，性能更好）
实测中发现
原积木大数据导出性能：1万条数据导出耗时30秒，1.5万条耗时1.5分钟导出失败，数据超过一万条后经常导出失败，还会导致容器实例探活失败/内存撑爆重启
异步+自实现导出功能优化方案导出性能：48万条数据一次导出耗时2.5分钟，导出时快速返回空数据文件，避免页面死等不可用，导出后的表格是在线oss的文件url直接每个人都可以快速下载，可随时多次下载。

需求背景和解决方案的思考

解决方案

在数据获取的api接口上加注解标识这个导出走下载中心方案，若要恢复原样，则注释这个注解即可

流程描述：

识别并拦截积木导出操作，透传导出时参数生成自己设计的导出任务，异步执行导出任务(自行实现积木导出的逻辑)，并使用高性能的阿里巴巴开源的easyexcel工具，最后将easyexcel导出的表格文件上传到oss变为表格url（记得做安全防护，避免表格泄漏）

积木的逻辑
积木前端导出按钮->积木导出接口->分页调取你的数据接口拉数据->生成excel返回

关键代码

引入easy excel

			<dependency>
                <groupid>com.alibaba</groupid>
                <artifactid>easyexcel-core</artifactid>
                <version>3.1.1</version>
            </dependency>
            <dependency>
                <groupid>com.alibaba</groupid>
                <artifactid>easyexcel</artifactid>
                <version>3.1.1</version>
            </dependency>

新建数据库表

create table `t_download_task` (
  `id` int(10) unsigned not null auto_increment comment 'id',
  `account` varchar(64) collate utf8mb4_unicode_ci not null comment '提交任务的账号',
  `title` varchar(256) collate utf8mb4_unicode_ci not null comment '下载任务标题',
  `icon` varchar(1024) collate utf8mb4_unicode_ci default null comment '图标',
  `url` varchar(1024) collate utf8mb4_unicode_ci default null comment '文件url',
  `file_size` varchar(16) collate utf8mb4_unicode_ci default null comment '文件大小',
  `percent` varchar(16) collate utf8mb4_unicode_ci default null comment '进度（例如50%）',
  `state` tinyint(4) default '0' comment '任务状态（0 等待执行，1执行中，2执行成功，3执行失败）',
  `error` varchar(1024) collate utf8mb4_unicode_ci default null comment '执行报错信息（有则填）',
  `json` varchar(4096) collate utf8mb4_unicode_ci not null default '{}' comment '预留的json扩展字段',
  `create_time` datetime not null default current_timestamp comment '创建时间',
  `update_time` datetime not null default current_timestamp on update current_timestamp comment '更新时间',
  primary key (`id`),
  key `idx_account_create_time` (`account`,`create_time`),
  key `idx_create_time` (`create_time`)
) engine=innodb auto_increment=78 default charset=utf8mb4 collate=utf8mb4_unicode_ci comment='下载中心的任务';

下载任务

/**
 * <p>
 * 下载任务
 * </p>
 *
 * @author humorchen
 * @since 2024-01-05
 */
@data
@equalsandhashcode(callsuper = false)
@accessors(chain = true)
@tablename("t_download_task")
public class downloadtask implements serializable {

    private static final long serialversionuid = 1l;

    /**
     * id
     */
    @tableid(value = "id", type = idtype.auto)
    private integer id;
    /**
     * 创建下载任务的账号
     */
    @tablefield("account")
    private string account;

    /**
     * 下载任务标题
     */
    @tablefield("title")
    private string title;

    /**
     * 图标
     */
    @tablefield("icon")
    private string icon;

    /**
     * 文件url
     */
    @tablefield("url")
    private string url;

    /**
     * 文件大小
     */
    @tablefield("file_size")
    private string filesize;

    /**
     * 进度（例如50%）
     */
    @tablefield("percent")
    private string percent;

    /**
     * 任务状态（0 等待执行，1执行中，2执行成功，3执行失败）
     */
    @tablefield("state")
    private integer state;

    /**
     * 执行报错信息（有则填）
     */
    @tablefield("error")
    private string error;

    /**
     * 预留的json扩展字段
     */
    @tablefield("json")
    private string json;

    /**
     * 创建时间
     */
    @tablefield("create_time")
    private date createtime;

    /**
     * 更新时间
     */
    @tablefield("update_time")
    private date updatetime;


    public static final string id = "id";

    public static final string account = "account";

    public static final string title = "title";

    public static final string icon = "icon";

    public static final string url = "url";

    public static final string file_size = "file_size";

    public static final string percent = "percent";

    public static final string state = "state";

    public static final string error = "error";

    public static final string json = "json";

    public static final string create_time = "create_time";

    public static final string update_time = "update_time";

}

使用mabatis plus code generator生成service、mapper等文件

识别所需注解、基类

数据获取api所调用服务上方法上标注的注解

/**
 * @author: humorchen
 * date: 2024/1/15
 * description: 该报表接口使用下载任务中心代理掉，完成下载任务
 * 使用要求：
 * 参数中需要有一个参数是downloadcenterbaseparam的子类，方法返回值类型需要是支持泛型的jimupagedto类，方法上加注@usedownloadtaskcenter注解
 * 参考cn.sffix.recovery.report.service.impl.reportserviceimpl#dashboardnewversion(cn.sffix.recovery.report.entity.dto.dashboardquerydto)
 **/
@retention(retentionpolicy.runtime)
@target({elementtype.method})
@documented
public @interface usedownloadtaskcenter {

}

示范给数据接口调用的服务加注解

    @override
    @usedownloadtaskcenter
    public jimupagedto<dashboarddatadto> dashboardnewversion(dashboardquerydto dashboardquerydto) {
    //  你的数据接口逻辑
    }

参数基类

/**
 * @author: humorchen
 * date: 2024/1/15
 * description:
 **/
@data
public class downloadcenterbaseparam {

    /**
     * 分页数据页号和页大小
     */
    private integer pageno;
    /**
     * 分页数据页号和页大小
     */
    private integer pagesize;
}

结果基类

/**
 * @author: humorchen
 * date: 2023/12/19
 * description:
 **/
@data
@fieldnameconstants
@accessors(chain = true)
public class jimupagedto<t> {
    /**
     * 数据
     */
    private list<t> data;
    /**
     * 积木的count是总数据条数，不是当前页多少条！！！
     */
    private long count;
    /**
     * 积木的total是总页数，不是总数据条数！！！
     */
    private long total;

    public static final jimupagedto empty = new jimupagedto().setdata(collections.emptylist()).settotal(0).setcount(0);
}

/**
 * @author: humorchen
 * date: 2024/1/5
 * description: 下载任务状态
 **/
@getter
public enum downloadtaskstateenum {
    wait(0, "等待执行"),
    running(1, "执行中"),
    success(2, "执行成功"),
    failed(3, "执行失败"),

    ;

    private final int state;
    private final string title;

    downloadtaskstateenum(int state, string title) {
        this.state = state;
        this.title = title;
    }

    /**
     * 根据状态获取枚举
     *
     * @param state
     * @return
     */
    public static downloadtaskstateenum of(int state) {
        for (downloadtaskstateenum value : values()) {
            if (value.state == state) {
                return value;
            }
        }
        return null;
    }
}

下载中心服务（报表下载的导出任务）

/**
 * <p>
 * 下载任务 服务类
 * </p>
 *
 * @author humorchen
 * @since 2024-01-05
 */
public interface idownloadtaskservice extends iservice<downloadtask> {
    /**
     * 注册任务
     *
     * @param downloadtask
     * @return
     */
    downloadtask registertask(@nonnull downloadtask downloadtask);

    /**
     * 10秒内是否有相同任务未完成，不给再次注册下载任务
     *
     * @param account
     * @param requestbody
     * @return
     */
    boolean setsametasklock(string account, string requestbody);
    /**
     * 更新任务
     *
     * @param downloadtask
     * @return
     */
    int updatetaskbyid(@nonnull downloadtask downloadtask);

    /**
     * 更新任务进度
     *
     * @param id
     * @param percent
     * @return
     */
    int changetaskpercent(int id, @nonnull string percent);

    /**
     * 更新任务状态
     *
     * @param id
     * @param state
     * @return
     */
    int changetaskstate(int id, @nonnull downloadtaskstateenum state);

    /**
     * 更新任务状态
     *
     * @param id
     * @param expectstate
     * @param targetstate
     * @return
     */
    int compareandswaptaskstate(int id, @nonnull downloadtaskstateenum expectstate, @nonnull downloadtaskstateenum targetstate);
    /**
     * 根据任务id获取任务
     *
     * @param id
     * @return
     */
    downloadtask getdownloadtaskbyid(int id);

    /**
     * 分页查下载任务
     *
     * @param pagelistdownloadtaskdto
     * @return
     */
    ipage<downloadtask> pagelistdownloadtask(pagelistdownloadtaskdto pagelistdownloadtaskdto);

    /**
     * 重新执行下载任务
     *
     * @param taskid
     * @return
     */
    @requestmapping("/reruntask")
    result<string> reruntask(integer taskid);

    /**
     * 根据报表id获取报表名称
     *
     * @param reportid
     * @return
     */
    string getreportnamebyreportid(string reportid);

    /**
     * 从请求体中获取报表id
     *
     * @param requestbody
     * @return
     */
    string getreportidfromrequestbody(string requestbody);

    /**
     * 根据报表id获取报表api地址或者sql
     *
     * @param reportid
     * @return
     */
    jimureportdatasourcedto getreportapiorsqlbyreportid(string reportid);

    /**
     * 获取积木报表的头
     *
     * @param reportid
     * @return
     */
    list<jimureportdatacolumndto> getreporthead(string reportid);

    /**
     * 从积木请求体中获取请求参数
     *
     * @param json
     * @return
     */
    string getrequestparamfromjson(string json);
}

实现类

/**
 * <p>
 * 下载任务 服务实现类
 * </p>
 *
 * @author humorchen
 * @since 2024-01-05
 */
@service
@slf4j
public class downloadtaskserviceimpl extends serviceimpl<downloadtaskmapper, downloadtask> implements idownloadtaskservice {
    @autowired
    private downloadtaskmapper downloadtaskmapper;
    @autowired
    private ireportdatagetservice reportdatagetservice;
    @autowired
    private redistemplate<string, string> redistemplate;

    /**
     * 注入spring 事件发布器
     */
    @autowired
    private applicationeventpublisher eventpublisher;

    /**
     * 注册任务
     *
     * @param downloadtask
     * @return
     */
    @override
    public downloadtask registertask(@nonnull downloadtask downloadtask) {
        downloadtaskmapper.insert(downloadtask);
        return downloadtask;
    }

    /**
     * 10秒内是否有相同任务未完成，不给再次注册下载任务
     *
     * @param account
     * @param requestbody
     * @return
     */
    @override
    public boolean setsametasklock(string account, string requestbody) {
        downloadtasksubmitlimitcachekey limitcachekey = new downloadtasksubmitlimitcachekey(account, md5.create().digesthex(requestbody));
        boolean setifabsent = redistemplate.opsforvalue().setifabsent(limitcachekey.getkey(), dateutil.now(), limitcachekey.getexpire(), limitcachekey.gettimeunit());
        return boolean.true.equals(setifabsent);
    }

    /**
     * 更新任务
     *
     * @param downloadtask
     * @return
     */
    @override
    public int updatetaskbyid(@nonnull downloadtask downloadtask) {
        return downloadtaskmapper.updatebyid(downloadtask);
    }

    /**
     * 更新任务进度
     *
     * @param id
     * @param percent
     * @return
     */
    @override
    public int changetaskpercent(int id, @nonnull string percent) {
        updatewrapper<downloadtask> updatewrapper = new updatewrapper<>();
        updatewrapper.eq(downloadtask.id, id);
        updatewrapper.set(downloadtask.percent, percent);
        log.info("【下载中心】更新任务进度 id:{} percent:{}", id, percent);
        return downloadtaskmapper.update(null, updatewrapper);
    }

    /**
     * 更新任务状态
     *
     * @param id
     * @param state
     * @return
     */
    @override
    public int changetaskstate(int id, @nonnull downloadtaskstateenum state) {
        updatewrapper<downloadtask> updatewrapper = new updatewrapper<>();
        updatewrapper.eq(downloadtask.id, id);
        updatewrapper.set(downloadtask.state, state.getstate());
        return downloadtaskmapper.update(null, updatewrapper);
    }

    /**
     * 更新任务状态
     *
     * @param id
     * @param expectstate
     * @param targetstate
     * @return
     */
    @override
    public int compareandswaptaskstate(int id, @nonnull downloadtaskstateenum expectstate, @nonnull downloadtaskstateenum targetstate) {
        updatewrapper<downloadtask> updatewrapper = new updatewrapper<>();
        updatewrapper.eq(downloadtask.id, id);
        updatewrapper.eq(downloadtask.state, expectstate.getstate());
        updatewrapper.set(downloadtask.state, targetstate.getstate());
        return downloadtaskmapper.update(null, updatewrapper);
    }

    /**
     * 根据任务id获取任务
     *
     * @param id
     * @return
     */
    @override
    public downloadtask getdownloadtaskbyid(int id) {
        return downloadtaskmapper.selectbyid(id);
    }

    /**
     * 查下载任务
     *
     * @param pagelistdownloadtaskdto
     * @return
     */
    @override
    public ipage<downloadtask> pagelistdownloadtask(pagelistdownloadtaskdto pagelistdownloadtaskdto) {
        integer id = pagelistdownloadtaskdto.getid();
        string starttime = pagelistdownloadtaskdto.getstarttime();
        string endtime = pagelistdownloadtaskdto.getendtime();
        string filename = pagelistdownloadtaskdto.getfilename();
        integer taskstate = pagelistdownloadtaskdto.gettaskstate();
        userinfo userinfo = userinfoholder.get();
        string account = userinfo.getaccount();
        int pageno = optional.ofnullable(pagelistdownloadtaskdto.getpageno()).orelse(1);
        int pagesize = optional.ofnullable(pagelistdownloadtaskdto.getpagesize()).orelse(10);

        querywrapper<downloadtask> querywrapper = new querywrapper<>();
        querywrapper.eq(downloadtask.account, account);
        querywrapper.eq(id != null, downloadtask.id, id);
        querywrapper.between(starttime != null && endtime != null, downloadtask.create_time, starttime, endtime);
        querywrapper.like(strutil.isnotblank(filename), downloadtask.title, "%" + filename + "%");
        querywrapper.eq(taskstate != null, downloadtask.state, taskstate);
        // 最新的在前
        querywrapper.orderbydesc(downloadtask.create_time);
        return page(new page<>(pageno, pagesize), querywrapper);
    }

    /**
     * 重新执行下载任务
     *
     * @param taskid
     * @return
     */
    @override
    public result<string> reruntask(integer taskid) {
        downloadtask downloadtask = getdownloadtaskbyid(taskid);
        if (downloadtask == null) {
            return result.fail("未找到该任务，请刷新后重试");
        }
        if (downloadtask.getstate() == downloadtaskstateenum.running.getstate()) {
            return result.fail("任务正在执行中，请稍后重试");
        }
        eventpublisher.publishevent(new downloadtaskpublishevent(taskid));
        return result.ok("重新执行中");
    }

    /**
     * 根据报表id获取报表名称
     *
     * @param reportid
     * @return
     */
    @override
    public string getreportnamebyreportid(string reportid) {
        if (strutil.isblank(reportid)) {
            return "";
        }
        string sql = "select name from report.jimu_report where id = '" + reportid + "'";
        jsonobject jsonobject = reportdatagetservice.getone(sql);
        return optional.ofnullable(jsonobject.getstring("name")).orelse("");
    }

    /**
     * 从请求体中获取报表id
     *
     * @param requestbody
     * @return
     */
    @override
    public string getreportidfromrequestbody(string requestbody) {
        if (strutil.isnotblank(requestbody)) {
            jsonobject jsonobject = jsonobject.parseobject(requestbody);
            return jsonobject.getstring("excelconfigid");
        }
        return null;
    }

    /**
     * 根据报表id获取报表api地址或者sql
     *
     * @param reportid
     * @return
     */
    @override
    public jimureportdatasourcedto getreportapiorsqlbyreportid(string reportid) {
        jimureportdatasourcedto jimureportdatasourcedto = new jimureportdatasourcedto();
        if (strutil.isnotblank(reportid)) {
            string sql = "select db_dyn_sql,api_url from report.jimu_report_db where jimu_report_id = '" + reportid + "'";
            jsonobject jsonobject = reportdatagetservice.getone(sql);
            jimureportdatasourcedto.setsql(jsonobject.getstring("db_dyn_sql"));
            jimureportdatasourcedto.setapiurl(jsonobject.getstring("api_url"));
        }
        list<list<string>> head = new arraylist<>();
        easyexcel.write(new outputstream() {
            @override
            public void write(int b) throws ioexception {

            }
        }).head(head).sheet("sheet").dowrite(new arraylist<>());

        return jimureportdatasourcedto;
    }

    /**
     * 获取积木报表的头
     *
     * @param reportid
     * @return
     */
    @override
    public list<jimureportdatacolumndto> getreporthead(string reportid) {
        if (strutil.isblank(reportid)) {
            return collections.emptylist();
        }
        string sql = "select json_str from report.jimu_report where id = '" + reportid + "'";
        jsonobject jsonobject = reportdatagetservice.getone(sql);
        string jsonstr = jsonobject.getstring("json_str");
        jsonobject json = jsonobject.parseobject(jsonstr);
        jsonobject rows = json.getjsonobject("rows");
        jsonobject rows0cells = rows.getjsonobject("0").getjsonobject("cells");
        jsonobject rows1cells = rows.getjsonobject("1").getjsonobject("cells");

        set<string> rows0keysets = rows0cells.keyset();
        list<jimureportdatacolumndto> heads = rows0keysets.stream().map(key -> {
            jsonobject keyobject = rows0cells.getjsonobject(key);
            jsonobject columnobject = rows1cells.getjsonobject(key);
            if (keyobject == null || columnobject == null) {
                return null;
            }
            string name = keyobject.getstring("text");
            string column = columnobject.getstring("text");
            if (strutil.isblank(name) || strutil.isblank(column)) {
                return null;
            }
            // 处理 #{vpjcgifyua.orderid}
            int indexof = column.lastindexof(".");
            int indexof2 = column.lastindexof("}");
            if (column.startswith("#") && indexof >= 0 && indexof2 >= 0) {
                column = column.substring(indexof + 1, indexof2);
                if (strutil.isblank(column)) {
                    return null;
                }
            }
            jimureportdatacolumndto jimureportdatacolumndto = new jimureportdatacolumndto();
            jimureportdatacolumndto.setname(name);
            jimureportdatacolumndto.setcolumn(column);
            jimureportdatacolumndto.setindex(integer.parseint(key));
            return jimureportdatacolumndto;
        }).filter(objects::nonnull).sorted(comparator.comparing(jimureportdatacolumndto::getindex)).collect(collectors.tolist());
        log.info("【下载中心】获取积木报表的头 reportid:{}，heads:{}", reportid, heads);
        return heads;
    }

    /**
     * 从积木请求体中获取请求参数
     *
     * @param json
     * @return
     */
    @override
    public string getrequestparamfromjson(string json) {
        if (strutil.isnotblank(json)) {
            jsonobject jsonobject = jsonobject.parseobject(json);
            if (jsonobject.containskey("param")) {
                return jsonobject.getjsonobject("param").tojsonstring();
            }
            return "{}";
        }
        return "{}";
    }


}

数据跨库获取工具

服务接口

/**
 * @author: humorchen
 * date: 2023/12/19
 * description: 获取数据服务，直接sql跨库拿数据
 **/
@ds("slave_1")
public interface ireportdatagetservice<t> {
    /**
     * 执行sql返回数据
     *
     * @param sql
     * @return
     */
    jsonobject getone(string sql);

    /**
     * 执行sql返回数据，数据封装到类cls对象里
     *
     * @param sql
     * @param cls
     * @return
     */
    t getone(string sql, class<t> cls);

    /**
     * 执行sql返回数据
     *
     * @param sql
     * @return
     */
    jsonarray getlist(string sql);

    /**
     * 执行sql返回数据，数据封装到类cls对象里
     *
     * @param sql
     * @param cls
     * @return
     */
    list<t> getlist(string sql, class<t> cls);

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @return
     */
    jsonarray pagegetlist(string sql, int page, int pagesize);

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @return
     */
    jimupagedto<jsonobject> pagegetlistforjimu(string sql, int page, int pagesize);

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @param cls
     * @return
     */
    jimupagedto<t> pagegetlistforjimu(string sql, int page, int pagesize, class<t> cls);

    /**
     * 计数
     *
     * @param sql
     * @return
     */
    long count(string sql);


    /**
     * 生成in语句
     *
     * @param columnname
     * @param elements
     * @return string
     */
    default string getcolumninsql(string columnname, list<string> elements) {
        stringbuilder sqlbuilder = new stringbuilder();
        sqlbuilder.append(" ");
        sqlbuilder.append(columnname);
        sqlbuilder.append(" in (");
        for (int i = 0; i < elements.size(); i++) {
            string id = elements.get(i);
            if (i > 0) {
                sqlbuilder.append(",");
            }
            sqlbuilder.append("'");
            sqlbuilder.append(id);
            sqlbuilder.append("'");
        }
        sqlbuilder.append(")");
        return sqlbuilder.tostring();
    }

}

服务实现

/**
 * @author: chenfuxing
 * date: 2023/12/19
 * description:
 **/
@service
@slf4j
public class reportdatagetserviceimpl implements ireportdatagetservice {
    @autowired
    private datasource datasource;


    /**
     * 执行sql返回数据
     *
     * @param sql
     * @return
     */
    @override
    public jsonobject getone(string sql) {
        jsonobject ret = null;
        connection connection = null;
        statement statement = null;
        resultset resultset = null;
        try {
            connection = datasource.getconnection();
            statement = connection.createstatement();
            logsql(sql);
            resultset = statement.executequery(sql);
            if (resultset != null) {
                while (resultset.next()) {
                    if (ret != null) {
                        throw new runtimeexception("查询结果不止一条数据");
                    }
                    ret = new jsonobject();
                    resultsetmetadata metadata = resultset.getmetadata();
                    int columncount = metadata.getcolumncount();
                    for (int i = 1; i <= columncount; i++) {
                        string columnname = metadata.getcolumnlabel(i);
                        ret.put(columnname, resultset.getobject(columnname));
                    }
                }
            }
        } catch (exception e) {
            log.error("获取数据报错", e);
        } finally {
            // 释放资源
            ioutil.close(resultset);
            ioutil.close(statement);
            ioutil.close(connection);
        }
        return ret;
    }

    /**
     * 执行sql返回数据
     *
     * @param sql
     * @return
     */
    @override
    public jsonarray getlist(string sql) {
        jsonarray ret = new jsonarray();
        connection connection = null;
        statement statement = null;
        resultset resultset = null;
        try {
            connection = datasource.getconnection();
            statement = connection.createstatement();
            logsql(sql);
            resultset = statement.executequery(sql);
            if (resultset != null) {
                while (resultset.next()) {
                    // 组装数据为json 对象
                    jsonobject data = new jsonobject();
                    resultsetmetadata metadata = resultset.getmetadata();
                    int columncount = metadata.getcolumncount();
                    for (int i = 1; i <= columncount; i++) {
                        string columnname = metadata.getcolumnlabel(i);
                        data.put(columnname, resultset.getobject(columnname));
                    }
                    ret.add(data);
                }
            }
        } catch (exception e) {
            log.error("获取数据报错", e);
        } finally {
            // 释放资源
            ioutil.close(resultset);
            ioutil.close(statement);
            ioutil.close(connection);
        }
        return ret;
    }

    private void logsql(string sql) {
        int len = 5000;
        // 执行sql
        log.info("执行的sql：{}", strutil.isnotblank(sql) && sql.length() > len ? sql.substring(0, len) : sql);
    }

    /**
     * 计数
     *
     * @param sql
     * @return
     */
    @override
    public long count(string sql) {
        string countsql = getcountsqlfromquerysql(sql);
        if (strutil.isblank(countsql)) {
            throw new runtimeexception("计数语句不得为空，sql为：" + sql);
        }
        long ret = 0;
        connection connection = null;
        statement statement = null;
        resultset resultset = null;
        try {
            connection = datasource.getconnection();
            statement = connection.createstatement();
            logsql(sql);
            resultset = statement.executequery(countsql);
            if (resultset != null) {
                while (resultset.next()) {
                    ret = resultset.getlong(1);
                }
            }
        } catch (exception e) {
            log.error("获取数据报错", e);
        } finally {
            // 释放资源
            if (resultset != null) {
                try {
                    resultset.close();
                } catch (exception ignored) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (exception ignored) {
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (exception ignored) {
                }
            }
        }
        return ret;
    }

    /**
     * 从查询语句变计数语句
     *
     * @param sql
     * @return
     */
    public string getcountsqlfromquerysql(string sql) {
        string selectstr = "select";
        int selectindex = sql.indexof(selectstr);
        int fromindex = sql.indexof("from");
        return sql.replace(sql.substring(selectindex + selectstr.length(), fromindex), " count(*) as c ");
    }

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @return
     */
    @override
    public jsonarray pagegetlist(string sql, int page, int pagesize) {
        string querysql = getpagesqlfromquerysql(sql, page, pagesize);
        if (strutil.isblank(querysql)) {
            throw new runtimeexception("分页查询解析失败，sql：" + sql + " 页号: " + page + " 每页数量：" + pagesize);
        }
        return getlist(querysql);
    }

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @return
     */
    @override
    public jimupagedto<jsonobject> pagegetlistforjimu(string sql, int page, int pagesize) {
        jimupagedto<jsonobject> jimupagedto = new jimupagedto<>();
        // 查count
        long count = count(sql);
        long total = count / pagesize + (count % pagesize > 0 ? 1 : 0);
        log.info("数据总条数：{} 条，每页：{} 条，总页数：{} 页", count, pagesize, total);
        jimupagedto.settotal(total);
        // 查分页数据
        jsonarray data = pagegetlist(sql, page, pagesize);
        list<jsonobject> datalist = new arraylist<>(data.size());
        for (int i = 0; i < data.size(); i++) {
            jsonobject jsonobject = data.getjsonobject(i);
            datalist.add(jsonobject);
        }
        jimupagedto.setdata(datalist);
        jimupagedto.setcount(count);
        return jimupagedto;
    }

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @return
     */
    public string getpagesqlfromquerysql(string sql, int page, int pagesize) {
        assert.istrue(page >= 1, () -> new illegalargumentexception("page不得小于1"));
        assert.istrue(pagesize >= 1, () -> new illegalargumentexception("pagesize不得小于1"));
        int skip = (page - 1) * pagesize;
        stringbuilder builder = new stringbuilder(sql);
        builder.append(" limit ");
        if (skip > 0) {
            builder.append(skip);
            builder.append(",");
        }
        builder.append(pagesize);
        string querysql = builder.tostring();
        log.info("分页查询原sql：{}\n分页sql处理后：{}", sql, querysql);
        return querysql;
    }

    /**
     * 执行sql返回数据，数据封装到类cls对象里
     *
     * @param sql
     * @param cls
     * @return
     */
    @override
    public object getone(string sql, class cls) {
        return getone(sql).tojavaobject(cls);
    }

    /**
     * 执行sql返回数据，数据封装到类cls对象里
     *
     * @param sql
     * @param cls
     * @return
     */
    @override
    public list getlist(string sql, class cls) {
        return getlist(sql).tojavalist(cls);
    }

    /**
     * 分页查询
     *
     * @param sql
     * @param page
     * @param pagesize
     * @param cls
     * @return
     */
    @override
    public jimupagedto pagegetlistforjimu(string sql, int page, int pagesize, class cls) {
        jimupagedto<jsonobject> jimupagedto = pagegetlistforjimu(sql, page, pagesize);
        jimupagedto ret = new jimupagedto<>();
        list list = new arraylist(jimupagedto.getdata().size());
        for (int i = 0; i < jimupagedto.getdata().size(); i++) {
            list.add(jimupagedto.getdata().get(i).tojavaobject(cls));
        }
        ret.setdata(list);
        ret.settotal(jimupagedto.gettotal());
        ret.setcount(jimupagedto.getcount());
        return ret;
    }


}

拦截切面

/**
 * @author: humorchen
 * date: 2024/1/15
 * description: 下载任务切面
 * 对加上了@usedownloadtaskcenter注解的方法进行切面，使用下载任务中心代理掉，完成下载任务
 **/
@aspect
@component
@slf4j
public class downloadtaskaspect {
    @autowired
    private idownloadtaskservice downloadtaskservice;
    /**
     * 注入spring 事件发布器
     */
    @autowired
    private applicationeventpublisher eventpublisher;



    /**
     * 环绕通知
     *
     * @return
     */
    @around("@annotation(cn.sffix.recovery.report.annotations.usedownloadtaskcenter))")
    @order(50)
    public object around(proceedingjoinpoint joinpoint) throws throwable {
        log.info("【下载中心】进入下载中心切面");
        // 是下载中心发的请求则直接执行分页数据
        if (downloadcenterutil.isdownloadcenterrequest()) {
            log.info("【下载中心】下载中心发的请求，直接执行分页数据");
            return joinpoint.proceed();
        }

        // 识别下载请求
        int pageno = 1;
        int pagesize = 20;
        object[] args = joinpoint.getargs();
        if (args != null && args.length > 0) {
            downloadcenterbaseparam downloadcenterbaseparam = null;
            // 找到参数
            for (object arg : args) {
                if (arg instanceof downloadcenterbaseparam) {
                    downloadcenterbaseparam = (downloadcenterbaseparam) arg;
                    break;
                }
            }
            // 检查参数
            if (downloadcenterbaseparam != null) {
                pageno = optional.ofnullable(downloadcenterbaseparam.getpageno()).orelse(pageno);
                pagesize = optional.ofnullable(downloadcenterbaseparam.getpagesize()).orelse(pagesize);
            }
            log.info("【下载中心】下载中心切面，downloadcenterbaseparam:{}", downloadcenterbaseparam);
            if (downloadcenterbaseparam != null) {
                object target = joinpoint.gettarget();
                method method = ((methodsignature) joinpoint.getsignature()).getmethod();
                class<?> returntype = method.getreturntype();
                // 返回值类型检查
                if (returntype.equals(jimupagedto.class)) {
                    // 如果是导出请求，则使用下载任务中心代理掉
                    if (isexportfirstpagerequest(pageno, pagesize)) {
                        // 如果是导出第一页请求，则使用下载任务中心代理掉
                        downloadtask downloadtask = registertask(downloadcenterbaseparam, target, method, args);

                        if (downloadtask == null || downloadtask.getid() == null) {
                            log.error("【下载中心】注册下载任务失败，任务信息：{}", downloadtask);
                            return joinpoint.proceed();
                        }
                        log.info("【下载中心】注册下载任务成功，任务信息：{}", downloadtask);
                        // 返回积木所需要的数据
                        jimupagedto<jsonobject> jimupagedto = new jimupagedto<>();
                        jimupagedto.settotal(0);
                        jimupagedto.setcount(0);
                        jsonobject jsonobject = new jsonobject();
                        string downloadtaskjsonstr = downloadtask.getjson();
                        downloadtaskjson downloadtaskjson = jsonobject.parseobject(downloadtaskjsonstr, downloadtaskjson.class);
                        string requestbody = downloadtaskjson.getrequestbody();
                        string reportid = downloadtaskservice.getreportidfromrequestbody(requestbody);
                        list<jimureportdatacolumndto> reporthead = downloadtaskservice.getreporthead(reportid);
                        log.info("【下载中心】reporthead:{}", reporthead);
                        if (collectionutil.isnotempty(reporthead) && reporthead.size() > 1) {
                            string column = reporthead.get(1).getcolumn();
                            jsonobject.put(column, "请前往报表中台-下载中心查看（任务id " + downloadtask.getid() + "）");
                            log.info("【下载中心】返回数据：{}", jsonobject);
                        } else {
                            log.info("【下载中心】返回数据为空");
                        }
                        list<jsonobject> list = collections.singletonlist(jsonobject);
                        jimupagedto.setdata(list);
                        eventpublisher.publishevent(new downloadtaskpublishevent(downloadtask.getid()));
                        return jimupagedto;
                    } else {
                        log.info("【下载中心】不是导出请求，直接执行分页数据");
                    }
                } else {
                    log.error("【下载中心】返回值类型不是jimupagedto，无法使用下载任务中心代理掉");
                }

            }

        }

        return joinpoint.proceed();
    }

    /**
     * 生成下载任务
     *
     * @param downloadtaskparam
     * @return
     */
    private downloadtask registertask(downloadcenterbaseparam downloadtaskparam, object proxytarget, method method, object[] args) {
        userinfo loginuser = userinfoholder.get();
        string account = loginuser.getaccount();
        httpservletrequest currentrequest = requestutil.getcurrentrequest();
        string requestbody = downloadcenterutil.getrequestbodyfromheader(currentrequest);

        // 防止10秒内重复点击
        if (!downloadtaskservice.setsametasklock(account, requestbody)) {
            log.error("【下载中心】10秒内重复点击，不给再次注册下载任务");
            return null;
        }

        string title = "导出-" + dateutil.now().replace(" ", "_") + ".xlsx";
        try {
            title = downloadtaskservice.getreportnamebyreportid(downloadtaskservice.getreportidfromrequestbody(requestbody)) + title;
        } catch (exception e) {
            log.error("【下载中心】获取报表名称失败", e);
        }
        string url = generatefileurl();
        downloadtask downloadtask = new downloadtask();
        downloadtask.setaccount(account);
        downloadtask.settitle(title);
        downloadtask.seticon("");
        downloadtask.seturl(url);
        downloadtask.setfilesize("");
        downloadtask.setpercent("0%");
        downloadtask.setstate(downloadtaskstateenum.wait.getstate());
        downloadtaskjson downloadtaskjson = new downloadtaskjson();
        // 拷贝最开始请求积木的token和requestbody，执行下载任务时需要
        downloadtaskjson.setrequesttoken(downloadcenterutil.getrequesttokenfromheader(currentrequest));
        downloadtaskjson.setrequestbody(requestbody);
        downloadtaskjson.setproxymethod(method.getname());
        if (args != null) {
            for (object arg : args) {
                if (arg instanceof downloadcenterbaseparam) {
                    downloadtaskjson.setparam((downloadcenterbaseparam) arg);
                    break;
                }
            }
        }
        downloadtask.setjson(jsonobject.tojsonstring(downloadtaskjson));

        downloadtask = downloadtaskservice.registertask(downloadtask);
        return downloadtask;
    }

    /**
     * 生成文件url地址
     *
     * @return
     */
    private string generatefileurl() {
        // todo 生成文件url地址
        return "";
    }

    /**
     * 注入jm report分页大小
     */
    @value("${jeecg.jmreport.page-size-number:5000}")
    private int jmreportpagesizenumber;

    /**
     * 判断是否为导出请求
     *
     * @param pageno
     * @param pagesize
     * @return
     */
    private boolean isexportpagerequest(int pageno, int pagesize) {
        return pagesize == jmreportpagesizenumber;
    }

    /**
     * 判断是否为导出请求
     *
     * @param pageno
     * @param pagesize
     * @return
     */
    private boolean isexportfirstpagerequest(int pageno, int pagesize) {
        log.info("【下载中心】判断是否为导出请求 isexportfirstpagerequest pageno:{},pagesize:{}，积木报表导出size：{}", pageno, pagesize, jmreportpagesizenumber);
        return pageno == 1 && isexportpagerequest(pageno, pagesize);
    }
}

报表下载任务处理事件

/**
 * @author: humorchen
 * date: 2024/1/16
 * description: 下载任务发布事件
 **/
@getter
public class downloadtaskpublishevent extends applicationevent {
    /**
     * 任务id
     */
    private final integer taskid;

    public downloadtaskpublishevent(integer taskid) {
        super(taskid);
        this.taskid = taskid;
    }
    
}

处理器

/**
 * @author: humorchen
 * date: 2024/1/16
 * description: 处理下载任务发布事件
 **/
@component
@slf4j
public class downloadtaskpublisheventhandler implements applicationlistener<downloadtaskpublishevent> {
    @autowired
    private idownloadtaskservice downloadtaskservice;
    @autowired
    private ireportservice reportservice;
    @autowired
    @qualifier(asyncconfig.async_thread_pool)
    private executor executor;

    @autowired
    private hwproperties hwproperties;
    @autowired
    private envutil envutil;

    /**
     * 注入jm report分页大小
     */
    @value("${jeecg.jmreport.page-size-number:5000}")
    private int jmreportpagesizenumber;

    @autowired
    private redissonclient redissonclient;


    /**
     * 超时时间
     */
    private static final int timeout_mills = 1000 * 60 * 20;

    @autowired
    private hwobjectstorageservice hwobjectstorageservice;


    /**
     * 上传文件到oss的路径
     *
     * @param account
     * @param filename
     * @return
     */
    private final storagepath getstoragepath(string account, string filename) {
        return storagepath.path().addfolder("downloadtask").addfolder(account).filename(filename);
    }

    /**
     * handle an application event.
     *
     * @param event the event to respond to
     */
    @override
    @async(asyncconfig.async_thread_pool)
    public void onapplicationevent(downloadtaskpublishevent event) {
        integer taskid = event.gettaskid();

        log.info("【下载中心】执行下载任务 taskid:{}", taskid);
        downloadtask downloadtask = downloadtaskservice.getbyid(taskid);
        if (downloadtask == null) {
            log.error("【下载中心】下载任务不存在，taskid:{}", taskid);
            return;
        }
        if (downloadtask.getstate() == downloadtaskstateenum.running.getstate()) {
            log.error("【下载中心】下载任务正在执行中，taskid:{}", taskid);
            return;
        }

        try {
            log.info("【下载中心】下载任务开始执行，taskid:{}", taskid);
            // 改状态到执行中
            downloadtaskstateenum downloadtaskstateenum = optional.ofnullable(downloadtaskstateenum.of(downloadtask.getstate())).orelse(downloadtaskstateenum.wait);
            int compareandswaptaskstate = downloadtaskservice.compareandswaptaskstate(taskid, downloadtaskstateenum, downloadtaskstateenum.running);
            if (compareandswaptaskstate < 1) {
                log.info("【下载中心】下载任务状态不对，taskid:{}, state:{}", taskid, downloadtaskstateenum);
                return;
            }
            downloadtaskjson downloadtaskjson = jsonobject.parseobject(downloadtask.getjson(), downloadtaskjson.class);

            // 获取数据
            string requestbody = downloadtaskjson.getrequestbody();
            string requesttoken = downloadtaskjson.getrequesttoken();
            string reportid = downloadtaskservice.getreportidfromrequestbody(requestbody);
            string reportname = downloadtaskservice.getreportnamebyreportid(reportid);
            string requestparam = downloadtaskservice.getrequestparamfromjson(downloadtask.getjson());
            jimureportdatasourcedto datasourcedto = downloadtaskservice.getreportapiorsqlbyreportid(reportid);
            list<jimureportdatacolumndto> reporthead = downloadtaskservice.getreporthead(reportid);
            // 打印上面拿到的数据
            log.info("reportid ：{} \n reportname：{} \n requestparam：{} \n requestbody:{}  \n datasourcedto：{} \n reporthead：{}", reportid, reportname, requestparam, requestbody, datasourcedto, reporthead);
            jimureportdynamiceasyexcelimpl jimureportdynamiceasyexcel = new jimureportdynamiceasyexcelimpl(reportid, reportname, taskid, downloadtaskservice, requestparam, requesttoken, datasourcedto, reporthead);
            // 生成excel文件
            list<list<string>> head = reporthead.stream().map(d -> collections.singletonlist(d.getname())).collect(collectors.tolist());
            // 分页写数据
            inputstream inputstream = dynamiccolumneasyexcelutil.writepagedata(head, jimureportdynamiceasyexcel, jmreportpagesizenumber);



            // 上传excel文件到oss
            storagepath storagepath = getstoragepath(downloadtask.getaccount(), downloadtask.gettitle());
            downloadtask.setpercent("100%");
            stopwatch stopwatch = new stopwatch();
            stopwatch.start();
            uri uri = hwobjectstorageservice.savepublicfile(inputstream, storagepath);
            stopwatch.stop();
            log.info("【下载中心】上传文件到oss，耗时：{} ms，uri：{}", stopwatch.getlasttasktimemillis(), uri);
            // 更新任务信息
            string url = geturlprefix() + uri.getpath();
            downloadtask.seturl(url);
            downloadtask.setstate(downloadtaskstateenum.success.getstate());
            log.info("【下载中心】下载任务成功，taskid:{}，task：{}", taskid, downloadtask);
            boolean updated = downloadtaskservice.updatebyid(downloadtask);
            log.info("【下载中心】下载任务更新结果，taskid:{}, updated:{}", taskid, updated);
        } catch (exception e) {
            log.error("【下载中心】下载任务执行失败", e);
            // 更新任务信息
            downloadtask.setstate(downloadtaskstateenum.failed.getstate());
            downloadtask.seterror("【下载中心】执行失败（" + e.getmessage() + "）");
            boolean updated = downloadtaskservice.updatebyid(downloadtask);
            log.info("【下载中心】下载任务更新结果，taskid:{}, updated:{}", taskid, updated);
        } finally {
            log.info("【下载中心】下载任务 {} 执行完毕", taskid);
        }


    }

    /**
     * 根据环境获取文件url前缀
     *
     * @return
     */
    private string geturlprefix() {
        string envcode = envutil.ispreorprodenv() ? "pro" : "test";
        string prefix = "https://test-obs.xxxxx.com";
        for (string key : hwproperties.geturlmap().keyset()) {
            if (key.contains(envcode)) {
                prefix = hwproperties.geturlmap().get(key);
            }
        }
        return prefix;
    }

}

透传信息

主要是在这个customapiheader() 方法里downloadcenterutil.copydownloadcenterheader(request, header);自定义获取数据请求的header这，读取这次积木导出请求的参数信息，写到获取数据请求的header里去

/**
 * 自定义积木报表鉴权(如果不进行自定义，则所有请求不做权限控制)
 * 1.自定义获取登录token
 * 2.自定义获取登录用户
 */
@slf4j
@component
public class jimureporttokenservice implements jmreporttokenservicei {

    @autowired
    @lazy
    private userautenticationfeign userautenticationfeign;

    @autowired
    @lazy
    private userinfofeign userinfofeign;

    /**
     * 通过请求获取token
     * @param request
     * @return
     */
    @override
    public string gettoken(httpservletrequest request) {
        string token = request.getparameter("token");
        if (token == null) {
            token = request.getheader("x-access-token");
        }
        if (token == null) {
            token = request.getheader("token");
        }
        if (token == null) {
            token = request.getheader("token");
        }
        return token;
    }

    /**
     * 通过token获取登录人用户名
     * @param token
     * @return
     */
    @override
    public string getusername(string token) {
        usertokendto tokeninfo = new usertokendto();
        tokeninfo.settoken(token);
        result<customerinfodto> customerinfodtoresult = userinfofeign.customerinfo(tokeninfo);
        customerinfodto data = customerinfodtoresult.getdata();
        if(data != null){
            fxuserinfodto userinfo = jsonobject.parseobject(jsonobject.tojsonstring(data.getbase()), fxuserinfodto.class);
            if(userinfo == null){
                throw new runtimeexception("找不到相应平台用户信息");
            } else {
                // 写到上下文
                userinfo user = new userinfo();
                beanutils.copyproperties(userinfo, user);
                userinfoholder.set(user);
                log.info("成功将用户信息写入上下文");
            }
            if(userinfo.getclienttype() != platformtypeenum.fx.name()){
                return userinfo.getname();
            }else{
                throw new runtimeexception("平台类型不支持");
            }
        }else {
            throw new runtimeexception("用户不存在");
        }
    }

    /**
     * 自定义用户拥有的角色
     *
     * @param token
     * @return
     */
    @override
    public string[] getroles(string token) {
        return new string[]{"admin"};
    }

    /**
     * token校验
     * @param token
     * @return
     */
    @override
    public boolean verifytoken(string token) {
        servletrequestattributes requestattributes = (servletrequestattributes) requestcontextholder.getrequestattributes();
        httpservletrequest request = requestattributes.getrequest();
        request.setattribute(useraccountconstant.token,token);
        try {
            usertokendto tokeninfo = new usertokendto();
            tokeninfo.settoken(token);
            result<customerinfodto> customerinfodtoresult = userinfofeign.customerinfo(tokeninfo);

            return customerinfodtoresult.getdata() != null;
        }catch (exception e){
            log.error("校验token异常:" + e.getmessage());
            return false;
        }
    }

    /**
     *  自定义请求头
     * @return
     */
    @override
    public httpheaders customapiheader() {
        servletrequestattributes requestattributes = (servletrequestattributes) requestcontextholder.getrequestattributes();
        httpheaders header = new httpheaders();
        header.add("custom-header1", "please set a custom value 1");
        if (requestattributes != null) {
            httpservletrequest request = requestattributes.getrequest();
            header.set("authorization", gettoken(request));
            header.set("token", gettoken(request));
            // 拷贝请求过去
            downloadcenterutil.copydownloadcenterheader(request, header);
            // 如果是下载中心发起的请求，设置请求头
            if (downloadcenterutil.isdownloadcenterrequest(requestattributes.getrequest())) {
                downloadcenterutil.setdownloadcenterheaderrequest(header);
            }
        }

        return header;
    }
}

下载中心工具类

/**
 * @author: humorchen
 * date: 2024/1/18
 * description: 下载中心工具类
 **/
@slf4j
public class downloadcenterutil {
    private static final string download_center_header_request = "download-center-request";

    /**
     * 是否为下载中心发起的请求
     *
     * @return
     */
    public static boolean isdownloadcenterrequest() {
        servletrequestattributes requestattributes = (servletrequestattributes) requestcontextholder.getrequestattributes();
        if (requestattributes == null) {
            log.debug("downloadcenterutil#isdownloadcenterrequest requestattributes is null");
            return false;
        }
        return isdownloadcenterrequest(requestattributes.getrequest());
    }

    /**
     * 是否为下载中心发起的请求
     *
     * @param servletrequest
     * @return
     */
    public static boolean isdownloadcenterrequest(httpservletrequest servletrequest) {
        return servletrequest != null && strutil.isnotblank(servletrequest.getheader(download_center_header_request));
    }

    /**
     * 设置下载中心请求头
     *
     * @param headers
     */
    public static void setdownloadcenterheaderrequest(httpheaders headers) {
        if (headers != null) {
            headers.set(download_center_header_request, "true");
        }
    }

    /**
     * 复制下载中心请求头
     *
     * @param request
     * @param headers
     */
    public static void copydownloadcenterheader(httpservletrequest request, httpheaders headers) {
        if (request == null || headers == null) {
            return;
        }
        // 复制request请求里的token请求头
        string token = request.getheader("token");
        if (strutil.isnotblank(token)) {
            headers.set(downloadtaskjson.fields.requesttoken, token);
        }
        // 复制request请求里的请求体
        headers.set(downloadtaskjson.fields.requestbody, requestutil.getrequestbody(request));
    }

    /**
     * 获取下载请求头token
     *
     * @param request
     * @return
     */
    public static string getrequesttokenfromheader(httpservletrequest request) {
        if (request == null) {
            return null;
        }
        return request.getheader(downloadtaskjson.fields.requesttoken);
    }

    /**
     * 获取下载请求头
     *
     * @param request
     * @return
     */
    public static string getrequestbodyfromheader(httpservletrequest request) {
        if (request == null) {
            return null;
        }
        return request.getheader(downloadtaskjson.fields.requestbody);
    }
    /**
     * 设置下载中心请求头
     *
     * @param request
     */
    public static void setdownloadcenterheaderrequest(httprequest request) {
        if (request != null) {
            request.header(download_center_header_request, "true");
        }
    }


    /**
     * 获取带参数的url
     *
     * @param url
     * @param params
     * @return
     */
    public static string geturlwithparams(string url, jsonobject params) {
        if (strutil.isblank(url) || params == null) {
            return url;
        }
        stringbuilder sb = new stringbuilder(url);
        if (url.contains("?")) {
            sb.append("&");
        } else {
            sb.append("?");
        }
        for (string key : params.keyset()) {
            sb.append(key).append("=").append(params.getstring(key)).append("&");
        }
        return sb.substring(0, sb.length() - 1);
    }

}

异步线程池

/**
 * @author: humorchen
 * date: 2024/1/16
 * description: 异步配置
 **/
@slf4j
@configuration
public class asyncconfig {
    public static final string async_thread_pool = "asyncthreadpool";

    /**
     * 异步线程池
     */
    @bean(name = async_thread_pool)
    public executor asyncexecutor() {
        threadpooltaskexecutor executor = new threadpooltaskexecutor();
        executor.setcorepoolsize(8);
        executor.setmaxpoolsize(64);
        executor.setqueuecapacity(128);
        executor.setthreadnameprefix("asyncthreadpool-");
        executor.initialize();
        return executor;

    }
}

给前端暴露接口

/**
 * @author humorchen
 * date: 2024/2/28
 * description: 下载中心api
 **/

@requestmapping("/report/form/downloadcenter")
public interface downloadcenterapi {


    /**
     * 分页查下载任务
     *
     * @param pagelistdownloadtaskdto
     * @return
     */
    @requestmapping("/pagelistdownloadtask")
    result<ipage<downloadtaskvo>> pagelistdownloadtask(pagelistdownloadtaskdto pagelistdownloadtaskdto);


    /**
     * 删除下载任务
     *
     * @param taskid
     * @return
     */
    @requestmapping("/deletetask")
    result<string> deletetask(integer taskid);

    /**
     * 重新执行下载任务
     *
     * @param taskid
     * @return
     */
    @requestmapping("/reruntask")
    result<string> reruntask(integer taskid);
}

/**
 * @author humorchen
 * date: 2024/2/28
 * description: 下载中心
 **/
@restcontroller
public class downloadcentercontroller implements downloadcenterapi {
    @autowired
    private idownloadtaskservice downloadtaskservice;


    /**
     * 分页查下载任务
     *
     * @param pagelistdownloadtaskdto
     * @return
     */
    @override
    public result<ipage<downloadtaskvo>> pagelistdownloadtask(pagelistdownloadtaskdto pagelistdownloadtaskdto) {
        ipage<downloadtask> downloadtaskpages = downloadtaskservice.pagelistdownloadtask(pagelistdownloadtaskdto);
        page<downloadtaskvo> downloadtaskvopage = new page<>();
        downloadtaskvopage.setcurrent(downloadtaskpages.getcurrent());
        downloadtaskvopage.setpages(downloadtaskpages.getpages());
        downloadtaskvopage.setsize(downloadtaskpages.getsize());
        downloadtaskvopage.settotal(downloadtaskpages.gettotal());
        list<downloadtaskvo> downloadtaskvos = downloadtaskpages.getrecords().stream().map(downloadtask -> {
            downloadtaskvo downloadtaskvo = beanutils.convert(downloadtask, downloadtaskvo.class);
            downloadtaskvo.setstatestr(optional.ofnullable(downloadtaskstateenum.of(downloadtask.getstate())).orelse(downloadtaskstateenum.wait).gettitle());
            return downloadtaskvo;
        }).collect(collectors.tolist());
        downloadtaskvopage.setrecords(downloadtaskvos);

        return result.ok(downloadtaskvopage);
    }

    /**
     * 删除下载任务
     *
     * @param taskid
     * @return
     */
    @override
    public result<string> deletetask(integer taskid) {
        boolean removebyid = downloadtaskservice.removebyid(taskid);

        return removebyid ? result.ok("删除成功") : result.fail("未找到该任务，请刷新后重试）");
    }

    /**
     * 重新执行下载任务
     *
     * @param taskid
     * @return
     */
    @override
    public result<string> reruntask(integer taskid) {
        return downloadtaskservice.reruntask(taskid);
    }
}

dynamiccolumneasyexcelutil 动态字段easyexcel工具

/**
 * @author humorchen
 * date: 2024/3/5
 * description: 动态列easyexcel工具类
 **/
@slf4j
public class dynamiccolumneasyexcelutil {
    public static final string row = "=row()";
    public static interface dynamiccolumneasyexcelinterface<t> {
        /**
         * 分页获取数据
         *
         * @param page
         * @param size
         * @return
         */
        jimupagedto<t> pagegetdata(int page, int size);

        /**
         * 数据对象转换为字符串
         *
         * @param t
         * @return
         */
        list<string> mapdatatostringlist(t t);

        /**
         * 分页获取数据加载第i页时触发函数，用于实现进度变更
         *
         * @param pageno
         * @param pagesize
         */
        void onloadedpage(int pageno, int pagesize, int pages);
    }
 /**
     * 从数据库分页读数据并写入成excel文件，把文件内容写到输出流
     *
     * @param head
     * @param dynamiccolumneasyexcelinterface
     * @param pagesize
     * @param <t>
     * @return
     */
    public static <t> bytearrayinputstream writepagedata(list<list<string>> head, dynamiccolumneasyexcelinterface<t> dynamiccolumneasyexcelinterface, int pagesize) {
        bytearrayoutputstream outputstream = new bytearrayoutputstream();
        excelwriter excelwriter = easyexcel.write(outputstream).head(head).build();
        // 当前分页
        int currentpage = 1;
        // 总页数
        long pages = 1;
        // 当前写入的sheet页
        int sheetno = 1;
        // 写入计数（用于自动翻sheet页）
        int writerowcount = 0;
        // 积木序号编号
        int index = 1;
        stopwatch stopwatch = new stopwatch("报表分页写入excel");
        writesheet sheet = getwritesheet(sheetno);

        do {
            // 加载数据
            stopwatch.start("加载第" + currentpage + "页数据");
            jimupagedto<t> jimupagedto = dynamiccolumneasyexcelinterface.pagegetdata(currentpage, pagesize);
            stopwatch.stop();
            // 数据判空
            list<t> records = jimupagedto.getdata();
            if (collectionutil.isempty(records)) {
                break;
            }
            // 转换数据
            stopwatch.start("转换第" + currentpage + "页数据");
            list<list<string>> data = records.stream().map(dynamiccolumneasyexcelinterface::mapdatatostringlist).collect(collectors.tolist());
            stopwatch.stop();
            // 处理序号 row()
            if (collectionutil.isnotempty(data) && collectionutil.isnotempty(data.get(0)) && row.equals(data.get(0).get(0))) {
                for (list<string> stringlist : data) {
                    if (collectionutil.isnotempty(stringlist) && row.equals(stringlist.get(0))) {
                        stringlist.set(0, string.valueof(index));
                        ++index;
                    }
                }
            }
            // 自动跳sheet页
            if (writerowcount + data.size() >= max_size_per_sheet) {
                ++sheetno;
                writerowcount = 0;
                index = 1;
                sheet = getwritesheet(sheetno);
            }
            // 写入数据
            stopwatch.start("写入第" + currentpage + "页数据（" + data.size() + "条数据）");
            excelwriter.write(data, sheet);
            stopwatch.stop();

            pages = jimupagedto.gettotal();
            // 更新进度
            dynamiccolumneasyexcelinterface.onloadedpage(currentpage, pagesize, (int) pages);

            log.info("【下载中心】 分页获取数据，第{}页，总页数：{} 第一行数据是：{}", currentpage, pages, data.get(0));
            // 自增
            currentpage++;
            writerowcount += data.size();
        } while (currentpage <= pages);
        log.info("【下载中心】 耗时打印");
        for (stopwatch.taskinfo taskinfo : stopwatch.gettaskinfo()) {
            log.info("【下载中心】 {} 耗时：{} ms", taskinfo.gettaskname(), taskinfo.gettimemillis());
        }
        excelwriter.finish();


        return new bytearrayinputstream(outputstream.tobytearray());
    }

    /**
     * 生成writesheet对象
     *
     * @param sheetno 1开始
     * @return writesheet
     */

    private static writesheet getwritesheet(int sheetno) {
        writesheet sheet = new writesheet();
        sheet.setsheetname("sheet" + sheetno);
        sheet.setsheetno(sheetno - 1);
        return sheet;
    }
    /**
     * 获取字段宽度策略
     *
     * @return
     */
    private abstractheadcolumnwidthstylestrategy getabstractcolumnwidthstylestrategy() {
        return new abstractheadcolumnwidthstylestrategy() {
            /**
             * returns the column width corresponding to each column head.
             *
             * <p>
             * if return null, ignore
             *
             * @param head        nullable.
             * @param columnindex not null.
             * @return
             */
            @override
            protected integer columnwidth(head head, integer columnindex) {
                return null;
            }
        };
    }

}