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

November 29, 2017 Ashish Awasthi  3d Comments

Sharing is Caring

FacebookTwitterLinkedInEmailShare15

Hello All
Previously I have posted about importing data in ADF Table from Excel file. This post shows how to export viewObject data to Excel file using Apache POI API, Apache POI provides HSFF and XSFF classes 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 the model project of ADF Application

Create a page in the view controller project and drop Departments View Object as an ADF table and a button to export its data on the page.

Add all required JARs as the library in the viewController project

See managed bean code to generate excel file, This method iterates over view object and then creates row and cell in excel file using Apache POI API

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");
}

Now Run and Check application, Click on the button and check the generated file. This is how we can Export ViewObject data to Excel file easily

Sample ADF Application (Jdev 12.1.3)- Download