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:
Package Body:
One demonstration about creating and formatting Excel in PL/SQL using COM with ORDExcelSB:
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 :
Please help me on Writing and Formatting word Using PL/SQL with ORDCOM
ReplyDeletewhere is your package.....ordcom......i thnk your solution is not complete.so please....if u've time then provide spec..and body of your package 'ordcom'...
ReplyDeletethen only your post will be help full.....
This comment has been removed by the author.
ReplyDeleteWhere is the OrdCom Package. Without that we cannot use the above package also. Please post the Ordcom package.
ReplyDeleteThe installation of ORDCOM is given in PART 1 http://saubbane.blogspot.com/2011/05/reading-and-writing-excel-file-xls-from.html
ReplyDeleteBut, this is old, now I suggest you use https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/
For more information you can check https://community.oracle.com/message/9362007#9362007