聊聊Excel解析：如何處理百萬行EXCEL檔案
一、引言
Excel表格在後臺管理系統中使用非常廣泛，多用來進行批次設定、資料匯出工作。在日常開發中，我們也免不了進行Excel資料處理。
那麼，如何恰當地處理資料量龐大的Excel檔案，避免記憶體溢位問題？本文將對比分析業界主流的Excel解析技術，並給出解決方案。
如果這是您第一次接觸Excel解析，建議您從第二章瞭解本文基礎概念；如果您已經對POI有所瞭解，請跳轉第三章閱讀本文重點內容。
二、基礎篇-POI
說到Excel讀寫，就離不開這個圈子的的老大哥——POI。
Apache POI是一款Apache軟體基金會用Java編寫的免費開源的跨平臺的 Java API，全稱Poor Obfuscation Implementation，「簡潔版的模糊實現」。它支援我們用Java語言和包括Word、Excel、PowerPoint、Visio在內的所有Microsoft Office檔案互動，進行資料讀寫和修改操作。
（1）「糟糕」的電子試算表
在POI中，每種檔案都有一個與之對應的檔案格式，如97-2003版本的Excel檔案（.xls），檔案格式為HSSF——Horrible SpreadSheet Format，意為「糟糕的電子試算表格式」。雖然Apache幽默而謙虛地將自己的API冠以「糟糕」之名，不過這確實是一款全面而強大的API。
以下是部分「糟糕」的POI檔案格式，包括Excel、Word等：
| Office檔案 | 對應POI格式 |
|---|---|
| Excel (.xls) | HSSF (Horrible SpreadSheet Format) |
| Word (.doc) | HWPF (Horrible Word Processor Format) |
| Visio (.vsd) | HDGF (Horrible DiaGram Format) |
| PowerPoint(.ppt) | HSLF（Horrible Slide Layout Format） |
（2）OOXML簡介
微軟在Office 2007版本推出了基於XML的技術規範：Office Open XML，簡稱OOXML。不同於老版本的二進位制儲存，在新規範下，所有Office檔案都使用了XML格式書寫，並使用ZIP格式進行壓縮儲存，大大提升了規範性，也提高了壓縮率，縮小了檔案體積，同時支援向後相容。簡單來說，OOXML定義瞭如何用一系列的XML檔案來表示Office檔案。
Xlsx檔案的本質是XML
讓我們看看一個採用OOML標準的Xlsx檔案的構成。我們右鍵點選一個Xlsx檔案，可以發現它可以被ZIP解壓工具解壓（或直接修改擴充套件名為.zip後解壓），這說明：Xlsx檔案是用ZIP格式壓縮的。解壓後，可以看到如下目錄格式：
開啟其中的「/xl」目錄，這是這個Excel的主要結構資訊：
其中workbook.xml儲存了整個Excel工作簿的結構，包含了幾張sheet表單，而每張表單結構儲存在/wooksheets資料夾中。styles.xml存放單元格的格式資訊，/theme資料夾存放一些預定義的字型、顏色等資料。為了減少壓縮體積，表單中所有的字元資料被統一存放在sharedStrings.xml中。經過分析不難發現，Xlsx檔案的主體資料都以XML格式書寫。
XSSF格式
為了支援新標準的Office檔案，POI也推出了一套相容OOXML標準的API，稱作poi-ooxml。如Excel 2007檔案（.xlsx）對應的POI檔案格式為XSSF（XML SpreadSheet Format）。
以下是部分OOXML檔案格式：
| Office檔案 | 對應POI格式 |
|---|---|
| Excel (.xlsx) | XSSF (XML SpreadSheet Format) |
| Word (.docx) | XWPF (XML Word Processor Format) |
| Visio (.vsdx) | XDGF (XML DiaGram Format) |
| PowerPoint (.pptx) | XSLF (XML Slide Layout Format) |
（3）UserModel
在POI中為我們提供了兩種解析Excel的模型，UserModel（使用者模型）和EventModel（事件模型） 。兩種解析模式都可以處理Excel檔案，但解析方式、處理效率、記憶體佔用量都不盡相同。最簡單和實用的當屬UserModel。
UserModel & DOM解析
使用者模型定義瞭如下介面：
-
Workbook-工作簿，對應一個Excel檔案。根據版本不同，有HSSFWorkbook、XSSFWorkbook等類。
-
Sheet-表單，一個Excel中的若干個表單，同樣有HSSFSheet、XSSFSheet等類。
-
Row-行，一個表單由若干行組成，同樣有HSSFRow、XSSFRow等類。
-
Cell-單元格，一個行由若干單元格組成，同樣有HSSFCell、XSSFCell等類。
可以看到，使用者模型十分貼合Excel使用者的習慣，易於理解，就像我們開啟一個Excel表格一樣。同時使用者模型提供了豐富的API，可以支援我們完成和Excel中一樣的操作，如建立表單、建立行、獲取表的行數、獲取行的列數、讀寫單元格的值等。
為什麼UserModel支援我們進行如此豐富的操作？因為在UserModel中，Excel中的所有XML節點都被解析成了一棵DOM樹，整棵DOM樹都被載入進記憶體，因此可以進行方便地對每個XML節點進行隨機存取。
UserModel資料轉換
瞭解了使用者模型，我們就可以直接使用其API進行各種Excel操作。當然，更方便的辦法是使用使用者模型將一個Excel檔案轉化成我們想要的Java資料結構，更好地進行資料處理。
我們很容易想到關係型資料庫——因為二者的實質是一樣的。類比資料庫的資料表，我們的思路就有了：
-
將一個Sheet看作表頭和資料兩部分，這二者分別包含表的結構和表的資料。
-
對錶頭（第一行），校驗表頭資訊是否和實體類的定義的屬性匹配。
-
對資料（剩餘行），從上向下遍歷每一個Row，將每一行轉化為一個物件，每一列作為該物件的一個屬性，從而得到一個物件列表，該列表包含Excel中的所有資料。
接下來我們就可以按照我們的需求處理我們的資料了，如果想把操作後的資料寫回Excel，也是一樣的邏輯。
使用UserModel
讓我們看看如何使用UserModel讀取Excel檔案。此處使用POI 4.0.0版本，首先引入poi和poi-ooxml依賴：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
我們要讀取一個簡單的Sku資訊表，內容如下：
如何將UserModel的資訊轉化為資料列表？
我們可以通過實現反射+註解的方式定義表頭到資料的對映關係，幫助我們實現UserModel到資料物件的轉換。實現基本思路是： ① 自定義註解，在註解中定義列號，用來標註實體類的每個屬性對應在Excel表頭的第幾列。 ② 在實體類定義中，根據表結構，為每個實體類的屬性加上註解。 ③ 通過反射，獲取實體類的每個屬性對應在Excel的列號，從而到相應的列中取得該屬性的值。
以下是簡單的實現，首先準備自定義註解ExcelCol，其中包含列號和表頭：
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCol {
/**
* 當前列數
*/
int index() default 0;
/**
* 當前列的表頭名稱
*/
String header() default "";
}
接下來，根據Sku欄位定義Sku物件，並新增註解，列號分別為0，1，2，並指定表頭名稱：
import lombok.Data;
import org.shy.xlsx.annotation.ExcelCol;
@Data
public class Sku {
@ExcelCol(index = 0, header = "sku")
private Long id;
@ExcelCol(index = 1, header = "名稱")
private String name;
@ExcelCol(index = 2, header = "價格")
private Double price;
}
然後，用反射獲取表頭的每一個Field，並以列號為索引，存入Map中。從Excel的第二行開始（第一行是表頭），遍歷後面的每一行，對每一行的每個屬性，根據列號拿到對應Cell的值，併為資料物件賦值。根據單元格中值型別的不同，如文字/數位等，進行不同的處理。以下為了簡化邏輯，只對表頭出現的型別進行了處理，其他情況的處理邏輯類似。全部程式碼如下：
import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.shy.domain.pojo.Sku;
import org.shy.xlsx.annotation.ExcelCol;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyUserModel {
public static void main(String[] args) throws Exception {
List<Sku> skus = parseSkus("D:\sunhaoyu8\Documents\Files\skus.xlsx");
System.out.println(JSON.toJSONString(skus));
}
public static List<Sku> parseSkus(String filePath) throws Exception {
FileInputStream in = new FileInputStream(filePath);
Workbook wk = new XSSFWorkbook(in);
Sheet sheet = wk.getSheetAt(0);
// 轉換成的資料列表
List<Sku> skus = new ArrayList<>();
// 獲取Sku的註解資訊
Map<Integer, Field> fieldMap = new HashMap<>(16);
for (Field field : Sku.class.getDeclaredFields()) {
ExcelCol col = field.getAnnotation(ExcelCol.class);
if (col == null) {
continue;
}
field.setAccessible(true);
fieldMap.put(col.index(), field);
}
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row r = sheet.getRow(rowNum);
Sku sku = new Sku();
for (int cellNum = 0; cellNum < fieldMap.size(); cellNum++) {
Cell c = r.getCell(cellNum);
if (c != null) {
setFieldValue(fieldMap.get(cellNum), getCellValue(c), sku);
}
}
skus.add(sku);
}
return skus;
}
public static void setFieldValue(Field field, String value, Sku sku) throws Exception {
if (field == null) {
return;
}
//得到此屬性的型別
String type = field.getType().toString();
if (StringUtils.isBlank(value)) {
field.set(sku, null);
} else if (type.endsWith("String")) {
field.set(sku, value);
} else if (type.endsWith("long") || type.endsWith("Long")) {
field.set(sku, Long.parseLong(value));
} else if (type.endsWith("double") || type.endsWith("Double")) {
field.set(sku, Double.parseDouble(value));
} else {
field.set(sku, value);
}
}
public static String getCellValue(Cell cell) {
DecimalFormat df = new DecimalFormat("#.##");
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case NUMERIC:
return df.format(cell.getNumericCellValue());
case STRING:
return cell.getStringCellValue().trim();
case BLANK:
return null;
}
return "";
}
最後，將轉換完成的資料列表列印出來。執行結果如下：
[{"id":345000,"name":"電腦A","price":5999.0},{"id":345001,"name":"手機C","price":4599.0}]
Tips：如果您的程式出現「NoClassDefFoundError」，請引入ooxml-schemas依賴：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
版本選擇見下表，如POI 4.0.0對應ooxml-schemas 1.4版本：
UserModel的侷限
以上處理邏輯對於大部分的Excel檔案都很適用，但最大的缺點是記憶體開銷大，因為所有的資料都被載入入記憶體。實測，以上3列的Excel檔案在7萬行左右就會出現OOM，而XLS檔案最大行數為65535行，XLSX更是達到了1048576行，如果將幾萬甚至百萬級別的資料全部讀入記憶體，記憶體溢位風險極高。
那麼，該如何解決傳統UserModel無法處理大批次Excel的問題呢？開發者們給出了許多精彩的解決方案，請看下一章。
三、進階篇-記憶體優化的探索
接下來介紹本文重點內容，同時解決本文所提出的問題：如何進行Excel解析的記憶體優化，從而處理百萬行Excel檔案？
（1）EventModel
前面我們提到，除了UserModel外，POI還提供了另一種解析Excel的模型：EventModel事件模型。不同於使用者模型的DOM解析，事件模型採用了SAX的方式去解析Excel。
EventModel & SAX解析
SAX的全稱是Simple API for XML，是一種基於事件驅動的XML解析方法。不同於DOM一次性讀入XML，SAX會採用邊讀取邊處理的方式進行XML操作。簡單來講，SAX解析器會逐行地去掃描XML檔案，當遇到標籤時會觸發解析處理器，從而觸發相應的事件Handler。我們要做的就是繼承DefaultHandler類，重寫一系列事件處理方法，即可對Excel檔案進行相應的處理。
下面是一個簡單的SAX解析的範例，這是要解析的XML檔案：一個sku表，其中包含兩個sku節點，每個節點有一個id屬性和三個子節點。
<?xml version="1.0" encoding="UTF-8"?>
<skus>
<sku id="345000">
<name>電腦A</name>
<price>5999.0</price>
</sku>
<sku id="345001">
<name>手機C</name>
<price>4599.0</price>
</sku>
</skus>
對照XML結構，建立Java實體類：
import lombok.Data;
@Data
public class Sku {
private Long id;
private String name;
private Double price;
}
自定義事件處理類SkuHandler：
import com.alibaba.fastjson.JSON;
import org.shy.domain.pojo.Sku;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;
public class SkuHandler extends DefaultHandler {
/**
* 當前正在處理的sku
*/
private Sku sku;
/**
* 當前正在處理的節點名稱
*/
private String tagName;
@Override
public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
if ("sku".equals(qName)) {
sku = new Sku();
sku.setId(Long.valueOf((attributes.getValue("id"))));
}
tagName = qName;
}
@Override
public void endElement(String uri, String localName, String qName) throws SAXException {
if ("sku".equals(qName)) {
System.out.println(JSON.toJSONString(sku));
// 處理業務邏輯
// ...
}
tagName = null;
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
if ("name".equals(tagName)) {
sku.setName(new String(ch, start, length));
}
if ("price".equals(tagName)) {
sku.setPrice(Double.valueOf(new String(ch, start, length)));
}
}
}
其中，SkuHandler重寫了三個事件響應方法：
startElement（）——每當掃描到新XML元素時，呼叫此方法，傳入XML標籤名稱qName，XML屬性列表attributes；
characters（）——每當掃描到未在XML標籤中的字串時，呼叫此方法，傳入字元陣列、起始下標和長度；
endElement（）——每當掃描到XML元素的結束標籤時，呼叫此方法，傳入XML標籤名稱qName。
我們用一個變數tagName儲存當前掃描到的節點資訊，每次掃描節點傳送變化時，更新tagName；
用一個Sku範例維護當前讀入記憶體的Sku資訊，每當該Sku讀取完成時，我們列印該Sku資訊，並執行相應業務邏輯。這樣，就可以做到一次讀取一條Sku資訊，邊解析邊處理。由於每行Sku結構相同，因此，只需要在記憶體維護一條Sku資訊即可，避免了一次性把所有資訊讀入記憶體。
呼叫SAX解析器時，使用SAXParserFactory建立解析器範例，解析輸入流即可，Main方法如下：
import org.shy.xlsx.sax.handler.SkuHandler;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.InputStream;
public class MySax {
public static void main(String[] args) throws Exception {
parseSku();
}
public static void parseSku() throws Exception {
SAXParserFactory saxParserFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxParserFactory.newSAXParser();
InputStream inputStream = ClassLoader.getSystemResourceAsStream("skus.xml");
saxParser.parse(inputStream, new SkuHandler());
}
}
輸出結果如下：
{"id":345000,"name":"電腦A","price":5999.0}
{"id":345001,"name":"手機C","price":4599.0}
以上演示了SAX解析的基礎原理。EventModel的API更復雜，同樣通過重寫Event handler，實現SAX解析。有興趣的讀者，請參見POI官網的範例程式碼： https://poi.apache.org/components/spreadsheet/how-to.html
EventModel的侷限
POI官方提供的EventModel API雖然使用SAX方式解決了DOM解析的問題，但是存在一些侷限性：
① 屬於low level API，抽象級別低，相對比較複雜，學習使用成本高。
② 對於HSSF和XSSF型別的處理方式不同，程式碼需要根據不同型別分別做相容。
③ 未能完美解決記憶體溢位問題，記憶體開銷仍有優化空間。
④ 僅用於Excel解析，不支援Excel寫入。
因此，筆者不建議使用POI原生的EventModel，至於有哪些更推薦的工具，請看下文。
（2）SXSSF
SXSSF簡介
SXSSF，全稱Streaming XML SpreadSheet Format，是POI 3.8-beta3版本後推出的低記憶體佔用的流式Excel API，旨在解決Excel寫入時的記憶體問題。它是XSSF的擴充套件，當需要將大批次資料寫入Excel中時，只需要用SXSSF替換XSSF即可。SXSSF的原理是滑動視窗——在記憶體中儲存一定數量的行，其餘行儲存在磁碟。這麼做的好處是記憶體優化，代價是失去了隨機存取的能力。SXSSF可以相容XSSF的絕大多數API，非常適合瞭解UserModel的開發者。
記憶體優化會難以避免地帶來一定限制：
① 在某個時間點只能存取有限數量的行，因為其餘行並未被載入入記憶體。
② 不支援需要隨機存取的XSSF API，如刪除/移動行、克隆sheet、公式計算等。
③ 不支援Excel讀取操作。
④ 正因為它是XSSF的擴充套件，所以不支援寫入Xls檔案。
UserModel、EventModel、SXSSF對比
到這裡就介紹完了所有的POI Excel API，下表是所有這些API的功能對比，來自POI官網：
可以看到，UserModel基於DOM解析，功能是最齊全的，支援隨機存取，唯一缺點是CPU和記憶體效率不穩定；
EventModel是POI提供的流式讀取方案，基於SAX解析，僅支援向前存取，其餘API不支援；
SXSSF是POI提供的流式寫入方案，同樣僅能向前存取，支援部分XSSF API。
（3）EasyExcel
EasyExcel簡介
為了解決POI原生的SAX解析的問題，阿里基於POI二次開發了EasyExcel。下面是參照自EasyExcel官網的介紹：
Java解析、生成Excel比較有名的框架有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗記憶體，poi有一套SAX模式的API可以一定程度的解決一些記憶體溢位的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓後儲存都是在記憶體中完成的，記憶體消耗依然很大。 easyexcel重寫了poi對07版Excel的解析，一個3M的excel用POI sax解析依然需要100M左右記憶體，改用easyexcel可以降低到幾M，並且再大的excel也不會出現記憶體溢位；03版依賴POI的sax模式，在上層做了模型轉換的封裝，讓使用者更加簡單方便。
如介紹所言，EasyExcel同樣採用SAX方式解析，但由於重寫了xlsx的SAX解析，優化了記憶體開銷；對xls檔案，在上層進一步進行了封裝，降低了使用成本。API上，採用註解的方式去定義Excel實體類，使用方便；通過事件監聽器的方式做Excel讀取，相比於原生EventModel，API大大簡化；寫入資料時，EasyExcel對大批資料，通過重複多次寫入的方式從而降低記憶體開銷。
EasyExcel最大的優勢是使用簡便，十分鐘可以上手。由於對POI的API都做了高階封裝，所以適合不想了解POI基礎API的開發者。總之，EasyExcel是一款值得一試的API。
使用EasyExcel
引入easyexcel依賴：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
首先，用註解定義Excel實體類：
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Sku {
@ExcelProperty(index = 0)
private Long id;
@ExcelProperty(index = 1)
private String name;
@ExcelProperty(index = 2)
private Double price;
}
接下來，重寫AnalysisEventListener中的invoke和doAfterAllAnalysed方法，這兩個方法分別在監聽到單行解析完成的事件時和全部解析完成的事件時呼叫。每次單行解析完成時，我們列印解析結果，程式碼如下：
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.shy.domain.pojo.easyexcel.Sku;
public class MyEasyExcel {
public static void main(String[] args) {
parseSku();
}
public static void parseSku() {
//讀取檔案路徑
String fileName = "D:\sunhaoyu8\Documents\Files\excel.xlsx";
//讀取excel
EasyExcel.read(fileName, Sku.class, new AnalysisEventListener<Sku>() {
@Override
public void invoke(Sku sku, AnalysisContext analysisContext) {
System.out.println("第" + analysisContext.getCurrentRowNum() + "行：" + JSON.toJSONString(sku));
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("全部解析完成");
}
}).sheet().doRead();
}
}
測驗一下，用它解析一個十萬行的excel，該檔案用UserModel讀取會OOM，如下：
執行結果：
（4）Xlsx-streamer
Xlsx-streamer簡介
Xlsx-streamer是一款用於流式讀取Excel的工具，同樣基於POI二次開發。雖然EasyExcel可以很好地解決Excel讀取的問題，但解析方式為SAX，需要通過實現監聽器以事件驅動的方式進行解析。有沒有其他的解析方式呢？Xlsx-streamer給出了答案。
譯自官方檔案的描述：
如果您過去曾使用 Apache POI 讀取 Excel 檔案，您可能會注意到它的記憶體效率不是很高。 閱讀整個工作簿會導致嚴重的記憶體使用高峰，這會對伺服器造成嚴重破壞。 Apache 必須讀取整個工作簿的原因有很多，但其中大部分與該庫允許您使用隨機地址進行讀寫有關。 如果（且僅當）您只想以快速且記憶體高效的方式讀取 Excel 檔案的內容，您可能不需要此功能。 不幸的是，POI 庫中唯一用於讀取流式工作簿的東西要求您的程式碼使用類似 SAX 的解析器。 該 API 中缺少所有友好的類，如 Row 和 Cell。 該庫充當該流式 API 的包裝器，同時保留標準 POI API 的語法。 繼續閱讀，看看它是否適合您。 注意：這個庫只支援讀取 XLSX 檔案。
如介紹所言，Xlsx-streamer最大的便利之處是相容了使用者使用POI UserModel的習慣，它對所有的UserModel介面都給出了自己的流式實現，如StreamingSheet、StreamingRow等，對於熟悉UserModel的開發者來說，幾乎沒有學習門檻，可以直接使用UserModel存取Excel。
Xlsx-streamer的實現原理和SXSSF相同，都是滑動視窗——限定讀入記憶體中的資料大小，將正在解析的資料讀到記憶體緩衝區中，形成一個臨時檔案，以防止大量使用記憶體。緩衝區的內容會隨著解析的過程不斷變化，當流關閉後，臨時檔案也將被刪除。由於記憶體緩衝區的存在，整個流不會被完整地讀入記憶體，從而防止了記憶體溢位。
與SXSSF一樣，因為記憶體中僅載入入部分行，故犧牲了隨機存取的能力，僅能通過遍歷順序存取整表，這是不可避免的侷限。換言之，如果呼叫StreamingSheet.getRow(int rownum)方法，該方法會獲取sheet的指定行，會丟擲「不支援該操作」的異常。
Xlsx-streamer最大的優勢是相容UserModel，尤其適合那些熟悉UserModel又不想使用繁瑣的EventModel的開發者。它和SXSSF一樣，都通過實現UserModel介面的方式給出解決記憶體問題的方案，很好地填補了SXSSF不支援讀取的空白，可以說它是「讀取版」的SXSSF。
使用Xlsx-streamer
引入pom依賴：
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
下面是一個使用xlsx-streamer的demo：
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileInputStream;
public class MyXlsxStreamer {
public static void main(String[] args) throws Exception {
parseSku();
}
public static void parseSku() throws Exception {
FileInputStream in = new FileInputStream("D:\sunhaoyu8\Documents\Files\excel.xlsx");
Workbook wk = StreamingReader.builder()
//快取到記憶體中的行數，預設是10
.rowCacheSize(100)
//讀取資源時，快取到記憶體的位元組大小，預設是1024
.bufferSize(4096)
//開啟資源，必須，可以是InputStream或者是File
.open(in);
Sheet sheet = wk.getSheetAt(0);
for (Row r : sheet) {
System.out.print("第" + r.getRowNum() + "行：");
for (Cell c : r) {
if (c != null) {
System.out.print(c.getStringCellValue() + " ");
}
}
System.out.println();
}
}
}
如程式碼所示，Xlsx-streamer的使用方法為：使用StreamingReader進行引數設定和流式讀取，我們可以手動設定固定的滑動視窗大小，有兩個指標，分別是快取在記憶體中的最大行數和快取在記憶體的最大位元組數，這兩個指標會同時限制該滑動視窗的上限。接下來，我們可以使用UserModel的API去遍歷存取讀到的表格。
使用十萬行量級的excel檔案實測一下，執行結果：
StAX解析
Xlsx-streamer底層採用的解析方式，被稱作StAX解析。StAX於2004年3月在JSR 173規範中引入，是JDK 6.0推出的新特性。它的全稱是Streaming API for XML，流式XML解析。更準確地講，稱作「流式拉分析」。之所以稱作拉分析，是因為它和「流式推分析」——SAX解析相對。
之前我們提到，SAX解析是一種事件驅動的解析模型，每當解析到標籤時都會觸發相應的事件Handler，將事件「推」給響應器。在這樣的推模型中，解析器是主動，響應器是被動，我們不能選擇想要響應哪些事件，因此這樣的解析比較不靈活。
為了解決SAX解析的問題，StAX解析採用了「拉」的方式——由解析器遍歷流時，原來的響應器變成了驅動者，主動遍歷事件解析器（迭代器），從中拉取一個個事件並處理。在解析過程中，StAX支援使用peek()方法來"偷看"下一個事件，從而決定是否有必要分析下一個事件，而不必從流中讀取事件。這樣可以有效提高靈活性和效率。
下面用StAX的方式再解析一下相同的XML：
<?xml version="1.0" encoding="UTF-8"?>
<skus>
<sku id="345000">
<name>電腦A</name>
<price>5999.0</price>
</sku>
<sku id="345001">
<name>手機C</name>
<price>4599.0</price>
</sku>
</skus>
這次我們不需要監聽器，把所有處理的邏輯整合在一個方法中：
import com.alibaba.fastjson.JSON;
import org.apache.commons.lang3.StringUtils;
import org.shy.domain.pojo.Sku;
import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.events.Attribute;
import javax.xml.stream.events.StartElement;
import javax.xml.stream.events.XMLEvent;
import java.io.InputStream;
import java.util.Iterator;
public class MyStax {
/**
* 當前正在處理的sku
*/
private static Sku sku;
/**
* 當前正在處理的節點名稱
*/
private static String tagName;
public static void main(String[] args) throws Exception {
parseSku();
}
public static void parseSku() throws Exception {
XMLInputFactory inputFactory = XMLInputFactory.newInstance();
InputStream inputStream = ClassLoader.getSystemResourceAsStream("skus.xml");
XMLEventReader xmlEventReader = inputFactory.createXMLEventReader(inputStream);
while (xmlEventReader.hasNext()) {
XMLEvent event = xmlEventReader.nextEvent();
// 開始節點
if (event.isStartElement()) {
StartElement startElement = event.asStartElement();
String name = startElement.getName().toString();
if ("sku".equals(name)) {
sku = new Sku();
Iterator iterator = startElement.getAttributes();
while (iterator.hasNext()) {
Attribute attribute = (Attribute) iterator.next();
if ("id".equals(attribute.getName().toString())) {
sku.setId(Long.valueOf(attribute.getValue()));
}
}
}
tagName = name;
}
// 字元
if (event.isCharacters()) {
String data = event.asCharacters().getData().trim();
if (StringUtils.isNotEmpty(data)) {
if ("name".equals(tagName)) {
sku.setName(data);
}
if ("price".equals(tagName)) {
sku.setPrice(Double.valueOf(data));
}
}
}
// 結束節點
if (event.isEndElement()) {
String name = event.asEndElement().getName().toString();
if ("sku".equals(name)) {
System.out.println(JSON.toJSONString(sku));
// 處理業務邏輯
// ...
}
}
}
}
}
以上程式碼與SAX解析的邏輯是等價的，用XMLEventReader作為迭代器從流中讀取事件，迴圈遍歷事件迭代器，再根據事件型別做分類處理。有興趣的小夥伴可以自己動手嘗試一下，探索更多StAX解析的細節。
四、結論
EventModel、SXSSF、EasyExcel和Xlsx-streamer分別針對UserModel的記憶體佔用問題給出了各自的解決方案，下面是對所有本文提到的Excel API的對比：
| UserModel | EventModel | SXSSF | EasyExcel | Xlsx-streamer | |
|---|---|---|---|---|---|
| 記憶體佔用量 | 高 | 較低 | 低 | 低 | 低 |
| 全表隨機存取 | 是 | 否 | 否 | 否 | 否 |
| 讀Excel | 是 | 是 | 否 | 是 | 是 |
| 讀取方式 | DOM | SAX | -- | SAX | StAX |
| 寫Excel | 是 | 是 | 是 | 是 | 否 |
建議您根據自己的使用場景選擇適合的API：
-
處理大批次Excel檔案的需求，推薦選擇POI UserModel、EasyExcel；
-
讀取大批次Excel檔案，推薦選擇EasyExcel、Xlsx-streamer；
-
寫入大批次Excel檔案，推薦選擇SXSSF、EasyExcel。
使用以上API，一定可以滿足關於Excel開發的需求。當然Excel API不止這些，還有許多同型別的API，歡迎大家多多探索和創新。
頁面連結：
POI官網： https://poi.apache.org/
EasyExcel官網：https://easyexcel.opensource.alibaba.com
Xlsx-streamer Github： https://github.com/monitorjbl/excel-streaming-reader
作者：京東保險 孫昊宇
來源：京東雲開發者社群