注册 登录  
 加关注

网易博客网站关停、迁移的公告:

将从2018年11月30日00:00起正式停止网易博客运营
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

jiwenxu的博客

CODE

 
 
 

日志

 
 

Oracle中使用PL/SQL操作COM对象  

2006-12-18 20:17:37|  分类: J2EE |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
使用Excel对象一般针对单元格进行操作(如,设置第一个单元格字体大小的代码为:Range("A1").Font.Size= 20)。对应的COM外部例程操作的步骤为:

  1、获取程序句柄;

  2、获取工作簿句柄;

  3、获取工作表句柄;

  4、获取Range区句柄;

  5、获取Font类句柄;

  6、设置Size属性。

  如下为操作Excel对象的例子,首先建立示例表,然后建立操作包(package),最后是使用包函数的过程(procedure)。创建一个测试用表:

  -- 销售表(脚本)

CREATE TABLE IT_SALE_TAB(ITS_ID VARCHAR2(5),ITS_DATEDATE,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) RETURNBINARY_INTEGER;
-- 插入数据(字符型)
FUNCTION setCellValues(RANGE VARCHAR2,data VARCHAR2,TYPEVARCHAR2)
RETURN BINARY_INTEGER;
-- 插入数据(日期型)
FUNCTION setCellValues(RANGE VARCHAR2,data DATE,TYPEVARCHAR2)
RETURN BINARY_INTEGER;
-- 插入数据(整型)
FUNCTION setCellValues(RANGE VARCHAR2,data BINARY_INTEGER,TYPEVARCHAR2)
RETURN BINARY_INTEGER;
-- 插入数据(实型)
FUNCTION setCellValuesReal(RANGE VARCHAR2,data DOUBLEPRECISION,TYPE VARCHAR2)

RETURN BINARY_INTEGER;
-- 设置列宽
FUNCTION setCellColWidth(RANGE VARCHAR2,width DOUBLE PRECISION,TYPEVARCHAR2) RETURN
BINARY_INTEGER;
-- 设置表格
FUNCTION setCellLines(RANGE VARCHAR2,BordersIndexBINARY_INTEGER,weight
BINARY_INTEGER DEFAULT xlThin,TYPE VARCHAR2) RETURNBINARY_INTEGER;
-- 合并单元格
FUNCTION setCellMerge(RANGE VARCHAR2,bValues BOOLEAN,TYPEVARCHAR2)
RETURN BINARY_INTEGER;
-- 设置字体属性
FUNCTION setCellFont(RANGE VARCHAR2,Property VARCHAR2,
fontValues BINARY_INTEGER,TYPE VARCHAR2) RETURNBINARY_INTEGER;
-- 调用方法
FUNCTION callMethod(RANGE VARCHAR2,MethodName VARCHAR2) RETURNBINARY_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) RETURNBINARY_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, TYPEVARCHAR2)
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,TYPEVARCHAR2)
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 DOUBLEPRECISION,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,TYPEVARCHAR2)
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,TYPEVARCHAR2)
RETURN i;
END setCellValues;
FUNCTION setCellColWidth(RANGE VARCHAR2,width DOUBLE PRECISION,TYPEVARCHAR2)
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,BordersIndexBINARY_INTEGER,
weight BINARY_INTEGER DEFAULT xlThin,TYPE VARCHAR2) RETURNBINARY_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,fontValuesBINARY_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) RETURNBINARY_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, yposBINARY_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_INTEGERIS
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;

  成功创建oraExcel包后,最后创建使用包的存储过程,实现把数据表里的字符型、日期型和数值型分别传到Excel工作表里,对数值型数据进行统计和使用Excel中的图表。该过程具体操作步骤为:

  ·创建Excel对象。

  ·建立表头,设置列宽。

  ·将游标数据传到工作表。

  ·画表格。

  ·设置字体属性。

  ·插入图表。

  ·保存为Excel格式文件,关闭Excel对象。

  如下为dp_toExcel存储过程:

