?【Alibaba工具型技術系列】「EasyExcel技術專題」摒除OOM！讓你的Excel操作變得更加優雅和安全

前提概要

針對於後端開發者而言的，作為報表的匯入和匯出是一個很基礎且有很棘手的問題！之前常用的工具和方案大概有這麼幾種：

1. JXL（Java Excel API 工具服務），此種只支援xls的檔案格式，而且對於記憶體的管理特別的差，現在基本不用了！
2. 目前大多數會操作Excel工具服務或者解析都是利用Apache POI進行操作。
3. 其他第三方的工具很多也是基於POI作為實現基礎！

存在隱患問題

因為當資料量特別大的時候，比如：說Excel匯出，如果資料量在百萬級，很有可能會出現倆點記憶體溢位的問題以及頁面極具卡頓。

解決方案

• 首先，在百萬級大資料量Excel檔案的匯出我們可以採用分批查詢資料來避免記憶體溢位的劇增！

• 此外，POI給出的方案是：使用SXSSFWorkbook方式快取資料到檔案上以解決下載大檔案EXCEL卡死頁面的問題。

  • SXSSFWorkbook資料模型：主要可以解決在下載傳輸到瀏覽器的時候大Excel檔案轉換的輸出流記憶體溢位
  • 此外其還可以通過其建構函式執指定在記憶體中快取的行數，剩餘的會自動快取在硬碟的臨時目錄上，同時，並不會存在頁面卡頓的情況。
  1. SXSSF機制而言還是需要手動進行封裝及定製化開發增加了一定的工作量！
  2. POI的操作方式仍然還是存在記憶體佔用過大的問題，仍會存在記憶體溢位的隱患。
  3. 存在空迴圈和整除的時候資料有缺陷的問題。

更優秀的選擇

對此阿里巴巴發明咯一個“萬金油”！EasyExcel（簡單Excel操作工具，讓Excel變得更簡單！），除此之外它可以將解析的Excel的記憶體佔用控制在KB級別，並且絕對不會記憶體溢位，還有就是速度極快， 不用想了就它了！

---

EasyExcel的介紹說明

EasyExcel是一個基於Java實現的、以節省記憶體為主要目標的的讀寫Excel檔案的開源專案。經過官方統計，在儘可能節約記憶體的情況下支援讀寫百M的Excel檔案的讀寫操作能力！

• 原始碼庫（github地址）：https://github.com/alibaba/easyexcel
• 官方文件：https://alibaba-easyexcel.github.io/index.html

技術原理對比

POI

當利用POI去讀取Excel時，首先會將資料全部載入到記憶體中，然後返回給呼叫者，當資料量比較大時，及其容易發生OOM。以下是執行流程圖：

EasyExcel

與POI 不用的是，EasyExcel主要是採用sax模式一行一行解析，並將一行的解析結果以觀察者的模式通知處理，即使資料量較大時也不會發生OOM,以下是其執行流程圖

技術原理圖

借用官方圖：

節省記憶體的開銷

借用官方圖片：https://alibaba-easyexcel.github.io/images/large.png

64M記憶體1分鐘內讀取75M(46W行25列)的ExcelExcel 記憶體開銷圖。當然還有急速模式能更快，但是記憶體佔用會在100M多一點。其實就是拿空間換時間！

Maven倉庫依賴

我用的版本是2.2.6

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.6</version>
</dependency>

注： 系統內部如果有POI的包一定讓poi和poi-ooxml的版本要保持一致。

注： 如果是springboot2.0,則不需要poi依賴，如果是1.0，則需要poi依賴，並且poi和poi-ooxml的版本要保持一致。

<!-- poi -->
 <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.17</version>
 </dependency>
 <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.17</version>
 </dependency>

基礎API介紹（參考官方文件）

• EasyExcel 入口類，用於構建開始各種操作，屬於典型的門面+工廠模式

