Java使用xlsx-streamer和EasyExcel解決讀取超大excel檔案資料處理方法

红尘沙漏發表於2024-07-27
JavaExcel

前言

最近有個專案在生產環境做資料匯入時，發現開始執行匯入任務會出現cpu狂飆的情況。幾番定位查詢發現是在讀取excel的時候導致此問題的發生，因此在通常使用的為POI的普通讀取，在遇到大資料量excel，50MB大小或數五十萬行的級別的資料容易導致讀取時記憶體溢位或者cpu飆升。需要注意，本文討論的是針對xlsx格式的excel檔案上傳。

關於Excel相關技術

在Java技術生態圈中，可以進行Excel處理的主流技術包括：Apache POI，JXL，Alibaba EasyExcel等。由於JXL只支援Excel2003以下版本，所以不太常見。

Apache POI：基於DOM方式進行解析，將檔案直接載入記憶體，所以速度較快，適合Excel檔案數量不大的應用場景
Alibaba EasyExcel:採用逐行讀取的解析模式，將每一行的解析結果以觀察者模式通知處理（AnalyEventListener），所以比較適合資料體量較大的Excel檔案解析。

問題程式碼

這種方式POI會把檔案的所有內容都載入到記憶體中，讀取大的excel檔案時很容易佔用大量記憶體導致oom的發生，全部檔案載入如下：

  /**
     * POI方式讀取excel
     *
     * @param file
     */
    public static void readExcelByPoi(File file) {
        long start = System.currentTimeMillis();
         //整個檔案都一塊載入
        try (InputStream inp = new FileInputStream(file);
             Workbook wb = WorkbookFactory.create(inp)) {
 
            log.info("==讀取excel完畢，耗時：{}毫秒，", System.currentTimeMillis() - start);
            Sheet sheet = wb.getSheetAt(0);
            //更新總數
            System.out.println("讀取結束行數：" + sheet.getLastRowNum());
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

　　當前引入的poi依賴

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

　　

讀取50MB我本地欄位不是很多50萬行資料
首先在讀取excel檔案的斷點執行之前的cpu和記憶體的佔用分別為50%和42%，上傳的excel大小為50MB，這裡我就不一一帶大家測試了，以上此種方式肯定是行不通的。

解決方案一：xlsx-streamer

我們採用分段快取的方式載入資料到記憶體中，此種方式在建立Workbook物件時藉助xlsx-streamer（StreamingReader） 來建立一個緩衝區域批次地讀取檔案 ，因此不會將整個檔案例項化到物件當中，程式碼如下：

引入依賴：

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <!-- 讀取大量excel資料時使用 -->
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.1.0</version>
        </dependency>

　　

示例程式碼：

    /**
     * 大批次資料讀取 十萬級以上
     * 思路：採用分段快取載入資料，防止出現OOM的情況
     *
     * @param file
     * @throws Exception
     */
    public static void readLagerExcel(File file) throws Exception {
      InputStream inputStream = new FileInputStream(file);
        long start = System.currentTimeMillis();
        try (Workbook workbook = StreamingReader.builder()
                .rowCacheSize(10 * 10)  //快取到記憶體中的行數，預設是10
                .bufferSize(1024 * 4)  //讀取資源時，快取到記憶體的位元組大小，預設是1024
                .open(inputStream)) { //開啟資源，可以是InputStream或者是File，注意：只能開啟.xlsx格式的檔案
 
            Sheet sheet = workbook.getSheetAt(0);
            log.info("==讀取excel完畢，耗時：{}毫秒，", System.currentTimeMillis() - start);
            //遍歷所有的行
            for (Row row : sheet) {
                System.out.println("開始遍歷第" + row.getRowNum() + "行資料：");
                //遍歷所有的列
                for (Cell cell : row) {
                    System.out.print(cell.getStringCellValue() + " ");
                }
                System.out.println(" ");
            }
            //總數
            System.out.println("讀取結束行數：" + sheet.getLastRowNum());
        }
    }

　　

解決方案二：EasyExcel
使用EasyExcel解決大檔案Excel記憶體溢位的問題，基於POI進行封裝最佳化，可以在不考慮效能、記憶體的等因素的情況下，快速完成Excel的讀、寫等功能。

官網： https://easyexcel.opensource.alibaba.com/
github：https://github.com/alibaba/easyexcel

引入依賴

        <!--easyExcel工具-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.1</version>
        </dependency>

　　

示例程式碼


僅做簡單讀取示例:

 /**
  * EasyExcel方式讀取excel
   * 讀取並封裝為物件,ExcelData大家需要的物件
   * @param file
   */
  public static void readExcelByEasyExcel(File file) {
      long start = System.currentTimeMillis();
      List<ExcelData> excelDataList = EasyExcel.read(file).head(ExcelData.class).sheet(0).doReadSync();
      excelDataList.stream().forEach(x -> System.out.println(x.toString()));
      log.info("==讀取excel完畢，耗時：{}毫秒，", System.currentTimeMillis() - start);
  }
 
  /**
    * EasyExcel方式讀取excel
    * 不指定head類
    * @param file
    */
   public static void readExcelByEasyExcel1(File file) {
       long start = System.currentTimeMillis();
       List<Map<Integer, String>> listMap = EasyExcel.read(file).sheet(0).doReadSync();
       listMap.stream().forEach(x -> System.out.println(JSON.toJSONString(x)));
       log.info("==讀取excel完畢，耗時：{}毫秒，", System.currentTimeMillis() - start);
   }

　　

相關文章