I am trying to upload excel file through Oracle forms 6i. I have checked online for this code and it solved my purpose somehow.
The only problem I am facing is I am not getting cell value as EOD at the end of document.
Just have a look at following coding. When I am uploading excel through this,its going into infinete loop.But when I am hardcoding EOD at the end of the document,its working fine
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;
cell OLE2.OBJ_TYPE;
args 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
-- Get the name of the file to open
--Use a File Open Dialog to let the user select the file.
v_fName := GET_FILE_NAME('d:\', 'file_name.xlsx', 'xlsx Files (*.xlsx)|*.xlsx|', NULL, OPEN_FILE, TRUE);
--IN_FILE:=Text_IO.Fopen(flnm, 'W');
-- Make sure the user selected a file
IF ( v_fName IS NOT NULL ) THEN
-- The following sets up communication with the excel spreadsheet
-- --------------------------------------------------------------
-- Open the OLE application
application := OLE2.create_obj('Excel.Application');
-- Keep the application hidden
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('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..6 loop --3 fields per record
-- You have to know fields there are
args:= OLE2.create_arglist;
OLE2.add_arg(args, j);
OLE2.add_arg(args, k);
cell:= OLE2.get_obj_property(worksheet, 'Cells', args);
OLE2.destroy_arglist(args);
cell_value :=OLE2.get_char_property(cell, 'Value');
-- Check for End of Data…
if upper(cell_value) = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;
copy(cell_value,name_in('SYSTEM.CURSOR_ITEM'));
IF (cell IS NOT NULL) THEN
OLE2.release_obj(cell);
END IF;
next_item;
end loop; --for
j:=j+1;
end loop; --main loop
-- Release the OLE2 object handles
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;