Exporting large data from database into excel using resultset

Question

I need to export result of a resultset into an excel sheet using java. Resultset contains a table with thousands of rows and multiple columns. If someone could provide a sample code that will be very useful as I am stuck with this problem. Below is the sample code that I am using to achieve this but somehow not able to get correct data in excel file:-

SSFWorkbook workbook = new XSSFWorkbook();
    try {
    setConnection(appDB);

    String queryName="SELECT * FROM ALL_TABLES where table_name='table_name'";

    Reporter.addStepLog("----------------------------------- " + queryName.toUpperCase()
            + "\n - Validation Start" + " -----------------------------------");
    ps = con.prepareStatement(queryName, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    rs = ps.executeQuery();

    Statement statement = con.createStatement();
    XSSFSheet spreadsheet = workbook.createSheet("employedb");
      ResultSet resultSet = statement.executeQuery("select * from all_tab_columns where table_name='table_name'"); 
      XSSFRow row = spreadsheet.createRow(0);
      XSSFCell cell;
      int cc=resultSet.getMetaData().getColumnCount();
      for(int i=1;i<=cc;i++)
      {
          String headerVal=resultSet.getMetaData().getColumnName(i);
          headerValues.add(headerVal);
          cell = row.createCell(i-1);
          cell.setCellValue(resultSet.getMetaData().getColumnName(i));
      }
      System.out.println(headerValues);
      int i = 1;
      while (resultSet.next())
      {  
          for(int j=1;j<=cc;j++)
          {  
          System.out.println(resultSet.getString(j));
          XSSFRow row1 = spreadsheet.createRow((short) i);
          row1.createCell((short) i).setCellValue(resultSet.getString(resultSet.getMetaData().getColumnName(j)));
          i++;

      }  
      }

      FileOutputStream out = new FileOutputStream(new File("S:\\Downloads\\excel.xlsx"));
      workbook.write(out);
      out.close();  
      System.out.println("exceldatabase.xlsx written successfully");

}catch(Exception e){}
}

Please let me know if the question is not clear as I am new to this forum.

nothing to do with selenium here, you should remove the tag. — Valga
– Valga, Commented May 21, 2019 at 16:53

firegloves · Accepted Answer · 2019-07-02 09:09:53Z

1

You can try using MemPOI. Take a look:

File file = new File("S:\\Downloads\\excel.xlsx")

PreparedStatement prepStmt = this.connection.prepareStatement("SELECT * FROM ALL_TABLES where table_name='table_name'");
new MempoiBuilder()
                .setFile(file)
                .addMempoiSheet(new MempoiSheet(prepStmt, "employedb"))
                .build()
                .prepareMempoiReportToFile()
                .get();

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

answered Jul 2, 2019 at 9:09

firegloves

5,7292 gold badges32 silver badges53 bronze badges

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

Comments

Hubi · Accepted Answer · 2019-05-21 17:10:14Z

0

To me the Question is not so clear. You want to put calculate data to excel sheet? or you want to get calculatet data from an excel sheet?

I understood you want to put some data into the sheet. So you can use several Frameworks to do this. E.g. smartXLS or Apache POI library

answered May 21, 2019 at 17:10

Hubi

5602 gold badges13 silver badges25 bronze badges

4 Comments

Shashank Paliwal Over a year ago

Hubi - I want to export table data from Oracle database into excel using java. I hope now question is clear.

Hubi Over a year ago

Are you getting the data out of the database yet? You can retrieve the data from the database with a database query. For example, you can save it in a list. The Hibernate (ORM) framework is very helpful. Now you can iterate over the list and load the data into the Excel file and save it afterwards. For this application I would recommend Hibternate for the database query and SmartXLS for filling the ExcelSheet.

Shashank Paliwal Over a year ago

Hubi- I am using java for extracting the data from the database and storing it in the resultset. Now I want to write all the data from resultset to Excel. I have updated my question to show the code I am using to achieve this but somehow it's not working. Please help if you can provide some code or edit my code.

Hubi Over a year ago

unfortunately i'm not so much into POI. but i would recommend you to split the database query and the filling of the excel file into two separate methods

Hubi · Accepted Answer · 2019-05-21 18:00:03Z

0

    List<Double> resultList = new ArrayList();
    resultList.add(250.0);
    resultList.add(300.5);
    resultList.add(500.9);


public void setResultsToSheet(List results) throws Exception {
    WorkBook workBook = new WorkBook();
    int row = 0; //index of first row is 0
    for (int i = 0; i < results.size(); i++) {
        double result = (double) results.get(i);
        workBook.setNumber(row, 1, result);
        row++;
    }
    workBook.write("X\\yourDestination\\excel.xlsx");
}

Hope that can help you anyway.

answered May 21, 2019 at 18:00

Hubi

5602 gold badges13 silver badges25 bronze badges

3 Comments

Shashank Paliwal Over a year ago

Hubi- I will try to implement this tomorrow and let you know.As we are using poi so this might not work but thanks for quick reply.

Hubi Over a year ago

okay, let me know. Maybe I can take a closer look and help you.

Shashank Paliwal Over a year ago

Hubi- My above code worked after making minor changes. Thanks for your help.

Collectives™ on Stack Overflow

Exporting large data from database into excel using resultset

3 Answers 3

Comments

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related