Apache POI 中 HSSFWorkbook、XSSFWorkbook 和 SXSSFWorkbook 的比較

1. 概述

Apache POI 是一個開源程式庫，允許我們以程式設計方式處理 Microsoft Office 文檔，包括 Excel。 Apache POI 具有三個不同的類別可用於建立工作簿： HSSFWorkbook 、 XSSFWorkbook和SXSSFWorkbook 。

在本教程中，我們將比較這三個類別的功能並進行一些評估，以幫助我們為特定用例選擇最佳選項。

2. 建立 Excel 文件

在比較它們之前，我們先快速回顧如何使用 Apache POI 產生 Excel 檔案。我們需要pom.xml中的Apache POI和POI OOXML 架構依賴項：

<dependency>

 <groupId>org.apache.poi</groupId>

 <artifactId>poi</artifactId>

 <version>5.3.0</version>

 </dependency>

 <dependency>

 <groupId>org.apache.poi</groupId>

 <artifactId>poi-ooxml</artifactId>

 <version>5.3.0</version>

 </dependency>

要建立Workbook的實例，我們需要呼叫目標Workbook類別的預設建構子。之後，我們可以將內容寫入Workbook ，然後將Workbook寫入目標OutputStream 。

在下面的範例中，我們使用XSSFWorkbook建立一個Workbook並將一些內容寫入test.xlsx檔案：

try (Workbook workbook = new XSSFWorkbook();

 OutputStream outputStream = new BufferedOutputStream(new FileOutputStream("test.xlsx"))) {

 Sheet sheet = workbook.createSheet("test");

 sheet.createRow(0).createCell(0).setCellValue("test content");

 workbook.write(outputStream);

 }

替換Workbook類很簡單，因為我們只需將XSSFWorkbook的實例更改為我們想要使用的任何Workbook類，之後一切都一樣：在 Excel 文件中建立工作表、行和列。

另一件要記住的事情是， HSSFWorkbook用於舊的 Excel 格式，因此輸出檔案需要具有.xls副檔名。 XSSFWorkbook和SXSSFWorkbook用於較新的 Excel 格式，該格式提供副檔名為.xlsx輸出檔。

3. 一般比較

Apache POI 中可用的三個Workbook類別是HSSFWorkbook 、 XSSFWorkbook和SXSSFWorkbook.它們具有相似之處，但提供不同的功能來滿足不同的文件格式和用例。以下是其主要特徵的快速總結：

	HSSF工作簿	XSSF工作簿	SXSSF工作簿
文件格式	.xls（BIFF8 二進位文件，Excel 97-2003）	.xlsx（OpenXML、Excel 2007+）	.xlsx（OpenXML、Excel 2007+）
每張最大行數	65,536	1,048,576	1,048,576
每張紙的最大列數	256	16,384	16,384
串流媒體工作簿	不	不	是的
記憶體使用情況	高的	高的	低的

簡而言之， HSSFWorkbook產生舊版.xls格式的 Excel 檔案。 XSSFWorkbook和SXSSFWorkbook以 Excel 2007 及更高版本使用的基於 XML 的.xlsx格式建立檔案。

HSSFWorbook和XSSFWorkbook都是非串流工作簿，將所有資料行保留在記憶體中，而SXSSFWorkbook是串流工作簿，僅在記憶體中保留一定數量的行。因此，如果資料集很大，記憶體效率會更高。

4. `SXSSFWorkbook`中不支援的功能

當視窗中的行數達到稱為行存取視窗大小的閾值時，流程工作簿將刷新行。預設值是100，但我們可以使用建構函數來更改它。例如，讓我們看看如何建立一個視窗大小為 50 的工作簿：

Workbook workbook = new SXSSFWorkbook(50);

由於這種串流行為，同時存取所有行資訊是不可能的，這有時會導致某些 Apache POI 函數的呼叫失敗。在以下小節中，我們將檢查SXSSFWorkbook不支援哪些函數：

4.1.測試設定

讓我們設定一些測試來驗證在流程工作簿中不起作用的功能。我們將在工作表中建立一個包含兩行資料和一列的SXSSFWorkbook實例。

在我們的演示中，我們將明確地將視窗大小設為 1。

@BeforeEach

 void setup() {

 workbook = new SXSSFWorkbook(1);

 sheet = workbook.createSheet("Test Sheet");

 sheet.createRow(0).createCell(0).setCellValue(5);

 sheet.createRow(1).createCell(0).setCellValue(15);

 }

4.2.自動調整列大小

自動調整列大小功能會自動將列的寬度設定為該列中最長的儲存格值，以便資料完全可見，而無需手動調整大小。這應該在串流期間失敗，因為它不知道行寬度：

@Test

 void whenAutoSizeColumnOnSXSSFWorkbook_thenThrowsIllegalStateException() {

 assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0));

 }

4.3.複製表

克隆工作表函數會建立Workbook中現有工作表的副本。串流傳輸不支援此操作，因為它不會將工作表中的所有資料保留在記憶體中：

@Test

 void whenCloneSheetOnSXSSFWorkbook_thenThrowsIllegalStateException() {

 assertThrows(IllegalStateException.class, () -> workbook.cloneSheet(0));

 }

4.4.取得行

現在，讓我們嘗試取得已經刷新的行。它將從工作表傳回null ：

@Test

 void whenGetRowOnSXSSFWorkbook_thenReturnNull() {

 Row row = sheet.getRow(0);

 assertThat(row).isNull();

 }

