Home » Developer & Programmer » Forms » Dynamic Control Of rows in a excel to oracle forms importing form (win 10)
icon1.gif  Dynamic Control Of rows in a excel to oracle forms importing form [message #674387] Thu, 24 January 2019 06:03 Go to next message
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;
Re: Dynamic Control Of rows in a excel to oracle forms importing form [message #674390 is a reply to message #674387] Thu, 24 January 2019 10:06 Go to previous message
Michel Cadot
Messages: 66928
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals, as solution depends on it.

Previous Topic: LOV again change value on same row in oracle form
Next Topic: Re validating LOV in Oracle forms
Goto Forum:
  


Current Time: Sun Mar 29 01:11:29 CDT 2020