OAF export data from VO in xlsx format

Posted: 16-Mar-2015

Purpose:

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

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

1. Create project
2. Create EO
3. Create VO
4. Create application module
5. Create page
6. Assign AM to the page
7. Create a item with style as ‘exportButton’
8. Set controller on the page layout
9. 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.

Download workspace:

Download workspace and jar files

Display signature/logo on BI (XML) Publisher

In XML Publisher images can be displayed dynamically by following below step

1. FTP logo to $OA_MEDIA directory
2. Create dummy image
3. Reference the images via a URL

Example:

url:{concat('$[OA_MEDIA]/',..//XXYY,'.gif')}

XXYY -- is the run time element from the xml file which is passed to xml template.

<INVOICELIST>
 <IMG_DIR>/images</IMG_DIR>
 <XXYY>Logo</XXYY>
  ...
</INVOICELIST>

Running locally using BI publisher preview:

If you are planning to test dynamic image locally. Then change the url to something like

url:{'c:/temp/Org3.gif'}