Poi读取大数据量Excel文件

阳光穿透心脏的1/2处 2023-08-17 17:11 116阅读 0赞

**前言**

最近生产环境有个老项目一直内存报警，不时的还出现内存泄漏，导致需要重启服务器，已经严重影响正常服务了。

**分析**

**1.dump内存文件**

liunx使用如下命令：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>.</code><code>/jmap</code> <code>-dump:</code><code>format</code><code>=b,</code><code>file</code><code>=heap.hprof pid</code> </td> 
 </tr> 
 </tbody> 
</table>

**2.使用Eclipse Memory Analysis进行分析**

异常如下：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>at org.apache.poi.xssf.usermodel.XSSFRow.&lt;init&gt;(Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTRow;Lorg/apache/poi/xssf/usermodel/XSSFSheet;)V (XSSFRow.java:68)</code> <code>at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(Lorg/openxmlformats/schemas/spreadsheetml/x2006/main/CTWorksheet;)V (XSSFSheet.java:157)</code> <code>at org.apache.poi.xssf.usermodel.XSSFSheet.read(Ljava/io/InputStream;)V (XSSFSheet.java:132)</code> <code>at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead()V (XSSFSheet.java:119)</code> <code>at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead()V (XSSFWorkbook.java:222)</code> <code>at org.apache.poi.POIXMLDocument.load(Lorg/apache/poi/POIXMLFactory;)V (POIXMLDocument.java:200)</code> <code>at org.apache.poi.xssf.usermodel.XSSFWorkbook.&lt;init&gt;(Ljava/io/InputStream;)V (XSSFWorkbook.java:179)</code> </td> 
 </tr> 
 </tbody> 
</table>

POI在加载Excel引发了内存泄漏，中间创建了大量的对象，占用了大量的内存

**3.查看上传的Excel大小**

经查看发现很多Excel大小在9M的文件

**4.查看代码POI读取Excel的方式**

发现使用的是用户模式，这样会占用大量的内存；POI提供了2中读取Excel的模式，分别是：

*  用户模式：也就是poi下的usermodel有关包，它对用户友好，有统一的接口在ss包下，但是它是把整个文件读取到内存中的，  
    对于大量数据很容易内存溢出，所以只能用来处理相对较小量的数据；
 *  事件模式：在poi下的eventusermodel包下，相对来说实现比较复杂，但是它处理速度快，占用内存少，可以用来处理海量的Excel数据。

经上面分析基本可以确定问题出在使用POI的用户模式去读取Excel大文件，导致内存泄漏。

**本地重现**

下面模拟一个600kb大小的Excel（test.xlsx），分别用两种模式读取，然后观察内存波动；

**1.需要引入的库maven：**

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 </td> 
 <td> <code>&lt;dependencies&gt;</code> <code>&nbsp;</code><code>&lt;dependency&gt;</code> <code>&nbsp;&nbsp;</code><code>&lt;groupId&gt;org.apache.poi&lt;/groupId&gt;</code> <code>&nbsp;&nbsp;</code><code>&lt;artifactId&gt;poi-ooxml&lt;/artifactId&gt;</code> <code>&nbsp;&nbsp;</code><code>&lt;version&gt;</code><code>3.6</code><code>&lt;/version&gt;</code> <code>&nbsp;</code><code>&lt;/dependency&gt;</code> <code>&nbsp;</code><code>&lt;dependency&gt;</code> <code>&nbsp;&nbsp;</code><code>&lt;groupId&gt;com.syncthemall&lt;/groupId&gt;</code> <code>&nbsp;&nbsp;</code><code>&lt;artifactId&gt;boilerpipe&lt;/artifactId&gt;</code> <code>&nbsp;&nbsp;</code><code>&lt;version&gt;</code><code>1.2</code><code>.</code><code>1</code><code>&lt;/version&gt;</code> <code>&nbsp;</code><code>&lt;/dependency&gt;</code> <code>&lt;/dependencies&gt;</code> </td> 
 </tr> 
 </tbody> 
</table>

**2.用户模式代码如下：**

[?][Link 1]

