Re: OLE2- Package experience

From: Mike <mboduch_at_interaccess.com>
Date: 1996/04/06
Message-ID: <4k6ivn$fdg_at_nntp.interaccess.com>

Hope this helps:

Mike

procedure to_excel (p_block_name varchar2) is

  /*
  ** This procedure will send the contents of an Oracle Forms block
  ** to Microsoft Excel 5 and then prompt the User to specify a SaveAs
  ** Filename.  Once saved, Excel is closed and the user returns to
  ** the calling form.

  */

  /*
  ** Declare handles to the Excel Application, Workbook, Worksheet,
  ** and Cell Range
  */
  app     ole2.obj_type;
  book    ole2.obj_type;

  sheet ole2.obj_type;
  range ole2.obj_type;
  /*

• Declare an argument list. */ args ole2.list_type; /*
• Launch Excel and obtain object handles. */ procedure invoke_excel is begin
  • Open Excel in the background and obtain a handle to the
  • default Worksheet that is created.
    --
    sheet := ole2.create_obj('excel.sheet.5');
    --
    
  • Worksheets are owned by Workbooks. Get a handle to the
  • Workbook that owns the default Worksheet.
    --
    book := ole2.get_obj_property(sheet,'Parent');
    --
    
  • Workbooks are owned by the Excel Application. Get a
  • handle to the Application.
    --
    app := ole2.get_obj_property(book,'Parent');
    --
    
  • Now we can make the Excel Application visible to the user
  • by setting the appropriate Application-level property.
  • Please note that this is not required. Visible or not,
  • Excel is currently running.
    --
    ole2.set_property(app,'Visible','True'); end invoke_excel; /*
• Loop through the records and items of the source block sending the
• value of each item to Excel */ procedure populate_worksheet is first_itm varchar2(30) := get_block_property(p_block_name,first_item); curr_itm varchar2(30) := first_itm; row_num number := 0; row_str varchar2(255); col_num number := 0; /*
  • Transfer a value to a specified Excel cell. */ procedure send_cell (p_rownum number ,p_colnum number ,p_cellval varchar2) is begin
    • Place the cell's row and column numbers into a parameter list -- args := ole2.create_arglist; ole2.add_arg(args,p_rownum); ole2.add_arg(args,p_colnum); --
    • Invoke a Worksheet Method to obtain a handle to the cell Range -- range := ole2.invoke_obj(sheet,'cells',args); --
    • Discard the parameter list -- ole2.destroy_arglist(args); --
    • Set the "Value" property of the cell Range object. -- ole2.set_property(range,'value',p_cellval); --
    • Release the Range object handle. -- ole2.release_obj(range); end send_cell; /*
  • Use the Excel "Text to columns" method to parse a column. */ procedure parse_column (p_column_letter varchar2) is begin
    • Add the spreadsheet column letter to a parameter list -- args := ole2.create_arglist; ole2.add_arg(args,upper(p_column_letter)); --
    • Get a handle to the range that represents the column -- range := ole2.invoke_obj(sheet,'Columns',args); ole2.destroy_arglist(args); --
    • Invoke the Range object's "TextToColumns" method to parse. -- ole2.invoke(range,'TextToColumns'); ole2.release_obj(range); end parse_column; /*
  • Size the columns. */ procedure autofit_columns is begin
    • Get a handle to the range that represents the used columns -- range := ole2.get_obj_property(sheet,'UsedRange'); --
    • Get a handle to a range of *just* columns. -- range := ole2.invoke_obj(range,'Columns'); --
    • Invoke the Range object's "AutoFit" method to Size. -- ole2.invoke(range,'AutoFit'); ole2.release_obj(range); end autofit_columns; begin
      • Send the block item names as column headings.
        --
        while curr_itm is not null loop if get_item_property(p_block_name||'.'||curr_itm,displayed) = 'TRUE' and get_item_property(p_block_name||'.'||curr_itm,width) != 0 then col_num := col_num + 1; -- if col_num = 1 then row_str := curr_itm; else row_str := row_str||chr(9)||curr_itm; end if; end if; -- curr_itm := get_item_property(p_block_name||'.'||curr_itm,nextitem); end loop;
        --
        send_cell(1,1,row_str); go_record(1);
        --
        
      • Now send each the values in each row of the block
        --
        loop row_num := to_number(name_in('system.cursor_record')) + 1; row_str := null; col_num := 0; curr_itm := first_itm; -- while curr_itm is not null loop if get_item_property(p_block_name||'.'||curr_itm,displayed) = 'TRUE' and get_item_property(p_block_name||'.'||curr_itm,width) != 0 then col_num := col_num + 1; -- if col_num = 1 then row_str := nvl(name_in(p_block_name||'.'||curr_itm),' '); else row_str := row_str ||chr(9) ||nvl(name_in(p_block_name||'.'||curr_itm),' '); end if; end if; -- curr_itm := get_item_property(p_block_name||'.'||curr_itm,nextitem); end loop; -- send_cell(row_num,1,row_str); exit when name_in('system.last_record') = 'TRUE'; next_record; end loop;
        --
        
      • Parse Column "A" of the spreadsheet.
        --
        parse_column('A'); autofit_columns; end populate_worksheet; -- /*
• Have Excel present the user with the FileSaveAs dialog box and
• then save the current Workbook. */ procedure save_workbook is sname varchar2(50); begin
  • Set the default file path Property of the Excel Application object.
    --
    ole2.set_property(app,'DefaultFilePath','e:\apps\prod\mrs\exe');
    --
    
  • Ask Excel to display the SaveAs dialog box. Pass parameters to
  • the Method to default the filename and filetype. Store the
  • filename returned by the Method invokation for later use.
    --
    args := ole2.create_arglist; ole2.add_arg(args,'mrs.xls'); ole2.add_arg(args,'Microsoft Excel Workbooks (*.xls), *.xls'); sname := ole2.invoke_char(app,'GetSaveAsFilename',args); ole2.destroy_arglist(args);
    --
    
  • Now invoke a Worksheet Method to save the worksheet using the
  • filename obtained above.
    --
    args := ole2.create_arglist; ole2.add_arg(args,sname); ole2.invoke(sheet,'SaveAs',args); ole2.destroy_arglist(args);
    --
    
  • Finally, now that the worksheet has been saved, ask the Excel
  • Application object to hide itself.
    --
    ole2.set_property(app,'Visible','False'); end save_workbook; -- /*
• Release all handles to the Excel Application and Objects */ procedure release_excel is begin ole2.release_obj(sheet); ole2.release_obj(book); ole2.release_obj(app); end release_excel; begin invoke_excel; populate_worksheet; save_workbook; release_excel; end to_excel;

Lang Richard <richard_at_dse.it> wrote:

>Who has some experience in using OLE2-package and "Excel.Application.5".
>(How to get Objects, invoke methods and edit propertys...)
>-- 
>Lang Richard                 EMAIL: Richard.Lang_at_dse.it
>DSE	                      or  : richard_at_calico.dse.it
>39100 Bozen
>Galvanistr 33                   Tel.: 0039 471 555 711
>(BZ) ITALY
 
>Privat:--->          Lang Richard
>================     39019 Dorf Tirol
>                     Ruprechtweg. 4
>                     Tel.: +39 473 565 149
>                     (BZ) ITALY

Received on Sat Apr 06 1996 - 00:00:00 CEST