java 匯出 excel 最佳實踐，java 大檔案 excel 避免OOM(記憶體溢位) excel 工具框架

葉止水發表於2018-11-26
JavaExcelOOM記憶體溢位框架

產品需求

產品經理需要匯出一個頁面的所有的資訊到 EXCEL 檔案。

需求分析

對於 excel 匯出，是一個很常見的需求。

最常見的解決方案就是使用 poi 直接同步匯出一個 excel 檔案。

客戶體驗 & 服務效能

  • 客戶體驗

如果匯出的檔案比較大，比如幾十萬條資料，同步匯出頁面就會卡主，使用者無法進行其他操作。

  • 服務效能

匯出的時候，任務比較耗時就會阻塞主執行緒。

如果匯出的服務是暴露給外部（前後端分離），這種大量的資料傳輸十分消耗效能。

解決方案

使用異常處理匯出請求，後臺 MQ 通知自己進行處理。

MQ 消費之後，多執行緒處理 excel 檔案匯出，生成檔案後上傳到 FTP 等檔案伺服器。

前端直接查詢並且展現對應的任務執行列表，去 FTP 等檔案伺服器下載檔案即可。

EXCEL 匯出需要考慮的問題

OOM

正常的 poi 在處理比較大的 excel 的時候，會出現記憶體溢位。

網上的解決方案也比較多。

比如官方的 SXSSF (Since POI 3.8 beta3) 解決方式。

或者使用封裝好的包

  1. easypoi ExcelBatchExportServer

  2. hutool BigExcelWriter

原理都是強制使用 xssf 版本的Excel。

你也可以使用 easyexcel，當然這個註釋文件有些欠缺，而且設計的比較複雜，不是很推薦。

我這裡使用的是 hutool BigExcelWriter， 懶得自己再寫一遍。

FULL GC

如果一次查詢 100W 條資料庫，然後把這些資訊全部載入到記憶體中，是不可取的。

建議有2個：

  1. 限制每一次分頁的數量。比如一次最多查詢 1w 條。分成 100 次查詢。(必須)

  2. 限制查詢得總條數。比如限制為最多 10W 條。（根據實際情況選擇）

雖然使用者提出要匯出類似於 3 個月的所有資訊，但是數量太多，毫無意義。(提出者自己可能體會不到)

儘量避免 FULL-GC 的情況發生，因為目前的所有方式對於 excel 的輸出流都會佔用記憶體，100W 條很容易導致 FULL-GC。

資料庫的壓力

去資料庫讀取的時候一定要記得分頁，免得給資料庫太大的壓力。

一次讀取太多，也會導致記憶體直線上升。

比如 100W 條資料，則分成 100 次去資料庫讀取。

網路傳輸

傳統的 excel 匯出，都是前端一個請求，直接 HTTP 同步返回。匯出 100W 條，就在那裡傻等。

這客戶體驗不友好，而且網路傳輸，系統佔用多種問題。

建議使用非同步處理的方式，將檔案上傳到檔案伺服器。前端直接去檔案伺服器讀取。

程式設計的便利性

對於上面提到的工具，比如 Hutool，在表頭的處理方面沒法很方便的統一。

你可以自己定義類似於 easypoi/easyexcel 中的註解，自己反射解析。

然後統一處理表頭即可。

IExcel 方便優雅的 excel 框架

特性

  • OO 的方式操作 excel，程式設計更加方便優雅。

  • sax 模式讀取，SXSS 模式寫入。避免 excel 大檔案 OOM。

  • 基於註解，程式設計更加靈活。

  • 寫入可以基於物件列表，也可以基於 Map，實際使用更加方便。

  • 設計簡單，註釋完整。方便大家學習改造。

後期特性

  • 讀取跳過空白行

  • excel 樣式相關的註解開發

創作緣由

實際工作和學習中，apache poi 操作 excel 過於複雜。

