0

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.

Shashank Paliwal
  • 101
  • 1
  • 9

3 Answers3

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

firegloves
  • 5,581
  • 2
  • 29
  • 50
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

Hubi
  • 440
  • 1
  • 11
  • 25
  • 1
    Hubi - I want to export table data from Oracle database into excel using java. I hope now question is clear. – Shashank Paliwal May 21 '19 at 17:17
  • 1
    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. – Hubi May 21 '19 at 17:26
  • 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. – Shashank Paliwal May 21 '19 at 17:41
  • 1
    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 May 21 '19 at 17:57
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.

Hubi
  • 440
  • 1
  • 11
  • 25