基于EasyExcel实现的分页数据下载封装

功能概述

主要实现的功能:
1.分页查询,避免一次性查询全部数据加载到内存引起频繁FULL GC甚至OOM
2.当数据量超过单个工作簿最大行数(1048575)时,自动将数据写入新的工作簿
3.支持百万级数据量导出

具体实现

第一,定义数据实体父类和分页对象。

// 数据实体父类
public abstract class DataEntity {}

// 分页对象
@Data
public class Page<T> {
    /** 分页参数,第几页 */
    protected int pageNo;

    /** 分页参数,当前页数量 */
    protected int pageSize;

    /** 数据列表 */
    protected List<T> list;

    /** 数据总量 */
    protected long count;

    public Page(int pageNo, int pageSize) {
        this.pageNo = pageNo;
        this.pageSize = pageSize;
    }
}

第二,既然是分页查询导出,就需要一个实现分页查询的接口。

// 数据分页查询接口
public interface PageQuerier<T> {
    /**
     * 查询指定页数据,同时会查询总数
     *
     * @param page
     * @param entity
     * @return
     */
    Page<T> findPage(Page<T> page, DataEntity entity);
    /**
     * 查询指定页数据,只查询数据,不查询总数
     *
     * @param page
     * @param entity
     * @return
     */
    List<T> findList(Page<T> page, DataEntity entity);
}

第三,封装分页查询并导出数据的核心服务类。

// 分页导出Excel
public class ExcelExporter<T> {
    /** 实现分页查询 */
    private PageQuerier<T> pageQuerier;
    
    /**
     * @param pageQuerier 分页查询服务
     */
    public ExcelExporter(PageQuerier<T> pageQuerier) {
        this.pageQuerier = pageQuerier;
    }
    /**
     * 分页查询数据并写入到下载输出流
     *
     * @param response
     * @param fileName 文件名,如:测试.xlsx
     * @param header  表头映射类
     * @param entity 数据实体对象
     * @throws IOException
     */
    public void write(HttpServletResponse response, String fileName, Class header, DataEntity entity) throws IOException {
        if (header == null) {
            header = entity.getClass();
        }
        // 内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        HorizontalCellStyleStrategy contentStyleStrategy = new HorizontalCellStyleStrategy();
        contentStyleStrategy.setContentWriteCellStyleList(Arrays.asList(new WriteCellStyle[]{contentWriteCellStyle}));
        // 一定要对文件名做URL编码,否则会出现中文乱码
        String file = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
        response.reset();
        response.addHeader("Content-Disposition", "attachment;filename=" + file);
        response.addHeader("filename", file);
        response.setContentType("application/octet-stream; charset=UTF-8");
        ExcelWriter writer = new ExcelWriterBuilder()
                .autoCloseStream(true)
                .automaticMergeHead(true) // 这里一定要设置为true,否则在无法实现表头列合并
                .excelType(ExcelTypeEnum.XLSX)
                .file(response.getOutputStream())
                .head(header)
                .registerWriteHandler(contentStyleStrategy)
                .build();
        // xlsx文件单个工作簿上限是1048575行,这里如果超过104W需要分Sheet
        long sheetMax = 1048575;
        // 每一个工作簿可写入的数据量,需要把表头所占的行算上
        long sheetTotal = 1;
        // 工作簿编号
        int sheetNum = 1;
        // 分页数
        int pageNo = 1;
        // 每页查询数量
        int pageSize = 50000;
        WriteSheet writeSheet = EasyExcel.writerSheet("sheet"+sheetNum).head(header).build();
        // 先查询第一页,得到总数
        Page<T> page = this.pageQuerier.findPage(new Page(pageNo, pageSize), entity);
        writer.write(page.getList(), writeSheet);
        if (page.getCount() <= 0 || page.getCount() <= pageSize) {
            // 没有数据或只有一页数据
            writer.finish();
            return;
        }
        sheetTotal += page.getList().size();
        long total = page.getCount();
        long fetch = page.getList().size();
        for (;;) {
            // 只查询分页数据,不查询总数
            pageNo++;
            List<T> list = this.pageQuerier.findList(new Page(pageNo, pageSize), entity);
            fetch += list.size();
            sheetTotal += list.size();
            if (sheetTotal >= sheetMax) {
                sheetTotal = 1 + list.size();
                sheetNum++;
                writeSheet = EasyExcel.writerSheet("sheet"+sheetNum).head(header).build();
            }
            writer.write(list, writeSheet);
            if (fetch >= total) {
                writer.finish();
                break;
            }
        }
        writer.finish();
    }
}

可以在需要导出的数据封装实体中使用EasyExcel的注解实现样式设置:

@Data
@Builder
public class SubjectExport extends DataEntity {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("年龄")
    private int age;
    @ExcelProperty("住址")
    private String addr;
    @ExcelProperty("身份ID")
    private int number;
}

完成上述核心接口和类的定义之后,就可以调用封装好的分页查询和导出服务了。

// 以分页查询数据库方式导出百万级数据
@RequestMapping("/downloadPage")
public String downloadByPage(HttpServletRequest request, HttpServletResponse response,
                                @RequestParam("name") String name,
                                @RequestParam("age") int age) throws IOException {
    SubjectExport param = SubjectExport.builder().name(name).age(age).build();
    String fileName = String.format("%s%s.xlsx", "数据导出", System.currentTimeMillis());
    new ExcelExporter<SubjectExport>(this.subjectPageQuerier).write(response, fileName, SubjectExport.class, param);
    return null;
}

完整示例代码详见:test-web-downloadtip,可以直接下载到本地运行。

【参考】
https://easyexcel.opensource.alibaba.com/
https://www.cnblogs.com/throwable/p/13285518.html 百万级别数据Excel导出优化
https://blog.csdn.net/qq_41489540/article/details/120752527 Java 百万数据秒级导出到Excel中
https://zhuanlan.zhihu.com/p/533753443 Java百万数据导出Excel性能优化[读(并发)写分离/流式查询]
https://developer.aliyun.com/article/918507 百万级别数据Excel导出优化
https://blog.csdn.net/weixin_43991241/article/details/122079617 EasyExcel 的行高列宽的单位,直接设置行高列宽的大小

posted @ 2022-08-14 22:44  nuccch  阅读(2012)  评论(2编辑  收藏  举报