Re: Exporting Forms 4.5 data to Excel or other.

From: Eric Givler <egivler_at_flash.net>
Date: Mon, 11 Sep 2000 16:00:48 GMT
Message-ID: <Qk7v5.4309$oc3.206340_at_news.flash.net>

This is something I JUST put into a form as a user requested it. Things that are useful to know:

1. See \%oracle_root%\forms45\demos\oleexcel
2. The code in the excel.fmb was written for the EXCEL 7.0 object model, NOT for the EXCEL in Office97, so you need to make a few changes to get it working. There are Metalink articles that describe this process.

In short, here's the ACTUAL working code that exports data from a block on my form to an Excel Spreadsheet. The only problem I've run into is NAMING the file in the case of an "overwrite" action. IF the user Cancels the overwrite, then an EXCEL.EXE process is still running. I attempted to work around this for now, by appending the date and timestamp to the filename, which works well for me because they can't export all the data under 1 second. Here's the code:

PROCEDURE export_transactions_to_excel IS

• Declare the ole objects application ole2.obj_type; workbooks ole2.obj_type; workbook ole2.obj_type; worksheets ole2.obj_Type; worksheet ole2.obj_type; cell ole2.obj_type;
• my variables rowCounter Number := 1; local_cursor_Record number := :System.Cursor_Record; old_cursor_Style varchar2(100);

   errors_occurred     boolean := false;
   Ole_Error           Exception;

   pragma exception_init( Ole_Error, -305500 );

procedure place_value_in_cell( rownum_in in number

                             , colnum_in in number
                             , value_in  in varchar2 )

is

• Declare handles to OLE argument lists args ole2.list_Type; begin args := ole2.create_arglist; ole2.add_arg(args, rownum_in); /* row number */ ole2.add_arg(args, colnum_in); /* column number */
  • the next line is for excel97
  • cell := ole2.invoke_obj( worksheet, 'Cells', args ); cell := ole2.get_obj_property( worksheet, 'Cells', args ); ole2.Destroy_arglist( args ); ole2.set_property( cell, 'Value', value_in ); ole2.release_obj (cell); end place_value_in_cell;

procedure SaveSpreadsheet
is

   args ole2.List_Type;
   vDateStamp varchar2(20);
begin

   vDateStamp := to_char(sysdate,'mmddyyyy') || '_' || to_char(sysdate,'hh24miss');

   args := OLE2.CREATE_ARGLIST;

   OLE2.ADD_ARG(args, 'C:\DATAFILE_' || vDateStamp || '.XLS');
   OLE2.INVOKE(worksheet, 'SaveAs', args);
   OLE2.DESTROY_ARGLIST(args);

   --args := Ole2.Create_ARgList;
   ole2.invoke( application, 'Quit' );
end SaveSpreadSheet;

PROCEDURE Export_The_Data IS
BEGIN
   old_Cursor_style := get_application_property( CURSOR_STYLE );    set_application_property( CURSOR_STYLE, 'BUSY' );

   application := ole2.create_obj('Excel.Application');    --ole2.set_property( application, 'Visible', 'True' );

• Return object handle to the Workbooks collection workbooks := ole2.get_obj_property( application, 'Workbooks' ); -- invoke_obj w/ excel 97
• Add a new Workbook object to the Workbooks collection workbook := ole2.invoke_obj( workbooks, 'Add' );
• return object handle to the Worksheets collection for the
• Workbook worksheets := ole2.get_obj_property( workbook, 'Worksheets' ); -- invoke_obj w/ Excel97
• Add a new Worksheet to the Worksheets collection worksheet := ole2.invoke_obj( worksheets, 'Add');
• Return object handle to cell A1 on the Worksheet Go_block('Transactions'); First_Record; loop place_value_in_cell( rowCounter, 1, :TRANSACTIONS.NDB_PAYEE_NAME ); place_value_in_cell( rowCounter, 2, nvl(:TRANSACTIONS.NDB_PAYEE_ADDRESS,'unknown') ); place_value_in_cell( rowCounter, 3, nvl(:TRANSACTIONS.NDB_SSN,'unknown') ); place_value_in_cell( rowCounter, 4, :transactions.NDB_DESCRIPTION ); place_value_in_cell( rowCounter, 5, to_char(:Transactions.transaction_group_EID) ); place_Value_in_cell( rowCounter, 6, to_char(:transactions.pretax_amount) ); place_value_in_Cell( rowCounter, 7, to_char(:transactions.ndb_tax_amount) ); place_value_in_cell( rowCounter, 8, to_char(:transactions.amount_including_taxes) );

       exit when :SYSTEM.LAST_RECORD = 'TRUE';
       NEXT_RECORD;
       rowCounter := rowCounter + 1;

   end loop;

END Export_The_Data;

/*  ----------------------------------- main
procedure --------------------------------------*/

BEGIN    Begin

      Export_The_Data;
      exception
      when Ole_Error then
         AlertSend( 'There was an error exporting the data to Microsoft
Excel (receipt# ' ||
                    to_Char(:transactions.transaction_Group_eid) || ')',
false );
         errors_occurred := TRUE;

      when others then
         AlertSend( SQLERRM, false );
         errors_occurred := TRUE;

   End;

   /*

      Ideally, I'd like to call SaveSpreadsheet in Export_The_Data, and just ExitExcel here,

      but it prompts for whether to save the changes and I don't know how to get around that

      right now. SO, we just save and exit, regardless.    */
   SaveSpreadSheet;

• Release the OLE objects ole2.release_obj (worksheet); ole2.release_obj (worksheets); ole2.release_obj (workbook); ole2.release_obj (workbooks); ole2.release_obj (application);

   IF NOT errors_occurred then -- stay on the record that caused the error

      Go_Record( local_cursor_Record );
   End If;
   Set_Application_Property ( CURSOR_STYLE, old_cursor_style );

END export_transactions_to_excel;

Corto Maltese <corto_at_castel.nl> wrote in message news:8pa2rh$qek$1_at_nnrp1.deja.com...
> Hi all,

>

> I need to export the data on a forms 4.5 screen to, preferred Excel, or
> a text file.
> The data on the Forms screen is provided trough a specific query on the
> screen (F7, specific identifier, F8) and want to have it exported to
> Excel by pressing a button.

>

> Is there any possibility to achieve this????, what do I need ? (to do)

>

> Many regards,

>

> CM.

>
>

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Sep 11 2000 - 18:00:48 CEST