Writing and Formatting Excel Using PL/SQL with COM - PART II

Here is another version of my package ORDExcelSB. It includes almost all the formatting needs in Excel (See my first posting about this here).
Package Specification:

CREATE OR REPLACE PACKAGE ORDExcelSB AS
 
  /* Saubhik: These constants are added */
    BorderEdgeLeft CONSTANT BINARY_INTEGER:= 7; 
    BorderEdgeTop CONSTANT BINARY_INTEGER:=8; 
    BorderEdgeBottom CONSTANT BINARY_INTEGER:=9;
    BorderEdgeRight CONSTANT BINARY_INTEGER:=10;
    BorderInsideVertical CONSTANT BINARY_INTEGER:=11;
    BorderInsideHorizontal CONSTANT BINARY_INTEGER:=12;
 
  /* Declare externally callable subprograms. */
 
  /* Start: These functions are newly aded by Saubhik Banerjee */
  FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer;
 
  FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2)
    RETURN binary_integer;
 
  FUNCTION SetCellColor(range varchar2, style INTEGER)
    return binary_integer;
 
  FUNCTION SetCellBorder(range varchar2, BordersIndex BINARY_INTEGER, BorderStyle BINARY_INTEGER)
    return binary_integer;    
 
  FUNCTION FormatFontStyle(range varchar2, fontstyle varchar2)
    return binary_integer;    
  
  FUNCTION FormatFontColor(range varchar2, fontcolor INTEGER)
    return binary_integer;    
    
  FUNCTION FormatFontName(range varchar2, fontname varchar2)
    return binary_integer;   
 
  FUNCTION FormatFontSize(range varchar2, fontsize INTEGER)
    return binary_integer ;   
  /* End: These functions are newly aded by Saubhik Banerjee */
  
  FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer;
 
  FUNCTION InsertData(range varchar2, data binary_integer, type varchar2)
    return binary_integer;
 
  FUNCTION InsertDataReal(range varchar2,
                          data  double precision,
                          type  varchar2) return binary_integer;
 
  FUNCTION GetDataNum(range varchar2) return binary_integer;
 
  FUNCTION GetDataStr(range varchar2) return varchar2;
 
  FUNCTION GetDataReal(range varchar2) return double precision;
 
  FUNCTION GetDataDate(range varchar2) return date;
 
  FUNCTION InsertData(range varchar2, data varchar2, type varchar2)
    return binary_integer;
 
  FUNCTION InsertData(range varchar2, data Date, type varchar2)
    return binary_integer;
 
  FUNCTION InsertChart(xpos   binary_integer,
                       ypos   binary_integer,
                       width  binary_integer,
                       height binary_integer,
                       range  varchar2,
                       type   varchar2) return binary_integer;
 
  FUNCTION SaveExcelFile(filename varchar2) return binary_integer;
 
  FUNCTION ExitExcel return binary_integer;
 
END ORDExcelSB;

Package Body:

