使用Java操作Excel表格

CodeReaper發表於2021-09-22
JavaExcel

目錄

一、配置第三方庫

查閱了網上的資料後，發現Java程式操作Excel表格的庫中使用的比較多的是這個叫Apache POI API的庫：

image-20210919171146238

對應的maven依賴為：

<!-- 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>

二、使用Apache POI API

1. 開啟Excel檔案

這一步相當於讀取excel檔案中的資訊到物件中，方便我們進一步對資料進行操作（比較像Java程式中資料庫對映的POJO物件）。當前我使用的檔案是2007以上的，即字尾名為xlxs，此時建立物件的語句為：

Workbook book = new XSSFWorkbook("xxx.xlxs");

即在建構函式中填入對應的路徑即可。當然，此外還有其他的建構函式，如下圖所示：

image-20210922113055844

但個人覺得最為常用的應該是填入路徑和File型別的物件了。

2. 選擇對應的sheet

當我們使用Excel時可以發現左下角會有標註各個Sheet，即一個Excel檔案像是一本書一樣，裡面有很多個表格，當我們需要操作一個表格時需要先切換到對應的表格頁，如下圖所示：

image-20210922114711307

在程式中我們可以使用WorkBook的介面函式獲取到一個表格頁（即Sheet）：

image-20210922113759679

一共兩種獲取方式：

  1. 根據Sheet的名稱獲取：book.getSheet("Sheet1")
  2. 根據Sheet的索引獲取：book.getSheetAt(0);

這兩個方法的返回值都是Sheet介面型別。

3. Sheet介面的基本使用

3.1 獲取開頭行和結束行

在這個系統中，行號是從0開始數的，也就是說在程式中用到的行號是excel表格中顯示的行號-1。

這裡的測試表格採用資料庫課程使用到的資料庫表（即一個簡單的員工資料表），如下圖所示：

image-20210922120332652

獲取開頭行和結束行：

// public interface Sheet
int getFirstRowNum();
int getLastRowNum();

示例程式：

@Test
public void t1() throws IOException {
    Workbook book = new XSSFWorkbook("excel/test.xlsx");
    Sheet table = book.getSheet("Sheet1");
    System.out.println(table.getFirstRowNum());
    System.out.println(table.getLastRowNum());
}

測試結果：

image-20210922120633087

即開頭行為第1行，在Java中為0，結束行為第18行，在Java程式中得到17。

3.2 獲取Row物件

Row物件表示的是資料表中某一行的資料，可以通過以下方式獲取一個Row物件：

// interface Sheet
Row getRow(int var1);

示例（獲取包含所有第一行的資料的Row物件）：

Row row = table.getRow(0);

4. Row物件的使用

4.1 獲取本行的頭尾索引

① 獲取當前行第一個Cell物件的索引：row.getFirstCellNum()

tips: Cell物件為包含一個單元格所有資訊的物件，這裡即為獲取本行第一個非空的單元格的下標

② 獲取當前行最後一個Cell物件的索引+1：row.getLastCellNum()

注意這裡並不是最後一個Cell物件的索引，而是該值+1（和前面的Sheet.getLastCellNum()有所不同），例如我們用程式列印出上面員工表第一行的firstCellNum和lastCellNum：

@Test
public void t1() throws IOException {
    Workbook book = new XSSFWorkbook("excel/test.xlsx");
    Sheet table = book.getSheet("Sheet1");

    Row row = table.getRow(0);
    System.out.println(row.getFirstCellNum());
    System.out.println(row.getLastCellNum());
}

執行結果：

image-20210922131746998

而員工表最後一列的索引為6，這說明了getLastCellNum()這個函式得到的是最後一個Cell的索引+1

對比sheet.getLastRowNum()和 row.getLastCellNum()也能發現不同之處（一個是獲取索引，一個是獲取索引+1）：

// sheet.getLastRowNum()
public int getLastRowNum() {
    return this._rows.isEmpty() ? -1 : (Integer)this._rows.lastKey();
}