但是，在視窗大小為 2 的情況下，它將傳回該行的實例，因為它尚未被刷新。

4.5.配方評估

公式計算是指對Excel單元格中存在的公式結果進行重新計算。與先前的情況類似，僅當行未刷新時才可能發生評估：

@Test

 void whenEvaluateFormulaCellOnSXSSFWorkbook_thenThrowsIllegalStateException() {

 Cell formulaCell = sheet.createRow(sheet.getLastRowNum()).createCell(0);

 formulaCell.setCellFormula("SUM(A1:B1)");



 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

 assertThrows(SXSSFFormulaEvaluator.RowFlushedException.class,

 () -> evaluator.evaluateFormulaCell(formulaCell));

 }

4.6.移動列

最後，讓我們看看移動列，它將工作表中的現有列向左或向右移動。預計在流程工作簿中會失敗，因為移動列顯然需要工作表的整個資料都在記憶體中：

@Test

 void whenShiftColumnsOnSXSSFWorkbook_thenThrowsUnsupportedOperationException() {

 assertThrows(UnsupportedOperationException.class, () -> sheet.shiftColumns(0, 2, 1));

 }

5. 評價

我們已經討論了三個Workbook類別之間的功能差異。現在，是時候進行實驗來比較執行時間和記憶體消耗了。這些是根據我們的要求選擇Workbook類別的基本指標。

為了進行比較，我們將採用 JMH（Java Microbenchmark Harness）進行基準測試。讓我們將 JMH 依賴項新增到pom.xml中。核心處理器和註解處理器都可以在 Maven Central 中找到：

<dependency>

 <groupId>org.openjdk.jmh</groupId>

 <artifactId>jmh-core</artifactId>

 <version>1.37</version>

 </dependency>

 <dependency>

 <groupId>org.openjdk.jmh</groupId>

 <artifactId>jmh-generator-annprocess</artifactId>

 <version>1.37</version>

 </dependency>

5.1.實驗設定

在我們的實驗中，讓我們透過所選的Workbook類別建立一個工作表，並將指定數量的行寫入 Excel 檔案中，每個行包含 256 列，所有列中的文字都相同：

Sheet sheet = workbook.createSheet();

 for (int n=0;n<iterations;n++) {

 Row row = sheet.createRow(sheet.getLastRowNum()+1);

 for (int c=0;c<256;c++) {

 Cell cell = row.createCell(c);

 cell.setCellValue("abcdefghijklmnopqrstuvwxyz");

 }

 }

我們將對不同數量的行執行測試：2,500、5,000、10,000、20,000 和 40,000 行。將根據執行時間和記憶體消耗測試三類Workbook 。我們將每組實驗進行三次，並取平均值作為結果。

5.2.執行時間

讓我們看看每個工作簿類別將一定數量的行寫入 Excel 檔案所需的執行時間（以毫秒為單位）：

行數	HSSF工作簿	XSSF工作簿	SXSSF工作簿
2,500 人	73	2,658	296
5,000	174	4,522	612
10,000	第347章	10,994	1,808
20,000	第754章	21,733	3,751
40,000	1,455	42,331	7,342

在這三個類別中， HSSFWorkbook始終比XSSFWorkbook和SXSSFWorkbook更快。 XSSFWorkbook顯示了最高的執行時間，比HSSFWorkbook慢約 30 倍。 SXSSFWorkbook類提供了兩者之間的折衷方案。

出現此類結果的原因可能是二進位.xls格式處理起來不太複雜。很明顯，基於 XML 的.xlsx格式需要更多處理，並且對於較大的資料集，這種速度下降會更加明顯。

5.3.記憶體消耗

讓我們回顧一下寫入相同行數時每個工作簿類別的記憶體消耗（以兆位元組為單位）：

行數	HSSF工作簿	XSSF工作簿	SXSSF工作簿
2,500 人	828	1,871	258
5,000	1,070	2,926	212
10,000	1,268	4,136	209
20,000	1,766	7,443	209
40,000	1,475	10,119	210

對於HSSFWorkbook和XSSFWorkbook ，記憶體消耗隨著行數的增加而增加。這是因為這些Workbook類別將所有資料儲存在記憶體中。但是， XSSFWorkbook成長速度明顯快於HSSFWorkbook 。

就記憶體效率而言， SXSSFWorkbook是明顯的贏家。無論行數多少，其記憶體消耗幾乎保持不變，約為 210MB。

這是由於其流行為，在任何給定時間只有一小部分行保留在記憶體中。這使得SXSSFWorkbook非常適合處理大型資料集而不會耗盡記憶體。

六、結論

HSSFWorkbook 、 XSSFWorkbook和SXSSFWorkbook有不同的用例：

HSSFWorkbook速度最快，但僅限於舊的.xls格式和小型資料集。
XSSFWorkbook支援.xlsx格式的大多數 Excel 功能。然而，這非常消耗記憶體。
SXSSFWorkbook擅長處理大數據集，並且透過其串流功能僅使用很少的記憶體。然而，與XSSFWorkbook.

一般來說，對於大檔案我們可以選擇SXSSFWorkbook ，對於功能完整我們可以選擇XSSFWorkbook ，對於相容舊版Excel格式我們可以選擇HSSFWorkbook 。

與往常一樣，所有原始程式碼都可以在 GitHub 上取得。

本作品係原創或者翻譯，採用《署名-非商業性使用-禁止演繹4.0國際》許可協議