010_POI和EasyExcel

目錄

• 常用場景
• 流行技術：Apache POI和EasyExcel
• Apache POI簡介
• EasyExcel簡介
• JAVA解析Excel工具EasyExcel
  • 64M記憶體1分鐘内讀取75M(46W行25列)的Excel
  • Excel讀寫時候記憶體溢出
  • 其他開源架構使用複雜
  • 其他開源架構存在一些BUG修複不及時
  • Excel格式分析格式分析
  • 核心原理
    • 解壓檔案讀取通過檔案形式
    • 避免将全部資料一次加載到記憶體
    • 抛棄不重要的資料
• POI-Excel基本寫
  • 建立空項目
  • 建立子子產品qing-poi
  • 導入依賴
  • Excel的對象
    • 工作簿
    • 工作表
    • 行
    • 單元格
  • 寫03版Excel
  • 寫07版Excel
  • 03和07版的不同
• POI-Excel大檔案寫
  • HSSF寫大檔案（03）
  • XSSF寫大檔案（07）
  • SXSSF寫大檔案（07快速版）
• POI-Excel基本讀
  • 讀03版Excel
  • 讀07版Excel
  • 讀取不同的資料類型
    • 自己處理不同的資料類型
    • 使用cell.toString處理不同的資料類型，但會有日期和長數字會顯示問題
    • 使用cell.toString處理不同的資料類型，自己處理數字和日期顯示
  • 讀取公式及計算
• EasyExcel使用
  • 打開github，找到pom.xml
  • 複制EasyExcel依賴到本地pom.xml
  • 測試簡單的寫
    • 寫實體
    • 導入lombok依賴
    • 寫出檔案
  • 測試簡單的讀
    • 寫監聽器
    • 讀取檔案

1. 将資料導出為Excel表格
2. 将Excel表中的資料錄入到網站資料庫

​

EasyExcel是一個基于Java的簡單、省記憶體的讀寫Excel的開源項目。在盡可能節約記憶體的情況下支援讀寫百M的Excel。
github位址:https://github.com/alibaba/easyexcel
           

Java解析、生成Excel比較有名的架構有Apache poi、jxl。但他們都存在一個嚴重的問題就是非常的耗記憶體，poi有一套SAX模式的API可以一定程度的解決一些記憶體溢出的問題，但POI還是有一些缺陷，比如07版Excel解壓縮以及解壓後存儲都是在記憶體中完成的，記憶體消耗依然很大。easyexcel重寫了poi對07版Excel的解析，能夠原本一個3M的excel用POI sax依然需要100M左右記憶體降低到幾M，并且再大的excel不會出現記憶體溢出，03版依賴POI的sax模式。在上層做了模型轉換的封裝，讓使用者更加簡單友善

當然還有急速模式能更快，但是記憶體占用會在100M多一點

關于EasyExcel

雖然POI是目前使用最多的用來做excel解析的架構，但這個架構并不那麼完美。大部分使用POI都是使用他的userModel模式。userModel的好處是上手容易使用簡單，随便拷貝個代碼跑一下，剩下就是寫業務轉換了，雖然轉換也要寫上百行代碼，相對比較好了解。然而userModel模式最大的問題是在于非常大的記憶體消耗，一個幾兆的檔案解析要用掉上百兆的記憶體。現在很多應用采用這種模式，之是以還正常在跑一定是并發不大，并發上來後一定會OOM或者頻繁的full gc。

對POI有過深入了解的估計才知道原來POI還有SAX模式。但SAX模式相對比較複雜，excel有03和07兩種版本，兩個版本資料存儲方式截然不同，sax解析方式也各不一樣。想要了解清楚這兩種解析方式，才去寫代碼測試，估計兩天時間是需要的。再加上即使解析完，要轉換到自己業務模型還要很多繁瑣的代碼。總體下來感覺至少需要三天，由于代碼複雜，後續維護成本巨大。