CREATE OR REPLACE PACKAGE BODY ORDExcelSB AS
 
  DummyToken        binary_integer;
  applicationToken  binary_integer := -1;
  WorkBooksToken    binary_integer := -1;
  WorkBookToken     binary_integer := -1;
  WorkSheetToken    binary_integer := -1;
  WorkSheetToken1   binary_integer := -1;
  RangeToken        binary_integer := -1;
  ChartObjectToken  binary_integer := -1;
  ChartObject1      binary_integer := -1;
  Chart1Token       binary_integer := -1;
  i                 binary_integer;
  retNum            binary_integer;
  retReal           double precision;
  retStr            varchar2(255);
  retDate           DATE;
  error_src         varchar2(255);
  error_description varchar2(255);
  error_helpfile    varchar2(255);
  error_helpID      binary_integer;
 
  FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Creating Excel application...');
    i := OrdCOM.CreateObject('Excel.Application',
                             0,
                             servername,
                             applicationToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking Workbooks...');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkBooks',
                            0,
                            WorkBooksToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    RETURN i;
  END CreateExcelApplication;
 
  FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2)
    RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Opening Excel file ' || filename || ' ...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
  
    i := ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Opening WorkBook');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'ActiveWorkbook',
                            0,
                            WorkBookToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheets..');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkSheets',
                            0,
                            WorkSheetToken1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheet');
    ORDCOM.InitArg();
    ORDCOM.SetArg(sheetname, 'BSTR');
  
    i := ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Opened ');
  
    RETURN i;
  END OpenExcelFile;
 
  /***************************************************************************
  * Invoke the Excel Automation Server and create a Workbook object as
  * well as a worksheet object
  ***************************************************************************/
  FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer IS
  BEGIN
    dbms_output.put_line('Creating Excel application...');
    i := ORDCOM.CreateObject('Excel.Application',
                             0,
                             servername,
                             applicationToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    dbms_output.put_line('Invoking Workbooks...');
    /*i:=ORDCOM.Invoke(applicationToken, 'WorkBooks',0, WorkBooksToken);*/
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkBooks',
                            0,
                            WorkBooksToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    dbms_output.put_line('Invoking Add to WorkBooks...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(-4167, 'I4');
    i := ORDCOM.Invoke(WorkBooksToken, 'Add', 1, WorkBookToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheets..');
    ORDCOM.InitArg();
    ORDCOM.SetArg('Sheet 1', 'BSTR');
  
    /*  i:=ORDCOM.Invoke(applicationToken, 'WorkSheets', 1, WorkSheetToken);*/
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkSheets',
                            0,
                            WorkSheetToken1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.Invoke(WorkSheetToken1, 'Add', 0, WorkSheetToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    return i;
  END CreateExcelWorkSheet;
 
  /***************************************************************************
  * Invoke the Range method to obtain a range token. Then set the property value
  * at the specified range to the data required
  ***************************************************************************/
  FUNCTION InsertData(range varchar2, data binary_integer, type varchar2)
    RETURN binary_integer IS
  BEGIN
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    IF (i = 0) THEN
      i := ORDCOM.SetProperty(RangeToken, 'ColumnWidth', 15, 'I2');
    END IF;
  
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertData;
  
/* Saubhik: This function is added to set the cell Border */
  FUNCTION SetCellBorder(range varchar2, BordersIndex BINARY_INTEGER, BorderStyle BINARY_INTEGER)
    return binary_integer IS
    --fontToken binary_integer;
    BorderToken BINARY_INTEGER;
   BEGIN
       ORDCOM.InitArg();
      ORDCOM.SetArg(range, 'BSTR');
      
      i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
      ORDCOM.InitArg();
      ORDCOM.SetArg(BordersIndex, 'I2');
      i := ORDCOM.GetProperty(RangeToken, 'Borders', 1, BorderToken);
      i := ORDCOM.SetProperty(BorderToken, 'weight', BorderStyle, 'I2');
       
      i := ORDCOM.DestroyObject(BorderToken);
      i := ORDCOM.DestroyObject(RangeToken);
      return i;
    END SetCellBorder;  
 
/* Saubhik: This function is added to set the cell background color */
  FUNCTION SetCellColor(range varchar2, style INTEGER)
    return binary_integer IS
    --fontToken binary_integer;
    DummyToken BINARY_INTEGER;
    SelectionToken BINARY_INTEGER;
    InteriorToken BINARY_INTEGER;
    --applicationToken
      BEGIN
      ORDCOM.InitArg();
      ORDCOM.SetArg(range, 'BSTR');
      i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
      i := ORDCOM.Invoke(RangeToken,'SELECT',0,DummyToken);
      i := ORDCOM.GetProperty(applicationToken,'Selection',0,SelectionToken);
      i := ORDCOM.GetProperty(SelectionToken, 'Interior', 0, InteriorToken);
      i := ORDCOM.SetProperty(InteriorToken, 'ColorIndex', style, 'I2');
      i := ORDCOM.DestroyObject(RangeToken);
      return i;
    END SetCellColor;
 
/* Saubhik: This function is added to change the font style like Bold, Italic etc. */
  FUNCTION FormatFontStyle(range varchar2, fontstyle varchar2)
    return binary_integer IS
    fontToken binary_integer;
      BEGIN
      ORDCOM.InitArg();
      ORDCOM.SetArg(range, 'BSTR');
      i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
      i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
      IF fontstyle = 'Bold' OR fontstyle = 'Italic' OR
         fontstyle = 'Underline' THEN
          i := ORDCOM.SetProperty(FontToken, fontstyle, True, 'BOOL');
       
      END IF;
      i := ORDCOM.DestroyObject(FontToken);
      i := ORDCOM.DestroyObject(RangeToken);
      return i;
    END FormatFontStyle;
    
/* Saubhik: This function is added to change the font style like Arial, Bookman, Century etc. */
  FUNCTION FormatFontName(range varchar2, fontname varchar2)
    return binary_integer IS
    fontToken binary_integer;
      BEGIN
      ORDCOM.InitArg();
      ORDCOM.SetArg(range, 'BSTR');
      i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
      i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
      i := ORDCOM.SetProperty(FontToken, 'Name', fontname, 'BSTR');
       
      i := ORDCOM.DestroyObject(FontToken);
      i := ORDCOM.DestroyObject(RangeToken);
      return i;
    END FormatFontName;    
    
  /* Saubhik: This function is added to change the font style like Arial, Bookman, Century etc. */
  FUNCTION FormatFontSize(range varchar2, fontsize INTEGER)
    return binary_integer IS
    fontToken binary_integer;
      BEGIN
      ORDCOM.InitArg();
      ORDCOM.SetArg(range, 'BSTR');
      i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
      i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
      i := ORDCOM.SetProperty(FontToken, 'Size', fontsize, 'I2');
       
      i := ORDCOM.DestroyObject(FontToken);
      i := ORDCOM.DestroyObject(RangeToken);
      return i;
    END FormatFontSize;    
 
 /* Saubhik: This function is added to change the font color like Black, Blue etc. */
  FUNCTION FormatFontColor(range varchar2, fontcolor INTEGER)
    return binary_integer IS
    fontToken binary_integer;
      BEGIN
      ORDCOM.InitArg();
      ORDCOM.SetArg(range, 'BSTR');
      i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
      i := ORDCOM.GetProperty(RangeToken, 'Font', 0, FontToken);
    
          i := ORDCOM.SetProperty(FontToken, 'ColorIndex', fontcolor, 'I2');
      i := ORDCOM.DestroyObject(FontToken);
      i := ORDCOM.DestroyObject(RangeToken);
      return i;
    END FormatFontColor;
 
  /***************************************************************************
  * Invoke the Range method to obtain a range token. Then set the property value
  * at the specified range to the data required
  ***************************************************************************/
  FUNCTION GetDataNum(range varchar2) RETURN binary_integer IS
  BEGIN
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retNum);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retNum;
  END GetDataNum;
 
  FUNCTION GetDataReal(range varchar2) RETURN double precision IS
  BEGIN
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retReal);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retReal;
  END GetDataReal;
 
  FUNCTION GetDataStr(range varchar2) RETURN varchar2 IS
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retStr);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retStr;
  END GetDataStr;
 
  FUNCTION GetDataDate(range varchar2) RETURN Date IS
  BEGIN
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.GetProperty(RangeToken, 'Value', 0, retDate);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN retDate;
  END GetDataDate;
 
  FUNCTION InsertData(range varchar2, data DATE, type varchar2)
    RETURN binary_integer IS
  BEGIN
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertData;
 
  FUNCTION InsertDataReal(range varchar2,
                          data  double precision,
                          type  varchar2) RETURN binary_integer IS
  BEGIN
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertDataReal;
 
  FUNCTION InsertData(range varchar2, data varchar2, type varchar2)
    RETURN binary_integer IS
  BEGIN
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    i := ORDCOM.SetProperty(RangeToken, 'Value', data, type);
    i := ORDCOM.DestroyObject(RangeToken);
    RETURN i;
  END InsertData;
 
  /******************************************************************************
  * Insert a chart at the x and y position of the spreadsheet with the desired
  * height and width. Then also uses the ChartWizard to draw the graph with data
  * in a specified range area with a specified charting type.
  *******************************************************************************/
  FUNCTION InsertChart(xpos   binary_integer,
                       ypos   binary_integer,
                       width  binary_integer,
                       height binary_integer,
                       range  varchar2,
                       type   varchar2) RETURN binary_integer IS
    charttype binary_integer := -4099;
  BEGIN
    ORDCOM.InitArg();
    i := ORDCOM.GetProperty(WorkSheetToken,
                            'ChartObjects',
                            0,
                            ChartObjectToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(xpos, 'I2');
    ORDCOM.SetArg(ypos, 'I2');
    ORDCOM.SetArg(width, 'I2');
    ORDCOM.SetArg(height, 'I2');
    i := ORDCOM.Invoke(ChartObjectToken, 'Add', 4, ChartObject1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    i := ORDCOM.GetProperty(ChartObject1, 'Chart', 0, Chart1Token);
    ORDCOM.InitArg();
    ORDCOM.SetArg(range, 'BSTR');
    i := ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
    ORDCOM.InitArg();
    ORDCOM.SetArg(RangeToken, 'DISPATCH');
    IF type = 'xlPie' THEN
      charttype := -4102;
    ELSIF type = 'xl3DBar' THEN
      charttype := -4099;
    ELSIF type = 'xlBar' THEN
      charttype := 2;
    ELSIF type = 'xl3dLine' THEN
      charttype := -4101;
    END IF;
    ORDCOM.SetArg(charttype, 'I4');
    i := ORDCOM.Invoke(Chart1Token, 'ChartWizard', 2, DummyToken);
    i := ORDCOM.DestroyObject(RangeToken);
    i := ORDCOM.DestroyObject(ChartObjectToken);
    i := ORDCOM.DestroyObject(ChartObject1);
    i := ORDCOM.DestroyObject(Chart1Token);
    RETURN i;
  END InsertChart;
 
  /******************************************************************************
  * Save the Excel File. WARNING: Do not specify a filename that already exist
  * since there is no graphical context, Oracle would not be able to pop
  * out a warning message for existing file. This causes Excel to hang
  *******************************************************************************/
  FUNCTION SaveExcelFile(filename varchar2) return binary_integer IS
  BEGIN
    dbms_output.put_line('Saving Excel file...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
  
    i := ORDCOM.Invoke(WorkBookToken, 'SaveAs', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    RETURN i;
  END SaveExcelFile;
 
  /******************************************************************************
  * Close the Excel spreadsheet and exit from it
  ******************************************************************************/
  FUNCTION ExitExcel return binary_integer is
  BEGIN
    dbms_output.put_line('Closing workbook and quitting...');
    ORDCOM.InitArg();
  
    ORDCOM.InitArg();
    ORDCOM.SetArg(FALSE, 'BOOL');
    dbms_output.put_line('Closing workbook...');
    i := ORDCOM.Invoke(WorkBookToken, 'Close', 0, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(WorkBookToken);
    ORDCOM.InitArg();
    dbms_output.put_line('Closing workbooks...');
    i := ORDCOM.Invoke(WorkBooksToken, 'Close', 0, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
    i := ORDCOM.DestroyObject(WorkBooksToken);
    i := ORDCOM.Invoke(applicationToken, 'Quit', 0, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    i := ORDCOM.DestroyObject(WorkSheetToken);
    i := ORDCOM.DestroyObject(WorkSheetToken1);
  
    i := ORDCOM.DestroyObject(applicationToken);
    i := ORDCOM.DestroyObject(ChartObjectToken);
    i := ORDCOM.DestroyObject(Chart1Token);
    i := ORDCOM.DestroyObject(ChartObject1);
    i := ORDCOM.DestroyObject(dummyToken);
    RETURN i;
  END ExitExcel;
 
END ORDExcelSB;

One demonstration about creating and formatting Excel in PL/SQL using COM with ORDExcelSB:

DECLARE
 
  CURSOR c1 IS
    SELECT empno, ename, dname, sal, hiredate
      FROM emp e, dept d
     WHERE e.deptno = d.deptno;
  error_message varchar2(1200);
  n             binary_integer := 2;
  i             binary_integer;
  filename      varchar2(255);
  cellIndex     varchar2(40);
  cellValue     varchar2(40);
  cellColumn    varchar2(10);
  returnedTime  varchar2(20);
  currencyvalue double precision;
  datevalue     DATE;
  empno         binary_integer;
 
  looptext varchar2(20);
 
  error_src         varchar2(255);
  error_description varchar2(255);
  error_helpfile    varchar2(255);
  error_helpID      binary_integer;
 
begin
  filename := 'c:\example2.xls';
  i        := ORDExcelSB.CreateExcelWorkSheet('');
  i        := ORDExcelSB.InsertData('A1', 'EmpNo', 'BSTR');
  -----------------------------------------------
  --Setting Font Name s Bookman
  i := OrdexcelSB.FormatFontName('A1', 'Bookman');
  --Setting Font as Bold
  i := ORDExcelSB.FormatFontStyle('A1', 'Bold');
  --Setting Font Color as Blue
  i := ORDEXCELSB.FormatFontColor('A1', 5);
  --Setting Font Size as 12
  i := ORDEXCELSB.FormatFontSize('A1', 12);
  --Setting CellColor as Yellow!
  i := ORDExcelSB.SetCellColor('A1', 6);
  --Setting Border
  i := ORDExcelSB.SetCellBorder('A1', ORDExcelSB.BorderEdgeLeft, 4);
  i := ORDExcelSB.SetCellBorder('A1', ORDExcelSB.BorderEdgeTop, 4);
  i := ORDExcelSB.SetCellBorder('A1', ORDExcelSB.BorderEdgeBottom, 4);
  i := ORDExcelSB.SetCellBorder('A1', ORDExcelSB.BorderEdgeRight, 4);
  -----------------------------------------------
  i := ORDExcelSB.InsertData('B1', 'Name', 'BSTR');
  i := OrdexcelSB.FormatFontName('B1', 'Bookman');
  i := ORDExcelSB.FormatFontStyle('B1', 'Bold');
  i := ORDEXCELSB.FormatFontColor('B1', 5);
  i := ORDEXCELSB.FormatFontSize('B1', 12);
  i := ORDExcelSB.SetCellColor('B1', 6);
  --Setting Border
  i := ORDExcelSB.SetCellBorder('B1', ORDExcelSB.BorderEdgeLeft, 4);
  i := ORDExcelSB.SetCellBorder('B1', ORDExcelSB.BorderEdgeTop, 4);
  i := ORDExcelSB.SetCellBorder('B1', ORDExcelSB.BorderEdgeBottom, 4);
  i := ORDExcelSB.SetCellBorder('B1', ORDExcelSB.BorderEdgeRight, 4);
 
  i := ORDExcelSB.InsertData('C1', 'Dept', 'BSTR');
  i := OrdexcelSB.FormatFontName('C1', 'Bookman');
  i := ORDExcelSB.FormatFontStyle('C1', 'Bold');
  i := ORDEXCELSB.FormatFontColor('C1', 5);
  i := ORDEXCELSB.FormatFontSize('C1', 12);
  i := ORDExcelSB.SetCellColor('C1', 6);
  --Setting Border
  i := ORDExcelSB.SetCellBorder('C1', ORDExcelSB.BorderEdgeLeft, 4);
  i := ORDExcelSB.SetCellBorder('C1', ORDExcelSB.BorderEdgeTop, 4);
  i := ORDExcelSB.SetCellBorder('C1', ORDExcelSB.BorderEdgeBottom, 4);
  i := ORDExcelSB.SetCellBorder('C1', ORDExcelSB.BorderEdgeRight, 4);
 
  i := ORDExcelSB.InsertData('D1', 'Salary', 'BSTR');
  i := OrdexcelSB.FormatFontName('D1', 'Bookman');
  i := ORDExcelSB.FormatFontStyle('D1', 'Bold');
  i := ORDEXCELSB.FormatFontColor('D1', 5);
  i := ORDEXCELSB.FormatFontSize('D1', 12);
  i := ORDExcelSB.SetCellColor('D1', 6);
  --Setting Border
  i := ORDExcelSB.SetCellBorder('D1', ORDExcelSB.BorderEdgeLeft, 4);
  i := ORDExcelSB.SetCellBorder('D1', ORDExcelSB.BorderEdgeTop, 4);
  i := ORDExcelSB.SetCellBorder('D1', ORDExcelSB.BorderEdgeBottom, 4);
  i := ORDExcelSB.SetCellBorder('D1', ORDExcelSB.BorderEdgeRight, 4);
 
  i := ORDExcelSB.InsertData('E1', 'HireDate', 'BSTR');
  i := OrdexcelSB.FormatFontName('E1', 'Bookman');
  i := ORDExcelSB.FormatFontStyle('E1', 'Bold');
  i := ORDEXCELSB.FormatFontColor('E1', 5);
  i := ORDEXCELSB.FormatFontSize('E1', 12);
  i := ORDExcelSB.SetCellColor('E1', 6);
  --Setting Border
  i := ORDExcelSB.SetCellBorder('E1', ORDExcelSB.BorderEdgeLeft, 4);
  i := ORDExcelSB.SetCellBorder('E1', ORDExcelSB.BorderEdgeTop, 4);
  i := ORDExcelSB.SetCellBorder('E1', ORDExcelSB.BorderEdgeBottom, 4);
  i := ORDExcelSB.SetCellBorder('E1', ORDExcelSB.BorderEdgeRight, 4);
 
  For c1_rec IN c1 LOOP
  
    cellColumn := TO_CHAR(n);
  
    cellIndex := CONCAT('A', cellColumn);
    cellValue := TO_CHAR(c1_rec.empno);
    empno     := cellValue;
    i         := ORDExcelSB.InsertData(cellIndex, empno, 'I2');
    --Setting Font Name as Courier New
    i := OrdexcelSB.FormatFontName(cellIndex, 'Courier New');
    --Setting Border
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeLeft, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeTop, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeBottom, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeRight, 2);
  
    cellIndex := CONCAT('B', cellColumn);
    cellValue := c1_rec.ename;
    i         := ORDExcelSB.InsertData(cellIndex, cellValue, 'BSTR');
    --Setting Font Name as Courier New
    i := OrdexcelSB.FormatFontName(cellIndex, 'Courier New');
    --Setting Border
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeLeft, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeTop, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeBottom, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeRight, 2);
  
    cellIndex := CONCAT('C', cellColumn);
    cellValue := c1_rec.dname;
    i         := ORDExcelSB.InsertData(cellIndex, cellValue, 'BSTR');
    --Setting Font Name as Courier New
    i := OrdexcelSB.FormatFontName(cellIndex, 'Courier New');
    --Setting Border
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeLeft, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeTop, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeBottom, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeRight, 2);
  
    cellIndex     := CONCAT('D', cellColumn);
    cellValue     := c1_rec.sal;
    currencyValue := cellValue;
    i             := ORDExcelSB.InsertData(cellIndex, currencyValue, 'CY');
    --Setting Font Name as Courier New
    i := OrdexcelSB.FormatFontName(cellIndex, 'Courier New');
    --Setting Border
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeLeft, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeTop, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeBottom, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeRight, 2);
  
    cellIndex := CONCAT('E', cellColumn);
    dateValue := c1_rec.hiredate;
    i         := ORDExcelSB.InsertData(cellIndex, dateValue, 'DATE');
    --Setting Font Name as Courier New
    i := OrdexcelSB.FormatFontName(cellIndex, 'Courier New');
    --Setting Border
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeLeft, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeTop, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeBottom, 2);
    i := ORDExcelSB.SetCellBorder(cellIndex, ORDExcelSB.BorderEdgeRight, 2);
  
    n := n + 1;
  END LOOP;
   i := ORDExcelSB.SetCellBorder('A1', ORDExcelSB.BorderEdgeBottom, 4);
   i := ORDExcelSB.SetCellBorder('B1', ORDExcelSB.BorderEdgeBottom, 4);
   i := ORDExcelSB.SetCellBorder('C1', ORDExcelSB.BorderEdgeBottom, 4);
   i := ORDExcelSB.SetCellBorder('D1', ORDExcelSB.BorderEdgeBottom, 4);
   i := ORDExcelSB.SetCellBorder('E1', ORDExcelSB.BorderEdgeBottom, 4);
      
  i := ORDExcelSB.SaveExcelFile(filename);
  i := ORDExcelSB.ExitExcel();
EXCEPTION
  WHEN OTHERS THEN
    i := ORDExcelSB.ExitExcel();
    RAISE;
END;

The excel created is like :

Reading and Writing Excel file (.xls) From PL/SQL Using COM - PART I

This is also based on one of my posting in OTN. We can use COM to read/write excel from PL/SQL.
My orawpcom.dll file exists in the directory C:\oracle\product\10.2.0\db_2\bin

C:\oracle\product\10.2.0\db_2\bin>dir orawpco*.dll
 Volume in drive C is C_Drive
 Volume Serial Number is 8A93-1441
 
 Directory of C:\oracle\product\10.2.0\db_2\bin
 
03/20/2006  05:06 PM            61,440 orawpcom.dll
10/11/2006  03:20 PM            81,920 orawpcom10.dll
               2 File(s)        143,360 bytes
               0 Dir(s)  65,407,717,376 bytes free
 
C:\oracle\product\10.2.0\db_2\bin>

Information about my database version.

SQL> /* My databaser version */
SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
 
SQL>

Prepare the user SCOTT for COM automation.

Now, I will run comwrap.sql from scott user. I have edited the comwrap.sql to adjust my library path here:

create library utils_lib as 'C:\oracle\product\10.2.0\db_3\bin\orawpcom.dll'; 

 Running comwrap.sql and ExcelSolution.sql .....

SQL> conn scott@orclsb
Enter password: *****
Connected.
 
SQL> @c:\comwrap.sql
drop library utils_lib
*
ERROR at line 1:
ORA-04043: object UTILS_LIB does not exist
 
 
 
Library created.
 
drop package ORDCOM
*
ERROR at line 1:
ORA-04043: object ORDCOM does not exist
 
 
drop TYPE OAArgTable
*
ERROR at line 1:
ORA-04043: object OAARGTABLE does not exist
 
 
 
Type created.
 
drop TYPE OAArgTypeTable
*
ERROR at line 1:
ORA-04043: object OAARGTYPETABLE does not exist
 
 
 
Type created.
 
drop function OAgetNumber
*
ERROR at line 1:
ORA-04043: object OAGETNUMBER does not exist
 
 
 
Function created.
 
drop function OAgetStr
*
ERROR at line 1:
ORA-04043: object OAGETSTR does not exist
 
 
 
Function created.
 
drop function OAgetBool
*
ERROR at line 1:
ORA-04043: object OAGETBOOL does not exist
 
 
 
Function created.
 
drop function OAsetNumber
*
ERROR at line 1:
ORA-04043: object OASETNUMBER does not exist
 
 
 
Function created.
 
drop function OAsetString
*
ERROR at line 1:
ORA-04043: object OASETSTRING does not exist
 
 
 
Function created.
 
drop function OAsetBoolean
*
ERROR at line 1:
ORA-04043: object OASETBOOLEAN does not exist
 
 
 
Function created.
 
drop function OAInvokeDouble
*
ERROR at line 1:
ORA-04043: object OAINVOKEDOUBLE does not exist
 
 
 
Function created.
 
drop function OAInvokeBoolean
*
ERROR at line 1:
ORA-04043: object OAINVOKEBOOLEAN does not exist
 
 
 
Function created.
 
drop function OAInvokeString
*
ERROR at line 1:
ORA-04043: object OAINVOKESTRING does not exist
 
 
 
Function created.
 
drop function OACreate
*
ERROR at line 1:
ORA-04043: object OACREATE does not exist
 
 
 
Function created.
 
drop function OADestroy
*
ERROR at line 1:
ORA-04043: object OADESTROY does not exist
 
 
 
Function created.
 
drop function OAGetLastError
*
ERROR at line 1:
ORA-04043: object OAGETLASTERROR does not exist
 
 
 
Function created.
 
drop function OAQueryMethods
*
ERROR at line 1:
ORA-04043: object OAQUERYMETHODS does not exist
 
 
 
Function created.
 
 
Package created.
 
 
Package body created.
 
SQL> 
 
SQL> @c:\ExcelSolution.sql
drop package ORDExcel
*
ERROR at line 1:
ORA-04043: object ORDEXCEL does not exist
 
 
 
Package created.
 
 
Package body created.
 
SQL>

I have modified ORDExcel a little bit and renamed it as 

ORDExcelSB. You need this version for reading the excel.

 SQL> @C:\ExcelSolutionSB.sql
 
Package dropped.
 
 
Package created.
 
 
Package body created.
 
SQL>

The actual code of  ORDExcelSB (ExcelSolutionSB.sql) Is:

set serveroutput on;
drop package ORDExcelSB; 
CREATE PACKAGE ORDExcelSB AS
 
 
   /* Declare externally callable subprograms. */
   
   FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer;
   
   FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2) RETURN binary_integer;
    
   FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer;
 
   FUNCTION InsertData(range varchar2, data binary_integer, type varchar2) return binary_integer;
 
   FUNCTION InsertDataReal(range varchar2, data double precision, type varchar2) return binary_integer;
 
   FUNCTION GetDataNum(range varchar2) return binary_integer;
 
   FUNCTION GetDataStr(range varchar2) return varchar2;
 
   FUNCTION GetDataReal(range varchar2) return double precision;
 
   FUNCTION GetDataDate(range varchar2) return date;
 
   FUNCTION InsertData(range varchar2, data varchar2, type varchar2) return binary_integer;
 
   FUNCTION InsertData(range varchar2, data Date, type varchar2) return binary_integer;
 
   FUNCTION InsertChart(xpos binary_integer, ypos binary_integer, width binary_integer, 
      height binary_integer, range varchar2, type varchar2) return binary_integer;
 
 
   FUNCTION SaveExcelFile(filename varchar2) return binary_integer;
   
   FUNCTION ExitExcel return binary_integer;
 
END ORDExcelSB;
 
/ 
CREATE PACKAGE BODY ORDExcelSB AS
 
   DummyToken  binary_integer; 
   applicationToken binary_integer:=-1;
   WorkBooksToken binary_integer:=-1;
   WorkBookToken binary_integer:=-1;
   WorkSheetToken binary_integer:=-1;
   WorkSheetToken1 binary_integer:=-1;
   RangeToken  binary_integer:=-1;
   ChartObjectToken binary_integer:=-1;
   ChartObject1  binary_integer:=-1;
   Chart1Token  binary_integer:=-1;
   i    binary_integer;
   retNum   binary_integer;
   retReal   double precision;
   retStr   varchar2(255);
   retDate   DATE;
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
 
 
FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Creating Excel application...');
    i := OrdCOM.CreateObject('Excel.Application',
                             0,
                             servername,
                             applicationToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking Workbooks...');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkBooks',
                            0,
                            WorkBooksToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    RETURN i;
  END CreateExcelApplication;
 
  FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2)
    RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Opening Excel file ' || filename || ' ...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
  
    i := ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Opening WorkBook');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'ActiveWorkbook',
                            0,
                            WorkBookToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheets..');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkSheets',
                            0,
                            WorkSheetToken1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheet');
    ORDCOM.InitArg();
    ORDCOM.SetArg(sheetname, 'BSTR');
  
    i := ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Opened ');
  
    RETURN i;
  END OpenExcelFile;
 
/***************************************************************************
 * Invoke the Excel Automation Server and create a Workbook object as 
 * well as a worksheet object
 ***************************************************************************/
FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer IS
BEGIN
 dbms_output.put_line('Creating Excel application...');
 i:=ORDCOM.CreateObject('Excel.Application', 0, servername,applicationToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 dbms_output.put_line('Invoking Workbooks...');
 /*i:=ORDCOM.Invoke(applicationToken, 'WorkBooks',0, WorkBooksToken);*/
 i:=ORDCOM.GetProperty(applicationToken, 'WorkBooks', 0, WorkBooksToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 dbms_output.put_line('Invoking Add to WorkBooks...');
 ORDCOM.InitArg();
 ORDCOM.SetArg(-4167,'I4');
 i:=ORDCOM.Invoke(WorkBooksToken, 'Add', 1, WorkBookToken);
IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 dbms_output.put_line('Invoking WorkSheets..');
 ORDCOM.InitArg();
 ORDCOM.SetArg('Sheet 1','BSTR');
 
/* i:=ORDCOM.Invoke(applicationToken, 'WorkSheets', 1, WorkSheetToken);*/
i:=ORDCOM.GetProperty(applicationToken, 'WorkSheets', 0, WorkSheetToken1);
IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
i:=ORDCOM.Invoke(WorkSheetToken1, 'Add', 0, WorkSheetToken);
IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 return i;
END CreateExcelWorkSheet;
 
 
/***************************************************************************
 * Invoke the Range method to obtain a range token. Then set the property value
 * at the specified range to the data required
 ***************************************************************************/
FUNCTION InsertData( range varchar2,
     data binary_integer,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 IF (i=0) THEN
       i:=ORDCOM.SetProperty(RangeToken, 'ColumnWidth', 15, 'I2');
 END IF;
 
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertData;
 
/***************************************************************************
 * Invoke the Range method to obtain a range token. Then set the property value
 * at the specified range to the data required
 ***************************************************************************/
FUNCTION GetDataNum( range varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retNum);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retNum;
END GetDataNum;
 
FUNCTION GetDataReal( range varchar2) 
     RETURN double precision IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retReal);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retReal;
END GetDataReal;
 
FUNCTION GetDataStr( range varchar2) 
     RETURN varchar2 IS
BEGIN
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retStr);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retStr;
END GetDataStr;
 
FUNCTION GetDataDate( range varchar2) 
     RETURN Date IS
BEGIN
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retDate);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retDate;
END GetDataDate;
 
FUNCTION InsertData( range varchar2,
     data DATE,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertData;
 
FUNCTION InsertDataReal( range varchar2,
     data double precision,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertDataReal;
 
FUNCTION InsertData( range varchar2,
     data varchar2,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertData;
 
/******************************************************************************
 * Insert a chart at the x and y position of the spreadsheet with the desired
 * height and width. Then also uses the ChartWizard to draw the graph with data
 * in a specified range area with a specified charting type.
 *******************************************************************************/
FUNCTION InsertChart(xpos binary_integer, ypos binary_integer, 
      width binary_integer, height binary_integer, 
      range varchar2, type varchar2) RETURN binary_integer IS
 charttype binary_integer:= -4099;
BEGIN
 ORDCOM.InitArg();
 i:=ORDCOM.GetProperty(WorkSheetToken, 'ChartObjects', 0, ChartObjectToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(xpos,'I2');
 ORDCOM.SetArg(ypos,'I2');
 ORDCOM.SetArg(width,'I2');
 ORDCOM.SetArg(height,'I2');
 i:=ORDCOM.Invoke(ChartObjectToken, 'Add', 4, ChartObject1);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 i:=ORDCOM.GetProperty(ChartObject1, 'Chart', 0,Chart1Token);
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken,'Range', 1, RangeToken);
 ORDCOM.InitArg();
 ORDCOM.SetArg(RangeToken, 'DISPATCH');
 IF type='xlPie' THEN
  charttype := -4102;
 ELSIF type='xl3DBar' THEN
  charttype := -4099;
 ELSIF type='xlBar' THEN
  charttype := 2;
 ELSIF type='xl3dLine' THEN
  charttype:= -4101;
 END IF;
 ORDCOM.SetArg(charttype,'I4');
 i:=ORDCOM.Invoke(Chart1Token,'ChartWizard', 2, DummyToken);
 i:=ORDCOM.DestroyObject(RangeToken);
 i:=ORDCOM.DestroyObject(ChartObjectToken);
 i:=ORDCOM.DestroyObject(ChartObject1);
 i:=ORDCOM.DestroyObject(Chart1Token);
 RETURN i;
END InsertChart;
 
/******************************************************************************
 * Save the Excel File. WARNING: Do not specify a filename that already exist
 * since there is no graphical context, Oracle would not be able to pop
 * out a warning message for existing file. This causes Excel to hang
 *******************************************************************************/
FUNCTION SaveExcelFile(filename varchar2) return binary_integer IS
BEGIN
 dbms_output.put_line('Saving Excel file...');
 ORDCOM.InitArg();
 ORDCOM.SetArg(filename,'BSTR');
 
 i:=ORDCOM.Invoke(WorkBookToken, 'SaveAs', 1, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 RETURN i; 
END SaveExcelFile;
 
/******************************************************************************
 * Close the Excel spreadsheet and exit from it
 ******************************************************************************/
FUNCTION ExitExcel return binary_integer is
BEGIN
 dbms_output.put_line('Closing workbook and quitting...');
 ORDCOM.InitArg();
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(FALSE,'BOOL');
 dbms_output.put_line('Closing workbook...');
 i:=ORDCOM.Invoke(WorkBookToken, 'Close', 0, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(WorkBookToken); 
 ORDCOM.InitArg();
 dbms_output.put_line('Closing workbooks...');
 i:=ORDCOM.Invoke(WorkBooksToken, 'Close', 0, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(WorkBooksToken);
 i:=ORDCOM.Invoke(applicationToken, 'Quit', 0, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 i:=ORDCOM.DestroyObject(WorkSheetToken); 
 i:=ORDCOM.DestroyObject(WorkSheetToken1); 
 
 
 i:=ORDCOM.DestroyObject(applicationToken);
 i:=ORDCOM.DestroyObject(ChartObjectToken);
 i:=ORDCOM.DestroyObject(Chart1Token);
 i:=ORDCOM.DestroyObject(ChartObject1);
 i:=ORDCOM.DestroyObject(dummyToken);
 RETURN i;
END ExitExcel;
 
 
END ORDExcelSB;
/ 

 I have created an excel named as C:\Example.xls.

Name SlNo Job Dept Salary Bonus

Saubhik Banerjee 706090 IT Specialist GBS 100 10

Partha S Mohanty 706091 Pogrmmer APPS 70 20

Partha Sarkar 889300 Condultant FIN 200 30

Useless 98009 PM PM 900 90

 You can use this code to read excel file (.xls)

SQL> SET SERVEROUT ON
SQL> DECLARE
  2  
  3    v_Name          varchar2(90);
  4    v_SlNo          varchar2(100);
  5    v_Job           varchar2(200);
  6    v_Dept          varchar2(100);
  7    v_recon_remark  varchar2(50);
  8    v_sal_amt_usd   number;
  9    v_Bonus_amt_usd number;
 10  
 11    result INTEGER;
 12  
 13    i        binary_integer;
 14    filename varchar2(255);
 15  
 16  BEGIN
 17  
 18    filename := 'C:\Example.xls';
 19  
 20    result := ORDExcelSB.CreateExcelApplication('');
 21    result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');
 22  
 23    /* Excluding the header row and reading the first 5 row */
 24    FOR n in 2 .. 5 LOOP
 25    
 26      v_Name          := ORDExcelSB.GetDataStr('A' || n);
 27      v_SlNo          := ORDExcelSB.GetDataReal('B' || n);
 28      v_Job           := ORDExcelSB.GetDataStr('C' || n);
 29      v_Dept          := ORDExcelSB.GetDataStr('D' || n);
 30      v_sal_amt_usd   := ORDExcelSB.GetDataNum('E' || n);
 31      v_Bonus_amt_usd := ORDExcelSB.GetDataNum('F' || n);
 32    
 33      dbms_output.put_line(v_Name || '  ' || v_SlNo || '  ' || v_Job || '  ' ||
 34                           v_Dept || '  ' || v_sal_amt_usd || '  ' ||
 35                           v_Bonus_amt_usd);
 36    
 37    END LOOP;
 38  
 39    result := ORDExcelSB.ExitExcel();
 40  EXCEPTION
 41    WHEN OTHERS THEN
 42      result := ORDExcelSB.ExitExcel();
 43      RAISE;
 44  END;
 45  / 
Creating Excel application...
Invoking Workbooks...
Opening Excel file C:\Example.xls ...
Opening WorkBook
Invoking WorkSheets..
Invoking WorkSheet
Opened
Saubhik Banerjee  706090  IT Specialist  GBS  100  10
Partha S Mohanty  706091  Pogrmmer  APPS  70  20
Partha Sarkar  889300  Condultant  FIN  200  30
Useless  98009  PM  PM  900  90
Closing workbook and quitting...
Closing workbook...
Closing workbooks...
 
PL/SQL procedure successfully completed.
 
SQL> 

 You can use this code to write to excel file (.xls)

DECLARE 
 
CURSOR c1 IS 
 SELECT empno, ename, dname, sal, hiredate
 FROM emp e, dept d
 WHERE e.deptno = d.deptno;
error_message varchar2(1200);
n binary_integer:=2;
i binary_integer;
filename varchar2(255);
cellIndex varchar2(40);
cellValue varchar2(40);
cellColumn varchar2(10);
returnedTime varchar2(20);
currencyvalue double precision;
datevalue DATE;
empno binary_integer;
 
looptext varchar2(20);
 
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
 
begin
filename:='c:\example2.xls';
i:=ORDExcel.CreateExcelWorkSheet('');
i:=ORDExcel.InsertData('A1', 'EmpNo', 'BSTR');
i:=ORDExcel.InsertData('B1', 'Name', 'BSTR');
i:=ORDExcel.InsertData('C1', 'Dept', 'BSTR');
i:=ORDExcel.InsertData('D1', 'Salary', 'BSTR');
i:=ORDExcel.InsertData('E1', 'HireDate', 'BSTR');
 
For c1_rec IN c1 LOOP
 
cellColumn:=TO_CHAR(n);
 
cellIndex:=CONCAT('A',cellColumn);
cellValue:=TO_CHAR(c1_rec.empno);
empno:=cellValue;
i:=ORDExcel.InsertData(cellIndex, empno, 'I2');
 
 
cellIndex:=CONCAT('B',cellColumn);
cellValue:=c1_rec.ename;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
 
cellIndex:=CONCAT('C',cellColumn);
cellValue:=c1_rec.dname;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
 
cellIndex:=CONCAT('D',cellColumn);
cellValue:=c1_rec.sal;
currencyValue:=cellValue;
i:=ORDExcel.InsertData(cellIndex, currencyValue, 'CY');
 
cellIndex:=CONCAT('E',cellColumn);
dateValue:=c1_rec.hiredate;
i:=ORDExcel.InsertData(cellIndex, dateValue, 'DATE');
 
 
n:=n+1;
END LOOP;
 
i:=ORDExcel.SaveExcelFile(filename);
i:=ORDExcel.ExitExcel();
EXCEPTION
 WHEN OTHERS THEN
  i:=ORDExcel.ExitExcel();
  RAISE;
END;