Apache POI is a powerful library for reading and writing Microsoft Office files, including Excel. Writing JDBC ResultSet data to an Excel file is a common task for generating reports or exporting database results. This article will demonstrate how to fetch data from a database using JDBC and write it to an Excel file using Apache POI.
1. Table Structure and Sample Data
Suppose that we have a customers table in a database with the following structure:
The table contains the following sample data:
We will write code to export this data into an Excel file.
2. Maven Configuration
To get started, include the Apache POI dependencies and the required JDBC driver in your pom.xml file. For instance, if you’re using Maven with MySQL, add the following dependencies:
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.3.0</version> <!-- Use the latest version -->
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.3.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
</dependencies>
Make sure to replace the version numbers with the latest stable releases available at the time.
3. Code Example: Writing JDBC ResultSet to Excel
Here is a complete example that demonstrates how to export a JDBC ResultSet to an Excel file using Apache POI.
public class JdbcToExcelExporter {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/customer";
String username = "root";
String password = "password";
String sql = "SELECT * FROM customers";
try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); Workbook workbook = new XSSFWorkbook()) {
Sheet sheet = workbook.createSheet("Data");
writeHeader(rs, sheet);
writeData(rs, sheet);
try (FileOutputStream fileOut = new FileOutputStream("ResultSetOutput.xlsx")) {
workbook.write(fileOut);
}
System.out.println("Excel file has been generated successfully!");
} catch (SQLException | IOException e) {
}
}
private static void writeHeader(ResultSet rs, Sheet sheet) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
Row headerRow = sheet.createRow(0);
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Cell cell = headerRow.createCell(i - 1);
cell.setCellValue(metaData.getColumnLabel(i));
}
}
private static void writeData(ResultSet rs, Sheet sheet) throws SQLException {
int rowNum = 1;
while (rs.next()) {
Row row = sheet.createRow(rowNum++);
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Cell cell = row.createCell(i - 1);
cell.setCellValue(rs.getString(i));
}
}
}
}
Below is the explanation of the code.
3.1 Writing Header Row
The writeHeader() method writes the column names from the ResultSet to the first row of the Excel sheet. We get the column names from the ResultSetMetaData object, which provides metadata about the ResultSet, such as column names and types. For each column in the result set, a corresponding cell is created in the header row.
private static void writeHeader(ResultSet rs, Sheet sheet) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
Row headerRow = sheet.createRow(0);
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Cell cell = headerRow.createCell(i - 1);
cell.setCellValue(metaData.getColumnLabel(i));
}
}
3.2 Writing Data
The writeData() method writes the actual data from the ResultSet into subsequent rows of the Excel sheet. It loops through the ResultSet, and for each row, it creates a new row in the sheet and populates the cells with the data from the ResultSet. The getString() method is used to retrieve the data as a string (you can modify it based on the column type).
private static void writeData(ResultSet rs, Sheet sheet) throws SQLException {
int rowNum = 1;
while (rs.next()) {
Row row = sheet.createRow(rowNum++);
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Cell cell = row.createCell(i - 1);
cell.setCellValue(rs.getString(i));
}
}
}
3.3 Saving the Excel File
Once all the data is written, the Excel workbook is saved using a FileOutputStream to create the ResultSetOutput.xlsx file. The workbook.write() method writes the content of the workbook to the output stream, and the file is then saved to disk.
try (FileOutputStream fileOut = new FileOutputStream("ResultSetOutput.xlsx")) {
workbook.write(fileOut);
}
3.4 Output
When you run this program, it will create an Excel file named ResultSetOutput.xlsx containing all the data from the specified database table. Open this file using the Microsoft Excel application, and you will see the following data displayed in the spreadsheet:
4. Conclusion
In this article, we explored how to export a JDBC ResultSet to an Excel file using Apache POI in Java. We covered the necessary dependencies and explained how to handle both the header and data rows efficiently. This approach provides a straightforward way to generate Excel reports directly from database queries, making it a valuable tool for developers working on data export features. By leveraging Apache POI’s capabilities, we can customize and enhance our Excel files to meet a variety of reporting needs.
5. Download the Source Code
This article covered how to export a JDBC ResultSet to an Excel file.
You can download the full source code of this example here: export a jdbc resultset to an excel file
