Java操作Excel：POI和EasyExcel

對弈發表於2020-07-12
JavaExcel

文章與CSDN同步，歡迎訪問：https://blog.csdn.net/qq_40280582/article/details/107300081
程式碼地址：https://gitee.com/ilovemo/poi-study

前言

我們經常需要將專案中的表格資料或者文件資料進行匯入或者匯出操作，這個如果自己從零開始做還比較麻煩。比如我之前就職的公司都是自己做的元件，但是很不好用，BUG 太多。關於表格匯入匯出，市面上比較知名的開源就是 Apache 的POI 和 阿里巴巴的 EasyExcel了。EasyExcel 也是對 POI 的改進和封裝， 更加好用。下面通過一些 demo 學習如何使用這兩個開源元件。這兩個元件都不難，多看文件就能會，尤其是 EasyExcel 的文件非常詳細。這篇部落格主要自己在寫 demo 的時候整理的筆記，方便以後使用的時候查閱。如果能幫到你那就更好了

日常開發需求

1、將使用者的資訊匯出為 excel 表格。

2、將 Excel 表中的資訊錄入到網站資料庫。

開發中經常會涉及到 excel 的 處理，如匯出 Excel ，匯入 Excel 到資料庫中。

操作 Excel 目前比較流行的就是 Apache POI 和阿里巴巴的 EasyExcel。

Apache POI

Apache POI 官網： http://poi.apache.org/index.html 。

結構：

HSSF － 提供讀寫Microsoft Excel格式檔案的功能。excel 2003 版本（塊，但是不能讀寫超過65536行記錄）

XSSF － 提供讀寫Microsoft Excel OOXML格式檔案的功能。excel 207 版本（慢，但是能讀寫超過65536行記錄）

SXSSF － 提供讀寫Microsoft Excel OOXML格式檔案的功能。excel 207 版本(XSSF升級版，比XSSF快，能讀寫超過65536行記錄)

HWPF － 提供讀寫Microsoft Word格式檔案的功能。

HSLF － 提供讀寫Microsoft PowerPoint格式檔案的功能。

HDGF － 提供讀寫Microsoft Visio格式檔案的功能。

EasyExcel

GitHub 地址： https://github.com/alibaba/easyexcel

EasyExcel 官網： https://www.yuque.com/easyexcel/doc/easyexcel

EasyExcel 是阿里巴巴開源的一個 excel處理框架，以使用簡單、節省記憶體著稱。

EasyExcel 能大大減少記憶體佔用的主要原因是在解析 Excel 時沒有將檔案資料一次性全部載入到記憶體中，而是從磁碟上一行行讀取資料，逐個解析。

Java操作Excel：POI和EasyExcel

1、POI-Excel 寫

1、建立一個空專案，建立普通的 Module 。

2、引入依賴：

<!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<!--時間格式化工具-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.6</version>
</dependency>
<!--junit-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
</dependency>

03 | 07 版本的寫操作，就是物件不一樣，方法都是一樣的。

需要注意：2003 版本和 2007 版本存在相容性問題， 03 版本最多隻有 65535 行。

03 版本：

package com.godfrey;

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.Test;

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

/**
 * description : 測試類
 *
 * @author godfrey
 * @since 2020-07-11
 */
public class ExcelWriteTest {

    String PATH = "H:\\code\\java\\StudyByKuang\\poi-study\\";

    @Test
    public void testWrite03() throws IOException {
        //1.建立工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.建立工作表
        Sheet sheet = workbook.createSheet("godfrey日常統計表");
        //3.建立一個行
        Row row1 = sheet.createRow(0);
        //4.建立一個單元格（1，1）
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("今日的新增安排");
        //（1，2）
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue(666);

        //第二行（2，1）
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("統計時間");
        //(2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);

        //生成一張表（IO流） 03版本就是使用xls結尾
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "godfrey日常統計表03.xls");
        //輸出
        workbook.write(fileOutputStream);
        //關閉流
        fileOutputStream.close();

