Friday, 10 June 2016

Problem with Export to Excel Feature in Oracle 12c

I am trying export the table data to excel sheet using Export to Excel in 12c,it is giving the errors like <org.apache.myfaces.trinidad.component.UIXCollection> <UIXCollection> <_verifyComponentInContext> <COLLECTION_NOT_IN_CONTEXT>.
To make it work we can alternatively use the download listener(af:fileDownloadActionListener) and apache POI.
Follow the below process.
  1. Download the Apache POI jar and add it to project libraries.
  2. Create a page and drag and drop the view object as a table
  3. Drag and drop the button on the page and add a fileDownloadListener(af:fileDownloadActionListener) to the button
  4. Point the method attribute of the file download listener to a managed bean method.
//This is the code you will have in the page
<af:fileDownloadActionListener contentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                                   filename="EmployeeDtails" method="#{pageFlowScope.EmployesBean.downLoadExcel}"/> 
  1. Write the below code in the method that creates and downloads the excel file.
//This is the managed bean method
        public void downLoadExcel(FacesContext facesContext, OutputStream outputStream) {
                        try {
                                   HSSFWorkbook workbook = new HSSFWorkbook();
                                   HSSFSheet worksheet = null;
                               worksheet = workbook.createSheet("Employee Details");
                       DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
                                   DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding(iterName);
                       RowSetIterator rowSetIterator=  dcIteratorBindings.getRowSetIterator();
                       rowSetIterator=  dcIteratorBindings.getViewObject().createRowSetIterator("ExportToExcel");
                                   int i = 0;
                                   Row row=null;
                                    HSSFRow excelrow = null;
                                   while(rowSetIterator.hasNext()){
                                        row=rowSetIterator.next();
                                       if (i == 0) {
                                           excelrow = (HSSFRow) worksheet.createRow(i);
                                       int j = 0;                      
                                           for (String colName : row.getAttributeNames()) {                      
                                               HSSFCell cellA1 = excelrow.createCell(j);
                                                        cellA1.setCellValue(colName);
                                                       }
                                                    j++;
                                                  }
                                                   }
                      
                                       ++i;
                                       int j = 0;
                                       excelrow = worksheet.createRow(i);
                                       for (String colName : row.getAttributeNames()) {
                                                   HSSFCell cell = excelrow.createCell(j);
                                                   cell.setCellValue(row.getAttribute(colName).toString());
                                                   }  
                                               j++;
                                               workbook.write(outputStream);
                                               outputStream.flush();
                                               outputStream.close();
                                               rowSetIterator.closeRowSetIterator();
                                               logger.info("Export excel is finished");
                                               } catch (IOException e) {
                                               e.printStackTrace();
                                               }

                                           }

Reference 
http://www.techartifact.com/blogs/2013/08/generate-excel-file-in-oracle-adf-using-apache-poi.html#sthash.XzGOCOMf.dpbs

No comments:

Post a Comment