由于我們的系統大多數都是大并發的情況下運作的，在大并發情況下，我們會發現poi存在一些bug,如果讓POI團隊修複估計遙遙無期了。是以我們在easyexcel對這些bug做了規避。

• xls是Microsoft Excel2007前excel的檔案存儲格式，實作原理是基于微軟的ole db是微軟com元件的一種實作，本質上也是一個微型資料庫，由于微軟的東西很多不開源，另外也已經被淘汰，了解它的細節意義不大，底層的程式設計都是基于微軟的com元件去開發的。
• xlsx是Microsoft Excel2007後excel的檔案存儲格式，實作是基于openXml和zip技術。這種存儲簡單，安全傳輸友善，同時處理資料也變的簡單。
• csv 我們可以了解為純文字檔案，可以被excel打開。他的格式非常簡單，解析起來和解析文本檔案一樣。

寫有大量資料的xlsx檔案時，POI為我們提供了SXSSFWorkBook類來處理，這個類的處理機制是當記憶體中的資料條數達到一個極限數量的時候就flush這部分資料，再依次處理餘下的資料，這個在大多數場景能夠滿足需求。

讀有大量資料的檔案時，使用WorkBook處理就不行了，因為POI對檔案是先将檔案中的cell讀入記憶體，生成一個樹的結構（針對Excel中的每個sheet，使用TreeMap存儲sheet中的行）。如果資料量比較大，則同樣會産生java.lang.OutOfMemoryError: Java heap space錯誤。POI官方推薦使用“XSSF and SAX（event API）”方式來解決。

分析清楚POI後要解決OOM有3個關鍵。

采用sax模式一行一行解析，并将一行的解析結果以觀察者的模式通知處理。

Excel解析時候會包含樣式，字型，寬度等資料，但這些資料是我們不關心的，如果将這部分資料抛棄可以大大降低記憶體使用。Excel中資料如下Style占了相當大的空間。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qing</groupId>
    <artifactId>qing-poi</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--導入依賴-->
    <dependencies>
        <!--xls(03)-->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <!--xlsx(07)-->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <!--日期格式化工具-->
        <!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.10</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter-api -->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.7.2</version>
        </dependency>


    </dependencies>

</project>
           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 寫03版Excel
     */
    @Test
    public void testWrite03() throws IOException {
        // 1.建立一個工作簿，03版使用對象HSSFWorkbook，07版使用對象XSSFWorkbook
        Workbook workbook = new HSSFWorkbook();
        // 2.建立一個工作表，不傳參預設Sheet1
        Sheet sheet = workbook.createSheet("自定義Sheet1");
        // 3.建立一個行，0表示第一行
        Row row1 = sheet.createRow(0);
        // 4.建立一個單元格，0表示第一列的單元格

        // 第一行
        // (1,1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列");
        // (1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("第一行第二列");

        // 第二行
        Row row2 = sheet.createRow(1);
        // (2,1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列");
        // (2,2)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("第二行第二列");

        // 第三行
        Row row3 = sheet.createRow(2);
        // (3,1)
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("時間");
        // (2,2)
        Cell cell32 = row3.createCell(1);
        // joda的日期比較好用
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell32.setCellValue(time);

        // 5.生成一張表（IO流） 03版本必須使用xls結尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03.xls");
        // 輸出
        workbook.write(fileOutputStream);
        // 6.關閉流
        fileOutputStream.close();

        System.out.println("03.xls生成完畢");
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 寫07版Excel
     */
    @Test
    public void testWrite07() throws IOException {
        // 1.建立一個工作簿，03版使用對象HSSFWorkbook，07版使用對象XSSFWorkbook
        Workbook workbook = new XSSFWorkbook();
        // 2.建立一個工作表，不傳參預設Sheet1
        Sheet sheet = workbook.createSheet("自定義Sheet1");
        // 3.建立一個行，0表示第一行
        Row row1 = sheet.createRow(0);
        // 4.建立一個單元格，0表示第一列的單元格

        // 第一行
        // (1,1)
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("第一行第一列");
        // (1,2)
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("第一行第二列");

        // 第二行
        Row row2 = sheet.createRow(1);
        // (2,1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("第二行第一列");
        // (2,2)
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue("第二行第二列");

        // 第三行
        Row row3 = sheet.createRow(2);
        // (3,1)
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("時間");
        // (2,2)
        Cell cell32 = row3.createCell(1);
        // joda的日期比較好用
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell32.setCellValue(time);

        // 5.生成一張表（IO流） 07版本必須使用xlsx結尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07.xlsx");
        // 輸出
        workbook.write(fileOutputStream);
        // 6.關閉流
        fileOutputStream.close();

        System.out.println("07.xlsx生成完畢");
    }
}

           

