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

Question

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

谢谢!

Answer 1

尝试:引用Apache POI's Developer Guide

示例Person表：

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

Answer 2

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

这样做的优点是不会使数据库查询与POI API的调用纠缠在一起。

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

Answer 3

如果你正在使用(或者你可以使用) 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);
    }
}