Import Data from Excel sheet to DB Table through OAF page
- by PRajkumar
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();
}
}
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 --
