| Display Excel column names in a form List Item Elements [message #272509] |
Fri, 05 October 2007 05:44  |
anisam10674
Messages: 18
Registered: March 2007
|
Junior Member |
|
|
We could finally display excel column names from a user specified Excel filename and worksheet name, Courtesy: some very useful information from this forum and other sites....
Following is the code which I would like to share with you....
--Procedure to get the first row data from passed-on parameters
--Any suggestions/improvements regarding the code will be very helpful
procedure insert_rg_xlcols
(
in_xlname varchar2, --Excel filename along with its path passed by calling event_trigger/procedure/function
in_wsname varchar2, --Excel worksheet name within <in_xlname> passed by calling event_trigger/procedure/function
in_lename varchar2 --List Item name whose list elements will display column names from <in_wsname>
) is
--Declare handles to ole2 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;
args ole2.obj_type;
--Variables used to fetch cell values from the excel worksheet
v_RowNum integer;
v_ColNum integer;
v_Buffer varchar2(10000);
--List Item Element Row count
v_LERowCnt integer;
--Variables used to manipulate record group created at runtime
v_rgid recordgroup;
v_gc1 groupcolumn;
v_gc2 groupcolumn;
v_RGRowCnt integer;
v_RGRowNum integer;
file_not_found exception;
pragma exception_init(file_not_found,-302000);
begin
------------------------- get excel column names into Record Group (rg1) ----------------------------------------
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,in_xlname);
workbook := ole2.get_obj_property(workbooks,'Open',args);
ole2.destroy_arglist(args);
worksheets := ole2.get_obj_property(workbook,'Worksheets');
args := ole2.create_arglist;
ole2.add_arg(args,in_wsname);
worksheet := ole2.get_obj_property(Workbook,'Worksheets',args);
ole2.destroy_arglist(args);
v_rgid := find_group('rg1');
if not id_null(v_rgid) then
delete_group(v_rgid);
end if;
v_rgid := create_group('rg1');
v_gc1 := add_group_column(v_rgid,'gc1',char_column,2000);
v_gc2 := add_group_column(v_rgid,'gc2',number_column);
v_RowNum := 1;
v_ColNum := 1;
V_RGRowNum := 1;
loop
args := ole2.create_arglist;
ole2.add_arg(args,v_RowNum);
ole2.add_arg(args,v_ColNum);
cell := ole2.get_obj_property(worksheet,'Cells',args);
ole2.destroy_arglist(args);
v_buffer := ole2.get_char_property(cell,'Value');
if v_buffer is null then
exit;
end if;
add_group_row(v_rgid,end_of_group);
set_group_char_cell(v_gc1,v_RGRowNum,v_buffer);
set_group_number_cell(v_gc2,v_RGRowNum,v_ColNum);
v_ColNum := v_ColNum+1;
v_RGRowNum := v_RGRowNum+1;
end loop;
ole2.invoke(worksheet,'Close');
ole2.invoke(workbook,'Close');
ole2.invoke(application,'Quit');
ole2.release_obj(worksheet);
ole2.release_obj(worksheets);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
------------------------- Get Record Group (rg1) values into List Element values -------------------------------------
v_LERowCnt := get_list_element_count(in_lename);
if v_LERowCnt>0 then
for i in 1..v_LERowCnt loop
delete_list_element(in_lename,1);
end loop;
end if;
v_RGRowCnt := get_group_row_count(v_rgid);
for i in reverse 1..v_RGRowCnt loop
add_list_element(in_lename,1,get_group_char_cell(v_gc1,i),get_group_number_cell(v_gc2,i));
end loop;
delete_group(v_rgid);
exception
when file_not_found then
message('File Not Found');
end;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