        System.out.println("godfrey日常統計表03生成完畢");
    }
}

大檔案寫 HSSF

缺點：最多隻能處理 65536 行，否則會丟擲異常

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

優點：過程中寫入快取，不操作磁碟，最後一次性寫入磁碟，速度快。

@Test
public void testWrite03BigData() throws IOException {
    //時間
    long begin = System.currentTimeMillis();

    //建立一個工作簿
    Workbook workbook = new HSSFWorkbook();

    //建立表
    Sheet sheet = workbook.createSheet();

    //寫入資料
    for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
        Row row = sheet.createRow(rowNumber);
        for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
            Cell cell = row.createCell(cellNumber);
            cell.setCellValue(cellNumber);
        }
    }
    System.out.println("over");

    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);//1.29s
}

大檔案寫 XSSF

缺點：寫資料時速度非常慢，非常耗記憶體，也會發生記憶體溢位，如 100 萬條資料。

優點：可以寫較大的資料量，如 20 萬條。

@Test
public void testWrite07BigData() throws IOException {
    //時間
    long begin = System.currentTimeMillis();

    //建立一個工作簿
    Workbook workbook = new XSSFWorkbook();

    //建立表
    Sheet sheet = workbook.createSheet();

    //寫入資料
    for (int rowNumber = 0; rowNumber < 65537; rowNumber++) {
        Row row = sheet.createRow(rowNumber);
        for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
            Cell cell = row.createCell(cellNumber);
            cell.setCellValue(cellNumber);
        }
    }
    System.out.println("over");

    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);//7.34s
}

大檔案寫 SXSSF

優點： 可以寫非常大的資料量，如 100 萬條甚至更多，寫資料速度快，佔用更少的記憶體。

注意：

過程總會產生臨時檔案，需要清理臨時檔案。預設由 100 條記錄被儲存在記憶體中，則最前面的資料被寫入臨時檔案。如果想要自定義記憶體中資料的數量，可以使用 new SXSSFWorkbook (數量)。

@Test
public void testWrite07BigDataS() throws IOException {
    //時間
    long begin = System.currentTimeMillis();

    //建立一個工作簿
    Workbook workbook = new SXSSFWorkbook();

    //建立表
    Sheet sheet = workbook.createSheet();

    //寫入資料
    for (int rowNumber = 0; rowNumber < 100000; rowNumber++) {
        Row row = sheet.createRow(rowNumber);
        for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
            Cell cell = row.createCell(cellNumber);
            cell.setCellValue(cellNumber);
        }
    }
    System.out.println("over");

    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();


    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);//1.85s
}

SXSSFWorkbook 來自官方的解釋：實現“BigGridDemo” 策略的流式 SXSSFWorkbook 版本。這允許寫入非常大的檔案而不會耗盡記憶體，因為任何時候只有可配置的行部分被儲存在記憶體中。

請注意，仍然可能會消耗大量記憶體，這些記憶體基於你正在使用的功能，例如合併區域，註釋。。。仍然只儲存在記憶體中，因此如果廣泛使用，可能需要大量記憶體。

2、POI-Excel 讀

03 | 07 版本的讀操作

03 版本

String PATH = "H:\\code\\java\\StudyByKuang\\poi-study\\";

@Test
public void testRead03() throws IOException {

    //獲取檔案流
    FileInputStream inputStream = new FileInputStream(PATH + "godfrey日常統計表03.xls");

    //1.建立工作簿,使用excel能操作的這邊都看看操作
    Workbook workbook = new HSSFWorkbook(inputStream);
    //2.得到表
    Sheet sheet = workbook.getSheetAt(0);
    //3.得到行
    Row row = sheet.getRow(0);
    //4.得到列
    Cell cell = row.getCell(0);

    //getStringCellValue獲取字串型別
    System.out.println(cell.getStringCellValue());
    inputStream.close();
}

07 版本

