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

3 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