徹底解決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代碼并填寫業務代碼就可以完成讀寫操作。但它的問題也會比較明顯：

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及流式支援

先貼一張來自官網非常經典的圖

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