1. 2003版本和2007版本存在相容性的問題
2. 03版最多隻有65536行
3. 03和07版本的寫，就是對象不同，方法是一樣的
4. 字尾不同，03使用xls，07使用xlsx

• 優點
  • 過程中寫入緩存，不操作磁盤，最後一次性寫入磁盤，速度快
• 缺點
  • 最多隻能處理65536行，否則會抛出異常

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 寫03版Excel-大檔案
     */
    @Test
    public void testWrite03BigData() throws IOException {
        // 起始時間
        long begin = System.currentTimeMillis();

        // 1.建立一個工作簿，03版使用對象HSSFWorkbook，07版使用對象XSSFWorkbook
        Workbook workbook = new HSSFWorkbook();
        // 2.建立一個工作表，不傳參預設Sheet1
        Sheet sheet = workbook.createSheet("自定義Sheet1");
        // 3.寫入資料
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        // 4.生成一張表（IO流） 03版本必須使用xls結尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03BigData.xls");
        // 輸出
        workbook.write(fileOutputStream);
        // 5.關閉流
        fileOutputStream.close();

        System.out.println("03BigData.xls生成完畢");


        // 截止時間
        long end = System.currentTimeMillis();
        System.out.println("消耗時間：" + ((double)end-begin)/1000 + "s");
    }
}

           

• • 可以寫較大的資料量，如：20萬條
• • 寫資料時速度非常慢，非常耗記憶體，也會發生記憶體溢出，如：100萬條、1000萬條

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 寫07版Excel-大檔案
     */
    @Test
    public void testWrite07BigData() throws IOException {
        // 起始時間
        long begin = System.currentTimeMillis();

        // 1.建立一個工作簿，03版使用對象HSSFWorkbook，07版使用對象XSSFWorkbook
        Workbook workbook = new XSSFWorkbook();
        // 2.建立一個工作表，不傳參預設Sheet1
        Sheet sheet = workbook.createSheet("自定義Sheet1");
        // 3.寫入資料
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        // 4.生成一張表（IO流） 07版本必須使用xlsx結尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigData.xlsx");
        // 輸出
        workbook.write(fileOutputStream);
        // 5.關閉流
        fileOutputStream.close();

        System.out.println("07BigData.xlsx生成完畢");


        // 截止時間
        long end = System.currentTimeMillis();
        System.out.println("消耗時間：" + ((double)end-begin)/1000 + "s");
    }
}

           

• • 可以寫非常大的資料量，如：100萬條甚至更多條
  • 寫資料速度快，占用更少的記憶體