// row.getLastCellNum()
public short getLastCellNum() {
    return (short)(this._cells.size() == 0 ? -1 : (Integer)this._cells.lastKey() + 1);
}

4.2 獲取Cell物件

一行中有多個Cell物件（即單元格物件），我們可以通過row.getCell(int index)獲取Cell物件，例如我們這裡迴圈獲取第2行的每一個Cell物件，並且將它們分別列印出來：

Row row = table.getRow(1);
for (int i = 0; i < row.getLastCellNum(); i++) {
    Cell cell = row.getCell(i);
    System.out.println(cell);
}

Excel第2行：

image-20210922132814267

執行結果：

image-20210922132844161

此外，我們還可以通過迭代器的方式獲取該行的每個Cell物件，獲取迭代器的的方法為row.cellIterator()，最後將它們列印出來：

Row row = table.getRow(1);
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
    Cell cell = cellIterator.next();
    System.out.println(cell);
}

執行結果與上同。

閱讀原始碼後發現在JDK1.8之後，當一個物件實現了Iterable介面之後，就已經實現了forEach方法，因此我們也可以使用這個方法來遍歷Row物件中的Cell：

Row row = table.getRow(1);
row.forEach(cell -> {
    System.out.print(cell+", ");
    System.out.println(cell.getCellType());
});

同理，Sheet物件也可以使用forEach進行遍歷。

5. Cell物件的使用

5.1 獲取單元格值的型別

使用方法：

// public interface Cell
CellType getCellType();

返回型別為CellType，它是一個列舉型別，原始碼如下：

public enum CellType {
    _NONE(-1),
    NUMERIC(0),
    STRING(1),
    FORMULA(2),
    BLANK(3),
    BOOLEAN(4),
    ERROR(5);
}

一般Excel中填寫的內容對應的型別：

  1. 沒有填值（空單元格）：BLANK
  2. 數字（整數或小數）：NUMERIC
  3. 字串：STRING
  4. 公式：FORMULA
  5. TRUE或FALSE：BOOLEAN

準備如下的測試行：

image-20210922134844628

執行以下程式碼（即將上面這行單元格的值和型別都列印出來）：

Row row = table.getRow(18);
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
    Cell cell = cellIterator.next();
    System.out.print(cell+", ");
    System.out.println(cell.getCellType());
}

執行結果：

image-20210922135205038

5.2 獲取單元格的值

方法：cell.getXXXValue()

image-20210922140636310

例如如果這個單元格是數字型別的，就使用getNumericCellValue()即可

需求，獲取所有SALESMAN員工的工資和：

5.3 設定單元格的值

和前面獲取值類似地，可以使用：cell.setValue(Object value)的方法設定單元格的值：

image-20210922154332590

但需要注意的是，這裡僅是對存在於記憶體中的物件進行屬性的設定，並不會直接影響到Excel表格中實際的內容，如果需要用book物件來修改表格內容則還需要使用儲存功能來執行。（和POJO是類似的，需要先修改物件的值，然後再用整個物件去修改資料庫中實際的值）

6. 儲存表格

步驟如下：

  1. 新建一個FileOutputStream物件，相當於是一個檔案，如果是已存在的檔案則進行覆蓋操作
  2. 使用Workbook物件的void write(OutputStream var1)方法，填入的引數為前面的FileOutputStream物件

這樣表格就儲存完成了。

示例：

@Test
public void t() throws IOException {
    Workbook book = new XSSFWorkbook();
    Sheet table = book.createSheet();
    Row row = table.createRow(0);
    Cell firstCell = row.createCell(0);
    firstCell.setCellValue("Hello");
    Cell secondCell = row.createCell(1);
    secondCell.setCellValue("World");
    FileOutputStream fos = new FileOutputStream("excel/output.xlsx");
    book.write(fos);
    book.close();
}

執行效果：

image-20210922155437335

如果希望修改原表格只需在fos的路徑上填原表格就可以進行覆蓋了

相關文章