POI打开Excel文件遇到out of memory的解决实践

发布时间：2024/01/05 20:01 发布者：Leo

背景问题

在Java开发处理Excel文件的工程中，许多人首选使用Apache POI工具。然而，随着需求的增加和工程的复杂化，可能会遇到打开复杂Excel文件的问题。根据测试，在打开包含50万个单元格数据的文件时，会出现OOM（OutOfMemory）问题。同样地，当打开包含20万个合并单元格（带有边框或背景色）的文件时，也可能遇到OOM问题。

测试使用的是WorkbookFactory来直接打开Excel文件的方式，代码示例如下：

File file = new File("testFile.xlsx");
Workbook workbook = WorkbookFactory.create(file);
//打开文件后进行其他处理

然而，这段代码在处理大型Excel文件时会导致OOM问题的发生。

网上有很多关于POI内存溢出问题的讨论，主流的方法有以下两种：

可以把文件转换为CSV格式，然后导入数据。
将Excel文件拆分为较小的Excel文件，分别构建workbook，然后进行处理。

针对网上建议的第一个办法，对于仅需要导入数据的情况非常有效。然而，对于包含样式的Excel文件，转换为CSV格式会导致样式丢失，因此对于该问题不适用。

第二个办法似乎可行，把文件分割成多个小文件，分别构建workbook，然后去处理。

因此这里手动把Excel文件拆分开，修改了代码，进行一次实际的测试。

File file = new File("test.xlsx");
File file1 = new File("test1.xlsx");
File file2 = new File("test2.xlsx");
File file3 = new File("test3.xlsx");
File file4 = new File("test4.xlsx");
File file5 = new File("test5.xlsx");
File file6 = new File("test6.xlsx");

Workbook workbook = WorkbookFactory.create(file);
Workbook workbook1 = WorkbookFactory.create(file1);
Workbook workbook2 = WorkbookFactory.create(file2);
Workbook workbook3 = WorkbookFactory.create(file3);
Workbook workbook4 = WorkbookFactory.create(file4);
Workbook workbook5 = WorkbookFactory.create(file5);
Workbook workbook6 = WorkbookFactory.create(file6);

然而，尝试后仍然遇到OOM问题。测试是通过单元测试进行的，错误信息如下：

...
at org.gradle.process.internal.worker.child.ActionExecutionWorker.execute(ActionExecutionWorker.java:56)
at org.gradle.process.internal.worker.child.SystemApplicationClassLoaderWorker.call(SystemApplicationClassLoaderWorker.java:113)
at org.gradle.process.internal.worker.child.SystemApplicationClassLoaderWorker.call(SystemApplicationClassLoaderWorker.java:65)
at worker.org.gradle.process.internal.worker.GradleWorkerMain.run(GradleWorkerMain.java:69)
at worker.org.gradle.process.internal.worker.GradleWorkerMain.main(GradleWorkerMain.java:74)
Caused by: java.lang.OutOfMemoryError: GC overhead limit exceeded
at java.util.Arrays.copyOfRange(Arrays.java:3664)
at java.lang.String.<init>(String.java:207)
at com.sun.org.apache.xerces.internal.xni.XMLString.toString(XMLString.java:190)
at com.sun.org.apache.xerces.internal.util.XMLAttributesImpl.getValue(XMLAttributesImpl.java:523)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser$AttributesProxy.getValue(AbstractSAXParser.java:2321)
...

经过一些尝试，发现问题出在同时构建太多的Workbook对象上。当将同时构建的Workbook数量减少到4个时，单元测试可以正常运行。

POI实际出现的这个问题着实让人感到头疼，因为在实际应用中很难预测Excel文件的数量。根据测试结果看，Workbook的数量可能与Excel文件的大小有关，这可能导致在后续开发中遇到更多随着Excel文件大小的不确定，程序随机崩溃的问题。

解决实践

关于处理复杂Excel文件，除了Apache POI之外，还有一些工具，比如阿里巴巴的EasyExcel和GcExcel可以作为替代。

EasyExcel是一个开源组件，主要专注于处理高并发的读写场景，并且表现良好。而GcExcel则是一款商业软件，提供了全面的API功能。

