使用apache poi将数据库查询结果导出到excel [英] Exporting database query results to excel using apache poi

问题描述

我通过一些示例代码使用 Apache POI 将数据导出到 excel.但是，我不确定如何将数据库查询结果导出到 excel 文件.我知道我们必须在行中创建单元格，然后为单元格设置值.但是我已经在结果集中有了数据，必须将相同的数据导出到 excel 文件中.谁能给我提供一个小/简单的代码来做同样的事情.

I went through some sample codes to export data to excel using Apache POI. However, I am not sure as to how we can export database query results to an excel file. I know that we must create cells in rows and then set values to the cell. But I already have the data in the resultset and must just export the same to an excel file. Can anyone provide me a small/easy code to do the same.

谢谢！

推荐答案

Try : Reference Apache POI 的开发人员指南

Try : Reference Apache POI's Developer Guide

示例人员表:

+------------------+
| NAME  | ADDRESS  |
+------------------+
| Jhone | USA      |  
| Smith | USA      |
+------------------+

示例程序

Workbook wb = new HSSFWorkbook();
Sheet personSheet = wb.createSheet("PersonList");
Row headerRow = personSheet.createRow(0);
Cell nameHeaderCell = headerRow.createCell(0);
Cell addressHeaderCell = headerRow.createCell(1);

String sql = "select name, address from person_table";
PrepareStatement ps =  connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();    

int row = 1;
while(resultSet.next()) {
    String name = resultSet.getString("name");
    String address = resultSet.getString("address");

    Row dataRow = personSheet.createRow(row);

    Cell dataNameCell = dataRow.createCell(0);
    dataNameCell.setCellValue(name);

    Cell dataAddressCell = dataRow.createCell(1);
    dataAddressCell.setCellValue(address);

    row = row + 1;
}

String outputDirPath = "D:/PersonList.xls";
FileOutputStream fileOut = new FileOutputStream(outputDirPath);
wb.write(fileOut);
fileOut.close();

这篇关于使用apache poi将数据库查询结果导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！