Requirement:Generate excel file using open source “Apache POI” to generate excel spreadsheets from my ADF application.
Advantage of using POI over default export collectionListener of ADF –
1. Using Apache POI, you can not only export excels but also can read excel files (.xls or .xlsx) from local machines and upload them to ADF applications
2. User has more control on the excel output file (than using “Export Collection Action Listener”)
for e.g. you can put data formatting for your currency/date column, freeze panes, apply filters etc.
3. Size of generated excel is less than from export CollectionListener.
Solution-
You need to download the apache POI jar from poi.apache.org
–>> poi-3.7-20101029.jar
You need to add this jar in project libraries and classpath of project.
Creating a sample project (using Department and Employee tables) and attaching libararies.
I would like to keep it simple as we don’t want to focus on any steps other than excel here, here are quick steps to create a sample application
1. create view object using query on locations table.
2. Expose the VO to applicationModule.then drag drop VO from data control to page as table
3. Place a command button anywhere on the page and label it as “ExporToExcel”
4. Drop a “
Put property of af:fileDownloadActionListener as follows
-> contentType: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet or you can use “application/octet-stream” .
-> ExcelFromPoi.xls
-> Method: #{PoiBean.generateExcel} //custom method call to export data
Now writing a custom method in managed bean to export locations shown in the screen.
I am writing an generateExcel() method.
In short, follow the steps-
create a Workbook object.create a Sheet object.Get object of bindings .After that we will get page iterator and from that iterator we will get all rows in that range. Start creating row first and then cells. After completion of sheet , we will writing outputStream on the workBook.
Following is code of managed bean –
package vinay.view;
import com.sun.jmx.snmp.Timestamp;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
import javax.faces.context.FacesContext;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import oracle.adf.model.BindingContainer;
import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCBindingContainer;
import oracle.adf.model.binding.DCIteratorBinding;
public class PoiBean {
public PoiBean() {
}
public static BindingContainer getBindingContainer() {
//return (BindingContainer)JSFUtils.resolveExpression("#{bindings}");
return (BindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
}
public static DCBindingContainer getDCBindingContainer() {
return (DCBindingContainer)getBindingContainer();
}
public void generateExcel(FacesContext facesContext, OutputStream outputStream) throws IOException {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
DCBindingContainer bindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("ViewObj1Iterator");
HSSFRow excelrow = null;
// Get all the rows of a iterator
oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
int i = 0;
for (oracle.jbo.Row row : rows) {
//print header on first row in excel
if (i == 0) {
excelrow = (HSSFRow)worksheet.createRow((short)i);
short j = 0;
for (String colName : row.getAttributeNames()) {
HSSFCell cellA1 = excelrow.createCell((short) j);
cellA1.setCellValue(colName);
j++;
}
}
//print data from second row in excel
++i;
short j = 0;
excelrow = worksheet.createRow((short)i);
for (String colName : row.getAttributeNames()) {
System.out.println("hello "+row.getAttribute(colName));
System.out.println("hello "+colName);
HSSFCell cell = excelrow.createCell(j);
if (colName.equalsIgnoreCase("DepartmentId")) {
cell.setCellValue(row.getAttribute(colName).toString());
System.out.println("colName "+colName+"row.getAttribute(colName).toString()"+row.getAttribute(colName).toString());
}
//logic for cell formatting
if (colName.equalsIgnoreCase("DepartmenName")) {
cell.setCellValue(row.getAttribute(colName).toString());
}
//make it double if you want and convert accordingly
else if (colName.equalsIgnoreCase("LocationId")){
cell.setCellValue(row.getAttribute(colName).toString());
}
else if (colName.equalsIgnoreCase("ManagerId")){
if(null!=row.getAttribute(colName)){
cell.setCellValue(row.getAttribute(colName).toString());
}
} else
cell.setCellValue(row.getAttribute(colName).toString());
j++;
}
worksheet.createFreezePane(0, 1, 0, 1);
}
workbook.write(outputStream);
outputStream.flush();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
You can freeze the header like this —
worksheet.createFreezePane(0, 1, 0, 1);
Resize the column automatically –
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
And finally you can get the generated excel report like below
you can download the sample application from below, I have also illustrat how you can generate excel using export collectionListner in sample application.Button label as exportCollectionActionListener –
Happy coding with Vinay kumar in Techartifact
