OAF export data from VO in xlsx format

In this article we are going to see how to export view object in Microsoft office excel xlsx format

To export with xlsx format few basic things needed are
Jar files (List of jar i've used is shown in below screen shot)

For better understanding I’ll illustrated steps to follow.

Sample Workspace and the jar files can be downloaded at the end of the article

Steps to create data module to be used in the page:

a. Create table
create table suren_employee (emplno number, empl_name varchar2(100),LAST_UPDATE_DATE DATE,LAST_UPDATED_BY NUMBER (15),LAST_UPDATE_LOGIN NUMBER (15),CREATION_DATE DATE,CREATED_BY NUMBER (15));

b. Insert data to table
insert into suren_employee (emplno,empl_name) values (10, 'SURENDRANATH');
insert into suren_employee (emplno,empl_name) values (20, 'SUBRAMANI');

Steps to design the page:
Create project
Create EO
Create VO
Create application module
Create page
Assign AM to the page
Create a item with style as ‘exportButton’
Set controller on the page layout
Create button in the page layout

After following above steps your project should look like this

Copy paste below code in the controller:

/* This is a sample code which should be used in the controller
writeXLSXFile is the method which will export data
Call writeXLSXFile method in the PFR (Process Form Request)
*/

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.http.HttpServletResponse;

import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import suren.oracle.apps.ak.server.SurenEmployeeVORowImpl;
import oracle.jbo.domain.Number ;

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
if(pageContext.getParameter("Download")!=null)
{
try{
System.out.println("Before writeXLSXFile");
writeXLSXFile(pageContext, "SurenEmployeeVO1");
System.out.println("After writeXLSXFile");
}catch(IOException e){
e.printStackTrace();
}

                }  

}

public void writeXLSXFile(OAPageContext pageContext,String view_inst_name) throws IOException {

OAViewObject vo = (OAViewObject) pageContext.getRootApplicationModule().findViewObject(view_inst_name);           
String sheetName = "Export code comb";//name of sheet
HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse();

// Set excel property

response.setHeader("Header-Name", "Codecombination");
response.setHeader("Content-Length", "100000");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition","attachment; filename=" + "SurenEmp" + ".xlsx");
response.setHeader("Cache-Control","max-age=0");

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName);

// Create header

XSSFRow row12 = sheet.createRow(0);

    XSSFCell celh0 = row12.createCell(0);
    celh0.setCellValue("Employee Number");      
    XSSFCell celh1 = row12.createCell(1);
    celh1.setCellValue("Employee Name");        

int f=1;
    for(SurenEmployeeVORowImpl row = (SurenEmployeeVORowImpl) vo.first(); row != null;row = (SurenEmployeeVORowImpl) vo.next())
{
// Create line
    XSSFRow row1 = sheet.createRow(f);

             for (int i = 0; i < vo.getAttributeCount(); i++)
             {
                     
                     if (i==0) {
                     XSSFCell cell = row1.createCell(i);
                     Number empno = (Number)row.getEmplno();
                     String empno1=empno.toString();

                       cell.setCellValue(empno1);
                      System.out.println(row.getEmplno());
                     }
                     if (i==1) {
                     XSSFCell cell = row1.createCell(i);
                     cell.setCellValue(row.getEmplName());    // Dept
                     }
               }
    System.out.println("row2:" + f);
    f=f+1;
}



ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
wb.write(outByteStream);
byte[] outArray = outByteStream.toByteArray();
response.setContentLength(outArray.length);
ServletOutputStream outStream = response.getOutputStream();
outStream.write(outArray);
outStream.flush();
outStream.close();

}

You will see errors in the controller saying missing “XSSFCell not found” etc.
To fix this go to your project property and add poi library to the project.