@Test
public void testRead07() throws IOException {

    //獲取檔案流
    FileInputStream inputStream = new FileInputStream(PATH + "godfrey日常統計表07.xlsx");

    //1.建立工作簿,使用excel能操作的這邊都看看操作
    Workbook workbook = new XSSFWorkbook(inputStream);
    //2.得到表
    Sheet sheet = workbook.getSheetAt(0);
    //3.得到行
    Row row = sheet.getRow(0);
    //4.得到列
    Cell cell = row.getCell(0);

    //getStringCellValue獲取字串型別
    System.out.println(cell.getStringCellValue());
    inputStream.close();
}

注意獲取值的型別。

讀取不同的資料型別（最麻煩的點）

@Test
public void testCellType() throws IOException {
    //獲取檔案流
    FileInputStream inputStream = new FileInputStream(PATH + "明細表.xls");

    //建立工作簿,使用excel能操作的這邊都看看操作
    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    //獲取標題內容
    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 + " | ");
            }
        }
        System.out.println();
    }

    //獲取表中的內容
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {
        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {
            //讀取列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");

                Cell cell = rowData.getCell(cellNum);
                //匹配型別資料
                if (cell != null) {
                    CellType cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType) {
                        case STRING: //字串
                            System.out.print("[String型別]");
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN: //布林型別
                            System.out.print("[boolean型別]");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case BLANK: //空
                            System.out.print("[BLANK型別]");
                            break;
                        case NUMERIC: //數字（日期、普通數字）
                            System.out.print("[NUMERIC型別]");
                            if (HSSFDateUtil.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 ERROR:
                            System.out.print("[資料型別錯誤]");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    inputStream.close();
}

結果：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片儲存下來直接上傳(img-YMGBegQq-1594538757771)(http://imgcloud.duiyi.xyz//data20200712144751.png)]

注意型別轉換問題。可以將上面的方法提取成工具類。

計算公式(瞭解)

.

@Test
public void testFormula() throws IOException {
    FileInputStream inputStream = new FileInputStream(PATH + "公式.xls");
    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

    //拿到計算公司eval
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    //輸出單元格內容
    CellType cellType = cell.getCellType();
    switch (cellType) {
        case FORMULA://公式
            String formula = cell.getCellFormula();
            System.out.println(formula);// SUM(A2:A4)

            //計算
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);//SUM(A2:A4)
            break;
    }
}

3、EsayExcel 操作

官方文件很詳細，可以根據文件快速入門 https://www.yuque.com/easyexcel/doc/easyexcel 。

匯入依賴

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>
<!--fastjson-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.72</version>
</dependency>
<!--時間格式化工具-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.6</version>
</dependency>
<!--junit-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
</dependency>

寫入測試

根據官方文件的測試程式碼： https://www.yuque.com/easyexcel/doc/write

1、DemoData.java

package com.godfrey;

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

import java.util.Date;

/**
 * description : 實體類
 *
 * @author godfrey
 * @since 2020-07-11
 */
@Data
public class DemoData {
    @ExcelProperty("字串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數字標題")
    private Double doubleData;
    /**
     * 忽略這個欄位
     */
    @ExcelIgnore
    private String ignore;
}

2、測試寫入資料

package com.godfrey;

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

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

/**
 * description : 測試
 *
 * @author godfrey
 * @since 2020-07-11
 */
public class EasyTest {
    String PATH = "H:\\code\\java\\StudyByKuang\\poi-study\\";

    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<>();
        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;
    }

    /**
     * 最簡單的寫
     * 1. 建立excel對應的實體物件 參照{@link DemoData}
     * 2. 直接寫即可
     */
    @Test
    public void simpleWrite() {
        // 寫法1
        String fileName = PATH + "EasyTest.xlsx";
        // 這裡 需要指定寫用哪個class去寫，然後寫到第一個sheet，名字為模板 然後檔案流會自動關閉
        // 如果這裡想使用03 則 傳入excelType引數即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

    }
}

最終結果

.

相關文章