彻底解决POI 读写excel 发生OOM问题

原创

布道8848 2023-05-14 23:01:52 博主文章分类：组件 ©著作权

文章标签 HSSFWorkbook XSSFWorkbook SXSSFWorkbook 数据临时文件 文章分类 办公效率

©著作权归作者所有：来自51CTO博客作者布道8848的原创作品，请联系作者获取转载授权，否则将追究法律责任

说实话之前之前没怎么接触过POI组件，只知道有这么一个东西可以解决excel读写问题，但不用不知道，使用起来真心无语，到处都是坑。接下来我讲分享一些在项目中遇到的坑及解决方法，其实社区也有不少类似文章，但讲的都比较零散。

1. .xls与.xlsx

首先，科普一些基础常识：

.xls 是用03版Office Excel ，新建Excel默认保存的Excel文件格式的后缀是.xls，不可以打开编辑07版的xlsx文件，否则出现乱码或者卡死。行列的上限为 65536行，256列。

.xlsx 是用07版Office Excel ，新建Excel默认保存的的Excel文件格式后缀是.xlsx，也能打开编辑03版的xls文件。行列的上限为 1048575行，16384列。

然后，在存储格式上：

.xls，文件存储格式实现原理是基于微软的ole db是微软com组件的一种实现，本质上也是一个微型数据库，由于微软的东西很多不开源，基本上也已经被淘汰，了解它的细节意义不大。

.xlsx ，文件存储格式实现是基于openXml和zip技术（（用winrar可以打开看）。它的优点是简单存储、安全传输方便、处理数据简单。

.csv，纯文本文件（以","为分割符），可以被excel打开。他的格式非常简单，解析起来和解析文本文件一样。

2. poi读写大文件的坑

为什么一定要用POI呢？虽然POI是目前使用最多的用来做excel解析的框架，但这个框架并不那么完美。大部分使用POI都是使用他的userModel模式，它上手容易使用简单，随便拷贝个API代码并填写业务代码就可以完成读写操作。但它的问题也会比较明显：

我的excel只有5行数据，为啥它就消耗了200MB内存？
在处理比较大的 excel 的时候（2w行），有时候会会出现内存溢出（2G）

它带来的后遗症就是，稍微几个并发（估计都不用并发），可怕的问题就来了出现full gc。

再分享一个最“热门”的坑，当它在大并发情况下就抛的一个异常

Caused by: java.io.IOException: Could not create temporary directory '/home/admin/dio2o/.default/temp/poifiles'
        at org.apache.poi.util.DefaultTempFileCreationStrategy.createTempDirectory(DefaultTempFileCreationStrategy.java:93) ~[poi-3.15.jar:3.15]
        at org.apache.poi.util.DefaultTempFileCreationStrategy.createPOIFilesDirectory(DefaultTempFileCreationStrategy.java:82) ~[poi-3.15.jar:3.15]

//引自org.apache.poi.util.DefaultTempFileCreationStrategy
private void createTempDirectory(File directory) throws IOException {
    if (!(directory.exists() || directory.mkdirs()) || !directory.isDirectory()) {
        throw new IOException("Could not create temporary directory '" + directory + "'");
    }
}
/**
*
如果2个线程同时判断directory.exists()都为false,但执行directory.mkdirs()如果一些线程优先执行完，另外一个线程就会返回false。
最终 throw new IOException(“Could not create temporary directory ‘” + directory + “’”)。
针对这个问题easyexcel在写文件时候首先创建了该临时目录，避免poi在并发创建时候引起不该有的报错。
**/

不过还好官方于2018-8月在4.0.0版本得以解决。

3. 重新认识poi及流式支持

先贴一张来自官网非常经典的图

彻底解决POI 读写excel 发生OOM问题_数据

usermodel，它是基于DOM的文档驱动，读写都支持，基于内存的，总之就是很垃圾

