Import Excel Data into Oracle using Oracle form Button

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;