Java Quick Trick - Transform JDBC ResultSet to Excel WorkBook

A quick guide on generating an excel workbook with the output from an SQL query.

Video guide of the process



 

Follow the below steps

Add Apache POI dependencies to your pom.xml

1
2
3
4
5
6
7
8
9
10
11
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.0.1</version>
</dependency>

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

Create a workbook and add the header row.

1
2
3
4
try (Workbook book = new XSSFWorkbook()) {
  Sheet sheet = book.createSheet();
  Row header = sheet.createRow(0);  
}

Extract columns from the ResultSet.

1
2
3
4
5
ResultSetMetaData rsmd = rs.getMetaData();
List<String> columns = new ArrayList<String>() {{
  for (int i = 1; i <= rsmd.getColumnCount(); i++)
      add(rsmd.getColumnLabel(i));
}};

Populate the header row with the extracted column names.

1
2
3
4
for (int i = 0; i < columns.size(); i++) {
  Cell cell = header.createCell(i);
  cell.setCellValue(columns.get(i));
}

Extract the rows from the ResultSet and populate the workbook.

1
2
3
4
5
6
7
8
9
int rowIndex = 0;
while (rs.next()) {
  Row row = sheet.createRow(++rowIndex);
  for (int i = 0; i < columns.size(); i++) {
    Cell cell = row.createCell(i);
    // if you have complex values like Date, then use cell formatter.
    cell.setCellValue(Objects.toString(rs.getObject(columns.get(i)), ""));
  }
}

Writing the excel workbook to filesystem.

1
2
3
try (FileOutputStream fos = new FileOutputStream("FILE_PATH")) {
  book.write(fos);  
}

You can download this entire class - ResultSetToExcel.java, which is readily usable.

That’s all.

If you have any queries, please do reach out to me via email / phone.