Please disable your adblock and script blockers to view this page

Search this blog

Wednesday 29 November 2017

Export ViewObject data to Excel File Using Apache POI in Oracle ADF

Hello All

Previously I have posted about importing data in ADF Table from Excel file

This post is about exporting viewObject data in Excel file using Apache POI API, Apache POI provides HSFF and XSFF to read , create and modify spreadsheets.
You can download POI jars from The APACHE Software Foundation or from here
Other than this you need to use xmlbeans and common-collections Jar

Here I am taking Departments table of HR Schema to prepare model

Create a page and drop Departments ViewObject as table and a button to export its data.

Add all JARs in viewController project

See managed bean code to generate excel file


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.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    //Path to save generated Excel File
    private static final String FILE_PATH = "D://Departments.xlsx";

    /**Method to get Binding Container of current viewport
     * @return
    public BindingContainer getBindingsCont() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();

     * Generic Method to execute operation
     * */
    public OperationBinding executeOperation(String operation) {
        OperationBinding createParam = getBindingsCont().getOperationBinding(operation);
        return createParam;

    /**Method to Export ViewObject to Excel using Apache POI API
     * @param actionEvent
    public void exportToExcelAction(ActionEvent actionEvent) {
        //Get Iterator of table
        DCIteratorBinding iter = (DCIteratorBinding) getBindingsCont().get("Department1Iterator");
        //Create RowSetIterator iterate over viewObject
        RowSetIterator rsi = iter.getViewObject().createRowSetIterator(null);
        //Create  Workbook object
        XSSFWorkbook xwb = new XSSFWorkbook();
        //Create Sheet in Workbook
        XSSFSheet sheet = xwb.createSheet("Departments");

        //No of total rows+ 1 for array sizing
        int totRows = ((int) iter.getEstimatedRowCount()) + 1;
        //Here 4 is the number of columns
        Object[][] content = new String[totRows][4];
        int column = 4;
        //Set header text in first row of table in PDF
        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 =;
            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();
        //Close RowSetIterator
        //Set data in Excel Sheet from Object array
        int rowNum = 0;
        //Iterate over Object array for each row
        for (Object[] datatype : content) {
            //Creating row in Excel Sheet
   row = sheet.createRow(rowNum++);
            //Set data in column of a row
            int colNum = 0;
            for (Object field : datatype) {
                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(FILE_PATH);
        } catch (FileNotFoundException e) {
        } catch (IOException e) {

Run and Check application, Click on button and check generated file

Sample ADF Application (Jdev 12.1.3)- Download

Cheers :) Happy Learning


  1. Hi Ashish,

    Can you suggest me what should be the next technology that i should learn after Oracle ADF.

  2. Hi i have <af:exportCollectionActionListener component in 12c but i am not able to get export data in excel it is showing error "Export is incomplete due to error" here i removed facet header="name" then it is working fine.but here i have to use acet header="name" inside select check box ihave .how to solve this problem any idea?

  3. refer this url once u will get clarity sudarsan.