• 注意
  • 過程中會産生臨時檔案，需要清理臨時檔案

    ((SXSSFWorkbook) workbook).dispose();

  • 預設由100條記錄被儲存在記憶體中，如果超過該數量，則最前面的資料被寫入臨時檔案
  • 如果想自定義記憶體中資料的數量，可以使用

    new SXSSFWorkbook(數量);

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 寫07版Excel-大檔案（SXSSF快速版）
     */
    @Test
    public void testWrite07BigDataS() throws IOException {
        // 起始時間
        long begin = System.currentTimeMillis();

        // 1.建立一個工作簿，03版使用對象HSSFWorkbook，07版使用對象XSSFWorkbook，07快速版使用對象SXSSFWorkbook
        Workbook workbook = new SXSSFWorkbook();
        // 2.建立一個工作表，不傳參預設Sheet1
        Sheet sheet = workbook.createSheet("自定義Sheet1");
        // 3.寫入資料
        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        // 4.生成一張表（IO流） 07版本必須使用xlsx結尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07BigDataS.xlsx");
        // 輸出
        workbook.write(fileOutputStream);
        // 5.關閉流
        fileOutputStream.close();
        // 清除臨時檔案
        ((SXSSFWorkbook) workbook).dispose();
        System.out.println("07BigDataS.xlsx生成完畢");


        // 截止時間
        long end = System.currentTimeMillis();
        System.out.println("消耗時間：" + ((double)end-begin)/1000 + "s");
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class ExcelReadTest {
    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 讀03版Excel
     */
    @Test
    public void testRead03() throws IOException {
        // 1.擷取檔案流
        FileInputStream fileInputStream = new FileInputStream(PATH + "03.xls");
        // 2.建立一個工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 3.得到表，可以通過名稱或下标擷取
        Sheet sheet = workbook.getSheetAt(0);
        // 4.得到行
        Row row = sheet.getRow(0);
        // 5.得到單元格
        Cell cell = row.getCell(0);
        // 6.讀取值，讀取值的時候要注意資料類型，否則會報錯
//        System.out.println(cell.getNumericCellValue());
        System.out.println(cell.getStringCellValue());
        // 7.關閉流
        fileInputStream.close();
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

public class ExcelReadTest {
    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 讀07版Excel
     */
    @Test
    public void testRead07() throws IOException {
        // 1.擷取檔案流
        FileInputStream fileInputStream = new FileInputStream(PATH + "07.xlsx");
       // 2.建立一個工作簿
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        // 3.得到表，可以通過名稱或下标擷取
        Sheet sheet = workbook.getSheetAt(0);
        // 4.得到行
        Row row = sheet.getRow(0);
        // 5.得到單元格
        Cell cell = row.getCell(0);
        // 6.讀取值，讀取值的時候要注意資料類型，否則會報錯
//        System.out.println(cell.getNumericCellValue());
        System.out.println(cell.getStringCellValue());
        // 7.關閉流
        fileInputStream.close();
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

public class ExcelReadTest {
    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 讀取不同的資料類型
     */
    @Test
    public void testCellType() throws IOException {
        // 1.擷取檔案流
        FileInputStream fileInputStream = new FileInputStream(PATH + "明細表.xls");
        // 2.建立一個工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 3.得到表，可以通過名稱或下标擷取
        Sheet sheet = workbook.getSheetAt(0);
        // 4.擷取表頭行内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            // 擷取該行列數
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    // 擷取值類型
                    CellType cellType = cell.getCellType();
                    // 知道表頭行都是字元串，是以不需要根據類型輸出
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
        }
        // 5.擷取表體行内容
        // 擷取行數
        int rowCount = sheet.getPhysicalNumberOfRows();
        // 跳過表頭行，所有從1開始
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            System.out.println();
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                // 擷取表頭行列數
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        // 擷取值類型
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        // 比對資料類型
                        switch (cellType) {
                            case _NONE: // 未知類型，僅限内部使用
                                System.out.print("[未知類型]");
                                break;
                            case NUMERIC: // 數字類型（日期、普通數字）
                                System.out.print("[數字類型（日期、普通數字）]");
                                if (DateUtil.isCellDateFormatted(cell)) { // 日期
                                    System.out.print("[日期]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    // 不是日期格式，防止數字過長，轉換為字元串輸出
                                    System.out.print("[數字]");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case STRING: // 字元串
                                System.out.print("[字元串]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case FORMULA: // 公式
                                System.out.print("[公式]");
                                
                                // 擷取公式計算程式
                                FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                                // 計算公式
                                CellValue evaluate = formulaEvaluator.evaluate(cell);
                                cellValue = evaluate.formatAsString();
                                break;
                            case BLANK: // 空單元格，沒值，但有單元格樣式
                                System.out.print("[空]");
                                break;
                            case BOOLEAN: // 布爾值
                                System.out.print("[布爾值]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case ERROR: // 錯誤單元格
                                System.out.print("[錯誤單元格]");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        // 7.關閉流
        fileInputStream.close();
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

public class ExcelReadTest {
    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 讀取不同的資料類型-使用cell.toString()
     */
    @Test
    public void testCellTypeCellToString() throws IOException {
        // 1.擷取檔案流
        FileInputStream fileInputStream = new FileInputStream(PATH + "明細表.xls");
        // 2.建立一個工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 3.得到表，可以通過名稱或下标擷取
        Sheet sheet = workbook.getSheetAt(0);
        // 4.擷取表頭行内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            // 擷取該行列數
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    // 擷取值類型
                    CellType cellType = cell.getCellType();
                    // 知道表頭行都是字元串，是以不需要根據類型輸出
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
        }
        // 5.擷取表體行内容
        // 擷取行數
        int rowCount = sheet.getPhysicalNumberOfRows();
        // 跳過表頭行，所有從1開始
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            System.out.println();
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                // 擷取表頭行列數
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        // 擷取值類型
                        CellType cellType = cell.getCellType();
                        System.out.print("[" + cellType + "]");
                        String cellValue = cell.toString();
                        System.out.println(cellValue);
                    }
                }
            }
        }
        // 7.關閉流
        fileInputStream.close();
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

public class ExcelReadTest {
    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 讀取不同的資料類型-使用cell.toString()-優化日期和數字的顯示
     */
    @Test
    public void testCellTypeCellToStringNumeric() throws IOException {
        // 1.擷取檔案流
        FileInputStream fileInputStream = new FileInputStream(PATH + "明細表.xls");
        // 2.建立一個工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 3.得到表，可以通過名稱或下标擷取
        Sheet sheet = workbook.getSheetAt(0);
        // 4.擷取表頭行内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            // 擷取該行列數
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    // 擷取值類型
                    CellType cellType = cell.getCellType();
                    // 知道表頭行都是字元串，是以不需要根據類型輸出
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
        }
        // 5.擷取表體行内容
        // 擷取行數
        int rowCount = sheet.getPhysicalNumberOfRows();
        // 跳過表頭行，所有從1開始
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            System.out.println();
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                // 擷取表頭行列數
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    System.out.print("[" + (rowNum+1) + "-" + (cellNum+1) + "]");
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        // 擷取值類型
                        CellType cellType = cell.getCellType();
                        System.out.print("[" + cellType + "]");
                        String cellValue = "";
                        // 比對資料類型
                        switch (cellType) {
                            case NUMERIC: // 數字類型（日期、普通數字）
                                System.out.print("[數字類型（日期、普通數字）]");
                                if (DateUtil.isCellDateFormatted(cell)) { // 日期
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    // 不是日期格式，防止數字過長，轉換為字元串輸出
                                    System.out.print("[數字]");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            default:
                                cellValue = cell.toString();
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        // 7.關閉流
        fileInputStream.close();
    }
}

           

package com.qing;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Date;

public class ExcelReadTest {
    // 路徑
    String PATH = "D:\\code\\excel\\qing-poi\\";

    /**
     * 讀取公式及計算
     */
    @Test
    public void testFormula() throws IOException {
        // 1.擷取檔案流
        FileInputStream fileInputStream = new FileInputStream(PATH + "公式.xls");
        // 2.建立一個工作簿
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        // 3.得到表，可以通過名稱或下标擷取
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(4);
        Cell cell = row.getCell(0);
        if (cell != null) {
            // 擷取公式計算程式
            FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
            // 擷取值類型
            CellType cellType = cell.getCellType();
            String cellValue = "";
            // 比對資料類型
            switch (cellType) {
                case FORMULA: // 公式
                    // 擷取公式
                    String formula = cell.getCellFormula();
                    System.out.println("公式：" + formula);
                    // 計算公式
                    CellValue evaluate = formulaEvaluator.evaluate(cell);
                    cellValue = evaluate.formatAsString();
                    break;
            }

            System.out.println(cellValue);
        }
        // 7.關閉流
        fileInputStream.close();
    }
}

           

<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
           

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qing</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--導入依賴-->
    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>
    </dependencies>

</project>
           

package com.qing;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

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

           

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <version>1.18.20</version>
  <scope>provided</scope>
</dependency>
           

package com.qing;

import com.alibaba.excel.EasyExcel;
import org.junit.jupiter.api.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelTest {

    // 路徑
    String PATH = "D:\\code\\excel\\easyexcel\\";

    /**
     * 最簡單的寫
     */
    @Test
    public void simpleWrite() {
        // 檔案名
        String fileName = PATH + "EasyExcelTest.xlsx";
        /*
        write(fileName, DemoData.class) 設定檔案名，寫入的實體類
        sheet("模闆") 設定sheet名稱
        doWrite(data() 設定寫的資料
         */
        EasyExcel.write(fileName, DemoData.class).sheet("模闆").doWrite(data());
    }

    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字元串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
}

           

package com.qing;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;

import java.util.ArrayList;
import java.util.List;

// 有個很重要的點 DemoDataListener 不能被spring管理，要每次讀取excel都要new,然後裡面用到spring可以構造方法傳進去
public class DemoDataListener extends AnalysisEventListener<DemoData> {

    /**
     * 每隔5條存儲資料庫，實際使用中可以3000條，然後清理list ，友善記憶體回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假設這個是一個DAO，當然有業務邏輯這個也可以是一個service。當然如果不用存儲這個對象沒用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 這裡是demo，是以随便new一個。實際使用如果到了spring,請使用下面的有參構造函數
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,請使用這個構造方法。每次建立Listener的時候需要把spring管理的類傳進來
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 這個每一條資料解析都會來調用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("解析到一條資料:" + JSON.toJSONString(data));
        list.add(data);
        // 達到BATCH_COUNT了，需要去存儲一次資料庫，防止資料幾萬條資料在記憶體，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存儲完成清理 list
            list.clear();
        }
    }
    /**
     * 所有資料解析完成了 都會來調用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 這裡也要儲存資料，確定最後遺留的資料也存儲到資料庫
        saveData();
        System.out.println("所有資料解析完成！");
    }
    /**
     * 加上存儲資料庫
     */
    private void saveData() {
        System.out.println(list.size() + "條資料，開始存儲資料庫！");
        demoDAO.save(list);
        System.out.println("存儲資料庫成功！");
    }
}

           

package com.qing;

import com.alibaba.excel.EasyExcel;
import org.junit.jupiter.api.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelTest {

    // 路徑
    String PATH = "D:\\code\\excel\\easyexcel\\";

    /**
     * 最簡單的讀
     * <p>1. 建立excel對應的實體對象 參照{@link DemoData}
     * <p>2. 由于預設一行行的讀取excel，是以需要建立excel一行一行的回調監聽器，參照{@link DemoDataListener}
     * <p>3. 直接讀即可
     */
    @Test
    public void simpleRead() {
        // 有個很重要的點 DemoDataListener 不能被spring管理，要每次讀取excel都要new,然後裡面用到spring可以構造方法傳進去
        String fileName = PATH + "EasyExcelTest.xlsx";
        // 這裡 需要指定讀用哪個class去讀，然後讀取第一個sheet 檔案流會自動關閉
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }
}