WebUtil: How to Read an Excel file into an Oracle Form

Description of included files (listed below):
Read Excel to Forms.dox – This document in Microsoft Word 2003 format
create_planets.sql – script to create the table used by this sample code
planets_ole_excel_read.fmb – sample form which demonstrates the concept
planets.xls – Excel (version 2003) sample spreadsheet

Setup steps:
Log into your database and run the “create_planets.sql” script
Ensure your environment is properly configured to run WebUtil. This document does not cover this configuration – there is plenty of information available in the OTN forums as well on the Internet that covers configuring WebUtil so I will not included it here.
Place “planets.xls’ some place on the client computer. I have modified Oracle’s original form to display a “File Select dialog” rather than hard code the file name and location in the code as Oracle demo did.
Ensure you have configured the Forms Builder to allow running a form from the Forms Builder. This document does not cover how to perform this configuration.
Run the form from the Forms Builder or on the client computer and push the “Read from Excel” button. Choose the ‘planets.xls’ file from the “Select Client filename to Open” dialog and the data will be read from the Excel spreadsheet into the form. You can then push the save button in the toolbar to commit the data to the table.
Here is the code behind the “Read from Excel” button with some explanatory comments:
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
-- Get the name of the file to open
--v_fName :=
'D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls';
-- My Way: Use a File Open Dialog to let the user select the file.
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);

-- 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 := Client_OLE2.create_obj('Excel.Application');
-- Keep the application hidden
Client_OLE2.set_property(application,'Visible','false');

workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');
args := Client_OLE2.CREATE_ARGLIST;

-- Open the selected File
-- ----------------------
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');

-- Get number of worksheets
-- ------------------------
num_wrkshts := Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := Client_OLE2.GET_OBJ_PROPERTY(
application,'activesheet');

--Go to the first record
go_block('planets');
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..3 loop --3 fields per record
-- You have to know fields there are
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');

-- Check for End of Data…
if upper(cell_value) = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;

-- Copy the value from Excel to the Forms block item
-- This is how the Oracle example copied values
/*if k =1 then
:dept.deptno:=cell_value;
end if;

if k =2 then
:dept.dname:=cell_value;
end if;

if k =3 then
:dept.loc:=cell_value;
end if;
*/

-- This is my way; which is more efficient and less code
copy(cell_value,name_in('system.cursor_item'));
next_item;

end loop; --for

j:=j+1;
end loop; --main loop

-- Release the Client_OLE2 object handles
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;