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 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;



    //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 = 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++;
        }
        //Close RowSetIterator
        rsi.closeRowSetIterator();
        //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
            org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowNum++);
            //Set data in column of a row
            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(FILE_PATH);
            xwb.write(fos);
            xwb.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("Done");
    }

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

Sample ADF Application (Jdev 12.1.3)- Download

Cheers :) Happy Learning