Java 使用apache poi将数据库查询结果导出到excel

Exporting database query results to excel using apache poi

提问by user3222635

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!

谢谢！

采纳答案by Zaw Than oo

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

回答by user3279935

For HSSF format it is possible to use a Cocoonpipeline 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 格式以克服最终电子表格中行数的 64k 限制所需的工作量。

回答by user7028012

if youre using (or you can use) SqlResultSet then this solution fits you: https://github.com/OfekRv/DraggerReports/blob/master/src/main/java/dragger/bl/exporter/ExcelReportExporter.java

如果你正在使用（或者你可以使用）SqlResultSet，那么这个解决方案适合你：https: //github.com/OfekRv/DraggerReports/blob/master/src/main/java/dragger/bl/exporter/ExcelReportExporter.java

public class ExcelReportExporter implements ReportExporter {
    private static final char UNDER_LINE = '_';
    private static final char SPACE = ' ';
    private static final String SUFFIX = ".xlsx";
    private static final int TITLE_ROW = 0;
    private static final int HEADER_ROW = 3;
    private static final int RESULTS_FIRST_ROW = HEADER_ROW + 1;
    private static final int FIRST_COLUMN_INDEX = 0;

    @Inject
    QueryGenerator generator;
    @Inject
    QueryExecutor executor;

    @Override
    public File export(Report reportToExport) throws DraggerExportException {
        String reportName = generateReportName(reportToExport);
        SqlRowSet results = executor.executeQuery(generator.generate(reportToExport.getQuery()));
        SqlRowSetMetaData resultsMetaData = results.getMetaData();

        try (Workbook workbook = new XSSFWorkbook();) {
            Sheet sheet = workbook.createSheet(reportName);
            createTitle(reportToExport, workbook, sheet);
            createHeaderRowFromMetadata(resultsMetaData, workbook, sheet);
            int excelRowIndex = createDataTableFromResultset(results, resultsMetaData, workbook, sheet);
            setTableAutoFilter(resultsMetaData, sheet, excelRowIndex);
            saveExcelFile(reportName, workbook);
            autoSizeColumns(resultsMetaData, sheet);

        } catch (IOException e) {
            throw new DraggerExportException("Could not create export file", e);
        }

        return new File(reportName);
    }

    private String generateReportName(Report reportToExport) {
        return reportToExport.getName().replace(SPACE, UNDER_LINE) + UNDER_LINE + LocalDate.now() + SUFFIX;
    }

    private void autoSizeColumns(SqlRowSetMetaData resultsMetaData, Sheet sheet) {
        for (int i = FIRST_COLUMN_INDEX; i < resultsMetaData.getColumnCount(); i++) {
            sheet.autoSizeColumn(i);
        }
    }

    private void saveExcelFile(String reportName, Workbook workbook) throws IOException, FileNotFoundException {
        try (FileOutputStream fileOut = new FileOutputStream(reportName);) {
            workbook.write(fileOut);
        }
    }

    private void setTableAutoFilter(SqlRowSetMetaData resultsMetaData, Sheet sheet, int excelRowIndex) {
        sheet.setAutoFilter(new CellRangeAddress(HEADER_ROW, excelRowIndex, FIRST_COLUMN_INDEX,
                resultsMetaData.getColumnCount() - 1));
    }

    private int createDataTableFromResultset(SqlRowSet results, SqlRowSetMetaData resultsMetaData, Workbook workbook,
            Sheet sheet) {
        int excelRowIndex = RESULTS_FIRST_ROW;
        CellStyle DataStyle = createDataCellStyle(workbook);

        while (results.next()) {
            Row row = sheet.createRow(excelRowIndex);

            for (int i = FIRST_COLUMN_INDEX; i < resultsMetaData.getColumnCount(); i++) {
                CreateCell(results.getObject(resultsMetaData.getColumnNames()[i]).toString(), DataStyle, row, i);
            }

            excelRowIndex++;
        }
        return excelRowIndex;
    }

    private void createHeaderRowFromMetadata(SqlRowSetMetaData resultsMetaData, Workbook workbook, Sheet sheet) {
        Row headerRow = sheet.createRow(HEADER_ROW);
        CellStyle headerStyle = createHeaderCellStyle(workbook);

        for (int i = FIRST_COLUMN_INDEX; i < resultsMetaData.getColumnCount(); i++) {
            CreateCell(resultsMetaData.getColumnNames()[i], headerStyle, headerRow, i);
        }
    }
    private void CreateCell(String data, CellStyle DataStyle, Row row, int cellIndex) {
        Cell cell = row.createCell(cellIndex);
        cell.setCellValue(data);
        cell.setCellStyle(DataStyle);
    }
}