2 Replies Latest reply: Aug 28, 2013 3:43 AM by Su.gi RSS

    How to open excel and save the data in oracle forms 6i?

    997421

      Hi to all,

                i have one requirment.that is when button pressed one  excel file be opened and save the form data in oracle forms 6i.i serach the google so many packeges also available but i don't how to use.any one implementing this please give the code and process.i am new  to forms.urgent.

       

       

       

      Redards,

      Stevie

        • 1. Re: How to open excel and save the data in oracle forms 6i?
          Andreas Weiden

          search the forum for "ole2 excel import" there are lots of examples already in the first few results.

          • 2. Re: How to open excel and save the data in oracle forms 6i?
            Su.gi

            hi,

             

            procedure xls is

            begin

            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);

            name_var1 varchar2(2000);

            name_var2 varchar2(2000);

            name_var3 varchar2(2000);

            name_var4 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

                 clear_block(no_validate);

            filename :='c:\ex1.xls';--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','false');

            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;

            --get_char_property for varchar

            --get_num_property for number

            name_var1 := ole2.get_char_property(cell,'value'); --cell value of the argument

            cols := cols+1;

             

             

            olearg;

            name_var2 := ole2.get_char_property(cell,'value'); --cell value of the argument

            cols := cols+1;

             

             

            olearg;

            name_var3 := ole2.get_char_property(cell,'value'); --cell value of the argument

            cols := cols+1;

             

             

            olearg;

            name_var4 := ole2.get_char_property(cell,'value'); --cell number value of the argument

             

             

            exit when name_var1 is null;--length(name_var1) = 0 or length(name_var1) is null;

            if ctr = 1 then

            :a:=name_var1;

            :b:=name_var2;

            :c:=name_var3;

            :d:=name_var4;

            null;

            else

            :a:=name_var1;

            :b:=name_var2;

            :c:=name_var3;

            :d:=name_var4;

            next_record;

            end if;

            synchronize;

            ctr := ctr + 1;

            cols := 1;

            end loop;

            delete_record;

            first_record;

            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;

            end;

             

            try this...

             

            Hope this will help.