Skip to Main Content

Java Development Tools

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Export data in Excel (FAST)

M Yaa SerApr 13 2021 — edited Apr 14 2021

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.

Comments

Post Details

Added on Apr 13 2021
3 comments
307 views