| Reg Exporting to Excel [message #182626] |
Mon, 17 July 2006 07:11  |
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
|
Senior Member |
|
|
Hi,
I am working in Oracle 10g Forms in a 3-tier architecture, with AS , Database Server and client.
Using Webutil i am successful in calling the Excel Application and further in the code i am trying to add worksheets and add the contents on the multi-record , multi-block form to the Excel sheet.
But only thing happening is the Excel Application is invoked , but no worksheets or workbooks are added and no data is going to the Excel sheet. Find the code below.
Kindly let me know what must have gone wrong with my code.
PROCEDURE EXPORT_2_EXCEL IS
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 CLIENT_OLE2.Obj_Type;
j INTEGER;
k INTEGER;
file_name_cl VARCHAR2(32767);
item_prompt VARCHAR2(32767);
user_cancel EXCEPTION;
item_name varchar2(250);
item_last varchar2(250);
item_count number := 0;
itm_type NUMBER;
fst_blkname varchar2(250);
last_blkname varchar2(250);
blkcount number :=0;
blk_row_count number;
fst_item varchar2(250);
row_sep number :=1 ;
BEGIN
file_name_cl := client_get_file_name('C:\','','|Excel Documents(*.xls)|*.xls|','Select output document...',SAVE_FILE,true);
file_name_cl := SUBSTR(file_name_cl,1,LENGTH(file_name_cl));
/*file_name_cl is not returning any value so the following lines raised a internal PLSQL Error since the Exception
user_cancel is not assigned to any error number ?
--IF file_name_cl IS NULL THEN
--RAISE user_cancel;
--END IF; */
application := CLIENT_OLE2.create_obj('Excel.Application');
CLIENT_OLE2.SET_PROPERTY(application,'Visible',1);
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');
/* Get the first and last block name */
fst_blkname := get_form_property(:system.current_form,FIRST_BLOCK);
last_blkname := get_form_property(:system.current_form,LAST_BLOCK);
/* Make last_blkname = NULL If both are same so that the loop executes once */
if fst_blkname = last_blkname then
last_blkname := 'NULL';
end if;
/* Loop through all the Blocks in the form */
GO_BLOCK(fst_blkname);
WHILE NOT fst_blkname=last_blkname LOOP
blk_row_count := 0;
item_count := 0;
fst_blkname := :system.current_block;
blk_row_count := get_block_property(fst_blkname,RECORDS_DISPLAYED);
/* Get the first and last items in the particular block */
item_name := get_block_property(fst_blkname,FIRST_ITEM);
fst_item := item_name;
item_last := get_block_property(fst_blkname,LAST_ITEM);
/* Loop through all the items in the block */
WHILE NOT item_name=item_last LOOP
/* Get item count */
item_count := item_count + 1;
next_item;
item_name := :system.current_item;
END LOOP;
if blk_row_count > 1 then
first_record;
end if;
GO_ITEM(fst_blkname ||'.'|| fst_item);
j:=row_sep+blkcount+1; /* Represents row number */
k:=1; /* Represemts column number */
/* Add the column headings using item prompts */
FOR k IN 1..item_count+1 /* Block has n visible columns */
LOOP
item_prompt := get_item_property(fst_blkname||'.'||:system.current_item, prompt_text);
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', item_prompt);
CLIENT_OLE2.release_obj(cell);
next_item;
END LOOP;
j:=j+1; /* Add to rowcount so that data won't overwrite column headings! */
LOOP
/* Add in all the data */
FOR k IN 1..item_count+1 /* Block has n visible columns */
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;
/* Check for last record */
if :system.last_record = 'TRUE' then
exit;
else
/* If Records_displayed is more than 1 then only move to the next record specifically for Master-detail blocks */
if blk_row_count > 1 then
next_record;
else
exit;
end if;
end if;
END LOOP;
/* Get block count */
blkcount := blkcount + 1;
if last_blkname <> 'NULL' then
next_block;
else
/* If only one block in the form */
last_blkname := fst_blkname;
end if;
/* Skip two lines before starting the Next Block */
row_sep := row_sep + 2;
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, file_name_cl);
CLIENT_OLE2.Invoke(workbook, 'Save', 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);
--EXCEPTION
--WHEN user_cancel THEN
--RAISE;
END;
Thanks and Regards,
Nirmal
|
|
|
|
|
|
| Re: Reg Exporting to Excel [message #182958 is a reply to message #182743] |
Tue, 18 July 2006 23:37  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
I suggest you find other people how have worked on similar problems and email or private mail them and ask for their assistance directly.
Can you use client_text_io to write a csv file which you then open as a 'dummy' spreadsheet and then update it. That might be easier than writing an excel spreadsheet from scratch.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
