在Oracle中使用PL/SQL操作COM对象(1)(2)

CREATE TABLE IT_SALE_TAB(ITS_ID VARCHAR2(5),ITS_DATE DATE,ITS_TOTAL NUMBER); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’1’,TO_DATE(’2004-01-01’,’YYYY-MM-DD’),250); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’2’,TO_DATE(’2004-02-01’,’YYYY-MM-DD’),150); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’3’,TO_DATE(’2004-03-01’,’YYYY-MM-DD’),80); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’4’,TO_DATE(’2004-04-01’,’YYYY-MM-DD’),96); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’5’,TO_DATE(’2004-05-01’,’YYYY-MM-DD’),300); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’6’,TO_DATE(’2004-06-01’,’YYYY-MM-DD’),210); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’7’,TO_DATE(’2004-07-01’,’YYYY-MM-DD’),320); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’8’,TO_DATE(’2004-08-01’,’YYYY-MM-DD’),280); 

INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’9’,TO_DATE(’2004-09-01’,’YYYY-MM-DD’),276); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’10’,TO_DATE(’2004-10-01’,’YYYY-MM-DD’),368); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’11’,TO_DATE(’2004-11-01’,’YYYY-MM-DD’),163); 
INSERT INTO IT_SALE_TAB(ITS_ID,ITS_DATE,ITS_TOTAL) 
VALUES(’12’,TO_DATE(’2004-12-01’,’YYYY-MM-DD’),305); 
COMMIT;  

操作Excel对象包（Package）。 

CREATE OR REPLACE PACKAGE oraExcel IS 
xlThin BINARY_INTEGER DEFAULT 2; 
DummyToken BINARY_INTEGER; 
applicationToken BINARY_INTEGER:=-1; -- Excel对象句柄 
WorkBooksToken BINARY_INTEGER:=-1; -- 工作簿句柄 
WorkBookToken BINARY_INTEGER:=-1; 
WorkSheetToken BINARY_INTEGER:=-1; -- 工作表句柄 
WorkSheetToken1 BINARY_INTEGER:=-1; 
RangeToken BINARY_INTEGER:=-1; -- Range区句柄 
ChartObjectToken BINARY_INTEGER:=-1; -- 图表对象句柄 
ChartObject1 BINARY_INTEGER:=-1; 
Chart1Token BINARY_INTEGER:=-1; 
hLines BINARY_INTEGER:=-1; 
i BINARY_INTEGER; 
err_src VARCHAR2(255); 
err_desc VARCHAR2(255); 
err_hpf VARCHAR2(255); 
err_hpID BINARY_INTEGER; 
-- 创建Excel对象 
FUNCTION CreateExcelWorkSheet(servername VARCHAR2) RETURN BINARY_INTEGER; 
-- 插入数据(字符型) 
FUNCTION setCellValues(RANGE VARCHAR2,data VARCHAR2,TYPE VARCHAR2) 
RETURN BINARY_INTEGER; 
-- 插入数据(日期型) 
FUNCTION setCellValues(RANGE VARCHAR2,data DATE,TYPE VARCHAR2) 
RETURN BINARY_INTEGER; 
-- 插入数据(整型) 
FUNCTION setCellValues(RANGE VARCHAR2,data BINARY_INTEGER,TYPE VARCHAR2) 
RETURN BINARY_INTEGER; 
-- 插入数据(实型) 
FUNCTION setCellValuesReal(RANGE VARCHAR2,data DOUBLE PRECISION,TYPE VARCHAR2) 

