Contact : oafqueries@gmail.com (OAF/ADF Trainings and Customizations)

Friday, October 22

How to upload excel to the database table

Hi,

We create an item type for file Upload and the coding is as under :

Suppose you want to insert the data into table directly from excel so create a EO based VO.

handle the click og Go button in Controller:

OAApplicationModule am = (OAApplicationModule) pageContext.getApplicationModule(webBean);
OAViewObjectImpl vo = (OAViewObjectImpl) am.findViewObject("TempVO1");
if (pageContext.getParameter("Go")!=null)
{
DataObject fileUploadData =(DataObject)pageContext.getNamedDataObject("FileUploadItem");
String fileName = null;
String contentType = null;
Long fileSize = null;
Integer fileType = new Integer(6);
BlobDomain uploadedByteStream = null;
BufferedReader in = null;

try
{
fileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");
contentType =(String)fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");
uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, fileName);
in = new BufferedReader(new InputStreamReader(uploadedByteStream.getBinaryStream()));

fileSize = new Long(uploadedByteStream.getLength());
System.out.println("fileSize : "+fileSize);
}
catch(NullPointerException ex)
{
throw new OAException("Please Select a File to Upload", OAException.ERROR);
}
try
{
//Open the CSV file for reading
String lineReader="";
long t =0;
String[] linetext;
while (((lineReader = in.readLine()) !=null) )
{
//Split the deliminated data and
if (lineReader.trim().length()>0)
{
System.out.println("lineReader : "+lineReader.length());
linetext = lineReader.split(",");
t++;
//Print the current line being
System.out.println(t + " - " +
linetext[0].trim() + " - " + linetext[1].trim() + " - " +
linetext[2].trim() + " - " + linetext[3].trim() + " - " +
linetext[4].trim() + " - " + linetext[5].trim() );

for(Row row = (Row)vo.first(); row!= null;  row = (Row)vo.next())
{
  if(linetext[0].trim().equals(row.getAttribute("EmpId")))
  {
    row.setAttribute("EmpName",linetext[1].trim());
    row.setAttribute("Department",linetext[2].trim());
    row.setAttribute("Designation",linetext[3].trim());
    row.setAttribute("Manager",linetext[4].trim());
    row.setAttribute("Residence",linetext[5].trim());
  }
}
}
}
}
catch (IOException e)
{
  throw new OAException(e.getMessage(),OAException.ERROR);
}
am.getTransaction().commit();
throw new OAException("File Uploaded SuccessFully!!!",OAException.CONFIRMATION);
}
}

Thanks,
Gaurav

4 comments:

  1. This code throws ArrayIndexOutOfBoundsException.
    Could you please help me.

    TIA!!

    ReplyDelete
  2. This code throws ArrayIndexOutOfBoundsException.
    Could you please help me.

    TIA!!

    ReplyDelete
  3. how to skip the first line (header line) while uploading the file?

    ReplyDelete
  4. Hi, I have create OAF page using this code which takes Excel data and dump into Oracle table.Its working properly in my local server but after deployment,On server if I have select the file or not it's going in NullpointerException please select the file to upload!!! please guide me!
    Thanks,
    Riddhi

    ReplyDelete