使用Java操作Excel表格

CodeReaper發表於2021-09-22

一、配置第三方庫

查閱了網上的資料後,發現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()有所不同),例如我們用程式列印出上面員工表第一行的firstCellNumlastCellNum

@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的路徑上填原表格就可以進行覆蓋了

相關文章