Monday, May 16, 2011

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');

7 comments :

  1. Hello Saubhik,

    I need your help in one aspect.
    I need to store the output of different queries in to one excel file in different Sheets. Can it be possible ?


    Thanks and Regards,
    Sarathi Dutta

    ReplyDelete
  2. Hi sarathi,

    solution for your request may be ORA_EXCEL, it is PL/SQL package that is able to create Excel document with multiple sheet and place result of query to separate sheets.

    Please take a look at following example:

    BEGIN
    ORA_EXCEL.new_document;

    ORA_EXCEL.add_sheet('Employees');
    ORA_EXCEL.query_to_sheet('select * from employees');

    ORA_EXCEL.add_sheet('Departments');
    ORA_EXCEL.query_to_sheet('select * from departments', FALSE);

    ORA_EXCEL.add_sheet('Locations');
    ORA_EXCEL.query_to_sheet('select * from locations');

    -- EXPORT_DIR is an Oracle directory with at least write permission
    ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx');
    END;

    Mode details you can find here: http://www.oraexcel.com/examples/pl-sql-excel-query-to-sheet-export


    Cheers

    ReplyDelete
    Replies
    1. This is not an advertising site, So please do not recommend any commercial product.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. hi again,

      tnx for sharing your work, this is a wonderfull package

      Delete