How to Export data in Excel (FAST)

M Yaa Ser

Hello Fellas,

I am using Oracle 12.2.1 Jdev,

I am exporting data by using "File Download Action Listener" Component with below code. but it's taking too much time when I have more than 1000 lines of record..

My query is How can I do it quickly for multiple rows.

**** Moderating action (Timo): edited the code into a code block for better readabillity ****

 public void excelExport(FacesContext facesContext, OutputStream outputStream) {
    try {
      HSSFWorkbook workbook = new HSSFWorkbook();
      HSSFSheet worksheet = workbook.createSheet("Renewal Worksheet");

      DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
      DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("RenewalCrmDueListingHdrView1Iterator");

      HSSFRow excelrow = null;
      // Get all the rows of a iterator
      //      oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
      oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
      int LineNumber = 0;
      ViewObject dueListVO = dcIteratorBindings.getViewObject();
      RowSetIterator customIter = dueListVO.createRowSetIterator("CustomIterator");
      customIter.reset();

      while (customIter.hasNext()) {
        //        for (oracle.jbo.Row row : rows) {
        oracle.jbo.Row row = customIter.next();
        //print header on first row in excel
        if (LineNumber == 0) {
          // Create a Font for styling header cells
          Font headerFont = workbook.createFont();
          headerFont.setBold(true);

          // Create a CellStyle with the font
          CellStyle headerCellStyle = workbook.createCellStyle();
          headerCellStyle.setFont(headerFont);

          excelrow = (HSSFRow) worksheet.createRow((short) LineNumber);
          short ColumnNumber = 0;
          for (String colName : row.getAttributeNames()) {
            if (("CcDueListingIdPk".equalsIgnoreCase(colName)) ||
              ("LastUpdatedBy".equalsIgnoreCase(colName)) || ("UserIdFk".equalsIgnoreCase(colName)) ||
              ("LastUpdatedDate".equalsIgnoreCase(colName)) || ("EnterDate".equalsIgnoreCase(colName))) {
            } else {
              HSSFCell cellA1 = excelrow.createCell((short) ColumnNumber);
              cellA1.setCellValue(colName.toUpperCase());
              cellA1.setCellStyle(headerCellStyle);
              ColumnNumber++;
            }
          }
        }

        ++LineNumber;
        short j = 0;
        excelrow = worksheet.createRow((short) LineNumber);
        for (String colName : row.getAttributeNames()) {
          HSSFCell cell = excelrow.createCell(j);
          Object CellValue = null;
          try {
            CellValue = row.getAttribute(colName);
          } catch (NullPointerException e) {
            CellValue = null;
          }

          if (("CcDueListingIdPk".equalsIgnoreCase(colName)) || ("LastUpdatedBy".equalsIgnoreCase(colName)) ||
            ("UserIdFk".equalsIgnoreCase(colName)) || ("LastUpdatedDate".equalsIgnoreCase(colName)) ||
            ("EnterDate".equalsIgnoreCase(colName))) {

          } else {
            if (CellValue instanceof String) {
              //              if (("CommencementDate".equalsIgnoreCase(colName)) ||
              //                ("DueSince".equalsIgnoreCase(colName)) || ("ReceiptDate".equalsIgnoreCase(colName)) ||
              //                ("EnterDate".equalsIgnoreCase(colName))) {
              //
              //                SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yy");
              //                java.util.Date date = formatter.parse((String) CellValue);
              //
              //                cell.setCellValue(formatter.format(date));
              //
              //              }
              //              else {
              cell.setCellValue((String) CellValue);
              //              }
            } else if (CellValue instanceof oracle.jbo.domain.Date) {

              //              if (("CommencementDate".equalsIgnoreCase(colName)) ||
              //                ("DueSince".equalsIgnoreCase(colName)) || ("ReceiptDate".equalsIgnoreCase(colName)) ||
              //                ("EnterDate".equalsIgnoreCase(colName))) {
              //                SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yy");
              //                java.util.Date date = formatter.parse((String) CellValue);
              //                cell.setCellValue((Date)formatter.format(date));
              cell.setCellValue((String) CellValue.toString());
              //              }
            } else if (CellValue instanceof oracle.jbo.domain.Number) {
              cell.setCellValue(((oracle.jbo.domain.Number) CellValue).doubleValue());
            } else {
              try {
                cell.setCellValue(CellValue.toString());
              } catch (Exception e) {
                cell.setCellValue("");
              }
            }
            j++;
          }
        }

        // Resize all columns to fit the content size
        for (int l = 0; l < row.getAttributeCount(); l++) {
          worksheet.autoSizeColumn(l);
        }
        //        worksheet.createFreezePane(0, 1, 0, 1);
      }

      customIter.closeRowSetIterator();
      workbook.write(outputStream);
      outputStream.flush();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

Urgent response would be appreciable.

Timo Hahn

User, I don't know, but it looks like you have copied some code without understanding it or howand for what purpost it is used.

Some parts of the code are commented out. Why?

If you don't need them remove them.

Put the code in a code block would help us to read the code.

From what I understand, you are creating an exel file. This will take some time, Have you tried an exportToExcel listener directly?

Check if this works faster then the code you copied.

If you need a real excel file, you should start analyse where the code is consuming the most time. -this part can then be optimized.

Back to the code: It reads teh rows multiple times using only one row later for pocessing.

Timo

M Yaa Ser

Some parts are commented because logic changed by user,

I know what I have written and copied, and everyone do same and apply it by there own way

No I didnt try exportToExcel directly,

Timo Hahn

User, I edited your original post to make the code readable. If you don't use some of the code, you should remove it. Otherwise it's hard to understand.

USE A CODE BLOCK FOR CODE, PLEASE!

Go other the code gain, and comment each block (at least) to describe what you try to do there.

Analyze where the time is spent. Optimizations don't make sense if you don't know where the time is spent.

Then it would help if you defined what you mean by 'fast.' I'm a fast runner, but without anything to compare 'fast' to, it doesn't mean anything.

Timo