优雅处理大量数据导出：easyPoi重构指南

优雅处理大量数据导出：easyPoi重构指南

1. 问题分析

在大规模数据导出中，存在以下问题：

• 代码冗余： 传统的导出代码可能包含大量样板代码，使得代码冗余度高。
• 可读性差： 大量重复的导出逻辑使代码难以理解和维护。
• 导出速度慢： 未优化的导出代码可能导致性能问题，尤其在处理大量数据时。

2. 引入easyPoi依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.4.0</version>
</dependency>

3. easyPoi重构步骤

4.1 重构工具类代码

package com.ruoyi.common.core.utils;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelBatchExportService;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ExcelExportServiceProcessor extends ExcelBatchExportService {

    private Workbook workbook;
    private Sheet sheet;
    private List<ExcelExportEntity> excelParams;
    private ExportParams entity;
    private int titleHeight;
    private Drawing patriarch;
    private short rowHeight;
    private volatile int index;
    private ExecutorService executorService;

    // 默认线程池大小为10
    private static final int DEFAULT_POOL_SIZE = 10;

    public ExcelExportServiceProcessor() {
        this.executorService = Executors.newFixedThreadPool(DEFAULT_POOL_SIZE);
    }

    /**
     * 带参数的构造方法
     *
     * @param poolSize 线程池大小
     */
    public ExcelExportServiceProcessor(int poolSize) {
        this.executorService = Executors.newFixedThreadPool(poolSize > 0 ? poolSize : DEFAULT_POOL_SIZE);
    }

    /**
     * 获取Workbook对象
     *
     * @return Workbook对象
     */
    public Workbook getWorkbook() {
        return this.workbook;
    }

    /**
     * 初始化并导出大数据Excel文件
     *
     * @param entity       ExportParams对象
     * @param server       IExcelExportServerPostProcessor对象
     * @param queryParams  查询参数
     * @param <T>          泛型类型
     * @return Workbook对象
     */
    public <T> Workbook initExportBigExcel(ExportParams entity, IExcelExportServerPostProcessor server, Object queryParams) {
        int page = 1;
        int pageAfter = page + 1;
        int i = 1;
        List<CompletableFuture<Void>> futureList = new ArrayList<>();

        // 循环从数据库获取数据，直到获取到空数据为止
        while (true) {
            List<T> list = server.selectListForExcelExportForT(queryParams, page);
            if (list == null || list.isEmpty()) {
                break;
            }

            List<T> finalList = list;
            if (i == 1 && finalList != null && !finalList.isEmpty()) {
                init(entity, finalList.get(0).getClass());
            }

            // 多线程异步写入excel
            CompletableFuture<Void> future = CompletableFuture.runAsync(() -> writeData(finalList), executorService);
            futureList.add(future);
            i++;

            // 如果有停止标志，跳出循环
            if (queryParams instanceof Map) {
                Map<String, Object> map = (Map<String, Object>) queryParams;
                Object isPageStop = map.get("isPageStop");
                if (isPageStop != null) {
                    break;
                }
            }
            page = pageAfter++;
        }

        // 等待异步线程结束
        CompletableFuture.allOf(futureList.toArray(new CompletableFuture[futureList.size()])).join();
        return this.close();
    }

    /**
     * 写入数据的异步任务
     *
     * @param data 数据
     */
    private void writeData(List<?> data) {
        this.index += this.createCells(this.patriarch, this.index, data, this.excelParams, this.sheet, this.workbook, this.rowHeight, 0)[0];
    }

    /**
     * 关闭Excel文件
     *
     * @return Workbook对象
     */
    public Workbook close() {
        if (this.entity.getFreezeCol() != 0) {
            this.sheet.createFreezePane(this.entity.getFreezeCol(), this.titleHeight, this.entity.getFreezeCol(), this.titleHeight);
        }

        this.mergeCells(this.sheet, this.excelParams, this.titleHeight);
        this.addStatisticsRow(this.getExcelExportStyler().getStyles(true, (ExcelExportEntity) null), this.sheet);
        executorService.shutdownNow(); // 关闭线程池，尝试停止所有执行中的线程
        return this.workbook;
    }
}



    

4.2 创建工具类代码

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelBatchExportService;
import cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import java.util.Collection;
import java.util.List;
import java.util.Map;

public class ExcelExportUtil {

    private ExcelExportUtil() {
    }
    /**
     * easypoi导出大数据量数据
     * @param entity
     * @param server
     * @param queryParams
     * @return
     */
    public static Workbook exportBigExcel(ExportParams entity, IExcelExportServerPostProcessor server, Object queryParams) {
        ExcelBatchExportServicePostProcessor batchServer = new ExcelBatchExportServicePostProcessor();
        return batchServer.initExportBigExcel(entity,server,queryParams);
    }
}

4.2 调用实现

4.2.1 HighDeptInAndOutController

@PostMapping("/export")
    public void export(HttpServletResponse response, @RequestParam Map<String,Object> params) throws IOException
    {
        params.keySet().removeIf(key->"pageSize,pageNum".indexOf(key) != -1);
        highDeptInAndOutService.export(response,params);
    }

4.2.1 IHighDeptInAndOutService

    /**
     * 导出
     * @param response
     * @param params
     */
    void export(HttpServletResponse response, Map<String, Object> params);

4.2.2 接口实现

@Override
    public void export(HttpServletResponse response, Map<String, Object> params) {
        int pageSizeSelf = 50000;
        ExportParams exportParams = new ExportParams("我开着法拉利", "我开着法拉利", ExcelType.XSSF);
        Workbook workbook = null;
        ServletOutputStream outputStream = null;
        workbook = ExcelExportCustomUtil.exportBigExcel(exportParams, new IExcelExportServerPostProcessor() {
            @Override
            public <T, U> List<T> selectListForExcelExportForT(U queryParams, int pageNum) {
                List<T> tList = exportTask(pageNum, pageSizeSelf, (Map<String, Object>) queryParams);
                //如果查询结果为空或者查询结果小于每页条数，则停止分页 这个使用的盘空是
                //hutool 工具类有需要自行引用
                if (CollectionUtils.isEmpty(tList) || tList.size() <pageSizeSelf) {
                    ((Map<String, Object>) queryParams).put("isPageStop", true);
                }
                return tList;
            }
        }, params);
        try {
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("高值科室进销存.xls", "UTF-8"));
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        } catch (IOException e) {
            throw new CustomException("导出失败", e.getMessage());

        }
    }

4.2.3 查询数据方法

  public <T> List<T> exportTask(int pageNum, int pageSize, Map<String, Object> params) {
        int offset = (pageNum - 1) *pageSize;
        //com.github.pagehelper:pagehelper 分页插件自行引用或者这个改为MyBatis-Plus自行更改
        PageHelper.offsetPage(offset, pageSize,false);
        List<HighDeptInAndOut> list = highDeptInAndOutMapper.exportList(params);
        return (List<T>)list;
    }

5. 总结

这个代码导出速度比程序员喝咖啡的速度还快。这个指南就像是easyPoi的炸鸡秘籍，让你在Excel的厨房里煮出美味的数据料理！实体类代码？那就像是我的“独家秘方”，不发，如果你有更酷的优化，别吝啬，大声说出来，我们一起玩转这个代码美食世界！有问题？滴滴作者，我们等着你的疑问，就像是等待新一季剧集的粉丝一样。如果有更好的优化，就像是技术男们的大杀器，不要藏着掖着，拿出来让大家都看看吧！