Home » Developer & Programmer » Forms » How to: Read a range of excel by webutil? (Oracle Form Builder 10g, Window 7 Pro)
How to: Read a range of excel by webutil? [message #553590] Mon, 07 May 2012 01:41 Go to next message
tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam
Junior Member

Hi All,

I want to read a range of excel by webutil in oracle form. I use command:

ole2_client.get_obj_property(worksheet, 'Range', args);

please help me!!!

I want to know a list property of an OLE2 Automation Object???

Re: How to: Read a range of excel by webutil? [message #553598 is a reply to message #553590] Mon, 07 May 2012 02:15 Go to previous messageGo to next message
tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam
Junior Member

Or read a row of excel file by webutil....
Re: How to: Read a range of excel by webutil? [message #553616 is a reply to message #553598] Mon, 07 May 2012 05:04 Go to previous messageGo to next message
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
Senior Member
First u neen to configure webutil for opening Dialogue Box

http://www.baigzeeshan.com/2010/01/open-file-dailog-box-example-using.html

Try this friend

   declare
         args client_ole2.list_type;
         application client_ole2.obj_type;
         vworkbooks client_ole2.obj_type;
         vdoc client_ole2.obj_type;
         vworksheet client_ole2.obj_type;
         vrange client_ole2.obj_type;
   begin
         application := client_ole2.create_obj('Excel.Application');
         client_OLE2.SET_PROPERTY(application, 'Visible','True');
         vworkbooks := client_ole2.get_obj_property(application, 'Workbooks');
         args := client_ole2.create_arglist;
         client_ole2.ADD_ARG(args, 'c:\baba.xls');
         vdoc :=client_ole2.INVOKE_OBJ(vworkbooks,'Open',args);
         client_ole2.destroy_arglist(args);
         args := client_ole2.create_arglist;
         client_ole2.ADD_ARG(args, 1);
         vworksheet := client_ole2.get_obj_property(vdoc,'Worksheets',args); 
         client_ole2.destroy_arglist(args);
         args := client_ole2.create_arglist;
         client_ole2.ADD_ARG(args, 'B6'); 
         vrange := client_ole2.get_obj_property(vworksheet,'Range',args);
         client_ole2.destroy_arglist(args);
         message(client_ole2.get_char_property(vrange,'Value'));
         client_ole2.release_obj(vrange);
         client_ole2.release_obj(vworksheet);
         client_ole2.release_obj(vdoc);
         client_ole2.release_obj(vworkbooks);
         client_ole2.release_obj(application);
    end; 

--------------------------------webutil---------------------------------------

   DECLARE 
        application    Client_OLE2.Obj_Type;
        workbooks      Client_OLE2.Obj_Type;
        workbook       Client_OLE2.Obj_Type;
        worksheets     Client_OLE2.Obj_Type;
        worksheet      Client_OLE2.Obj_Type;
        worksheet2     Client_OLE2.Obj_Type;
        cell           Client_OLE2.OBJ_TYPE;
        args           Client_OLE2.OBJ_TYPE;
        cell_value     varchar2(100);
        num_wrkshts    NUMBER;
        wksht_name     VARCHAR2(250);
        eod            Boolean := false;
        j              integer := 1;
        v_fName        VARCHAR2(250);
BEGIN 
        v_fName := WebUtil_File.File_Open_Dialog(
                    directory_name => 'C:\'
                   ,File_Filter => null
                   ,Title => 'Select Client filename to Open.'
                   );
   IF ( v_fName IS NOT NULL ) THEN
                   application := 
                   Client_OLE2.create_obj('Excel.Application');
                   Client_OLE2.set_property(application,'Visible','false');              
                workbooks :=
                   Client_OLE2.Get_Obj_Property(application, 'Workbooks');
           args := Client_OLE2.CREATE_ARGLIST;
                   Client_OLE2.add_arg(args,v_fName);
     workbook :=
                   Client_OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
                   Client_OLE2.destroy_arglist(args);
     worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
     num_wrkshts:= Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');        
     worksheet := 
                   Client_OLE2.GET_OBJ_PROPERTY(
                         application,'activesheet');
       go_block('baba');
            first_record;
              loop   
      If :system.record_status <> 'NEW' then
            create_record;
      end if;
            exit when eod;
                    for k in 1..3 loop
              args:= Client_OLE2.create_arglist;
              Client_OLE2.add_arg(args, j);
              Client_OLE2.add_arg(args, k);
              cell:= Client_OLE2.get_obj_property(worksheet, 'Cells', args);             Client_OLE2.destroy_arglist(args);
            cell_value :=Client_OLE2.get_char_property(cell, 'Value');
   if upper(cell_value) = 'EOD' then
             eod:=true;
                  Message('End of Data');
                  exit;
            end if;
                copy(cell_value,name_in('system.cursor_item'));
            next_item;
            end loop;
                j:=j+1;
      end loop;
                 IF (cell IS NOT NULL) THEN
                    Client_OLE2.release_obj(cell);
      END IF;
      IF (worksheet IS NOT NULL) THEN
             Client_OLE2.release_obj(worksheet);
      END IF;
      IF (worksheets IS NOT NULL) THEN
             Client_OLE2.release_obj(worksheets);
      END IF;
      IF (worksheet2 IS NOT NULL) THEN
             Client_OLE2.release_obj(worksheet2);
      END IF;
      IF (workbook IS NOT NULL) THEN
             Client_OLE2.release_obj(workbook);
      END IF;
      IF (workbooks IS NOT NULL) THEN
             Client_OLE2.release_obj(workbooks);
      END IF;
      Client_OLE2.invoke(application,'Quit');
      Client_OLE2.release_obj(application);
   ELSE
      Message('No File selected.');
      message(' ');
      RAISE Form_Trigger_Failure;
   END IF;
END;


regards
baba

[Updated on: Mon, 07 May 2012 05:30]

Report message to a moderator

Re: How to: Read a range of excel by webutil? [message #554360 is a reply to message #553616] Sun, 13 May 2012 22:07 Go to previous message
tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam
Junior Member

Thanks owais_baba very much!!!
Previous Topic: Display duplicate values just once
Next Topic: How to: Export data to excel with unicode font (Vietnamese) in oracle form by WebUtil
Goto Forum:
  


Current Time: Fri Jul 05 22:21:27 CDT 2024