CREATE OR REPLACE PROCEDURE dp_ToExcel IS
CURSOR c1 IS SELECT ITS_ID,ITS_DATE,ITS_TOTAL FROMIT_SALE_TAB;
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;
xlThin BINARY_INTEGER:=2;
xlEdgeLeft BINARY_INTEGER:=7;
xlEdgeTop BINARY_INTEGER:=8;
xlEdgeBottom BINARY_INTEGER:=9;
xlEdgeRight BINARY_INTEGER:=10;
xlInsideVertical BINARY_INTEGER:=11;
xlInsideHorizontal BINARY_INTEGER:=12;
BEGIN
i:=oraExcel.CreateExcelWorkSheet(’’);
i:=oraExcel.setCellValues(’A2’, ’序号’, ’BSTR’);
i:=oraExcel.setCellValues(’B2’, ’日期’, ’BSTR’);
i:=oraExcel.setCellValues(’C2’, ’销售’, ’BSTR’);

cellValue:=c1_rec.ITS_TOTAL;
currencyValue:=cellValue;
i:=oraExcel.setCellValuesReal(cellIndex, currencyValue,’CY’);
n:=n+1;
END LOOP;
i:=oraExcel.setCellValues(’A’||n,’合计’,’BSTR’);
i:=oraExcel.setCellValues(’C’||n,’=SUM(C3:C’||to_char(n-1)||’)’,’BSTR’);
-- 画表格
i:=oraExcel.setCellLines(’A1:C’||n,xlEdgeLeft,xlThin,’I2’);
i:=oraExcel.setCellLines(’A1:C’||n,xlEdgeTop,xlThin,’I2’);
i:=oraExcel.setCellLines(’A1:C’||n,xlEdgeBottom,xlThin,’I2’);
i:=oraExcel.setCellLines(’A1:C’||n,xlEdgeRight,xlThin,’I2’);
i:=oraExcel.setCellLines(’A1:C’||n,xlInsideVertical,xlThin,’I2’);
i:=oraExcel.setCellLines(’A1:C’||n,xlInsideHorizontal,xlThin,’I2’);
-- 设置字体属性
i:=oraExcel.setCellFont(’A1:C1’,’Size’,20,’I2’);
i:=oraExcel.setCellFont(’A1:C1’,’Bold’,1,’I2’);
i:=oraExcel.callMethod(’A1:C1’,’Merge’); -- 合并单元格
i:=oraExcel.setCellValues(’A1:C1’,’合计’,’BSTR’);
-- 插入图表
i:=oraExcel.setCellColWidth(’B:B’, 12.75,’CY’); --设置列宽
i:=oraExcel.setCellColWidth(’C:C’, 12.75,’CY’);
n:=3;
For c1_rec IN c1 LOOP
cellColumn:=TO_CHAR(n);
cellIndex:= ’A’||cellColumn;
cellValue:= TO_CHAR(c1_rec.ITS_ID);
i:=oraExcel.setCellValues(cellIndex, cellValue, ’BSTR’);
cellIndex:= ’B’ || cellColumn;
dateValue:=c1_rec.ITS_DATE;
i:=oraExcel.setCellValues(cellIndex, dateValue, ’DATE’);
cellIndex:= ’C’ || cellColumn;
i:=oraExcel.InsertChart(350,200,250,250,’C3:C’||TO_CHAR(n-1),’xlPie’);
SELECT TO_CHAR(SYSDATE, ’HH24MISS’) INTO returnedTime FROMdual;
filename:=’D:\testExcel’ || returnedTime || ’.xls’;
i:=oraExcel.SaveToFile(filename); -- 保存文件
i:=oraExcel.closeExcel(); -- 关闭对象
END;

  小结

  本文介绍如何从PL/SQL中直接调用C程序的外部例程,并以操作Excel对象为示例。在调用该外部例程时,有如下相关的限制:

  ·操作系统必需支持动态链接库(DLL)和共享库功能。

  ·监听器和extproc进程必须运行在数据库所在的同一台机子上,不支持远程数据库。

  ·extproc唯一支持的是C例程,但可以通过先调用C外部例程去使用其它对象(如:COM对象)。

  除了限制外,调用外部例程会引起额外的系统资源开销,在使用外部例程之前要考虑是否一定要用到外部例程。
  评论这张
 
阅读(44)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018