Exporting database query results to excel using ap

问题:

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.

Thanks!

回答1:

Try : Reference Apache POI's Developer Guide

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.

This has the advantage of not entangling the database query with the calling of the 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.

回答3:

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

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