reading Excel file using PL/SQL

A good way to read/write/control Miscrosoft office apps is through the OLE system.
You can create a PLL that can be attached to any form that you need to work with Excel.

The PLL should consist of a pl/sql package:

The package spec:

PACKAGE excel IS
 -- global variables (constants)
  application      ole2.obj_type;
  workbooks       ole2.obj_type;
  workbook            ole2.obj_type;
  worksheets       ole2.obj_type;
  worksheet      ole2.obj_type;

  procedure open_excel;
  procedure close_excel;
  procedure read_num_cell( p_row number, p_col number, p_value in out number );
  procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 );
  procedure open_workbook;
  procedure close_workbook;
  procedure open_excel_doc( p_pateka varchar2 );
  procedure close_excel_doc;
  procedure activate_sheet( p_redosled integer );
end;

PACKAGE BODY excel IS

  procedure open_excel is
  begin
            application := ole2.create_obj('Excel.Application');
  end;
  procedure close_excel is
  begin
        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;
        if application is not null then
                  ole2.release_obj(application);
      end if;
  end;
  procedure read_num_cell( p_row number, p_col number, p_value in out number ) is
            args       ole2.list_type;
            cell      ole2.obj_type;
      begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_row );
            ole2.add_arg(args, p_cel );
            cell := ole2.get_obj_property(worksheet,'Cells',args);
            ole2.destroy_arglist(args);
            p_value := ole2.get_num_property(cell,'Value');
            ole2.release_obj(cell);
      end;
  procedure read_chr_cell( p_row number, p_col number, p_value in out varchar2 ) is
            args       ole2.list_type;
            cell      ole2.obj_type;
      begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_row );
            ole2.add_arg(args, p_col );
            cell := ole2.get_obj_property(worksheet,'Cells',args);
            ole2.destroy_arglist(args);
            p_value := ole2.get_char_property(cell,'Value');
            ole2.release_obj(cell);
      end;

  procedure open_workbook is
  begin
            workbooks := ole2.get_obj_property(application,'Workbooks');
      end;      

  procedure close_workbook is
  begin
            ole2.invoke(workbooks,'Close');
      end;      
  procedure open_excel_doc( p_pateka varchar2 ) is
            args ole2.list_type;
  begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_pateka );
            workbook := ole2.invoke_obj(workbooks, 'Open', args);
            ole2.destroy_arglist(args);
            worksheets := ole2.get_obj_property (workbook,'Worksheets');
  end;
  procedure close_excel_doc is
  begin
            ole2.invoke(workbook, 'Close');
      end;      
  procedure activate_sheet( p_redosled integer ) is
            args ole2.list_type;
      begin
            args := ole2.create_arglist;
            ole2.add_arg(args, p_redosled );
            worksheet := ole2.get_obj_property(worksheets,'Item', args);
            ole2.destroy_arglist(args);
            ole2.invoke( worksheet, 'Activate');
      end;
end;


THEN attach the PLL to the form and IN THE MAIN PROCEDURE in the form:

declare
      p_value number := 4;
begin
      EXCEL.open_excel;
      EXCEL.open_workbook;
      EXCEL.open_excel_doc('c:\a.xls');
      EXCEL.activate_sheet(1);  -- 1 is the first worksheet from left
      EXCEL.READ_NUM_CELL(1, 1, p_value);
-- do what is needed with the p_value
      EXCEL.close_excel_doc;
      EXCEL.close_workbook;
      EXCEL.close_excel;
end;