Hi all,
I am importing data from an excel file into oracle database. I wanted to know the no of records in an excel file and no of columns while using oracle webutil (Client_ole2). I got a code from web which works fine , when appending a EOD statement at the end of file each time. This may not be practical always , cannot ask user each time to append an EOD statement, which in turn cause program to execute infinitely.
This is the code which am running
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:=3;
v_fName VARCHAR2(250);
worksheet_count number := 0;
BEGIN
-- Get the name of the file to open
--v_fName := 'D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls';
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
--,file_name => Get_Form_Property(:System.Current_form,Form_Name)||'.xls'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);
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('EXCEL_EXPORT_TABLE');
first_record;
loop
If :system.record_status <> 'NEW' then
create_record;
end if;
exit when eod;
for k in 1..15 loop --15 fields per record ----hardcoding the column length
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);
if Client_OLE2.get_char_property(cell, 'Value') = 'null' then
cell_value := '';
else
cell_value :=nvl(Client_OLE2.get_char_property(cell, 'Value'),0);
end if;
if upper(cell_value) = 'EOD' then ----Loop executes until 'EOD' statement is found.
eod:=true;
Message('End of Data');
exit;
end if;
copy(cell_value,name_in('system.cursor_item'));
next_item;
end loop; --for
j:=j+1;
end loop;--main loop
first_record;
-- 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;
Please suggest there is any built in to find no of records and columns in excel file using oracle forms.
Thanks & Regards,
JaKes