How to: Read a range of excel by webutil? [message #553590] |
Mon, 07 May 2012 01:41 |
|
Hi All,
I want to read a range of excel by webutil in oracle form. I use command:
ole2_client.get_obj_property(worksheet, 'Range', args);
please help me!!!
I want to know a list property of an OLE2 Automation Object???
|
|
|
|
Re: How to: Read a range of excel by webutil? [message #553616 is a reply to message #553598] |
Mon, 07 May 2012 05:04 |
owais_baba
Messages: 289 Registered: March 2008 Location: MUSCAT
|
Senior Member |
|
|
First u neen to configure webutil for opening Dialogue Box
http://www.baigzeeshan.com/2010/01/open-file-dailog-box-example-using.html
Try this friend
declare
args client_ole2.list_type;
application client_ole2.obj_type;
vworkbooks client_ole2.obj_type;
vdoc client_ole2.obj_type;
vworksheet client_ole2.obj_type;
vrange client_ole2.obj_type;
begin
application := client_ole2.create_obj('Excel.Application');
client_OLE2.SET_PROPERTY(application, 'Visible','True');
vworkbooks := client_ole2.get_obj_property(application, 'Workbooks');
args := client_ole2.create_arglist;
client_ole2.ADD_ARG(args, 'c:\baba.xls');
vdoc :=client_ole2.INVOKE_OBJ(vworkbooks,'Open',args);
client_ole2.destroy_arglist(args);
args := client_ole2.create_arglist;
client_ole2.ADD_ARG(args, 1);
vworksheet := client_ole2.get_obj_property(vdoc,'Worksheets',args);
client_ole2.destroy_arglist(args);
args := client_ole2.create_arglist;
client_ole2.ADD_ARG(args, 'B6');
vrange := client_ole2.get_obj_property(vworksheet,'Range',args);
client_ole2.destroy_arglist(args);
message(client_ole2.get_char_property(vrange,'Value'));
client_ole2.release_obj(vrange);
client_ole2.release_obj(vworksheet);
client_ole2.release_obj(vdoc);
client_ole2.release_obj(vworkbooks);
client_ole2.release_obj(application);
end;
--------------------------------webutil---------------------------------------
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;
worksheet2 Client_OLE2.Obj_Type;
cell Client_OLE2.OBJ_TYPE;
args Client_OLE2.OBJ_TYPE;
cell_value varchar2(100);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
BEGIN
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);
IF ( v_fName IS NOT NULL ) THEN
application :=
Client_OLE2.create_obj('Excel.Application');
Client_OLE2.set_property(application,'Visible','false');
workbooks :=
Client_OLE2.Get_Obj_Property(application, 'Workbooks');
args := Client_OLE2.CREATE_ARGLIST;
Client_OLE2.add_arg(args,v_fName);
workbook :=
Client_OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
Client_OLE2.destroy_arglist(args);
worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
num_wrkshts:= Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet :=
Client_OLE2.GET_OBJ_PROPERTY(
application,'activesheet');
go_block('baba');
first_record;
loop
If :system.record_status <> 'NEW' then
create_record;
end if;
exit when eod;
for k in 1..3 loop
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);
cell_value :=Client_OLE2.get_char_property(cell, 'Value');
if upper(cell_value) = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;
copy(cell_value,name_in('system.cursor_item'));
next_item;
end loop;
j:=j+1;
end loop;
IF (cell IS NOT NULL) THEN
Client_OLE2.release_obj(cell);
END IF;
IF (worksheet IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet);
END IF;
IF (worksheets IS NOT NULL) THEN
Client_OLE2.release_obj(worksheets);
END IF;
IF (worksheet2 IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet2);
END IF;
IF (workbook IS NOT NULL) THEN
Client_OLE2.release_obj(workbook);
END IF;
IF (workbooks IS NOT NULL) THEN
Client_OLE2.release_obj(workbooks);
END IF;
Client_OLE2.invoke(application,'Quit');
Client_OLE2.release_obj(application);
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
END;
regards
baba
[Updated on: Mon, 07 May 2012 05:30] Report message to a moderator
|
|
|
|