POI 读写Excel文档

各种类型的管理系统中，类似报表、课程表的功能模块经常需要读写Excel文档，Java的Apache POI库能够读写Microsoft Office（包括Word、Excel和PowerPoint）文档，它尤其适合用来处理Excel文档，这篇笔记我们简单介绍POI库的使用。

注：实际上早些年还有一个JXL库提供了读写XLS文档的API，使用也比较方便，但是JXL库不支持XLSX格式，XLS格式已经严重过时，因此现在基本不再采用JXL了。

关于XLS和XLSX

XLS和XLSX都是Microsoft Excel软件使用的表格文档格式，比较早期的XLS是一种闭源的专有二进制文件格式，这种格式由于种种历史原因虽然严重过时但目前仍在各种遗留系统中使用，XLSX则是Office Open XML开放格式表格文档，是目前主流的Office文档格式。POI库对于两种表格文档都是支持的，操作它们的API也基本一致，对于XLS我们需要使用HSSFWorkbook，而对于XLSX使用XSSFWorkbook或SXSSFWorkbook。

实际开发中，对于新系统我们应该尽量避免再使用XLS，POI库对于XLSX有更多高级功能特性的支持，下面我们也主要以XLSX格式为例进行介绍。

引入Maven依赖

使用POI库读写Excel文档，需要在Maven的pom.xml中引入以下依赖。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

其中，poi是核心库，poi-ooxml专门用于处理Office Open XML格式文档（包括XLSX、DOCX、PPTX等），poi-scratchpad主要用于支持旧格式（如HSSF的附加功能），若仅处理XLSX可不必引入。

读写XLSX文档

POI库处理XLSX文档其实很简单，我们其实就是围绕内存中的XSSFWorkbook对象进行读写操作。这里我们先通过下面两个基础的例子学习如何使用POI库读写Excel文档。

生成XLSX文档

下面例子代码中，我们调用POI库生成了一个XLSX格式文档，代码写入了若干单元格，每个单元格填入了当前的行号和列号，文档最终存储在test.xlsx文件中。

package com.gacfox.demo;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;

@Slf4j
public class Main {
    public static void main(String[] args) {
        File file = new File("C:\\Users\\HUAWEI\\test.xlsx");

        try (XSSFWorkbook workbook = new XSSFWorkbook();
             OutputStream outputStream = Files.newOutputStream(file.toPath())) {
            XSSFSheet sheet = workbook.createSheet("sheet1");
            for (int i = 0; i <= 5; i++) {
                XSSFRow row = sheet.createRow(i);
                for (int j = 0; j < 5; j++) {
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue("row" + i + "cell" + j);
                }
            }
            workbook.write(outputStream);
        } catch (IOException e) {
            log.error("Writing Excel failure: ", e);
        }
    }
}

代码生成Excel文档的逻辑和我们手动编辑表格是类似的，我们首先创建了XSSFWorkbook对象，它代表着一个Excel文档，在文档中我们创建了XSSFSheet类型的Sheet页，在Sheet页中我们创建了5次XSSFRow行对象，然后填充了每行的5个XSSFCell单元格对象，一个比较常规的XLSX文档生成就是按照类似这样的文档->Sheet页->行->单元格顺序编辑的。最终，我们调用workbook.write(OutputStream stream)方法将文档对象写入输出流。

另外要注意的是XSSFWorkbook通过实现POI的Workbook接口间接实现了Closeable接口，我们用完后需要在代码中调用close()方法释放资源，或者像上面代码一样使用try with resource语句自动处理，否则可能出现内存溢出的现象。

解析读取XLSX文档

除了生成Excel文档，POI也可以解析已有的文档，这需要在创建XSSFWorkbook时传入一个输入流对象，下面是一个例子。

package com.gacfox.demo;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Iterator;

@Slf4j
public class Main {
    public static void main(String[] args) {
        try (InputStream inputStream = Files.newInputStream(Paths.get("C:\\Users\\HUAWEI\\test.xlsx"));
             XSSFWorkbook workbook = new XSSFWorkbook(inputStream)) {
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.rowIterator();
            while (rowIterator.hasNext()) {
                XSSFRow row = (XSSFRow) rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    XSSFCell cell = (XSSFCell) cellIterator.next();
                    log.info("Cell value: {}", cell.getStringCellValue());
                }
            }
        } catch (IOException e) {
            log.error("Reading Excel failure: ", e);
        }
    }
}

代码中，我们先使用了getSheetAt(int index)方法获取Sheet页对象，我们也可以使用getSheet(String name)方法根据Sheet页名获取Sheet页。获取Sheet页后，我们获取了行迭代器Iterator<Row>，遍历每一行时我们又获取了单元格迭代器Iterator<Cell>，在双层迭代内我们调用了单元格的getStringCellValue()方法读取单元格的文本值，最终把值打印了出来。

单元格数据类型

POI中我们可以使用Cell对象的getCellType()方法获取单元格的类型。POI中定义的单元格有以下几种类型：

• CellType.STRING：文本类型
• CellType.NUMERIC：数字类型，除了最基本的数字，包括货币、日期、科学计数法等都采用该类型
• CellType.FORMULA：公式类型，读取使用公式作为结果的单元格时返回该类型
• CellType.BOOLEAN：布尔值类型，Excel中输入true或false时会看到它们自动变为了全大写形式的布尔类型，此时POI将以布尔值类型单元格读取
• CellType.ERROR：错误，通常不会出现
• CellType.BLANK：空白，默认情况下POI读取空白单元格时会返回null，因此不会出现类型为BLANK的单元格，但如果使用了类似如下写法row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getCellType()，这里传入的参数指定读取不存在的单元格时创建空类型单元格，因此可以读取到BLANK类型单元格