针对我们想要解决的问题，我们可以分别使用这两个组件进行验证，主要包括以下两个方面：

1. 能够一次性打开包含大量数据和样式的Excel文件。

2. 可以找到一种方法来保留样式或复制样式。

针对问题1，EasyExcel和GcExcel都能够很好地处理大量数据和样式的Excel文件，不再出现OOM的问题。在代码层面，这两个组件的使用风格略有不同。GcExcel和POI相似，直接构建Workbook对象。而EasyExcel则提供了注解的方式，更类似于反序列化的体验。同时，每次读取数据需要编写一个[监听器](https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read" \l "%E6%9C%80%E7%AE%80%E5%8D%95%E7%9A%84%E8%AF%BB%E7%9A%84%E7%9B%91%E5%90%AC%E5%99%A8)，在监听器中可以处理特殊逻辑。

针对问题2，我们通过UT代码进行对比，代码分别如下：

先看看EasyExcel。

首先EasyExcel需要定义一个Data类，来读取数据。

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    private String cell1;
    private String cell2;
}

定义一个listener类，处理style的逻辑需要在invoke里进行处理，没找到EasyExcel相关的API，还是使用到了POI本身的API来处理样式相关的内容。

@Slf4j 
public class DemoListener implements ReadListener<DemoData> { 
    private int rowNum = 0; private Sheet sheet; 
    @Override
    public void invoke(DemoData data, AnalysisContext context) {    
        if (sheet == null) {    
            sheet = (Sheet) context.readSheetHolder().getReadSheet();    
        }

        Row row = sheet.getRow(rowNum);    
        // 获取第一列    
        Cell cell0 = row.getCell(0);    
        CellStyle style0 = cell0.getCellStyle();    
        // 创建样式对象    
        Workbook workbook = sheet.getWorkbook();    
        CellStyle newStyle = workbook.createCellStyle();    
        // 复制原有样式到新创建的样式对象中    
        newStyle.cloneStyleFrom(style0);    
        // TODO: 其他操作    
        rowNum++;    
    }

    @Override    
    public void doAfterAllAnalysed(AnalysisContext context) {

    }    
}

从官网看到，在EasyExcel 2.0.0-beta1以后，可以使用extra方法获取批注，超链接，合并单元格信息。但是如果有border或者其他的样式，似乎好像不能用这个方法。

经过简单的测试，问题可以解决，但是样式处理起来还是比较复杂。

对于GcExcel，根据官方文档代码上手就很简单。直接基于Range的概念就可以通过set/get方法获取各种样式。
详见：https://www.grapecity.com.cn/developer/grapecitydocuments/excel-java/docs/Features/ApplyStyle

代码写起来很简单，只要理解Excel相关的概念就可以轻松获取到style。

@Test
public void testRepeatCreateObject() throws IOException {
    String fileName = "test.xlsx";
    Workbook workbook = new Workbook();
    workbook.open(fileName);
    IWorksheet sheet = workbook.getWorksheets().get(0);
    IStyle style = sheet.getRange(0,0).getStyle();
    System.out.println("font "+style.getFont().getName());
    System.out.println("border "+style.getBorders().getLineStyle().name());
}

总结

综合而言，如果你倾向于使用开源组件，可以选择EasyExcel。EasyExcel提供了类似反序列化的注解方式来读取数据，使数据读取变得简单。然而，在样式处理方面，需要依赖事件机制进行处理，这可能会稍显繁琐。

如果你是在商业项目开发中，可以考虑使用GcExcel。GcExcel在API设计上非常简单易用。此外，在实际测试中还发现，GcExcel打开文件的速度要快得多，这可以降低开发成本。

GcExcel | 下载试用
GrapeCity Documents for Excel （简称：GcExcel）是一款基于 Java 平台的服务端高性能表格组件，可与纯前端表格控件 SpreadJS 前后端兼容，无需依赖 Office、POI 或第三方应用软件，在前端展示电子表格数据，在服务端批量创建、加载、编辑、打印、导入/导出 Excel 文档，为您开发的应用程序提供在线文档的前后端数据同步、在线填报与服务端批量导出与打印，以及类 Excel 报表模板设计与服务端高性能处理等一整套类 Excel 全栈解决方案。