16 October, 2012

OAF : Upload Excel File to Database


I want to allow user to upload excel file in database from OAF page.

Suppose that excel file contains below columns
EmpNo
EmpName
Job

Suppose also that I have Entity Object named XxxEmpEO and I created View Object XxxEmpVO based on previous entity object which has below attributes
EmpNo
EmpName
Job

Scenario
I added new item in page of type messageFileUpload ["uploadExcelFile" ] and Button ["uploadButton"].
If user click a button, I will upload excel file that is entered in messageFileUpload item to Entity Object and then commit changes to database.
Idea of Implementation
I will read file as byte stream and then loop for every line in file and insert it in view object.
 
Now I should handle action of upload button, I will write my code in ProcessFromRequest method in Controller Class

   public void processFromRequest(OAPageContext oapagecontext,   
                   OAWebBean oawebbean) {  
     super.processFormRequest(oapagecontext, oawebbean);  
     OAApplicationModule am = oapagecontext.getApplicationModule(oawebbean);  
     OAViewObjectImpl empVO =   
       (OAViewObjectImpl)am.findViewObject("XxxEmpVO");  
     if (oapagecontext.getParameter("uploadButton") != null) {  
       //Get Data of uploaded Excel file  
       DataObject excelUploadData =   
         oapagecontext.getNamedDataObject("uploadExcelFile");  
       //Declare Variable that will be used in reading uploaded file  
       String fileName = null;  
       String contentType = null;  
       Long fileCapacity = null;  
       BlobDomain uploadStream = null;  
       BufferedReader inReader = null;  
       try {  
         fileName =   
             (String)excelUploadData.selectValue(null, "UPLOAD_FILE_NAME");  
         contentType =   
             (String)excelUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");  
         uploadStream =   
             (BlobDomain)excelUploadData.selectValue(null, fileName);  
         inReader =   
             new BufferedReader(new InputStreamReader(uploadStream.getBinaryStream()));  
         fileCapacity = new Long(uploadStream.getLength());  
       } catch (NullPointerException ex) {  
         throw new OAException("Please Select an Excel File to Upload it to Database!!!",   
                    OAException.ERROR);  
       }  
       try {  
         String wholeLine = "";  
         long counter = 0;  
         String[] seperatedCells;  
         while (((wholeLine = inReader.readLine()) != null)) {  
           //Split the deliminated data and  
           if (wholeLine.trim().length() > 0) {  
             //split whole line to cells  
             seperatedCells = wholeLine.split(",");  
             counter++;  
             //Print the current line in Console  
             System.out.println(" line #" + counter + " - " +   
                       seperatedCells[0] + " - " +   
                       seperatedCells[1] + " - " +   
                       seperatedCells[2]);  
             Row row = empVO.createRow();  
             row.setAttribute("EmpNo", seperatedCells[0]);  
             row.setAttribute("EmpName", seperatedCells[1]);  
             row.setAttribute("Job ", seperatedCells[2]);  
           }  
         }  
       } catch (IOException e) {  
         throw new OAException(e.getMessage(), OAException.ERROR);  
       }  
       am.getTransaction().commit();  
       throw new OAException("Excel File Uploaded SuccessFully!!!",   
                  OAException.CONFIRMATION);  
     }  
   }  


Thanks

4 comments:

  1. Hi Mahmoud,

    Its a great effort.
    I am doing this task but when i run this page I got an error on submit button. there is a dialog displayed showing following message:

    "Unable to FInd Source File for Package oracle.apps.fnd.framework.webui, filename OAPageBean.java."

    and this message displayed when creating view instance with following code:

    OAApplicationModule am = (OAApplicationModule) pageContext.getApplicationModule(webBean);

    OAViewObjectImpl vo = (OAViewObjectImpl) am.findViewObject("EmpVO1");

    I am getting nullpointer exception on 2nd row of code.

    Please help me in this problem.


    ReplyDelete
  2. Hi Can you please tell me how to get the id of dynamically created web bean so that we can use the id in extended controller

    ReplyDelete
    Replies
    1. please reply to my personal mail id ..
      soumya.dabbiru@gmail.com

      Delete
  3. Hi we are able to upload data from it. I want to know how to exclude first 4 rows and insert the remaining data. Please help me how to do it.

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...