读取单元格的值时，我们可能会用到cell.getStringCellValue()、cell.getNumericCellValue()等方法，这里要注意，如果文档中单元格格式是数字而使用了读取文本单元格的读取方法，或者单元格本来是文本类型却使用了读取数字的方法，POI都将抛出错IllegalStateException异常。这里比较坑，对于一些本就是文本的表格列，用户很可能意外的输入数字，因为未明确设置列数据类型时，Excel软件会自动将输入的数字单元格设置为数字类型，在开发导入功能时，我们可能经常遇到类似的数据类型解析错误。

如下图所示是Excel软件中的文本和数字两种单元格类型的界面。

Excel中，文本类型的数字内容左上角有一个绿色的小三角，而数值类型的单元格没有，用户对Excel操作不熟练时很容易混淆这两种单元格，造成程序解析报错。

对于这类问题，一种解决方式是预判用户的操作，额外判断单元格是数值还是文本，如果是数值则通过cell.getNumericCellValue()读取并转为文本，但这其实也不是一种好的方式。我们应尽量通过优化导入错误提示、提供预设置好数据类型的Excel模板、加强培训等方式避免该问题。注意不要试图在POI中预先将单元格设置为文本类型，这可能造成数值意外的被转换为科学计数法，导致无法读取或精度丢失。

设置单元格样式

POI库能够对单元格的基础样式进行设置，这里我们简单介绍一些常见用法。

下面例子代码设置了单元格的背景颜色。

CellStyle style = workbook.createCellStyle();
// 设置背景色
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
// 设置填充模式
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

XSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellValue("Hello, POI!");
cell.setCellStyle(style);

下面例子设置了单元格各个方向的边框。

CellStyle style = workbook.createCellStyle();
// 下边框
style.setBorderBottom(BorderStyle.THIN);
// 左边框
style.setBorderLeft(BorderStyle.THIN);
// 上边框
style.setBorderTop(BorderStyle.THIN);
// 右边框
style.setBorderRight(BorderStyle.THIN);

XSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellValue("Hello, POI!");
cell.setCellStyle(style);

下面例子设置了单元格的字体、字号等信息。

CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
// 字体名称
font.setFontName("Arial");
// 字体大小
font.setFontHeightInPoints((short) 12);
// 加粗
font.setBold(true);
// 字体颜色
font.setColor(IndexedColors.BLUE.getIndex());
style.setFont(font);

XSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellValue("Hello, POI!");
cell.setCellStyle(style);

下面代码设置了单元格的对齐方式。

CellStyle style = workbook.createCellStyle();
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
style.setVerticalAlignment(VerticalAlignment.CENTER);

XSSFCell cell = sheet.createRow(0).createCell(0);
cell.setCellValue("Hello, POI!");
cell.setCellStyle(style);

创建合并单元格

如果需要创建合并单元格，我们需要调用Sheet页对象的addMergedRegion()方法，它需要传入CellRangeAddress对象，其构造函数参数分别是firstRow、lastRow、firstCol、lastCol。

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

至于读取合并的单元格，我们使用的行列索引永远使用其左上角单元格的行列索引即可。

大规模数据导出

XSSFWorkbook是一个完整Excel文档的内存表示，它非常耗内存，不适合处理大规模数据的导出功能。POI提供了SXSSFWorkbook类型，SXSSF（Streaming Usermodel API）采用了滑动窗口的机制来控制内存使用，因此能够处理非常大的表格导出数据量，不过这种方式也造成了它相比XSSFWorkbook有很多高级功能并不支持。我们知道SpringBoot工程一旦OOM将导致应用程序彻底崩溃，测试环境由于数据量较小可能无法发现类似问题，而线上环境一旦出现该情况就会造成线上服务的不可用。对于开发中十分常见的批量数据导出功能，我们一定要优先考虑SXSSFWorkbook，避免出现线上服务JVM内存溢出导致服务彻底宕机的情况！

对于基础的数据导出，SXSSFWorkbook的API和XSSFWorkbook基本一致，下面是一个例子。

package com.gacfox.demo;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;

@Slf4j
public class Main {
    public static void main(String[] args) {
        File file = new File("C:\\Users\\HUAWEI\\test.xlsx");

        try (SXSSFWorkbook workbook = new SXSSFWorkbook();
             OutputStream outputStream = Files.newOutputStream(file.toPath())) {
            SXSSFSheet sheet = workbook.createSheet("sheet1");
            for (int i = 0; i <= 5; i++) {
                SXSSFRow row = sheet.createRow(i);
                for (int j = 0; j < 5; j++) {
                    SXSSFCell cell = row.createCell(j);
                    cell.setCellValue("row" + i + "cell" + j);
                }
            }
            workbook.write(outputStream);
        } catch (IOException e) {
            log.error("Writing Excel failure: ", e);
        }
    }
}

SXSSFWorkbook的用法和XSSFWorkbook类似，不过开发中要注意，毕竟我们操作的数据量较大，我们应该尽量避免一次性将全部数据载入内存中，而是采用分页的批量查询配合SXSSFWorkbook流式写出到磁盘，以节约内存的使用。另一点要注意的是SXSSFWorkbook不能读取已写入的单元格，因为它是流式处理，写入后部分数据可能会在内存中被清理，无法再次读取。