public void testReadEntity() {
  // 被讀取的檔案絕對路徑
  String fileName = "path/testDemo.xlsx";
  // 接收解析出的目標物件（Entity）指的是你的實體類
   List<Entity> entityList = new ArrayList<>();
  // 這裡需要指定讀用哪個class去讀，然後讀取第一個sheet檔案流會自動關閉
  // excel中表的列要與物件的欄位相對應
   EasyExcel.read(fileName, Entity.class, new AnalysisEventListener<Student>() {
    // 每解析一條資料都會呼叫該方法
    @Override
    public void invoke(Entity entity, AnalysisContext analysisContext) {
      System.out.println("解析一條Row行物件：" + JSON.toJSONString(entity));
      entityList.add(student);
	}
    // 解析完畢的回撥方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("excel檔案讀取完畢！");
    }
  }).sheet().doRead();
}

// 通過Map作為整體的資料結構模型
public void readWithoutObj() {
  // 被讀取的檔案絕對路徑
  String fileName = "path/testDemo.xlsx";
  // 接收結果集，為一個List列表，每個元素為一個map物件，key-value對為excel中每個列對應的值
  List<Map<Integer,String>> resultList = new ArrayList<>();
  EasyExcel.read(fileName, new AnalysisEventListener<Map<Integer,String>>() {
    @Override
    public void invoke(Map<Integer, String> map, AnalysisContext analysisContext) {
      System.out.println("解析到一條資料：" + JSON.toJSONString(map));
      resultList.add(map);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
      System.out.println("excel檔案解析完畢！" + JSON.toJSONString(resultList));
    }
  }).sheet().doRead();
}

• invoke方法代表每解析一行就會呼叫一次，data資料表示解析出來一行的資料。

• doAfterAllAnalysed 該方法表示將所有資料解析完畢以後才會去呼叫該方法。

• 內部實現機制：（可以理解成一個excel物件，一個excel只要構建一個）

  • ExcelReaderBuilder 構建出一個 ReadWorkbook

    • excelType 當前excel的型別 預設會自動判斷
    • inputStream 與file二選一。讀取檔案的流，如果接收到的是流就只用，不用流建議使用file引數。因為使用了inputStream easyexcel會幫忙建立臨時檔案，最終還是file
    • file 與inputStream二選一。讀取檔案的檔案。
    • autoCloseStream 自動關閉流。
    • readCache 預設小於5M用 記憶體，超過5M會使用 EhCache,這裡不建議使用這個引數。
    • useDefaultListener @since 2.1.4 預設會加入ModelBuildEventListener 來幫忙轉換成傳入class的物件，設定成false後將不會協助轉換物件，自定義的監聽器會接收到Map<Integer,CellData>物件，如果還想繼續接聽到class物件，請呼叫readListener方法，加入自定義的beforeListener、 ModelBuildEventListener、 自定義的afterListener即可。
    • 方法體現：
      • EasyExcel.read 該方法是用來建立ExcelReaderBuilder物件，該物件就是用來解析Excel文件
      • read方法需要傳入三個引數：
        • 第一個引數：需要解析檔案的路徑，當然除了傳入一個檔案路徑以外，還可以傳入InputStream
        • 第二引數：資料型別的Class型別物件，可以不傳
        • 第三個引數：事件監聽器，在之前介紹這款框架時說過，該框架是基於SAX的一種解析，載入一行資料到記憶體就會去解析一行，主要是為了節約記憶體。

  • ExcelWriterBuilder 構建出一個 WriteWorkbook

    • excelType 當前excel的型別 預設xlsx
    • outputStream 與file二選一。寫入檔案的流
    • file 與outputStream二選一。寫入的檔案
    • templateInputStream 模板的檔案流
    • templateFile 模板檔案
    • autoCloseStream 自動關閉流。
    • password 寫的時候是否需要使用密碼
    • useDefaultStyle 寫的時候是否是使用預設頭

  • WriteTable（就把excel的一個Sheet,一塊區域看一個table）引數

    • tableNo 需要寫入的編碼。預設0

