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

不念不忘少年蓝@ 2022-04-11 11:17 584阅读 0赞

说实话之前之前没怎么接触过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代码并填写业务代码就可以完成读写操作。但它的问题也会比较明显：

1.  我的excel只有5行数据，为啥它就消耗了200MB内存？
2.  在处理比较大的 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及流式支持 ##

先贴一张来自[官网][Link 1]非常经典的图

![Spreadsheet API Feature Summary][]

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

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

**2. Hutool-poi**

api可读性好，本质是对POI封装，[更多][Link 3]

## 5. 总结 ##

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

[4.0.0]: https://mvnrepository.com/artifact/org.apache.poi/poi/4.0.0
[Link 1]: http://poi.apache.org/components/spreadsheet/index.html
[Spreadsheet API Feature Summary]: /images/20220411/64f539ede2944f9097a13abfccb2da8f.png
[how use]: https://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api
[Link 2]: https://github.com/alibaba/easyexcel
[Link 3]: https://www.hutool.cn/docs/#/poi/%E6%A6%82%E8%BF%B0