**3.事件模式代码如下：**

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 </td> 
 <td> <code>import</code> <code>java.io.InputStream;</code> <code>&nbsp;</code>&nbsp; <code>import</code> <code>org.apache.poi.openxml4j.opc.OPCPackage;</code> <code>import</code> <code>org.apache.poi.xssf.eventusermodel.XSSFReader;</code> <code>import</code> <code>org.apache.poi.xssf.model.SharedStringsTable;</code> <code>import</code> <code>org.apache.poi.xssf.usermodel.XSSFRichTextString;</code> <code>import</code> <code>org.xml.sax.Attributes;</code> <code>import</code> <code>org.xml.sax.ContentHandler;</code> <code>import</code> <code>org.xml.sax.InputSource;</code> <code>import</code> <code>org.xml.sax.SAXException;</code> <code>import</code> <code>org.xml.sax.XMLReader;</code> <code>import</code> <code>org.xml.sax.helpers.DefaultHandler;</code> <code>import</code> <code>org.xml.sax.helpers.XMLReaderFactory;</code> <code>&nbsp;</code>&nbsp; <code>public</code> <code>class</code> <code>EventModel { </code> <code>&nbsp;</code>&nbsp; <code>&nbsp;</code><code>public</code> <code>void</code> <code>processOneSheet(String filename) </code><code>throws</code> <code>Exception { </code> <code>&nbsp;&nbsp;</code><code>OPCPackage pkg = OPCPackage.open(filename);</code> <code>&nbsp;&nbsp;</code><code>XSSFReader r = </code><code>new</code> <code>XSSFReader(pkg);</code> <code>&nbsp;&nbsp;</code><code>SharedStringsTable sst = r.getSharedStringsTable();</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;&nbsp;</code><code>XMLReader parser = fetchSheetParser(sst);</code> <code>&nbsp;&nbsp;</code><code>InputStream sheet2 = r.getSheet(</code><code>"rId1"</code><code>);</code> <code>&nbsp;&nbsp;</code><code>InputSource sheetSource = </code><code>new</code> <code>InputSource(sheet2);</code> <code>&nbsp;&nbsp;</code><code>parser.parse(sheetSource);</code> <code>&nbsp;&nbsp;</code><code>sheet2.close();</code> <code>&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;</code><code>public</code> <code>XMLReader fetchSheetParser(SharedStringsTable sst) </code><code>throws</code> <code>SAXException { </code> <code>&nbsp;&nbsp;</code><code>XMLReader parser = XMLReaderFactory.createXMLReader(</code><code>"org.apache.xerces.parsers.SAXParser"</code><code>);</code> <code>&nbsp;&nbsp;</code><code>ContentHandler handler = </code><code>new</code> <code>SheetHandler(sst);</code> <code>&nbsp;&nbsp;</code><code>parser.setContentHandler(handler);</code> <code>&nbsp;&nbsp;</code><code>return</code> <code>parser;</code> <code>&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;</code><code>private</code> <code>static</code> <code>class</code> <code>SheetHandler </code><code>extends</code> <code>DefaultHandler { </code> <code>&nbsp;&nbsp;</code><code>private</code> <code>SharedStringsTable sst;</code> <code>&nbsp;&nbsp;</code><code>private</code> <code>String lastContents;</code> <code>&nbsp;&nbsp;</code><code>private</code> <code>boolean</code> <code>nextIsString;</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;&nbsp;</code><code>private</code> <code>SheetHandler(SharedStringsTable sst) { </code> <code>&nbsp;&nbsp;&nbsp;</code><code>this</code><code>.sst = sst;</code> <code>&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;&nbsp;</code><code>public</code> <code>void</code> <code>startElement(String uri, String localName, String name, Attributes attributes) </code><code>throws</code> <code>SAXException { </code> <code>&nbsp;&nbsp;&nbsp;</code><code>if</code> <code>(name.equals(</code><code>"c"</code><code>)) { </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>System.out.print(attributes.getValue(</code><code>"r"</code><code>) + </code><code>" - "</code><code>);</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>String cellType = attributes.getValue(</code><code>"t"</code><code>);</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>if</code> <code>(cellType != </code><code>null</code> <code>&amp;&amp; cellType.equals(</code><code>"s"</code><code>)) { </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>nextIsString = </code><code>true</code><code>;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>} </code><code>else</code> <code>{ </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>nextIsString = </code><code>false</code><code>;</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;&nbsp;&nbsp;</code><code>lastContents = </code><code>""</code><code>;</code> <code>&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;&nbsp;</code><code>public</code> <code>void</code> <code>endElement(String uri, String localName, String name) </code><code>throws</code> <code>SAXException { </code> <code>&nbsp;&nbsp;&nbsp;</code><code>if</code> <code>(nextIsString) { </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>int</code> <code>idx = Integer.parseInt(lastContents);</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>lastContents = </code><code>new</code> <code>XSSFRichTextString(sst.getEntryAt(idx)).toString();</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>nextIsString = </code><code>false</code><code>;</code> <code>&nbsp;&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;&nbsp;&nbsp;</code><code>if</code> <code>(name.equals(</code><code>"v"</code><code>)) { </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>System.out.println(lastContents);</code> <code>&nbsp;&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;&nbsp;</code><code>public</code> <code>void</code> <code>characters(</code><code>char</code><code>[] ch, </code><code>int</code> <code>start, </code><code>int</code> <code>length) </code><code>throws</code> <code>SAXException { </code> <code>&nbsp;&nbsp;&nbsp;</code><code>lastContents += </code><code>new</code> <code>String(ch, start, length);</code> <code>&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;</code><code>}</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;</code><code>public</code> <code>static</code> <code>void</code> <code>main(String[] args) </code><code>throws</code> <code>Exception { </code> <code>&nbsp;&nbsp;</code><code>Thread.sleep(</code><code>5000</code><code>);</code> <code>&nbsp;&nbsp;</code><code>System.out.println(</code><code>"start read"</code><code>);</code> <code>&nbsp;&nbsp;</code><code>for</code> <code>(</code><code>int</code> <code>i = </code><code>0</code><code>; i &lt; </code><code>100</code><code>; i++) { </code> <code>&nbsp;&nbsp;&nbsp;</code><code>EventModel example = </code><code>new</code> <code>EventModel();</code> <code>&nbsp;&nbsp;&nbsp;</code><code>example.processOneSheet(</code><code>"D:/test.xlsx"</code><code>);</code> <code>&nbsp;&nbsp;&nbsp;</code><code>Thread.sleep(</code><code>1000</code><code>);</code> <code>&nbsp;&nbsp;</code><code>}</code> <code>&nbsp;</code><code>}</code> <code>}</code> </td> 
 </tr> 
 </tbody> 
</table>

具体代码来源：[http://poi.apache.org/spreadsheet/how-to.html\#xssf\_sax\_api][http_poi.apache.org_spreadsheet_how-to.html_xssf_sax_api]

**4.设置VM arguments：-Xms100m -Xmx100m**

UserModel运行结果直接报OutOfMemoryError，如下所示：

[?][Link 1]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>Exception in thread </code><code>"main"</code> <code>java.lang.OutOfMemoryError: GC overhead limit exceeded</code> <code>&nbsp;</code><code>at java.lang.String.substring(String.java:</code><code>1877</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.ss.util.CellReference.separateRefParts(CellReference.java:</code><code>353</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.ss.util.CellReference.&lt;init&gt;(CellReference.java:</code><code>87</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFCell.&lt;init&gt;(XSSFCell.java:</code><code>105</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFRow.&lt;init&gt;(XSSFRow.java:</code><code>68</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(XSSFSheet.java:</code><code>157</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:</code><code>132</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:</code><code>119</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:</code><code>222</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:</code><code>200</code><code>)</code> <code>&nbsp;</code><code>at org.apache.poi.xssf.usermodel.XSSFWorkbook.&lt;init&gt;(XSSFWorkbook.java:</code><code>179</code><code>)</code> <code>&nbsp;</code><code>at zh.excelTest.UserModel.main(UserModel.java:</code><code>23</code><code>)</code> </td> 
 </tr> 
 </tbody> 
</table>

UserModel模式下读取600kbExcel文件直接内存溢出，看了600kbExcel文件映射到内存中还是占用了不少内存；EventModel模式下可以流畅的运行。

**5.设置VM arguments：-Xms200m -Xmx200m**

UserModel模式和EventModel模式都可以正常运行，但是很明显UserModel模式回收内存更加频繁，而且在cpu的占用上更高。

**总结**

通过简单的分析以及本地运行两种模式进行比较，可以看到UserModel模式下使用的简单的代码实现了读取，但是在读取大文件时CPU和内存都不理想；

而EventModel模式虽然代码写起来比较繁琐，但是在读取大文件时CPU和内存更加占优。

[Link 1]: https://www.jb51.net/article/122529.htm#
[http_poi.apache.org_spreadsheet_how-to.html_xssf_sax_api]: http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api