• 內部實現機制：（可以理解成excel裡面的一頁,每一頁都要構建一個）

  • ExcelReaderSheetBuilder：構建出一個 ReadSheet物件。
    • sheetNo 需要讀取Sheet的編碼，建議使用這個來指定讀取哪個Sheet。
    • sheetName 根據名字去匹配Sheet,excel 2003不支援根據名字去匹配。
  • ExcelWriterSheetBuilder 構建出一個 WriteSheet物件。
    • 需要寫入的編碼。預設0
    • sheetName 需要些的Sheet名稱，預設同sheetNo

• 內部實現機制：（可以理解成excel裡面的一頁,每一頁都要構建一個）

  • ReadListener：每一行讀取完畢後都會呼叫ReadListener來處理資料
  • WriteHandler ：每一個操作包括建立單元格、建立表格等都會呼叫WriteHandler來處理資料

所有配置都是繼承的，Workbook的配置會被Sheet繼承，所以在用EasyExcel設定引數的時候，在EasyExcel...sheet()方法之前作用域是整個sheet,之後針對單個sheet。

• 相關使用註解
  • ExcelProperty index 指定寫到第幾列，預設根據成員變數排序。value指定寫入的名稱，預設成員變數的名字，多個value可以參照快速開始中的複雜頭。
  • ExcelIgnore 預設所有欄位都會寫入excel，這個註解會忽略這個欄位
  • DateTimeFormat 日期轉換，將Date寫到excel會呼叫這個註解。裡面的value參照java.text.SimpleDateFormat
  • NumberFormat 數字轉換，用Number寫excel會呼叫這個註解。裡面的value參照java.text.DecimalFormat
  • ExcelIgnoreUnannotated 預設不加ExcelProperty的註解的都會參與讀寫，加了不會參與。
  • 使用案例：

  	@Data
  	public class TestEntity {
  	  /**
  	   * 從0開始，2代表強制讀取第三個，
  	   * 不建議 index 和 name 同時用
  	   */
  	  @ExcelProperty(index = 2)
  	  private Double doubleData;
  	  /**
  	   * 用名字去匹配，這裡需要注意，
  	   * 名字重複，會導致只有一個欄位讀取到資料
  	   */
  	  @ExcelProperty("字串標題")
  	  private String string;
  
  	  @ExcelProperty("日期標題")
  	  private Date date;
  	}
  

  	@Data
  	public class MultiHeaderEntity implements Serializable {
  
  		@ExcelProperty(value = {"一層資訊","二層1"})
  		private Integer id;
  
  		@ExcelProperty(value = {"一層資訊","二層2"})
  		private String name;
  
  		@ExcelProperty(value = {"一層資訊","二層4"})
  		private String description;
  
  		@ExcelProperty(value = {"一層資訊","二層3"})
  		private Date birthday;
  	}
  

  	@Data
  	public class ConverterData {
  		/**
  		 * 我自定義 轉換器，不管資料庫傳過來什麼 。我給他加上“自定義：”
  		 */
  		@ExcelProperty(converter = CustomStringStringConverter.class)
  		private String string;
  		/**
  		 * 這裡用string 去接日期才能格式化。我想接收年月日格式
  		 */
  		@DateTimeFormat("yyyy年MM月dd日HH時mm分ss秒")
  		private String date;
  		/**
  		 * 我想接收百分比的數字
  		 */
  		@NumberFormat("#.##%")
  		private String doubleData;
  	}
  

注意 ：如果使用該類的物件去裝載Excel中的資料，那麼讀取時就只能讀取以下樣式的Excel資料模型，否則資料部分丟失或者全部丟失

實戰案例

讀取Excel實現Demo

資料模型DemoModel

@Data
public class DemoModel {
    private String attribute1;
    private Date attribute2;
    private Double attribute3;
}

