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

@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
– 99maas, Commented Oct 31, 2015 at 13:55

Mateo Barahona · Accepted Answer · 2015-10-31 13:54:51Z

0

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

answered Oct 31, 2015 at 13:54

Mateo Barahona

1,3918 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

99maas Over a year ago

I will try the above solution

firegloves · Accepted Answer · 2019-07-02 09:30:34Z

0

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

answered Jul 2, 2019 at 9:30

firegloves

5,7292 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to write fetched values from database to Excel file

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related