使用 Apache POI 将 JDBC 结果集写入 Excel 文件

banq

在本文中，我们介绍了如何使用 Apache POI 将数据从 JDBC ResultSet导出到 Excel 文件。我们创建了一个Workbook ，从ResultSetMetaData动态填充了标题行，并通过迭代ResultSet用数据行填充了工作表。

数据处理是软件开发中的关键任务之一。一个常见的用例是从数据库检索数据并将其导出为某种格式以供进一步分析，例如 Excel 文件。

本教程将展示如何使用Apache POI库将数据从 JDBC ResultSet导出到 Excel 文件。

 Maven 依赖
在我们的示例中，我们将从数据库表中读取一些数据并将其写入 Excel 文件。让我们在pom.xml中定义Apache POI和POI OOXML 模式依赖关系：

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

我们将采用H2 数据库进行演示。我们还要包括它的依赖项：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.3.232</version>
</dependency>

数据准备
接下来，让我们通过在 H2 数据库中创建产品表并在其中插入行来为演示准备一些数据：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255) NOT NULL, 
    category VARCHAR(255), 
    price DECIMAL(10, 2) 
);
INSERT INTO products(name, category, price) VALUES ('Chocolate', 'Confectionery', 2.99);
INSERT INTO products(name, category, price) VALUES ('Fruit Jellies', 'Confectionery', 1.5);
INSERT INTO products(name, category, price) VALUES ('Crisps', 'Snacks', 1.69);
INSERT INTO products(name, category, price) VALUES ('Walnuts', 'Snacks', 5.95);
INSERT INTO products(name, category, price) VALUES ('Orange Juice', 'Juices', 2.19);

创建表并插入数据后，我们可以使用JDBC获取产品表内存储的所有数据：

try (Connection connection = getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(dataPreparer.getSelectSql());) {
    // The logic of export data to Excel file.
}

我们忽略了getConnection()的实现细节。我们通常通过原始 JDBC 连接、通过连接池或从DataSource获取 JDBC 连接。

创建工作簿
Excel 文件由一个工作簿组成，可以包含多个工作表。在我们的演示中，我们将创建一个工作簿和一个工作表，稍后我们将把数据写入其中。首先，让我们创建一个工作簿：

Workbook workbook = new XSSFWorkbook();

我们可以从 Apache POI 中选择一些工作簿变体：

  • HSSFWorkbook – 较旧的 Excel 格式 (97-2003) 生成器，扩展名为.xls
  • XSSFWorkbook – 用于创建较新的基于 XML 的 Excel 2007 格式（扩展名为.xlsx ）
  • SXSSFWorkbook – 也创建带有.xlsx扩展名的文件，但通过流式传输，从而将内存使用量降至最低

在这个例子中，我们将使用XSSFWorkbook。但是，如果我们预计要导出很多行，比如超过 10,000 行，那么为了更有效地利用内存，我们最好使用SXSSFWorkbook而不是XSSFWorkbook 。

接下来，让我们在工作簿中创建一个名为“data”的工作表：

Sheet sheet = workbook.createSheet("data");

创建标题行
通常，标题将包含数据集中每列的标题。由于我们在这里处理从 JDBC 返回的ResultSet对象，因此我们可以使用ResultSetMetaData接口，该接口提供有关ResultSet列的元数据。

让我们看看如何使用ResultSetMetaData获取列名并使用 Apache POI 创建 Excel 表的标题行：

Row row = sheet.createRow(sheet.getLastRowNum() + 1);
for (int n = 0; n < numOfColumns; n++) {
    String label = resultSetMetaData.getColumnLabel(n + 1);
    Cell cell = row.createCell(n);
    cell.setCellValue(label);
}

在此示例中，我们从ResultSetMetaData动态获取列名，并将其用作 Excel 表的标题单元格。这样，我们将避免对列名进行硬编码。

创建数据行
添加标题行后，我们将表格数据加载到Excel文件中：

while (resultSet.next()) {
    Row row = sheet.createRow(sheet.getLastRowNum() + 1);
    for (int n = 0; n < numOfColumns; n++) {
        Cell cell = row.createCell(n);
        cell.setCellValue(resultSet.getString(n + 1));
    }
}

我们对ResultSet进行迭代，每次迭代时，我们在工作表中创建一个新行。根据之前通过sheet.getLastRowNum()在标题行中获得的列号，我们对每一列进行迭代，以获取当前行的数据并写入相应的 Excel 单元格。

编写工作簿
现在我们的Workbook已完全填充，我们可以将其写入 Excel 文件。由于我们使用XSSFWorkbook实例作为实现，因此导出文件将以 Excel 2007 文件格式保存，扩展名为.xslx：

File excelFile = // our file
try (OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(excelFile))) {
    workbook.write(outputStream);
    workbook.close();
}

编写完成后，通过在Workbook实例上调用close()方法显式关闭Workbook是一种很好的做法。这将确保资源得到释放，数据被刷新到文件中。