使用Java操作Excel表格
阅读原文时间:2021年09月22日阅读:1

目录

一、配置第三方库

查阅了网上的资料后,发现Java程序操作Excel表格的库中使用的比较多的是这个叫Apache POI API的库:

对应的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

这一步相当于读取excel文件中的信息到对象中,方便我们进一步对数据进行操作(比较像Java程序中数据库映射的POJO对象)。当前我使用的文件是2007以上的,即后缀名为xlxs,此时创建对象的语句为:

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

即在构造函数中填入对应的路径即可。当然,此外还有其他的构造函数,如下图所示:

但个人觉得最为常用的应该是填入路径File类型的对象了。

当我们使用Excel时可以发现左下角会有标注各个Sheet,即一个Excel文件像是一本书一样,里面有很多个表格,当我们需要操作一个表格时需要先切换到对应的表格页,如下图所示:

在程序中我们可以使用WorkBook的接口函数获取到一个表格页(即Sheet):

一共两种获取方式:

  1. 根据Sheet的名称获取:book.getSheet("Sheet1")
  2. 根据Sheet的索引获取:book.getSheetAt(0);

这两个方法的返回值都是Sheet接口类型。

3.1 获取开头行和结束行

在这个系统中,行号是从0开始数的,也就是说在程序中用到的行号是excel表格中显示的行号-1。

这里的测试表格采用数据库课程使用到的数据库表(即一个简单的员工数据表),如下图所示:

获取开头行和结束行:

// 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());
}

测试结果:

即开头行为第1行,在Java中为0,结束行为第18行,在Java程序中得到17。

3.2 获取Row对象

Row对象表示的是数据表中某一行的数据,可以通过以下方式获取一个Row对象:

// interface Sheet
Row getRow(int var1);

示例(获取包含所有第一行的数据的Row对象):

Row row = table.getRow(0);

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());
}

运行结果:

而员工表最后一列的索引为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行:

运行结果:

此外,我们还可以通过迭代器的方式获取该行的每个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.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

准备如下的测试行:

执行以下代码(即将上面这行单元格的值和类型都打印出来):

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());
}

运行结果:

5.2 获取单元格的值

方法:cell.getXXXValue()

例如如果这个单元格是数字类型的,就使用getNumericCellValue()即可

需求,获取所有SALESMAN员工的工资和:

5.3 设置单元格的值

和前面获取值类似地,可以使用:cell.setValue(Object value)的方法设置单元格的值:

但需要注意的是,这里仅是对存在于内存中的对象进行属性的设置,并不会直接影响到Excel表格中实际的内容,如果需要用book对象来修改表格内容则还需要使用保存功能来执行。(和POJO是类似的,需要先修改对象的值,然后再用整个对象去修改数据库中实际的值)

步骤如下:

  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();
}

运行效果:

如果希望修改原表格只需在fos的路径上填原表格就可以进行覆盖了