Import Data from Excel sheet to DB Table through OAF page

Posted by PRajkumar on Oracle Blogs See other posts from Oracle Blogs or by PRajkumar
Published on Sun, 8 Jul 2012 14:19:01 +0000 Indexed on 2012/07/08 15:20 UTC
Read the original article Hit count: 939

Filed under:

/Oracle

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 –

http://www.findjar.com/jar/net.sourceforge.jexcelapi/jars/jxl-2.6.jar.html

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

Steps to add jxl.jar file at EBS middle tier

On your EBS middile tier copy jxl.jar at $FND_TOP/java/3rdparty/standalone

Add $FND_TOP/java/3rdparty/standalone\jxl.jar to custom classpath in Jser.properties file which is at $IAS_ORACLE_HOME/Apache/Jserv/etc

wrapper.classpath=/U01/oracle/dev/devappl/fnd/11.5.0/java/3rdparty/stdalone/jxl.jar

Bounce Apache Server

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

Database Objects -- XX_IMPORT_EXCEL_DATA_DEMO

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:

Attribute	Property
ID	PageLayoutRN
AM Definition	prajkumar.oracle.apps.fnd.importxlsdemo.server.ImportxlsAM
Window Title	Import Data From Excel through OAF Page Demo Window
Title	Import Data From Excel through OAF Page Demo

11. Create messageComponentLayout Region Under Page Layout Region

Right click PageLayoutRN > New > Region

Attribute	Property
ID	MainRN
Item Style	messageComponentLayout

12. Create a New Item messageFileUpload Bean under MainRN

Right click on MainRN > New > messageFileUpload

Set Following Properties for New Item --

Attribute	Property
ID	MessageFileUpload
Item Style	messageFileUpload

13. Create a New Item Submit Button Bean under MainRN

Right click on MainRN > New > messageLayout

Set Following Properties for messageLayout --

Attribute	Property
ID	ButtonLayout

Right Click on ButtonLayout > New > Item

Attribute	Property
ID	Go
Item Style	submitButton
Attribute Set	/oracle/apps/fnd/attributesets/Buttons/Go

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;

  try
  {
   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);
  try
  {
   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())
{
  vo.executeQuery();
}

Row row = vo.createRow();
try
{
  for (int i=0; i < excel_data.length; i++)
  {
   row.setAttribute("Column" +(i+1) ,excel_data[i]);
  }
}
catch(Exception e)
{
  System.out.println(e.getMessage());
}

vo.insertRow(row);
getTransaction().commit();
}

public void ReadExcel(BlobDomain fileData) throws IOException
{
String[] excel_data = new String[5];
InputStream inputWorkbook = fileData.getInputStream();
Workbook w;

try
{
  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();
    }

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

catch (BiffException e)
{
  e.printStackTrace();
}
}