Home » Developer & Programmer » Forms » Using Webutil to export data to Excel
Using Webutil to export data to Excel [message #236877] Fri, 11 May 2007 03:36 Go to next message
RosieSA
Messages: 1
Registered: May 2007
Location: South Africa
Junior Member
Hi All,

I'm trying to export data displayed in a form (Forms 10G) to an Excel spreadsheet. Firstly let me say that I am not a Forms developer, so have been thrown rather in the deep end here. I basically got some code from the Internet, that I copied and pasted into my WHEN_BUTTON_PRESSED trigger, but the code does not appear to do anything.

The code looks like this (I added the messages when attempting to debug and figure out what is going on):

MESSAGE('INSIDE WHEN BUTTON PRESSED TRIGGER');

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;
ARGS CLIENT_OLE2.LIST_TYPE;
CELL OLE2.OBJ_TYPE;
J INTEGER;
K INTEGER;
file_name_cl VARCHAR2(32767);
user_cancel EXCEPTION;


BEGIN
MESSAGE('AFTER DECLARATION');

file_name_cl := CLIENT_GET_FILE_NAME('C:\', 'file_name.xls', 'XLS Files (*.xls)|*.xls|', NULL, SAVE_FILE, TRUE);
file_name_cl := SUBSTR(file_name_cl,1,LENGTH(file_name_cl));
--
IF file_name_cl IS NULL THEN
MESSAGE('INSIDE EXCEPTION STATEMENT');
RAISE user_cancel;
END IF;

MESSAGE('STARTING TRIGGER');

APPLICATION := CLIENT_OLE2.CREATE_OBJ('Excel.Application');
CLIENT_OLE2.SET_PROPERTY(APPLICATION,'Visible',True);

MESSAGE('AFTER CREATING APPLICATION');

WORKBOOKS := CLIENT_OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
WORKBOOK := CLIENT_OLE2.INVOKE_OBJ(WORKBOOKS, 'ADD');
WORKSHEETS := CLIENT_OLE2.GET_OBJ_PROPERTY(WORKBOOK, 'WORKSHEETS');
WORKSHEET := CLIENT_OLE2.INVOKE_OBJ(WORKSHEETS, 'ADD');

MESSAGE('RIGHT BEFORE GO BLOCK STATEMENT');
GO_BLOCK('P10B1');
FIRST_RECORD;
J:=1;
K:=1;
WHILE :SYSTEM.LAST_RECORD = 'FALSE'
LOOP
FOR K IN 1..7 /* Form has 7 columns */
LOOP
MESSAGE('INSIDE LOOP');
If not name_in(:system.cursor_item) is NULL Then
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);
Client_OLE2.set_property(cell, 'Value', name_in(:system.cursor_item));
Client_OLE2.release_obj(cell);

End If;
NEXT_ITEM;
END LOOP;

J:=J+1;
NEXT_RECORD;
END LOOP;

/* For the last record */
for k in 1..7
loop
If not name_in(:system.cursor_item) is NULL Then
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);
Client_OLE2.set_property(cell, 'Value', name_in(:system.cursor_item));
Client_OLE2.release_obj(cell);
End If;
next_item;
end loop;
Client_OLE2.Release_Obj(worksheet);
Client_OLE2.Release_Obj(worksheets); */
/* Save the Excel file created */
args := Client_OLE2.Create_Arglist;
Client_OLE2.Add_Arg(args,'c:\temp\test.xls');
Client_OLE2.Invoke(workbook, 'SaveAs', args);
Client_OLE2.Destroy_Arglist(args);
/* release workbook */
Client_OLE2.Release_Obj(workbook);
Client_OLE2.Release_Obj(workbooks);
/* Release application */
Client_OLE2.Invoke(application, 'Quit');
Client_OLE2.Release_Obj(application);
MESSAGE('RIGHT BEFORE END');
END;

If I comment out everything except the messages, then all the messages display. As soon as I uncomment any code, I get the pop up to save the filename, but after that I only get the message "AFTER DECLARATION", and then nothing appears to happen further, and I can't exit the form.

I searched, and all the code I found was exactly in the same format as the code that I used, so now I am at a loss. I also saw that there were some previous posts on this forum regarding the issue, but no answers.

The system is a very old Forms 3 system that was upgraded last year from Forms 3 to Forms 10G.

I would really really appreciate any help.

Regards
Rosie
Re: Using Webutil to export data to Excel [message #237269 is a reply to message #236877] Mon, 14 May 2007 00:53 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you considered writing your file using 'utl_file' or the d2kwutil facilty 'client_text_io'.

Have a look at http://www.orafaq.com/forum/m/218872/67467/?srch=utl_file#msg_218872 or search this forum for 'utl_file' or 'client_text_io' and write the file in 'csv' format and then open it with Excel.

David

[Updated on: Mon, 14 May 2007 00:53]

Report message to a moderator

Re: Using Webutil to export data to Excel [message #237442 is a reply to message #236877] Mon, 14 May 2007 09:03 Go to previous message
gacki
Messages: 33
Registered: May 2006
Location: Dueren, NRW, Germany
Member
Rosi,

that code works like a charm at my site, so it must be something wrong with the WebUtil configuration at your site (CLIENT_OLE2 is WebUtil-specific). So you should try to get familiar with your development machine environment. Check out for the webutil files, it has a manual (pdf file), and try to check your configuration as described in the manual.

Gerald
Previous Topic: You can use it....
Next Topic: Forms Version
Goto Forum:
  


Current Time: Sat Dec 03 13:44:36 CST 2016

Total time taken to generate the page: 0.19171 seconds