• July 8, 2012

Import Data from Excel sheet to DB Table through OAF page

Guest Author

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarImportxlsDemo


Automatically a new OA Project will also be created


Project Name -- ImportxlsDemo

Default Package -- prajkumar.oracle.apps.fnd.importxlsdemo


2. Add JAR file jxl-2.6.3.jar to Apache Library

Download jxl-2.6.3.jar from following link –



Steps to add jxl.jar file in Local Machine

Right Click on ImportxlsDemo > Project Properties > Libraries > Add jar/Directory and browse to directory where jxl-2.6.3.jar has been downloaded and select the JAR file





  Click here to know Steps to Add JAR file into R12 server in OA 


3. Create a New Application Module (AM)

Right Click on ImportxlsDemo > New > ADF Business Components > Application Module

Name -- ImportxlsAM

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.server


Check Application Module Class: ImportxlsAMImpl Generate JavaFile(s)


4. Create Test Table in which we will insert data from excel

CREATE TABLE xx_import_excel_data_demo
   -- --------------------
     -- Data Columns
     -- --------------------
     column1                 VARCHAR2(100),
     column2                 VARCHAR2(100),
     column3                 VARCHAR2(100),
     column4                 VARCHAR2(100),
     column5                 VARCHAR2(100),
     -- --------------------
     -- Who Columns
     -- --------------------
     last_update_date   DATE         NOT NULL,
     last_updated_by    NUMBER   NOT NULL,
     creation_date         DATE         NOT NULL,
     created_by             NUMBER    NOT NULL,
     last_update_login  NUMBER


5. Create a New Entity Object (EO)

Right click on ImportxlsDemo > New > ADF Business Components > Entity Object

Name – ImportxlsEO

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.schema.server



Note – By default ROWID will be the primary key if we will not make any column to be primary key

Check the Accessors, Create Method, Validation Method and Remove Method


6. Create a New View Object (VO)

Right click on ImportxlsDemo > New > ADF Business Components > View Object

Name -- ImportxlsVO

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.server


In Step2 in Entity Page select ImportxlsEO and shuttle it to selected list

In Step3 in Attributes Window select all columns and shuttle them to selected list


In Java page Uncheck Generate Java file for View Object Class: ImportxlsVOImpl

Select Generate Java File for View Row Class: ImportxlsVORowImpl -> Generate Java File -> Accessors


7. Add Your View Object to Root UI Application Module

Right click on ImportxlsAM > Edit ImportxlsAM > Data Model >

Select ImportxlsVO and shuttle to Data Model list


8. Create a New Page

Right click on ImportxlsDemo > New > Web Tier > OA Components > Page

Name -- ImportxlsPG

Package -- prajkumar.oracle.apps.fnd.importxlsdemo.webui


9. Select the ImportxlsPG and go to the strcuture pane where a default region has been created


10. Select region1 and set the following properties:






AM Definition


Window Title

Import Data From Excel through OAF Page Demo Window


Import Data From Excel through OAF Page Demo


11. Create messageComponentLayout Region Under Page Layout Region

Right click PageLayoutRN > New > Region






Item Style



12. Create a New Item messageFileUpload Bean under MainRN

Right click on MainRN > New > messageFileUpload

Set Following Properties for New Item --






Item Style



13. Create a New Item Submit Button Bean under MainRN

Right click on MainRN > New > messageLayout

Set Following Properties for messageLayout --







Right Click on ButtonLayout > New > Item






Item Style


Attribute Set



14. Create Controller for page ImportxlsPG

Right Click on PageLayoutRN > Set New Controller

Package Name: prajkumar.oracle.apps.fnd.importxlsdemo.webui

Class Name: ImportxlsCO


Write Following Code in ImportxlsCO in processFormRequest

import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import java.io.Serializable;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.cabo.ui.data.DataObject;
import oracle.jbo.domain.BlobDomain;

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
 super.processFormRequest(pageContext, webBean);

 if (pageContext.getParameter("Go") != null)
  DataObject fileUploadData = (DataObject)pageContext.getNamedDataObject("MessageFileUpload");
  String fileName = null;
   fileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
  catch(NullPointerException ex)
   throw new OAException("Please Select a File to Upload", OAException.ERROR);

  BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);
   OAApplicationModule oaapplicationmodule = pageContext.getRootApplicationModule();
   Serializable aserializable2[] = {uploadedByteStream};
   Class aclass2[] = {BlobDomain.class };
   oaapplicationmodule.invokeMethod("ReadExcel", aserializable2,aclass2);
  catch (Exception ex)
   throw new OAException(ex.toString(), OAException.ERROR);



Write Following Code in ImportxlsAMImpl.java

import java.io.IOException;
import java.io.InputStream;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.jbo.Row;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.server.OAViewObjectImpl;
import oracle.jbo.domain.BlobDomain;

public void createRecord(String[] excel_data)

 OAViewObject vo = (OAViewObject)getImportxlsVO1();  
 if (!vo.isPreparedForExecution())  
 Row row = vo.createRow();
  for (int i=0; i < excel_data.length; i++)
   row.setAttribute("Column" +(i+1) ,excel_data[i]);
 catch(Exception e)



public void ReadExcel(BlobDomain fileData) throws IOException
 String[] excel_data  = new String[5];
 InputStream inputWorkbook = fileData.getInputStream();
 Workbook w;
  w = Workbook.getWorkbook(inputWorkbook);
  // Get the first sheet
  Sheet sheet = w.getSheet(0);
  for (int i = 0; i < sheet.getRows(); i++)
   for (int j = 0; j < sheet.getColumns(); j++)
    Cell cell = sheet.getCell(j, i);
    CellType type = cell.getType();
    if (cell.getType() == CellType.LABEL)
     System.out.println("I got a label " + cell.getContents());
     excel_data[j] = cell.getContents();

    else if (cell.getType() == CellType.NUMBER)
     System.out.println("I got a number " + cell.getContents());
     excel_data[j] = cell.getContents();

     excel_data[j] = "";

 catch (BiffException e)


15. Congratulation you have successfully finished. Run Your page and Test Your Work


Consider Excel PRAJ_TEST.xls with following data --




Lets Try to import this data into DB Table --






Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.