How to write fetched values from database to Excel file

Question

I need your help in storing the fetched values from a SQL statement to an excel file. I wrote the below code, but I am facing difficulties on how to write the fetched values under the appropriate columns. For example at the beginning, I created 3 headers (ID - Name - Salary). Now, I need to write the fetched values from the SQL statement under each appropriate header, but I do not know how to write them. So kindly assist. The code is:

public void GenerateExcel() {
   Connection conn = null;
   Statement stmt = null;
   FileOutputStream fileOut = new FileOutputStream("C:\\Desktop\\poi-test.xls");
   HSSFWorkbook workbook = new HSSFWorkbook();
   HSSFSheet worksheet = workbook.createSheet("Employee Details");
   HSSFRow row1 = worksheet.createRow((short) 0);
   HSSFCell cellA1 = row1.createCell((short) 0);
   cellA1.setCellValue("ID");
   HSSFCell cellB1 = row1.createCell((short) 1);
   cellB1.setCellValue("Name");
   HSSFCell cellC1 = row1.createCell((short) 1);
   cellC1.setCellValue("Salary");
   try{
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(DB_URL, USER, PASS);
      stmt = conn.createStatement();

      String sql = "SELECT id, name, amount FROM Employee";
      ResultSet rs = stmt.executeQuery(sql);
      while(rs.next()){
         int id  = rs.getInt("id");
         int age = rs.getString("name");
         String first = rs.getInt("amount");
          }
      rs.close();
      workbook.write(fileOut);
      fileOut.flush();
      fileOut.close();
   }catch(SQLException se){
      se.printStackTrace();
   }catch(Exception e){
      e.printStackTrace();
   }finally{
      try{
         if(stmt!=null)
            conn.close();
      }catch(SQLException se){
      }
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }
   }
}
}

read this http://examples.javacodegeeks.com/core-java/writeread-excel-files-in-java-example/ — Kumaresan Perumal, Oct 31 '15 at 13:54
http://stackoverflow.com/questions/19655813/how-to-write-data-in-multiple-cells-in-excel-using-java — Kumaresan Perumal, Oct 31 '15 at 13:55
@pnuts I am facing difficulties in how to write the fetched values from the database under the appropriate columns which are created at the beginning. — 99maas, Oct 31 '15 at 13:55

score 0 · Accepted Answer · answered Oct 31 '15 at 13:54

Actually I can't see where you add data to the workbook ? I can only see you creating the workbook and adding the first row.

while(rs.next()){
      int id  = rs.getInt("id");
      int age = rs.getString("name");
      String first = rs.getInt("amount");
      // Adding data here
      Row newRow = worksheet.createRow(worksheet.getLastRowNum() + 1);
      newRow.createCell(0).setCellValue(id);
      newRow.createCell(1).setCellValue(age);
      newRow.createCell(2).setCellValue(first);
}

I will try the above solution – 99maas Oct 31 '15 at 13:59 — 99maas, Oct 31 '15 at 13:59

score 0 · Answer 2 · answered Jul 02 '19 at 09:30

Finally stop wasting time writing apache poi boiler plate. Look at MemPOI's solution:

You can try using MemPOI. Take a look:

File file = new File("C:\\Desktop\\poi-test.xls")

Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement prepStmt = this.connection.prepareStatement("SELECT id, name, amount FROM Employee");

new MempoiBuilder()
            .setFile(file)
            .addMempoiSheet(new MempoiSheet(prepStmt, "Employee Details"))
            .build()
            .prepareMempoiReportToFile()
            .get();

You can easily add a template or a subfoter. Take a look at the doc

How to write fetched values from database to Excel file

2 Answers2