how to load data from database to excel sheet

Question

I need to load data from database to excel sheet. so when I run the code throw this exception. This is what i am working with so far. database table name is pettycash so I need to load data from this table.

private void excelTest(ActionEvent event) {
    try {
        String cococo = "select * from pettycash";
        ResultSet rs = database.DB_Connector.search(cococo);

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("pettycash");
        XSSFRow Header = sheet.createRow(0);
        Header.createCell(0).setCellValue("Petty ID");
        Header.createCell(1).setCellValue("pettycash_static_ammount");
        Header.createCell(2).setCellValue("pettycash_balance");
        Header.createCell(3).setCellValue("pettygiv_Date");
        Header.createCell(4).setCellValue("pettycash_status");
        Header.createCell(5).setCellValue("Rider_idRider");

        int index = 1;
        while (rs.next()) {

            XSSFRow row = sheet.createRow(index);
            row.createCell(0).setCellValue(rs.getString("idpettycash"));
            row.createCell(1).setCellValue(rs.getString("pettycash_static_ammount"));
            row.createCell(2).setCellValue(rs.getString("pettycash_balance"));
            row.createCell(3).setCellValue(rs.getString("pettygiv_Date"));
            row.createCell(4).setCellValue(rs.getString("pettycash_status"));
            row.createCell(5).setCellValue(rs.getString("Rider_idRider"));
            index++;

        }

        FileOutputStream fileout = new FileOutputStream("petycash.xlsx");
        wb.write(fileout);
        fileout.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

"when I run the code throw this exception". Post the complete [stack trace](http://stackoverflow.com/q/3988788/2775450) in the question, and indicate which line in the code you posted is throwing the exception. — James_D, Apr 28 '17 at 02:13

score 0 · Answer 1 · answered Jul 02 '19 at 08:07

Look at this solution using MemPOI

private void excelTest(ActionEvent event) {
    try {
        String cococo = "select * from pettycash";
        PreparedStatement prepStmt = database.DB_Connector.preparedStatement(cococo);

        File file = new File("petycash.xlsx");

        new MempoiBuilder()
                .setFile(file)
                .addMempoiSheet(new MempoiSheet(prepStmt))
                .build()
                .prepareMempoiReportToFile()
                .get();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

score -1 · Accepted Answer · answered Apr 28 '17 at 02:17

-1

You can use this library to perform what you are trying to do manually. One line of code:

writer.writeAll(rs, includeHeaders);

answered Apr 28 '17 at 02:17

Dan

231
1
12

The question is asking how to write a database file to an Excel spreadsheet file, and why the posted code is throwing an exception. CSV is not the same as XLSX. This doesn't answer the question that was asked. – James_D Apr 28 '17 at 02:19

how to load data from database to excel sheet

2 Answers2