I want to import data from excel file into oracle database 11 g using forms 10 g. For this I googled many times and got the solution but this solution is not working in my application.
The solution is given below. Kindly give me the right solution. Thanks in advance for help.
PROCEDURE IMPORT_EXCEL_PROC IS
application OLE2.Obj_Type;
workbooks OLE2.Obj_Type;
workbook OLE2.Obj_Type;
worksheets OLE2.Obj_Type;
worksheet OLE2.Obj_Type;
cell OLE2.OBJ_TYPE;
args OLE2.OBJ_TYPE;
cell_value varchar2(100);
cell_value_num NUMBER;
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 2;
v_fName VARCHAR2(450);
BEGIN
v_fName := GET_FILE_NAME(directory_name => 'C:\IMPORT\',File_Filter => 'Excel Files (*.XLS)|*.XLS|',message => 'Select Client filename to Open.');
IF ( v_fName IS NOT NULL ) THEN -- Make sure the user selected a file
The following sets up communication with the excel spreadsheet:
application := OLE2.create_obj('Excel.Application'); -- Open the OLE application
OLE2.set_property(application,'Visible','false'); -- Keep the application hidden
OLE2.set_property(application,'DisplayAlerts','false');
workbooks := OLE2.Get_Obj_Property(application, 'Workbooks');
args := OLE2.CREATE_ARGLIST;
OLE2.add_arg(args,v_fName);
workbook := OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args); --Open the selected File
OLE2.destroy_arglist(args);
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
num_wrkshts := OLE2.GET_NUM_PROPERTY(worksheets, 'Count'); -- Get number of worksheets
worksheet := OLE2.GET_OBJ_PROPERTY(application,'activesheet');
Go_Item('GL_DAILY_COMPOUND.BRANCH'); --Go to the last record
last_record;
loop -- Loop through the Block and create a new row if needed.
exit when eod; -- Exit when the last row of the spreadsheet is reached.
for k in 1..7 loop --7 fields per record -- Loop through the spreadsheet and get cell values
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);
IF K=1 THEN
Cell_value_num :=OLE2.get_num_property(cell, 'Value');
if cell_value_num=0 or cell_value_num is null then --Check for End of Data…
eod:=true;
IF J>1 THEN
Message(j-1||' Records are imported successfully.');
Message(j-1||' Records are imported successfully.');
INSERT INTO GL_COMPOUND_HEADER_EXCEL(EXL_DATE, EXL_USER, EXL_REMARK)
VALUES(SYSDATE, :GLOBAL.USER,'Records:'||TO_CHAR(j-1)||' File:'||v_fName);
end if;
Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE');
exit;
end if;
If :system.record_status <> 'NEW' then
create_record;
end if;
copy(cell_value_num,name_in('system.cursor_item'));
else
IF K =6 THEN
cell_value :=OLE2.get_char_property(cell, 'Value');
copy(cell_value,name_in('system.cursor_item'));
else
cell_value_num :=OLE2.get_num_property(cell, 'Value');
copy(ROUND(cell_value_num,2),name_in('system.cursor_item'));
end if;
end if;
next_item;
end loop; --for
j:=j+1;
end loop; --main loop
IF (cell IS NOT NULL) THEN -- Release the OLE2 object handles
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 (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');
-- IF (args IS NOT NULL) THEN
-- OLE2.release_obj(args);
-- END IF;
OLE2.release_obj(application);
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
END;