讀取回撥監聽器

注意：DemoModelAnalysisListener 不要是單例模式或者全域性共享，要每次讀取excel都要new,否則會出現複用之前讀取的過程資料！

@Slf4j
public class DemoModelAnalysisListener extends AnalysisEventListener<DemoData> {
    /**
     * 每隔500條儲存資料庫，然後清理list ，方便記憶體回收
     */
    static final int BATCH_COUNT = 500;
    List<DemoModel> list = new ArrayList<>();
    /**
     * 這個也可以是一個service。當然如果不用儲存這個物件沒用。
     */
    private DemoService demoService;
    public DemoModelAnalysisListener(DemoService demoService) {
        this.demoService = demoService;
    }
    /**
     * 這個每一條資料解析都會來呼叫
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("解析到一條資料:{}", JSON.toJSONString(data));
        list.add(data);
        // 達到BATCH_COUNT了，需要去儲存一次資料庫，
		// 防止資料幾萬條資料在記憶體，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
        }
    }

	/**
     * 所有資料解析完成了 都會來呼叫
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 這裡也要儲存資料，確保最後遺留的資料也儲存到資料庫
        saveData();
        log.info("所有資料解析完成！");
    }

    /**
     * 加上儲存資料庫
     */
    private void saveData() {
        log.info("{}條資料，開始儲存資料庫！", list.size());
        demoService.insert(list);
		            // 儲存完成清理 list
		list.clear();//解析結束銷燬不用的資源
        log.info("儲存資料庫成功！");
    }
}

讀取操作測試類

    @Test
    public void readExcelTest() {
        // 寫法1：
		DemoService demoService = getService(); //獲取service業務實現類
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 這裡 需要指定讀用哪個class去讀，然後讀取第一個sheet 檔案流會自動關閉
        EasyExcel.read(fileName, DemoData.class, new DemoModelAnalysisListener(demoService)).sheet().doRead();

        // 寫法2：
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(fileName, DemoData.class, new DemoModelAnalysisListener(demoService)).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            excelReader.read(readSheet);
        } finally {
            if (excelReader != null) {
                // 這裡千萬別忘記關閉，讀的時候會建立臨時檔案，到時磁碟會崩的
                excelReader.finish();
            }
        }
    }

寫入Excel實現Demo

寫入資料模型

@Data
public class DemoData {
    @ExcelProperty("字串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數字標題")
    private Double doubleData;
    /**
     * 忽略這個欄位
     */
    @ExcelIgnore
    private String ignore;
}

寫入操作程式碼

    @Test
    public void demoWriteTest() {
        // 寫法1
        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 這裡 需要指定寫用哪個class去寫，然後寫到第一個sheet，名字為模板 然後檔案流會自動關閉
        // 如果這裡想使用03 則 傳入excelType引數即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
		// 寫法2
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 這裡 需要指定寫用哪個class去寫
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName, DemoData.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        } finally {
            // 千萬別忘記finish 會幫忙關閉流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

複雜頭寫入

@Data
public class MultiHeaderEntity implements Serializable {

			@ExcelProperty(value = {"一層資訊","二層1"})
			private Integer id;

			@ExcelProperty(value = {"一層資訊","二層2"})
			private String name;

			@ExcelProperty(value = {"一層資訊","二層4"})
			private String description;

			@ExcelProperty(value = {"一層資訊","二層3"})
			private Date birthday;
		}

複雜頭的寫入操作

    @Test
    public void complexHeadWrite() {
        String fileName = TestFileUtil.getPath() + "complexHeadWrite" + System.currentTimeMillis() + ".xlsx";
        // 這裡 需要指定寫用哪個class去寫，然後寫到第一個sheet，名字為模板 然後檔案流會自動關閉
        EasyExcel.write(fileName, MultiHeaderEntity.class).sheet("複雜").doWrite(datalist());
    }