 Dynamic Control Of rows in a excel to oracle forms importing form [message #674387] |
Thu, 24 January 2019 06:03  |
 |
sdhz_dev
Messages: 1
Registered: January 2019
|
Junior Member |
|
|
I have created the following .fmb file it is able to take a .xls file and send it on oracle forms but the problem is that i am not able to dynamically select the no of rows of a excel file,for my current application i have to write a "EOD" keyword at the end of excel file so that it fetches only the amount of data up till the "EOD" keyword
the code is as follows:
DECLARE
application OLE2.Obj_Type;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;
worksheets OLE2.Obj_Type;
worksheet OLE2.Obj_Type;
worksheet2 OLE2.Obj_Type;
c_arglist OLE2.list_type;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
cell_value varchar2(500);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
v_path varchar2(1000);
only_filepath varchar2(500);
filename_with_ext varchar2(500);
typ varchar2(500);
BEGIN
begin
v_path := get_file_name(file_filter=>'XLS Files (*.xls)|*.xls|');
:PATH:= v_path;
exception
when others then null;
end;
v_fName := :PATH;
IF ( v_fName IS NOT NULL ) THEN
application := OLE2.create_obj('Excel.Application');
OLE2.set_property(application,'Visible','false');
workbooks := OLE2.Get_Obj_Property(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
-- Open the selected File
-- ----------------------
OLE2.add_arg(args,v_fName);
workbook := OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
OLE2.destroy_arglist(args);
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
-- Get number of worksheets
num_wrkshts := OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');
--Go to the first record
go_block('MASTER_DETAIL');
first_record;
-- Loop through the Block and create a new row if needed.
Loop
If :system.record_status <> 'NEW' then
create_record;
end if;
-- Exit when the last row of the spreadsheet is reached.
exit when eod;
-- Loop through the spreadsheet and get cell values
for k in 1.. :COLS loop --Dynamic fetch of column
-- You have to know fields there are
args := OLE2.CREATE_ARGLIST;
OLE2.add_arg(args, j);-- =1
OLE2.add_arg(args, k);
cell:= OLE2.get_obj_property(worksheet, 'Cells', args);
OLE2.destroy_arglist(args);
cell_value :=OLE2.get_num_property(cell, 'Value');
--coversion of number data
If (cell_value) = 0 Then
cell_value :=OLE2.get_char_property(cell, 'Value');
end if;
-- Check for End of Data
if upper(cell_value) = 'EOD' then -- insted of this keyword i want my application to see no of rows dynamically
eod:=true;
Message('End of Data');
Message(' ');
exit ;
end if;
copy(cell_value,name_in('system.cursor_item'));
next_item;
end loop; --for
j := j+1;
end loop; --main loop
IF (cell IS NOT NULL) THEN
OLE2.release_obj(cell);
END IF;
IF (worksheet IS NOT NULL) THEN
OLE2.release_obj(worksheet);
END IF;
IF (worksheets IS NOT NULL) THEN
OLE2.release_obj(worksheets);
END IF;
IF (worksheet2 IS NOT NULL) THEN
OLE2.release_obj(worksheet2);
END IF;
IF (workbook IS NOT NULL) THEN
OLE2.release_obj(workbook);
END IF;
IF (workbooks IS NOT NULL) THEN
OLE2.release_obj(workbooks);
END IF;
OLE2.invoke(application,'Quit');
OLE2.release_obj(application);
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
END;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
