Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 21 July 2014

Import Excel Data into Oracle using Oracle form Button

1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger.
BEGIN
IF :System.Cursor_Block<>'GL_DAILY_COMPOUND_HEADER' THEN
Go_Item('GL_DAILY_COMPOUND_HEADER.V_DATE');
END IF;
IF :System.Mode = 'NORMAL' AND :System.Record_Status IN ('NEW','INSERT') THEN IMPORT_EXCEL_PROC;   ---Form procedure
ELSE
 MESSAGE('Import allowed only for new entry!!!');
 MESSAGE('Import allowed only for new entry!!!');
END IF;
END;

2. Now write the procedure "IMPORT_EXCEL_PROC" into the program unit. Don't forget to create required table and folder for procedure IMPORT_EXCEL_PROC
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;

3 comments: