|
|
 Re: how to implement the ole2 object in report 6i [message #303596 is a reply to message #303589] |
Sat, 01 March 2008 00:13   |
doss_arun
Messages: 10
Registered: February 2008
Location: iselin, new jersey
|
Junior Member |
|
|
hi,
check with below code and modify as per your requirement:-
-- Procedure to get the worksheets name of an excel file and display the names in a list item:
-- v_filename is the excel file name specified by user
-- v_blkitemname is the list item name which will display the names of the excel worksheets present in v_filename
procedure get_xl_ws_names(v_filename in varchar2, v_blkitemname in varchar2) is
--declare handles to ole object
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
--declare handles to ole argument lists
args ole2.list_type;
check_file text_io.file_type;
file_not_found exception;
pragma exception_init(file_not_found,-302000);
--user-defined varibles
v_ws_cnt integer;
v_ws_nam varchar2(100);
v_le_cnt integer;
begin
--delete existing elements from list item
v_le_cnt := get_list_element_count(v_blkitemname);
if v_le_cnt>=1 then
for i in 1..v_le_cnt loop
delete_list_element(v_blkitemname,1);
end loop;
end if;
--check whether file exists, else raise file_not_found exception
check_file := text_io.fopen(v_filename,'R');
text_io.fclose(check_file);
--open excel application
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,v_filename);
workbook := ole2.get_obj_property(workbooks,'Open',args);
ole2.destroy_arglist(args);
worksheets := ole2.get_obj_property(workbook,'Worksheets');
--get worksheet count into variable v_ws_cnt
v_ws_cnt := ole2.get_num_property(worksheets,'Count');
--get worksheets name and add it into list element of list item
for i in 1..v_ws_cnt loop
args := ole2.create_arglist;
ole2.add_arg(args,i);
worksheet:=ole2.get_obj_property(worksheets,'Item',args);
v_ws_nam := ole2.get_char_property(worksheet,'Name');
ole2.destroy_arglist(args);
ole2.release_obj(worksheet);
add_list_element(v_blkitemname,1,v_ws_nam, v_ws_nam);
end loop;
--close excel worksheet/workbook/application
ole2.invoke(worksheet,'Close');
ole2.invoke(workbook,'Close');
ole2.invoke(application,'Quit');
--release ole handles
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
exception
when file_not_found then
message('File not found');
end;
by:->
arun
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
