Home » Developer & Programmer » Forms » Display Excel column names in a form List Item Elements
Display Excel column names in a form List Item Elements [message #272509] Fri, 05 October 2007 05:44 Go to next message
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;
Re: Display Excel column names in a form List Item Elements [message #272864 is a reply to message #272509] Mon, 08 October 2007 01:34 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Thanks. I'll add this thread to my 'Excel' list.

David
Previous Topic: UPDATE RECORD PROBLEM
Next Topic: Making use of password change function through forms
Goto Forum:
  


Current Time: Sat Dec 10 06:43:14 CST 2016

Total time taken to generate the page: 0.08744 seconds