Showing posts with label Export to Excel. Show all posts
Showing posts with label Export to Excel. Show all posts

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