1 Reply Latest reply: Dec 3, 2013 10:35 AM by JuliusZ RSS

    Excel File

    Moazam Shareef

      Dear Guys,

      i'm using the below code to insert data into forms using excel file everything works fine but the file is not closing after inserting data i have to do it manually. anything wrong with the below code?

      DECLARE 
      application OLE2.OBJ_TYPE; 
      workbooks OLE2.OBJ_TYPE; 
      workbook OLE2.OBJ_TYPE; 
      worksheets OLE2.OBJ_TYPE; 
      worksheet OLE2.OBJ_TYPE; 
      cell OLE2.OBJ_TYPE; 
      args OLE2.OBJ_TYPE; 
      ctr NUMBER(12); 
      cols NUMBER(2);
      i_empid number(10);
      i_name_1 VARCHAR2(2000); 
      i_name_2 VARCHAR2(2000); 
      i_name_3 VARCHAR2(2000); 
      i_name_4 VARCHAR2(2000); 
      i_work_loc VARCHAR2(2000); 
      i_dob VARCHAR2(2000); 
      i_job_title VARCHAR2(2000); 
      i_j_date VARCHAR2(2000);
      i_nationality VARCHAR2(2000); 
      i_name_ara VARCHAR2(2000); 
      filename varchar2(100);
      
      
      PROCEDURE OLEARG IS
      args OLE2.OBJ_TYPE; 
      BEGIN
      args := OLE2.CREATE_ARGLIST; 
      ole2.add_arg(args,ctr); -- row value
      ole2.add_arg(args,cols); -- column value
      cell := ole2.GET_OBJ_PROPERTY(worksheet,'Cells',args); -- initializing cell
      ole2.destroy_arglist(args); 
      END;
      
      
      BEGIN
      filename :=GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
      application := OLE2.CREATE_OBJ('Excel.Application'); 
      ole2.set_property(application,'Visible','true'); 
      workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks'); 
      args := OLE2.CREATE_ARGLIST; 
      ole2.add_arg(args,filename); --'c:\13s002.xls'); -- file path and name
      workbook := ole2.GET_OBJ_PROPERTY(workbooks,'Open',args); 
      ole2.destroy_arglist(args); 
      args := OLE2.CREATE_ARGLIST; 
      ole2.add_arg(args,'Sheet1'); 
      worksheet := ole2.GET_OBJ_PROPERTY(workbook,'Worksheets',args); 
      ole2.destroy_arglist(args); 
      
      
      ctr := 2; --row number
      cols := 1; -- column number
      
      
      FIRST_RECORD; 
      LOOP 
      OLEARG;
      i_empid := ole2.get_num_property(cell,'Value'); --cell value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_name_1 := ole2.get_char_property(cell,'Value'); --cell value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_name_2 := ole2.get_char_property(cell,'Value'); --cell value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_name_3 := ole2.get_char_property(cell,'Value'); --cell value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_name_4 := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_work_loc := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_dob := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_job_title := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_j_date := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_nationality := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      OLEARG;
      i_name_ara := ole2.get_char_property(cell,'Value'); --cell number value of the argument
      cols := cols+1;
      
      
      IF ctr = 1 then 
      
      
      :MN_EMPLOYEE.EMPID := i_empid; 
      :MN_EMPLOYEE.NAME1_ENG := i_name_1; 
      :MN_EMPLOYEE.NAME2_ENG := i_name_2; 
      :MN_EMPLOYEE.NAME3_ENG := i_name_3;
      :MN_EMPLOYEE.NAME4_ENG := i_name_4;  
      :MN_EMPLOYEE.WORK_LOCATION := i_work_loc; 
      :MN_EMPLOYEE.DIS_DATE := i_dob;
      :MN_EMPLOYEE.JOB_TITLE := i_job_title; 
      :MN_EMPLOYEE.DIS_JOIN := i_j_date; 
      :MN_EMPLOYEE.NATIONALITY := i_nationality; 
      :MN_EMPLOYEE.NAME1_ARA := i_name_ara; 
      
      
      ELSE 
      NEXT_RECORD; 
      :MN_EMPLOYEE.EMPID := i_empid; 
      :MN_EMPLOYEE.NAME1_ENG := i_name_1; 
      :MN_EMPLOYEE.NAME2_ENG := i_name_2; 
      :MN_EMPLOYEE.NAME3_ENG := i_name_3;
      :MN_EMPLOYEE.NAME4_ENG := i_name_4;  
      :MN_EMPLOYEE.WORK_LOCATION := i_work_loc; 
      :MN_EMPLOYEE.DIS_DATE := i_dob;
      :MN_EMPLOYEE.JOB_TITLE := i_job_title; 
      :MN_EMPLOYEE.DIS_JOIN := i_j_date; 
      :MN_EMPLOYEE.NATIONALITY := i_nationality; 
      :MN_EMPLOYEE.NAME1_ARA := i_name_ara; 
      
      
      END IF; 
      
      
      EXIT WHEN length(i_empid)=0 or length(i_empid) is null; 
      ctr := ctr + 1; 
      cols := 1;
      
      
      END LOOP; 
      OLE2.INVOKE(application,'Quit'); 
      OLE2.RELEASE_OBJ(cell); 
      OLE2.RELEASE_OBJ(worksheet); 
      OLE2.RELEASE_OBJ(worksheets); 
      OLE2.RELEASE_OBJ(workbook); 
      OLE2.RELEASE_OBJ(workbooks); 
      OLE2.RELEASE_OBJ(application); 
      
      
      END; 
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      

       

      Regards

      Khaled

        • 1. Re: Excel File
          JuliusZ

          Hi,

          try to first release the objects and then quit the application. I mean:

          OLE2.RELEASE_OBJ(cell);  

          OLE2.RELEASE_OBJ(worksheet);  

          OLE2.RELEASE_OBJ(worksheets);  

          OLE2.RELEASE_OBJ(workbook);  

          OLE2.RELEASE_OBJ(workbooks);  

          OLE2.INVOKE(application,'Quit');  

          OLE2.RELEASE_OBJ(application);  

          Julius Z