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

Sharing is Caring

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

Export ViewObject data to Excel

Sample ADF Application (Jdev 12.1.3)- Download

Cheers 🙂 Happy Learning

Related Posts

An Oracle ACE, Blogger, Reviewer, Technical Lead working on Oracle ADF

3 thoughts on “Export ViewObject data to Excel File Using Apache POI in Oracle ADF”

  1. 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?

Leave a Reply

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