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!

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
319 views