Import Data from *.

xlsx file to DB Table through OAF page

By PRajkumar on Jul 24, 2012

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 following JAR files to Apache Library 1. poi-ooxml-3.7.jar http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/poi-ooxml/3.7

2. ooxml-schemas-1.1.jar http://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas/1.1

3. stax-api-1.0.1.jar http://www.jarfinder.com/index.php/jars/versionInfo/69009

4. log4j-1.2.16.jar http://mvnrepository.com/artifact/log4j/log4j/1.2.16

5. poi-ooxml-schemas-3.7.jar http://mavenhub.com/mvn/central/org.apache.poi/poi-ooxml-schemas/3.7

6. poi-3.7-20101029.jar http://mvnrepository.com/artifact/org.apache.poi/poi/3.7-beta1

7. xmlbeans-2.4.0.jar

http://www.jarfinder.com/index.php/jars/versionInfo/14387

8. dom4j-1.6.1.jar http://grepcode.com/snapshot/repo1.maven.org/maven2/dom4j/dom4j/1.6.1

Steps to add JAR files in Local Machine Right Click on ImportxlsDemo > Project Properties > Libraries > Add jar/Directory and browse to directory where all JAR files have been downloaded and select the JAR files

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 *.xlsx file CREATE ( --TABLE -Data xx_import_excel_data_demo -------------------Columns --------------------

column1 column2 column3 column4 column5 ---last_update_date last_updated_by creation_date created_by ); DATE NUMBER DATE NUMBER last_update_login NOT NOT NOT NOT Who

VARCHAR2(100), VARCHAR2(100), VARCHAR2(100), VARCHAR2(100), VARCHAR2(100), -------------------Columns -------------------NULL, NULL, NULL, NULL, 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

Select Generate Java File for View Object Class: ImportxlsVOImpl -> Generate Java File -> Bind Variable Accessors 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 ID AM Definition Window Title Title

Property PageLayoutRN prajkumar.oracle.apps.fnd.importxlsdemo.server.ImportxlsAM Import Data From Excel(*.xlsx) through OAF Page Demo Window Import Data From Excel(*.xlsx) through OAF Page Demo

11. Create messageComponentLayout Region Under Page Layout Region Right click PageLayoutRN > New > Region

Attribute

Property

ID Item Style

MainRN messageComponentLayout

12. Create a New Item messageFileUpload Bean under MainRN Right click on MainRN > New > messageFileUpload Set Following Properties for New Item --

Attribute ID Item Style

Property MessageFileUpload messageFileUpload

13. Create a New Item Submit Button Bean under MainRN Right click on MainRN > New > messageLayout Set Following Properties for messageLayout --

Attribute ID

Property ButtonLayout

Right Click on ButtonLayout > New > Item

Attribute ID Item Style Attribute Set

Property Go submitButton /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); OAApplicationModule am = pageContext.getApplicationModule(webBean); if (pageContext.getParameter("Go") { DataObject fileUploadData = != null)

(DataObject)pageContext.getNamedDataObject("MessageFileUpload"); String fileName; 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 oracle.jbo.domain.BlobDomain; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import oracle.apps.fnd.framework.server.OAApplicationModuleImpl; import java.io.*; import java.text.DateFormat; import java.text.SimpleDateFormat; import oracle.apps.fnd.framework.server.OAViewObjectImpl; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public void { ImportxlsVOImpl vo vo.InsertRecord(excel_data); getTransaction().commit(); } public void { try { InputStream XSSFWorkbook XSSFSheet int for (int { int int Row try { colCounts createRecord(String[] = excel_data)

(ImportxlsVOImpl)getImportxlsVO1();

ReadExcel(BlobDomain

fileData)

throws

IOException

in workbook sheet rowsCount i = j row

= = = = i

new

fileData.getBinaryStream(); XSSFWorkbook(in); workbook.getSheetAt(0); sheet.getLastRowNum(); rowsCount; i++) 0; colCounts; sheet.getRow(i);

0;

<= = =

row.getLastCellNum();

} catch { colCounts=1; } String[]

(NullPointerException

e)

excel_data =

= 0; k

new <

String[colCounts+1]; colCounts; k++)

for (int k { j=j+1; try { Cell cell = row.getCell(k); switch { case excel_data[j] break; case if { DateFormat excel_data[j] } else { int resultVar excel_data[j] } break; case excel_data[j] break; case excel_data[j] break; default: excel_data[j] } } catch {

(cell.getCellType()) Cell.CELL_TYPE_STRING: cell.getRichStringCellValue().getString(); Cell.CELL_TYPE_NUMERIC: (DateUtil.isCellDateFormatted(cell)) df = new SimpleDateFormat("MM/dd/yyyy"); =df.format(cell.getDateCellValue());

= =

resultVar; (int)cell.getNumericCellValue(); Integer.toString(resultVar);

Cell.CELL_TYPE_BOOLEAN: Boolean.toString(cell.getBooleanCellValue()); Cell.CELL_TYPE_FORMULA: (String)cell.getCellFormula();

"";

(NullPointerException

e)

excel_data[j] } } createRecord(excel_data); } } catch { e.printStackTrace(); } }

"";

(IOException

e)

Write Following Code in ImportxlsVOImpl.java

import import oracle.jbo.Row; public { try { executeQuery(); Row void oracle.apps.fnd.framework.server.OAViewObjectImpl; InsertRecord(String[] excel_data)

row i < +i

= excel_data.length;

createRow(); i++)

for (int i=1; { row.setAttribute("Column" insertRow(row); } } catch(Exception { System.out.println(e.getMessage()); } }

,excel_data[i]);

e)

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

Consider Excel PRAJ_TEST.xlsx with following data --

Lets Try to import this data into DB Table --

Category: Oracle Tags: importing_data_from_xlsx_sheet Permanent link to this entry

Oracle iRecruitment... | Main | How to deploy JAR...

Comments: Post a Comment:

Name: E-Mail: URL: Notify me by email of new comments Remember Information?

Your Comment: HTML Syntax: NOT allowed Please answer this simple math question 5+5