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

How to capture the Go button for the Query region and edit the where cause

Hi,

On query region we can capture the Go button click and execute our own search along with Standard Search.

     OAApplicationModule ap = (OAApplicationModule)pageContext.getApplicationModule(webBean);
    OAQueryBean bean = (OAQueryBean)webBean.findIndexedChildRecursive("queryRN");
    if(bean!=null)
    {
      String id = (String)bean.getGoButtonName();
      if(pageContext.getParameter(id)!=null)
      {
          ap.invokeMethod("initsearch",params);
       }

public void initsearch(String fromnum,String tonum,String fromdate,String todate)
 { 
    String whereClause = null;
    int whereClausecount = 0;
    OAViewObject vo = (OAViewObject)getReprintInvoiceVO1();
    vo.setWhereClause(null);
    vo.setWhereClauseParams(null);
   if(fromnum!=null&&!("".equals(fromnum.trim())))
    {
      whereClause = "INVOICE_NUMBER >= :1" ;
      vo.setWhereClauseParam(whereClausecount,fromnum);
      whereClausecount++;
    }
    if(tonum!=null&&!("".equals(tonum.trim())))
    {
      
      if(whereClause!=null)
      {
       
        whereClause = whereClause +" AND INVOICE_NUMBER <= :2";
        vo.setWhereClauseParam(whereClausecount,tonum);
        whereClausecount++;
      }
      else
      {
       
        whereClause = "INVOICE_NUMBER <= :1";
        vo.setWhereClauseParam(whereClausecount,tonum);
        whereClausecount++;
      }   
    }
    if(fromdate!=null&&!("".equals(fromdate.trim())))
    {
     if(whereClause!=null)
     {
       if((fromnum!=null&&!("".equals(fromnum.trim())))&&(tonum!=null&&!("".equals(tonum.trim()))))
       {
         whereClause = whereClause +" AND INVOICE_DATE > = :3";
         vo.setWhereClauseParam(whereClausecount,fromdate);
         whereClausecount++; 
       }
       else
       {
         whereClause = whereClause +" AND INVOICE_DATE > = :2";
         vo.setWhereClauseParam(whereClausecount,fromdate);
         whereClausecount++;
       }
      
     }
     else
     {
        whereClause = " INVOICE_DATE >= :1";
        vo.setWhereClauseParam(whereClausecount,fromdate);
        whereClausecount++;
     }
    }
    if(todate!=null&&!("".equals(todate.trim())))
    {
     if(whereClause!=null)
     {
       if((fromnum!=null&&!("".equals(fromnum.trim())))&&(tonum!=null&&!("".equals(tonum.trim())))&&(fromdate!=null&&!("".equals(fromdate.trim()))))
       {
         whereClause = whereClause +" AND INVOICE_DATE < = :4";
         vo.setWhereClauseParam(whereClausecount,todate);
         whereClausecount++;
       }
       else
       {
         whereClause = whereClause +" AND INVOICE_DATE < = :3";
         vo.setWhereClauseParam(whereClausecount,todate);
         whereClausecount++;
       }
     }
     else
     {
        whereClause = " INVOICE_DATE <= :1";
        vo.setWhereClauseParam(whereClausecount,todate);
        whereClausecount++;
     }
    }
   
    vo.setWhereClause(whereClause);
}