近期也看了一些其他的工具框架：

  • easypoi

  • easyexcel

  • hutool-poi

都或多或少難以滿足自己的實際需要，於是就自己寫了一個操作 excel 匯出的工具。

快速開始

引入 Jar

使用 maven 管理。

<dependency>
     <groupId>com.github.houbb</groupId>
     <artifactId>iexcel</artifactId>
     <version>0.0.2</version>
</dependency>
複製程式碼

定義物件

你可以直接參考 ExcelUtilTest.java

定義一個需要寫入/讀取的 excel 物件。

  • ExcelFieldModel.java

只有宣告瞭 @ExcelField 的屬性才會被處理，使用說明：@ExcelField

public class ExcelFieldModel {

    @ExcelField
    private String name;

    @ExcelField(headName = "年齡")
    private String age;

    @ExcelField(mapKey = "EMAIL", writeRequire = false, readRequire = false)
    private String email;

    @ExcelField(mapKey = "ADDRESS", headName = "地址", writeRequire = true)
    private String address;
    
    //getter and setter
}
複製程式碼

寫入例子

IExcelWriter 的實現

IExcelWriter 有幾個實現類，你可以直接 new 或者藉助 ExcelUtil 類去建立。

IExcelWriter 實現類 ExcelUtil 如何建立 說明
HSSFExcelWriter ExcelUtil.get03ExcelWriter() 2003 版本的 excel
XSSFExcelWriter ExcelUtil.get07ExcelWriter() 2007 版本的 excel
SXSSFExcelWriter ExcelUtil.getBigExcelWriter() 大檔案 excel，避免 OOM

IExcelWriter 介面說明

寫入到 2003

  • excelWriter03Test()

一個將物件列表寫入 2003 excel 檔案的例子。

/**
 * 寫入到 03 excel 檔案
 */
@Test
public void excelWriter03Test() {
    // 待生成的 excel 檔案路徑
    final String filePath = "excelWriter03.xls";

    // 物件列表
    List<ExcelFieldModel> models = buildModelList();

    try(IExcelWriter excelWriter = ExcelUtil.get03ExcelWriter();
        OutputStream outputStream = new FileOutputStream(filePath)) {
        // 可根據實際需要，多次寫入列表
        excelWriter.write(models);

        // 將列表內容真正的輸出到 excel 檔案
        excelWriter.flush(outputStream);
    } catch (IOException e) {
        throw new ExcelRuntimeException(e);
    }
}
複製程式碼
  • buildModelList()
/**
 * 構建測試的物件列表
 * @return 物件列表
 */
private List<ExcelFieldModel> buildModelList() {
    List<ExcelFieldModel> models = new ArrayList<>();
    ExcelFieldModel model = new ExcelFieldModel();
    model.setName("測試1號");
    model.setAge("25");
    model.setEmail("123@gmail.com");
    model.setAddress("貝克街23號");

    ExcelFieldModel modelTwo = new ExcelFieldModel();
    modelTwo.setName("測試2號");
    modelTwo.setAge("30");
    modelTwo.setEmail("125@gmail.com");
    modelTwo.setAddress("貝克街26號");

    models.add(model);
    models.add(modelTwo);
    return models;
}
複製程式碼

一次性寫入到 2007 excel

有時候列表只寫入一次很常見，所有就簡單的封裝了下：

/**
 * 只寫入一次列表
 * 其實是對原來方法的簡單封裝
 */
@Test
public void onceWriterAndFlush07Test() {
    // 待生成的 excel 檔案路徑
    final String filePath = "onceWriterAndFlush07.xlsx";

    // 物件列表
    List<ExcelFieldModel> models = buildModelList();

    // 對應的 excel 寫入物件
    IExcelWriter excelWriter = ExcelUtil.get07ExcelWriter();

    // 只寫入一次列表
    ExcelUtil.onceWriteAndFlush(excelWriter, models, filePath);
}
複製程式碼

讀取例子

