Rajesh K: Import Data from *.xlsx file to DB Table through OAF page

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 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

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	Property
ID	PageLayoutRN
AM Definition	prajkumar.oracle.apps.fnd.importxlsdemo.server.ImportxlsAM
Window Title	Import Data From Excel(*.xlsx) through OAF Page Demo Window
Title	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	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);

OAApplicationModule am = pageContext.getApplicationModule(webBean);

if (pageContext.getParameter("Go") != null)
{
DataObject fileUploadData =

(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 createRecord(String[] excel_data)
{
ImportxlsVOImpl vo = (ImportxlsVOImpl)getImportxlsVO1();
vo.InsertRecord(excel_data);
getTransaction().commit();
}

public void ReadExcel(BlobDomain fileData) throws IOException
{
try
{
  InputStream in = fileData.getBinaryStream();
  XSSFWorkbook workbook = new XSSFWorkbook(in);

  XSSFSheet sheet = workbook.getSheetAt(0);

  int rowsCount = sheet.getLastRowNum();
  for (int i = 0; i <= rowsCount; i++)
  {
   int j = 0;
   int colCounts;
   Row row = sheet.getRow(i);
   try
   {
    colCounts = row.getLastCellNum();
   }
   catch (NullPointerException e)
   {
    colCounts=1;
   }
   String[] excel_data = new String[colCounts+1];

   for (int k = 0; k < colCounts; k++)
   {
    j=j+1;
    try
    {
     Cell cell = row.getCell(k);

switch (cell.getCellType())
{

      case Cell.CELL_TYPE_STRING:
      excel_data[j] = cell.getRichStringCellValue().getString();
      break;

      case Cell.CELL_TYPE_NUMERIC:
      if (DateUtil.isCellDateFormatted(cell))
      {
       DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
       excel_data[j] =df.format(cell.getDateCellValue());
      }
      else
      {
       int resultVar;
       resultVar = (int)cell.getNumericCellValue();
       excel_data[j] = Integer.toString(resultVar);
      }
      break;

      case Cell.CELL_TYPE_BOOLEAN:
      excel_data[j] = Boolean.toString(cell.getBooleanCellValue());
      break;

      case Cell.CELL_TYPE_FORMULA:
      excel_data[j] = (String)cell.getCellFormula();
      break;

      default:
      excel_data[j] = "";
     }
    }
    catch (NullPointerException e)
    {
     excel_data[j] = "";
    }
   }

   createRecord(excel_data);
  }
}
catch (IOException e)
{
  e.printStackTrace();
}
}

Write Following Code in ImportxlsVOImpl.java

import oracle.apps.fnd.framework.server.OAViewObjectImpl;
import oracle.jbo.Row;

public void InsertRecord(String[] excel_data)
{
try
{
  executeQuery();
  Row row = createRow();

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

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

Consider Excel PRAJ_TEST.xlsx with following data --