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

说实话之前之前没怎么接触过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及流式支持
先贴一张来自官网非常经典的图

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)。
 

 

 

 

原文地址:https://blog.csdn.net/alex_xfboy/article/details/84844667

上一篇:Emmet语法


下一篇:python学习第二天