excel 讀取時會根據檔名稱判斷是哪個版本的 excel。

IExcelReader 的實現

IExcelReader 有幾個實現類，你可以直接 new 或者藉助 ExcelUtil 類去建立。

IExcelReader 實現類 ExcelUtil 如何建立 說明
ExcelReader ExcelUtil.getExcelReader() 小檔案的 excel 讀取實現
Sax03ExcelReader ExcelUtil.getBigExcelReader() 大檔案的 2003 excel 讀取實現
Sax07ExcelReader ExcelUtil.getBigExcelReader() 大檔案的 2007 excel 讀取實現

IExcelReader 介面說明

excel 讀取的例子

/**
 * 讀取測試
 */
@Test
public void readWriterTest() {
    File file = new File("excelWriter03.xls");
    IExcelReader<ExcelFieldModel> excelReader = ExcelUtil.getExcelReader(file);
    List<ExcelFieldModel> models = excelReader.readAll(ExcelFieldModel.class);
    System.out.println(models);
}
複製程式碼

ExcelField 註解說明

@ExcelField 的屬性說明如下：

屬性 型別 預設值 說明
mapKey String "" 僅用於生成的入參為 map 時,會將 map.key 對應的值對映到 bean 上。如果不傳：預設使用當前欄位名稱
headName String "" excel 表頭欄位名稱，如果不傳：預設使用當前欄位名稱
writeRequire boolean true excel 檔案是否需要寫入此欄位
readRequire boolean true excel 檔案是否讀取此欄位

IExcelWriter 介面說明

/**
 * 寫出資料，本方法只是將資料寫入Workbook中的Sheet，並不寫出到檔案<br>
 * <p>
 * data中元素支援的型別有：
 *  <pre>
 * 1. Bean，既元素為一個Bean，第一個Bean的欄位名列表會作為首行，剩下的行為Bean的欄位值列表，data表示多行 <br>
 * </pre>
 * @param data 資料
 * @return this
 */
IExcelWriter write(Collection<?> data);

/**
 * 寫出資料，本方法只是將資料寫入Workbook中的Sheet，並不寫出到檔案<br>
 *  將 map 按照 targetClass 轉換為物件列表
 *  應用場景: 直接 mybatis mapper 查詢出的 map 結果，或者其他的構造結果。
 * @param mapList map 集合
 * @param targetClass 目標型別
 * @return this
 */
IExcelWriter write(Collection<Map<String, Object>> mapList, final Class<?> targetClass);

/**
 * 將Excel Workbook刷出到輸出流
 *
 * @param outputStream 輸出流
 * @return this
 */
IExcelWriter flush(OutputStream outputStream);
複製程式碼

指定 sheet

建立 IExcelWriter 的時候，可以指定 sheet 的下標或者名稱。來指定寫入的 sheet。

是否包含表頭

建立 IExcelWriter 的後，可以呼叫 excelWriter.containsHead(bool) 指定是否生成 excel 表頭。

IExcelReader 介面說明

/**
 * 讀取當前 sheet 的所有資訊
 * @param tClass 對應的 javabean 型別
 * @return 物件列表
 */
List<T> readAll(Class<T> tClass);

/**
 * 讀取指定範圍內的
 * @param tClass 泛型
 * @param startIndex 開始的行資訊(從0開始)
 * @param endIndex 結束的行資訊
 * @return 讀取的物件列表
 */
List<T> read(Class<T> tClass, final int startIndex, final int endIndex);
複製程式碼

指定 sheet

建立 IExcelReader 的時候，可以指定 sheet 的下標或者名稱。來指定讀取的 sheet。

注意：大檔案 sax 讀取模式，只支援指定 sheet 的下標。

是否包含表頭

建立 IExcelReader 的後，可以呼叫 excelReader.containsHead(bool) 指定是否讀取 excel 表頭。

擴充閱讀

excel 匯出最佳實踐

iexcel 框架

相關文章