旧系统升级改造之java导出excel无大小限制

前言

某日, 我接到了一个需求, 优化现有系统的报表模块, 经过与产品经理沟通及调查发现, 报表导出超过10w条的数据会导致oom, 所以之前一直限制导出数量在10w以下, 这个数字对于一线的业务人员简直不能忍受, 有时候一个月的数据需要手动导出三次再合并才能使用.
经过优化, 我将报表整个模块抽离出旧系统做成一个报表服务, 具体处理用异步mq缓解数据库的压力, 可动态配置消费者个数即为数据库的并发数. 当然这不是本文的重点. 本文将单excel下载上限提高到无限大, 意思就是硬盘有多大, excel就能下载成多大, 梦想就有多大.

思路

思路其实想想也挺简单的, 但是具体操作起来问题就会频繁出现了, 简单画了一个流程图:

只画了主要逻辑, 具体代码有些许差别, 以下主要分析在报表服务中如何生成excel.
先分析之前的做法为啥不行, 一次性读取过多的数据进内存中再写入excel, 再大的内存也会有上限. 所以我打算一次读取一部分数据写入excel, 多读几次就ok了.
其中有几个点要特别注意下:

1. excel每个sheet页行上限1048576, 打开excel按ctrl+下箭头即可看到最后一行, 列的上限一般可以忽略, 所以说刨去表头每次写入的行数最好是可被104w整除的数, 因为叠加到最后, 会溢出. 我这里采用的是4w一次, 部分数据量少的报表可以设置高一些20w都可以, 增加一次读取的量可以减少访问数据库的次数.
2. 在处理大数据量的报表时, 内存几乎是一直在不停的gc, 所以要留出一定的空间处理其他的业务, 否则会报这个异常Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded, gc的时候释放的空间不足2%
3. excel写入时与硬盘的io交互, 我们在创建excel写入数据的过程都是在内存中进行的, 新版本的poi有一个特性, 可以设置一个值, 当一次写入后超过这个行数就把excel的数据写入到硬盘中, 我这里设置的值是动态的, 就是注意点1中的一次读取的数据量减去100, 一次读取20000, 那么这里设置19900, 所有的值都要根据系统的具体参数和报表的大小进行调整, 反复测试综合速度和稳定性得出最优的值.

代码

依赖的jar包:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

excel工具类:

public class ExcelWriterGo {
   

    private String reportType;
    private List<String> titleCells;
    private List<String> reportKeys;
    private String filename;
    private SXSSFWorkbook sxssfWorkbook;
    private Sheet sheet;
    private int oldDataCount;

    public ExcelWriterGo(String filename, String reportType) {
   
        this.filename = filename;
        this.reportType = reportType;
    }

    public void initExcel(int dealCount) {
   
        this.titleCells = new GetRvReportTitlesAction().getRvReport_Titles(reportType);
        this.reportKeys = new GetRvReportContentKeys().getRvReportContent_Keys(reportType);
        this.sxssfWorkbook = new SXSSFWorkbook(dealCount);
        this.createNewSheet();
    }

    private void createNewSheet() {
   
        this.sheet = sxssfWorkbook.createSheet();
        Row firstRow = sheet.createRow(0);
        for (int i = 0; i < titleCells.size(); i++) {
   
            Cell cell = firstRow.createCell(i);
            cell.setCellValue(titleCells.get(i));
        }
        oldDataCount = 1;
    }

    public void writeData(List<Map> datas) {
   
        if (datas != null && datas.size() > 0) {
   
            for (int i = 0; i < datas.size(); i++) {
   
                Row row = sheet.createRow(i + oldDataCount);
                Map map = datas.get(i);
                for (int j = 0, k = reportKeys.size(); j < k; j++) {
   
                    Cell cell = row.createCell(j);
                    cell.setCellValue(MapUtils.getString(map, reportKeys.get(j), ""));
                }
            }
            oldDataCount += datas.size();
            if (oldDataCount >= 1000000) {
   
                this.createNewSheet();
            }
        }
    }

    public void writeStream() {
   
        try {
   
            File file = new File(filename);
            OutputStream outputStream = new FileOutputStream(file, true);
            sxssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
   
            e.printStackTrace();
        } finally {
   
            sxssfWorkbook.dispose();
        }
    }

    public void closeExcel() {
   
        sxssfWorkbook.dispose();
    }
}

manager类涉及excel的部分代码: 这个类用于业务解耦, 承上启下, 相当于service跟各种中间件的交互小中台, 提供了各种操作excel写入数据, 创建及关闭, 记录进数据库等操作.