使用apache poi将数据库查询结果导出为ex​​cel

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.

我浏览了一些示例代码,使用Apache POI将数据导出到excel。但是,我不确定如何将数据库查询结果导出到excel文件。我知道我们必须在行中创建单元格,然后将值设置为单元格。但是我已经在结果集中拥有数据,并且必须将其导出到excel文件中。任何人都可以为我提供一个小/简单的代码来做同样的事情。

Thanks!

2 个解决方案

#1

Try : Reference Apache POI's Developer Guide

尝试:参考Apache POI的开发人员指南

Example Person table :

示例人员表:

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

Example Program

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

#2

For HSSF format it is possible to use a Cocoon pipeline that delivers the results of a database query as XML to the POI serializer.

对于HSSF格式,可以使用Cocoon管道将数据库查询的结果作为XML提供给POI序列化程序。

This has the advantage of not entangling the database query with the calling of the POI API.

这具有不通过调用POI API来纠缠数据库查询的优点。

I am investigating the amount of work required to upgrade the serializer to handle the XSSF format to overcome the 64k limit on the number of rows in the final speadsheet.

我正在调查升级序列化程序以处理XSSF格式所需的工作量,以克服最终speadsheet中行数的64k限制。