Writing JDBC ResultSet to an Excel File Using Apache POI

1. Overview

Data handling is one of the critical tasks in software development. A common use case is retrieving data from a database and exporting it into a format for further analysis such as Excel files.

This tutorial will show how to export data from a JDBC ResultSet to an Excel file using the Apache POI library.

2. Maven Dependency

In our example, we’ll read some data from a database table and write it into an Excel file. Let’s define the Apache POI and POI OOXML schema dependencies in the pom.xml:

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

We’ll adopt the H2 database for our demonstration. Let’s include its dependency as well:

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

3. Data Preparation

Next, let’s prepare some data for our demonstration by creating a products table in the H2 database and inserting rows into it:

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

With the table created and data inserted, we can use JDBC to fetch all data stored within the products table:

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

We have neglected the implementation details of getConnection(). We normally get a JDBC connection either by raw JDBC connection, via a connection pool, or from a DataSource.

4. Create a Workbook

An Excel file consists of a workbook and can contain multiple sheets. In our demonstration, we’ll create a Workbook and a single Sheet that we’ll write the data into later. First of all, let’s create a Workbook:

Workbook workbook = new XSSFWorkbook();

There are a few Workbook variants that we can choose from Apache POI:

• HSSFWorkbook – the older Excel format (97-2003) generator with extension .xls
• XSSFWorkbook – used to create the newer, XML-based Excel 2007 format with the .xlsx extension
• SXSSFWorkbook – also creates the files with the .xlsx extension but by streaming, hence keeping memory usage to a minimum

For this example, we’ll use XSSFWorkbook. However, if we anticipate exporting many rows, say more than 10,000 rows, then we’d be better off with SXSSFWorkbook over XSSFWorkbook for more efficient memory utilization.

Next, let’s create a Sheet named “data” within the Workbook:

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

5. Create the Header Row

Typically, a header would contain the title of each column in our dataset. Since we’re dealing here with the ResultSet object returned from JDBC, we could use the ResultSetMetaData interface that provides metadata regarding ResultSet columns.

Let’s see how to get the column names using ResultSetMetaData and create a header row of an Excel sheet using Apache POI:

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

In this example, we obtain the column names dynamically from ResultSetMetaData and use them as header cells of our Excel sheet. This way, we’ll avoid the hard-coding of column names.

6. Create Data Rows

After adding the header row, let’s load the table data to the Excel file:

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

We iterate on ResultSet and, for each iteration, we create a new row in the sheet. Based on the column numbers obtained earlier in the header row via the sheet.getLastRowNum(), we do an iteration on every column to get the data from the current row and write to the corresponding Excel cells.

7. Write the Workbook

Now that our Workbook is fully populated, we can write it into an Excel file. Since we used an instance of XSSFWorkbook as our implementation, the exporting file will be saved in the Excel 2007 file format with the .xslx extension:

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

It’s good practice to close the Workbook explicitly after writing by calling the close() method on the instance of the Workbook. This will ensure that resources get released and data gets flushed to the file.

Now, let’s see the exported results, which are according to our table definition and also maintain the insertion order of the data:

8. Conclusion

In this article, we went through how to export data from a JDBC ResultSet into an Excel file with Apache POI. We created a Workbook, dynamically populated header rows from ResultSetMetaData, and filled the sheet with data rows by iterating the ResultSet.