Using JAVA in PL/SQL - PART - II Getting Operating System Information

This is PART II of JAVA with PL/SQL series. We will obtain operating system's information using JAVA in PL/SQL.

SQL> /* My database Version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> /* Creating JAVA class */
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED GetOperatingSystem AS
import java.util.*;
import java.lang.*;
import java.net.*;

public class GetOperatingSystem {
    public static void GetOsInfo() {
        try {
            String osNameString=System.getProperty("os.name");
            String osVersionString=System.getProperty("os.version");
            String osArchString=System.getProperty("os.arch");
            String osUserString=System.getProperty("user.name");
           
            System.out.println("Operating System: " +osNameString);
            System.out.println("Operating System Version: " +osVersionString);
            System.out.println("Operating System Architecture: " +osArchString);
            System.out.println("Operating System User Name: " +osUserString);
           
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println("Exception:"+ e.getMessage());
        }

    }
}
  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26 
 27  /

Java created.

SQL> /* Creating a wrapper procedure */
SQL> CREATE OR REPLACE PROCEDURE Get_OS
AS LANGUAGE JAVA
NAME 'GetOperatingSystem.GetOsInfo()';  2    3 
  4  /

Procedure created.

SQL> set serverout on
SQL> BEGIN                                                                         
  DBMS_JAVA.SET_OUTPUT(1000000);                                              
  Get_OS;     
END;  2    3    4 
  5  /
Operating System: Linux
Operating System Version: 2.6.31-23-generic
Operating System Architecture: i686
Operating System User Name:

PL/SQL procedure successfully completed.

SQL>

Using JAVA in PL/SQL - PART - I Listing Files with timestamp

This is slightly modified version of DirList class in asktom. There are three steps involved:

1. Creating the JAVA class in the database.
2. Creating a wrapper stored procedure/function to call this JAVA class.
3. Running the wrapper procedure/function.

SQL> /* My database version */
SQL> SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> /* Creating JAVA stored procedure */
SQL>   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED ListDir AS
   import java.io.*;    
   import java.text.SimpleDateFormat;
   import java.util.*;
                                      
    public class ListDir                                    
   {                                                       
   public static void getList(String directory)            
                                       
   {                                                      
   File path = new File( directory );                     
   String[] list = path.list();                           
   String element;    
  
   for(int i = 0; i < list.length; i++)                   
   {                                                      
   element = list[i];
   String lasmod = new SimpleDateFormat("yyyy-MM-dd").format(new Date(path.lastModified()));
   System.out.println("File Name: "+element+" File Time Stamp: "+lasmod);                                   
     } 
    }
   }  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22 
 23  /

Java created.

SQL> /* Creating a wrapper procedure to call that JAVA class */
SQL> CREATE OR REPLACE
PROCEDURE Get_Filelist(path VARCHAR2)
AS LANGUAGE JAVA
NAME 'ListDir.getList(java.lang.String)';  2    3    4 
  5  /

Procedure created.

SQL> /* Calling that wrapper procedure */
SQL> BEGIN                                                                          
  DBMS_JAVA.SET_OUTPUT(1000000);                                               
  Get_Filelist('/home/oracle');                                                
END;   2    3    4 
  5  /

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> /
File Name: .cache File Time Stamp: 2011-06-09
File Name: .gksu.lock File Time Stamp: 2011-06-09
File Name: admin File Time Stamp: 2011-06-09
File Name: Downloads File Time Stamp: 2011-06-09
File Name: .bash_history File Time Stamp: 2011-06-09
File Name: Desktop File Time Stamp: 2011-06-09
File Name: .fontconfig File Time Stamp: 2011-06-09
File Name: .gstreamer-0.10 File Time Stamp: 2011-06-09
File Name: .recently-used.xbel File Time Stamp: 2011-06-09
File Name: workspace File Time Stamp: 2011-06-09
File Name: .icons File Time Stamp: 2011-06-09
File Name: .update-notifier File Time Stamp: 2011-06-09
File Name: .xsession-errors File Time Stamp: 2011-06-09
File Name: .pulse File Time Stamp: 2011-06-09
File Name: .sudo_as_admin_successful File Time Stamp: 2011-06-09
File Name: .gvfs File Time Stamp: 2011-06-09

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

For details see Part I and Part II.

Here is another version of the package ORDExcelSB. The earlier versions has a drawback. If you modify an existing excel file, then you can't save it in the same name. That caused the application to be hanged.
You always have to save the modified file with a new name ("save as").
In this version, you can save it as the same name.

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;

  FUNCTION SaveExcelFile(filename varchar2, overwrite VARCHAR2)
    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;

  /* Saubhik: This fuction is aded by Saubhik for readig an existing file */
  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;

  /* Saubhik: This new function is added by me. This can overwrite an existing excel file. */
  FUNCTION SaveExcelFile(filename varchar2, overwrite VARCHAR2)
    return binary_integer IS
  BEGIN
    dbms_output.put_line('Saving Excel file...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
    IF overwrite = 'Y' THEN
      i := ORDCOM.SetProperty(applicationToken,
                              'DisplayAlerts',
                              FALSE,
                              'BOOL');
    END IF;
    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;

To overwrite a excel file (save with same name) you have to call the newly written SaveExcelFile (an overloaded version of earlier function) with secnod parameter as 'Y'.


--Savig the file with same name name.
  result := Ordexcelsb.SaveExcelFile('c:\example3.xls','Y');

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 :