SAX，它是Simple API for XML的缩写，主要用在读文件，它并不是由W3C官方所提出的标准，但使用SAX的还是不少，几乎所有的XML解析器都会支持它。 SAX在概念上与DOM完全不同。它不同于DOM的文档驱动，它是事件驱动的，它并不需要读入整个文档，而文档的读入过程一行一行解析。所谓eventmodel（事件驱动），将一行的解析结果以观察者的模式通知处理，通知的方式基于回调（callback）机制的程序运行方法。how use？

sxssf，它你可理解为缓存流式支持，在写文件很重要。

到这里基本上对上图的理解已经到位了，可以下手进一步了解API了。

1. HSSFWorkbook（excel 2003）
它是基于usermodel，HSSFWorkbook 针对是 EXCEL2003 版本，扩展名为 .xls;所以此种的局限就是导出的行数至多为 65535 行，此种因为行数不够多所以一般不会发生OOM。

2. XSSFWorkbook （excel 2007）
它是基于usermodel，这种形式的出现是由于第一种HSSFWorkbook 的局限性而产生的，因为其所导出的行数比较少，所以XSSFWookbook应运而生其对应的是EXCEL2007+(1048576行，16384列)扩展名 .xlsx，最多可以导出 104 万行，不过这样就伴随着一个问题---OOM 内存溢出，原因是你所创建的 book sheet row cell 等此时是存在内存的并没有持久化，那么随着数据量增大内存的需求量也就增大，那么很大可能就是要 OOM了。

3. SXSSFWorkbook（excel 2007后，poi使用3.8+版本）

它是基于sxssf，因为数据量过大导致内存吃不消无法写文件，有读一批写一批的做法吗? 答案是肯定的。怎么做？此种的情况就是设置最大内存条数。比如：设置最大内存量为5000 rows（new SXSSFWookbook（5000））或者手动flush()，此时当行数达到 5000 时，把内存中的数据写到文件中，以此逐步写入避免OOM,那么这样就完美解决了大数据下导出的问题。

性能参数：SXSSFWorkbook.setCompressTempFiles(true)，SXSSF将sheet data刷新到临时文件（每张sheet一个临时文件）中，并且这些临时文件的大小可以增长到非常大的值。例如，对于一个20MB的CSV数据，临时XML的大小变得大于千兆字节。如果临时文件的大小是一个问题，你可以开启使用GZIP压缩。

4. WorkbookFactory.create(InputStream inputStm)

// 它会基于xls或xlsx判断创建HSSFWorkbook或XSSFWorkbook
public static Workbook create(InputStream inp, String password) throws IOException, InvalidFormatException, EncryptedDocumentException {
        // If clearly doesn't do mark/reset, wrap up
        if (! inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }

        // Ensure that there is at least some data there
        byte[] header8 = IOUtils.peekFirst8Bytes(inp);

        // Try to create
        if (NPOIFSFileSystem.hasPOIFSHeader(header8)) {
            NPOIFSFileSystem fs = new NPOIFSFileSystem(inp);
            return create(fs, password);
        }
        if (DocumentFactoryHelper.hasOOXMLHeader(inp)) {
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        throw new InvalidFormatException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
    }

5. 抛弃不重要的数据

Excel解析时候会包含样式，字体，宽度等数据，但这些数据占了相当大的空间，却是我们不关注的，如果将这部分数据抛弃可以大大降低内存使用。

4. 其他解决方案

1. easyexcel

alibaba开源的，基于注解，api可读性好，更多

2. Hutool-poi

api可读性好，本质是对POI封装，更多

5. 总结

如果对有一定并发的项目，大文件读最好是使用SAX模式，但它有一定的编码量，大文件的写最好基于sxssf。当然结合项目的实际情况，我们项目中是有定时做System.gc()，如果你gc不是cms模式要在启动项中要添加配置（-XX:+UseConcMarkSweepGC -XX:+CMSParallelRemarkEnabled）。