RETURN BINARY_INTEGER; 
-- 设置列宽 
FUNCTION setCellColWidth(RANGE VARCHAR2,width DOUBLE PRECISION,TYPE VARCHAR2) RETURN 
BINARY_INTEGER; 
-- 设置表格 
FUNCTION setCellLines(RANGE VARCHAR2,BordersIndex BINARY_INTEGER,weight 
BINARY_INTEGER DEFAULT xlThin,TYPE VARCHAR2) RETURN BINARY_INTEGER; 
-- 合并单元格 
FUNCTION setCellMerge(RANGE VARCHAR2,bValues BOOLEAN,TYPE VARCHAR2) 
RETURN BINARY_INTEGER; 
-- 设置字体属性 
FUNCTION setCellFont(RANGE VARCHAR2,Property VARCHAR2, 
fontValues BINARY_INTEGER,TYPE VARCHAR2) RETURN BINARY_INTEGER; 
-- 调用方法 
FUNCTION callMethod(RANGE VARCHAR2,MethodName 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 SaveToFile(filename VARCHAR2) RETURN BINARY_INTEGER; 
-- 关闭Excel对象 
FUNCTION CloseExcel RETURN BINARY_INTEGER; 
END oraExcel;  

数据包体内容（PACKAGE BODY） 

CREATE OR REPLACE PACKAGE BODY oraExcel IS 
FUNCTION CreateExcelWorkSheet(servername VARCHAR2) RETURN BINARY_INTEGER IS 
BEGIN 
-- 创建Excel对象 
i:=ordcom.CreateObject(’Excel.Application’, 0, servername,applicationToken); 
IF (i!=0) THEN -- 创建失败,提示返回的错误信息 
ordcom.GetLastError(err_src, err_desc, err_hpf, err_hpID); 
raise_application_error(-20000,err_src || err_desc || err_hpf || err_hpID); 
END IF; 
-- 通过程序对象句柄获取工作簿句柄 
i:=ordcom.GetProperty(applicationToken, ’WorkBooks’, 0, WorkBooksToken); 
ordcom.InitArg(); 
ordcom.SetArg(-4167,’I4’); 
i:=ordcom.Invoke(WorkBooksToken, ’Add’, 1, WorkBookToken); 
ordcom.InitArg(); 
ordcom.SetArg(’Sheet 1’,’BSTR’); 

-- 获取工作表句柄 
i:=ordcom.GetProperty(applicationToken, ’WorkSheets’, 0, WorkSheetToken1); 
i:=ordcom.Invoke(WorkSheetToken1, ’Add’, 0, WorkSheetToken); 
RETURN i; 
END CreateExcelWorkSheet; 
FUNCTION setCellValues(RANGE VARCHAR2,data VARCHAR2, TYPE VARCHAR2) 
RETURN BINARY_INTEGER IS 
BEGIN 
ordcom.InitArg(); 
ordcom.SetArg(RANGE,’BSTR’); 
-- 获取Range区句柄,之后将数据写入到指定的单元格 
i:=ordcom.GetProperty(WorkSheetToken, ’Range’, 1, RangeToken); 
i:=ordcom.SetProperty(RangeToken, ’Value’, data, TYPE); 
i:=ordcom.DestroyObject(RangeToken); 
RETURN i; 
END setCellValues; 
FUNCTION setCellValues(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); 
i:=ordcom.SetProperty(RangeToken, ’Value’, data, type); 
i:=ordcom.DestroyObject(RangeToken); 
RETURN i; 
END setCellValues; 
FUNCTION setCellValuesReal(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 setCellValuesReal; 
FUNCTION setCellValues(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 setCellColWidth; 
FUNCTION setCellMerge(RANGE VARCHAR2,bValues BOOLEAN,TYPE VARCHAR2) 
RETURN i; 
END setCellValues; 
FUNCTION setCellColWidth(RANGE VARCHAR2,width 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,’ColumnWidth’,width,TYPE); 
i:=ordcom.DestroyObject(RangeToken); 
RETURN BINARY_INTEGER IS 
BEGIN 
ordcom.InitArg(); 
ordcom.SetArg(RANGE,’BSTR’); 
i:=ordcom.GetProperty(WorkSheetToken,’Range’,1,RangeToken); 
i:=ordcom.SetProperty(RangeToken,’MergeCells’,bValues,’BOOLEAN’); 
i:=ordcom.DestroyObject(RangeToken); 
RETURN i; 
END setCellMerge; 
FUNCTION setCellLines(RANGE VARCHAR2,BordersIndex BINARY_INTEGER, 
weight BINARY_INTEGER DEFAULT xlThin,TYPE VARCHAR2) RETURN BINARY_INTEGER IS 
BEGIN 
ordcom.InitArg(); 
ordcom.SetArg(RANGE,’BSTR’); 
i:=ordcom.GetProperty(WorkSheetToken,’Range’,1,RangeToken); 
ordcom.InitArg(); 
ordcom.SetArg(BordersIndex,TYPE); -- 画表格的具体载入参数 
i:=ordcom.GetProperty(RangeToken,’Borders’,1,hLines); 
i:=ordcom.SetProperty(hLines,’weight’,weight,TYPE); 
i:=ordcom.DestroyObject(hLines); 
i:=ordcom.DestroyObject(RangeToken); 
RETURN i; 
END setCellLines; 
FUNCTION setCellFont(RANGE VARCHAR2,Property VARCHAR2,fontValues BINARY_INTEGER,TYPE 
VARCHAR2) RETURN BINARY_INTEGER IS 
BEGIN 
ordcom.InitArg(); 
ordcom.SetArg(RANGE,’BSTR’); 
i:=ordcom.GetProperty(WorkSheetToken,’Range’,1,RangeToken); 
ordcom.InitArg(); 
ordcom.SetArg(Property,TYPE); 

ordcom.InitArg(); 
i:=ordcom.GetProperty(WorkSheetToken, ’ChartObjects’, 0, ChartObjectToken); 
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); -- 添加图表 
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 
i:=ordcom.GetProperty(RangeToken,’Font’,0,hLines); -- 获取字体对象 
i:=ordcom.SetProperty(hLines,Property,fontValues,TYPE); 
i:=ordcom.DestroyObject(hLines); 
i:=ordcom.DestroyObject(RangeToken); 
RETURN i; 
END; 
FUNCTION callMethod(RANGE VARCHAR2,MethodName VARCHAR2) RETURN BINARY_INTEGER IS 
reti BINARY_INTEGER := -1; 
BEGIN 
ordcom.InitArg(); 
ordcom.SetArg(RANGE,’BSTR’); 
i:=ordcom.GetProperty(WorkSheetToken,’Range’,1,RangeToken); 
ordcom.InitArg(); 
i:=ordcom.Invoke(RangeToken,MethodName,0,reti); -- 调用对象的方法 
i:=ordcom.DestroyObject(RangeToken); 
RETURN reti; 
END; 
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 

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; 
FUNCTION SaveToFile(filename VARCHAR2) RETURN BINARY_INTEGER IS 
BEGIN 
ordcom.InitArg(); 
ordcom.SetArg(filename,’BSTR’); 
i:=ordcom.Invoke(WorkBookToken, ’SaveAs’, 1, DummyToken); -- 保存文件 
IF (i!=0) THEN 
ordcom.GetLastError(err_src, err_desc, err_hpf, err_hpID); 
raise_application_error(-20000,err_src || err_desc || err_hpf || err_hpID); 
END IF; 
RETURN i; 
END SaveToFile; 
FUNCTION CloseExcel RETURN BINARY_INTEGER IS 
BEGIN 
ordcom.InitArg(); 
ordcom.InitArg(); 
ordcom.SetArg(FALSE,’BOOL’); 
i:=ordcom.Invoke(WorkBookToken, ’Close’, 0, DummyToken); 
i:=ordcom.DestroyObject(WorkBookToken); 
ordcom.InitArg(); 
i:=ordcom.Invoke(WorkBooksToken, ’Close’, 0, DummyToken); 
i:=ordcom.DestroyObject(WorkBooksToken); 
i:=ordcom.Invoke(applicationToken, ’Quit’, 0, DummyToken); 
-- 关闭所有句柄 
i:=ordcom.DestroyObject(WorkSheetToken); 
i:=ordcom.DestroyObject(WorkSheetToken1); 
i:=ordcom.DestroyObject(applicationToken); 
i:=ordcom.DestroyObject(ChartObjectToken); 
i:=ordcom.DestroyObject(Chart1Token); 
i:=ordcom.DestroyObject(hLines); 
i:=ordcom.DestroyObject(ChartObject1); 
i:=ordcom.DestroyObject(dummyToken); 
RETURN i; 
END CloseExcel; 
END oraExcel;