Wed. Aug 4th, 2021

Requirement: I have Department Data displayed in the ADF Table in the User Interface. Click on the “Export Data to Excel” button, and it should download the Departments View Object Data to the Excel file.

Solution: For solution of the above requirement follow the steps as shown below:

Step 1: Create an Oracle ADF Fusion Web Application.

Create a Business Components from Tables for the Departments table of the HR Schema of the Oracle Database 11g XE Edition.

Step 2: Create a demo.jspx page. Drag and drop DepartmentVO1 from the Data Control palette on the demo.jspx page as an ADF Table.

Drag and drop af:button on the demo.jspx page.

Set text=”Export Data to Excel” and actionListener=”#{pageFlowScope.MyBean.exportDataToExcelAL}”

Thus, the complete code for the demo.jspx page is shown below:

<?xml version='1.0' encoding='UTF-8'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1" xmlns:f="http://java.sun.com/jsf/core"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
    <jsp:directive.page contentType="text/html;charset=UTF-8"/>
    <f:view>
        <af:document title="demo.jspx" id="d1">
            <af:messages id="m1"/>
            <af:form id="f1">
                <af:panelCollection id="pc1">
                    <f:facet name="menus"/>
                    <f:facet name="toolbar">
                        <af:toolbar id="t1">
                            <af:button text="Export Data to Excel" id="b1"
                                       actionListener="#{pageFlowScope.MyBean.exportDataToExcelAL}"/>
                        </af:toolbar>
                    </f:facet>
                    <f:facet name="statusbar"/>
                    <af:table value="#{bindings.DepartmentsVO1.collectionModel}" var="row"
                              rows="#{bindings.DepartmentsVO1.rangeSize}"
                              emptyText="#{bindings.DepartmentsVO1.viewable ? 'No data to display.' : 'Access Denied.'}"
                              rowBandingInterval="0"
                              selectedRowKeys="#{bindings.DepartmentsVO1.collectionModel.selectedRow}"
                              selectionListener="#{bindings.DepartmentsVO1.collectionModel.makeCurrent}"
                              rowSelection="single" fetchSize="#{bindings.DepartmentsVO1.rangeSize}" id="t2">
                        <af:column headerText="#{bindings.DepartmentsVO1.hints.DepartmentId.label}" id="c1">
                            <af:outputText value="#{row.DepartmentId}"
                                           shortDesc="#{bindings.DepartmentsVO1.hints.DepartmentId.tooltip}" id="ot1">
                                <af:convertNumber groupingUsed="false"
                                                  pattern="#{bindings.DepartmentsVO1.hints.DepartmentId.format}"/>
                            </af:outputText>
                        </af:column>
                        <af:column headerText="#{bindings.DepartmentsVO1.hints.DepartmentName.label}" id="c2">
                            <af:outputText value="#{row.DepartmentName}"
                                           shortDesc="#{bindings.DepartmentsVO1.hints.DepartmentName.tooltip}"
                                           id="ot2"/>
                        </af:column>
                        <af:column headerText="#{bindings.DepartmentsVO1.hints.ManagerId.label}" id="c3">
                            <af:outputText value="#{row.ManagerId}"
                                           shortDesc="#{bindings.DepartmentsVO1.hints.ManagerId.tooltip}" id="ot3">
                                <af:convertNumber groupingUsed="false"
                                                  pattern="#{bindings.DepartmentsVO1.hints.ManagerId.format}"/>
                            </af:outputText>
                        </af:column>
                        <af:column headerText="#{bindings.DepartmentsVO1.hints.LocationId.label}" id="c4">
                            <af:outputText value="#{row.LocationId}"
                                           shortDesc="#{bindings.DepartmentsVO1.hints.LocationId.tooltip}" id="ot4">
                                <af:convertNumber groupingUsed="false"
                                                  pattern="#{bindings.DepartmentsVO1.hints.LocationId.format}"/>
                            </af:outputText>
                        </af:column>
                    </af:table>
                </af:panelCollection>
            </af:form>
        </af:document>
    </f:view>
</jsp:root>

Step 3: Import the below Apache POI Jar files

Please download POI jars by clicking on this link The APACHE Software Foundation

Step 4: Write the code in the MyBean.java class. Thus, the complete code in the MyBean.java class is as shown below.

package susantotech.com;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.faces.event.ActionEvent;

import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCIteratorBinding;

import oracle.binding.BindingContainer;
import oracle.binding.OperationBinding;

import oracle.jbo.Row;
import oracle.jbo.RowSetIterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MyBean {

    public MyBean() {
    }

    private static final String DOWNLOAD_FILE_PATH = "C://Users//susanto//Downloads//Departments.xlsx";

    public BindingContainer getBindingsCont() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }

    public OperationBinding executeOperation(String operation) {
        OperationBinding createParam = getBindingsCont().getOperationBinding(operation);
        return createParam;
    }

    public void exportDataToExcelAL(ActionEvent actionEvent) {
        DCIteratorBinding iter = (DCIteratorBinding) getBindingsCont().get("DepartmentsVO1Iterator");
        RowSetIterator rsi = iter.getViewObject().createRowSetIterator(null);
        XSSFWorkbook xwb = new XSSFWorkbook();
        XSSFSheet sheet = xwb.createSheet("Departments");
        int totRows = ((int) iter.getEstimatedRowCount()) + 1;
        Object[][] content = new String[totRows][4];
        int column = 4;
        content[0][0] = "Department ID";
        content[0][1] = "Department Name";
        content[0][2] = "Manager ID";
        content[0][3] = "Location ID";

        int i = 1;
        while (rsi.hasNext()) {
            Row nextRow = rsi.next();
            for (int j = 0; j < column; j++) {
                if (j == 0 && nextRow.getAttribute("DepartmentId") != null) {
                    content[i][j] = nextRow.getAttribute("DepartmentId").toString();
                }
                if (j == 1 && nextRow.getAttribute("DepartmentName") != null) {
                    content[i][j] = nextRow.getAttribute("DepartmentName").toString();
                }
                if (j == 2 && nextRow.getAttribute("ManagerId") != null) {
                    content[i][j] = nextRow.getAttribute("ManagerId").toString();
                }
                if (j == 3 && nextRow.getAttribute("LocationId") != null) {
                    content[i][j] = nextRow.getAttribute("LocationId").toString();
                }
            }
            i++;
        }
        rsi.closeRowSetIterator();
        int rowNum = 0;
        for (Object[] datatype : content) {
            org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : datatype) {
                System.out.println(field);
                Cell cell = row.createCell(colNum++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
        }
        try {
            FileOutputStream fos = new FileOutputStream(DOWNLOAD_FILE_PATH);
            xwb.write(fos);
            xwb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Save all and run the application.

lick on the “Export Data to Excel” button, and it should download the Departments View Object Data to the Departments.xlsx file.

Hence, the solution to the error.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 483 total views,  4 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *