早就知道Oracle能操作XML文件,有自己得XML文件解析器。但当听说Oracle还能操作Excel文件多少还是有点意外。曾经听说PL/SQL能作任何你想作得,真是一点不假。
PL/SQL允许开发人员调用外部过程,这些外部过程可以是C语言的程序模块,也可以是Java的程序。在网上搜了一下,找到一些资料,附在后面仅供参考。
基本原理:
在PL/SQL调用COM对象的过程种,Oracle的Extproc进程是一个很关键的中间枢纽。它负责将PL/SQL的调用转化为对COM对象的调用,也负责将COM对象的返回结果转化为PL/SQL能识别的对象。关于Extproc的配置可以参照http://soft.zdnet.com.cn/software_zone/2007/0816/457002.shtml。这里重点介绍PL/SQL的调用方式,只有了解了它的调用方式才能运用自如。
PL/SQL将对COM对象的调用分作3种,1:执行某种操作,如追加一个文档什么的。它不改变COM对象的属性。2:改变某种属性的操作:如设定单元格的内容、设定线条等。3:取得某个属性:如取得单元格的值,取得一个Cell或者Range等。PL/SQL中,这3种函数分别有各自的调用方式,参看下表:
调用方式 | 对应的函数 | 参数说明 |
执行某种操作 | Invoke | 1:调用该方法的对象句柄 2:一个字符串,标明要执行的操作 3:调用该函数时的参数个数 4:返回对象的句柄 |
改变属性的操作 | SetProperty | 1:调用该方法的对象句柄 2:要改变的属性的名字 3:参数的设定值 4:参数类型 |
取得属性的操作 | GetProperty | 1:调用该方法的对象句柄 2:要取得的属性的名字 3:参数的设定值 4:保存取得结果的对象句柄 |
PL/SQL能直接调用Excel的函数来执行操作(可以在Excel中按下Alt+F11来查看Excel中该方法的调用方式)。只是在调用的时候要弄清楚以下的内容,1:执行该方法的对象,例如:保存文件时对象是当前的WorkBook、取得一个Range时操作对象是当前的Sheet。2:要明白执行该操作的方法名,这可以在VBA的相关资料中查到。3:如果操作有返回对象,要明白返回的是什么对象,如:新建一个Sheet时的返回对象当然是新建的对象的句柄、而取得一个Range的返回对象当然就是一个表示Range的对象句柄。
关于参数的说明,Invoke和GetProperty的参数应该通过InitArg和SetArg来设定,而SetProperty则需要直接在函数中设定参数。关于PL/SQL中的参数与COM和API中的参数的对应关于可参照附件中的说明。
除了上面的那些函数外,还有一些特殊函数。如创建一个Excel运用程序,取得执行的错误信息和关闭销毁对象句柄等。在后面的例子中会看到。函数在执行时参数的设定也是通过指定的函数来完成的。InitArg()用来清空参数列表,而SetArg用来设定参数。PL/SQL中参数类型与COM中参数的对应可以参照参考资料。
最后还有一点,PL/SQL中对上面的函数的调用也是放在一个PL/SQl包中的,这个包是Oracle自带的,但没有编译在DB中,你需要自己编译。在Oracle9中该文件位于com下的comwrap.sql。直接取出来编译就行了。我们使用的报名叫作ORDCOM。直接使用该报名调用上面的函数就行了。
有了上面这些准备后再来理解下面的例子就不再困难了。
-- Package Head
-- ===================================
create or replace package XLDemo is
-- RunDemo: run a demo using the functions in XLDemo
-- Requires the SCOTT schema objects
-- For base_filename, supply the full path and the first part of the filename
-- .xls will be appended to <base_filename>
-- procedure RunDemo(base_filename in varchar2);
-- Start the Excel application
function startExcel return binary_integer;
-- Create a new workbook and create a worksheet in it
function CreateAWorkbook return binary_integer;
-- Insert string data into a cell range in the current worksheet
-- cell_range fmt examples: 'A1' for single cell, 'A1,D9' for multi cells
function InsertDataToRange ( cell_range varchar2 ,
data varchar2 )
return binary_integer ;
-- Save the current workbook as <filename>.xls
function SaveCurrWorkbookAs (filename varchar2 ) return binary_integer;
-- Close all open workbooks and exit Excel
function quitExcel return binary_integer ;
-- Set color to the cell
FUNCTION SetCellColor(var_CellRange VARCHAR2 ,
var_ColorIndex INTEGER ) RETURN BINARY_INTEGER;
end XLDemo;
/
以下是具体的实现
-- ====================================
-- Parameter Definition
-- ====================================
-- Application token
ApplicationHandle BINARY_INTEGER : = - 1 ;
-- All workbooks open in the application:
WorkbooksHandle BINARY_INTEGER : = - 1 ;
-- Current workbook:
CurrWorkbookHandle BINARY_INTEGER : = - 1 ;
-- Current worksheet:
CurrSheetHandle BINARY_INTEGER : = - 1 ;
dummySheetHandle BINARY_INTEGER : = - 1 ;
-- Dummy:
DummyHandle BINARY_INTEGER : = - 1 ;
-- Workbook file name
wkbkname VARCHAR2 ( 2000 ) : = null ;
-- many ORDCom functions return the COM error code, a binary_integer
-- conventionally known as hresult
hresult BINARY_INTEGER : = 0 ;
-- Variable for the name of the remote DCOM server on which the COM object is being instantiated
-- To create a local COM object, pass an empty string and be sure that the registry indicates that the COM object exists locally
servername VARCHAR2 ( 1000 ) : = '' ;
-- Variables to hold information from ORDCOM.GetLastError
error_src VARCHAR2 ( 255 );
error_description VARCHAR2 ( 255 );
error_helpfile VARCHAR2 ( 255 );
error_helpID BINARY_INTEGER;
-- =======================================
-- Functions Definitions
-- =======================================
-- ======================================================================================
-- Name: StartExcel
-- Description:Start the Excel application
-- Run this first
-- Parameters: No Parameter needed
-- Returns: When Error,Return a non-zero value
-- ======================================================================================
FUNCTION StartExcel() RETURN BINARY_INTEGER
IS
thisWkBkHandle BINARY_INTEGER : = - 1 ;
reslt BINARY_INTEGER : = 0 ;
BEGIN
-- Creates Application object and puts its token in ApplicationHandle
DBMS_OUTPUT.PUT_LINE( ' Starting Excel ' );
DBMS_OUTPUT.PUT_LINE( ' Creating application object ' );
hresult: = ORDCOM.CreateObject( ' Excel.Application ' , 0 , servername,ApplicationHandle);
IF (hresult != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error creating application, aborting: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
reslt : = hresult;
return reslt;
END IF ;
-- Application.DisplayAlerts = False
DBMS_OUTPUT.PUT_LINE( ' Setting DisplayAlerts to False ' );
hresult: = ORDCOM.SetProperty(ApplicationHandle, ' DisplayAlerts ' ,false, ' BOOL ' );
IF (hresult != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error setting DisplayAlerts to false: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
DBMS_OUTPUT.PUT_LINE( ' Done opening Excel ' );
RETURN reslt;
END StartExcel;
-- ======================================================================================
-- Name: CreateAWorkbook
-- Description:Create a new workbook and create a new worksheet in it
-- ApplicationHandle must be populated, ie run StartExcel first
-- Parameters: No Parameter needed
-- Returns: When Error,Return a non-zero value
-- ======================================================================================
FUNCTION CreateAWorkbook() RETURN BINARY_INTEGER
IS
-- create a workbook; populate CurrWorkbookHandle and CurrSheetHandle
reslt BINARY_INTEGER : = 0 ;
BEGIN
-- get workbooks handle
DBMS_OUTPUT.PUT_LINE( ' Getting WorkBooks property of Application object ' );
-- equivalent to: WorkBooksHandle := ApplicationHandle.WorkBooks
result: = ORDCOM.GetProperty(applicationHandle, ' WorkBooks ' , 0 , WorkBooksHandle);
IF hresult != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error getting WorkBooksHandle: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
RETURN hresult;
END IF ;
DBMS_OUTPUT.PUT_LINE( ' WorkBooksHandle: ' || WorkBooksHandle);
-- invoke workbooks.add method
-- equivalent to: CurrWorkbookHandle := WorkBooksHandle.Add(xlWBATWorksheet)
ORDCOM.InitArg();
ORDCOM.SetArg( - 4167 , ' I4 ' ); -- template argument. The constant xlWBATWorksheet = -4167
hresult : = ORDCOM.Invoke(WorkbooksHandle, ' Add ' , 1 ,CurrWorkbookHandle);
IF hresult != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error adding workbook: ' || reslt);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
reslt : = hresult;
RETURN reslt;
END IF ;
-- get the token for Sheet1 and place in DummySheetHandle
-- Then create a new worksheet and place its token in CurrSheetHandle
-- this is a member of the WorkSheets collection, its index is Sheet1
-- equivalent to: dummySheetHandle := ApplicationHandle.WorkSheets()
-- followed by: currSheetHandle := dummySheetHandle.Add
OrdCom.InitArg();
OrdCom.SetArg( ' Sheet 1 ' , ' BSTR ' );
hresult: = ORDCOM.GetProperty(applicationHandle, ' WorkSheets ' , 0 , DummySheetHandle);
DBMS_OUTPUT.PUT_LINE( ' DummySheetHandle: ' || dummySheetHandle);
IF hresult != 0 THEN
OrdCom.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error getting Worksheets(): ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
RETURN hresult;
END IF ;
hresult: = ORDCOM.Invoke(dummySheetHandle, ' Add ' , 0 , CurrSheetHandle);
DBMS_OUTPUT.PUT_LINE( ' CurrSheetHandle: ' || CurrSheetHandle);
IF hresult != 0 THEN
OrdCom.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error adding worksheet: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
RETURN hresult;
END IF ;
RETURN reslt;
END CreateAWorkbook ;
-- ======================================================================================
-- Name: InsertDataToRange
-- Description:Insert string data into a cell range in the current worksheet
-- CurrSheetHandle must be populated before calling this
-- cell_range fmt examples: 'A1' for single cell, 'A1,D9' for multi cells
-- Parameters: cell_range The cell where the value will be inserted
-- data The value will be setted
-- Returns: When Error,Return a non-zero value
-- ======================================================================================
FUNCTION InsertDataToRange ( cell_range varchar2 ,data varchar2 ) RETURN BINARY_INTEGER
IS
reslt BINARY_INTEGER : = 0 ;
rangeHandle BINARY_INTEGER : = - 1 ;
BEGIN
DBMS_OUTPUT.PUT_LINE( ' Inserting " ' || data || ' " into ' || cell_range );
-- in VB we could do this in one step:
-- CurrSheetHandle.Range(cell_range).Value := data
-- In Oracle we must break it down into two steps:
-- 1) RangeHandle := CurrSheetHandle.Range(cell_range)
-- 2) RangeHandle.Value := data
-- This step is equivalent to: RangeHandle := CurrSheetHandle.Range(cell_range)
ORDCOM.InitArg();
ORDCOM.SetArg(cell_range, ' BSTR ' );
hresult: = ORDCOM.GetProperty(CurrSheetHandle, ' Range ' , 1 , RangeHandle);
-- equivalent to: RangeHandle.Value := data
hresult: = ORDCOM.SetProperty(RangeHandle, ' Value ' , data, ' BSTR ' );
-- this is the hresult we want to return
reslt : = hresult;
IF hresult != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error inserting data: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
-- destroy the RangeHandle object
hresult: = ORDCOM.DestroyObject(RangeHandle);
RETURN reslt;
END InsertDataToRange;
-- ======================================================================================
-- Name: SaveCurrWorkbookAs
-- Description:Save the current workbook as <filename>.xls
-- Warning: Don't specify a <filename> that already exists or it will hang
-- unless you have set ApplicationHandle.DisplayAlerts=false as in StartExcel
-- If DisplayAlerts is not set to False,
-- Excel's response to an existing filename is to pop up a graphical
-- confirmation box
-- Since there is no graphical context, Oracle is unable to pop up the
-- confirmation box. Thus, Excel hangs
-- unless ApplicationHandle.DisplayAlerts = false.
-- To unhang: Kill the Excel process in Task Manager.
-- Parameters: cell_range The cell where the value will be inserted
-- data The value will be setted
-- Returns: When Error,Return a non-zero value
-- ======================================================================================
FUNCTION SaveCurrWorkbookAs(filename varchar2 ) RETURN BINARY_INTEGER
IS
reslt BINARY_INTEGER : = 0 ;
BEGIN
DBMS_OUTPUT.PUT_LINE( ' Invoking SaveAs method of current workbook ' );
DBMS_OUTPUT.PUT_LINE( ' Saving workbook as ' || filename);
-- equivalent to: CurrWorkbookHandle.SaveAs(filename)
ORDCOM.InitArg();
ORDCOM.SetArg(filename, ' BSTR ' );
hresult: = ORDCOM.Invoke(CurrWorkbookHandle, ' SaveAs ' , 1 , DummyHandle);
IF hresult != 0 THEN
OrdCom.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error saving current wkbk as ' || filename || ' : ' || reslt);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
reslt : = hresult;
return reslt;
end if ;
return reslt;
end SaveCurrWorkbookAs;
-- Close all open workbooks and exit Excel
-- Run this last, to terminate the EXCEL process started with StartExcel
-- and destroy all the COM objects created
function quitExcel
return binary_integer
is
reslt binary_integer : = 0 ;
begin
dbms_output.put_line( ' Exiting Excel ' );
-- first we close the current workbook
dbms_output.put_line( ' Closing workbook CurrWorkBookHandle ' );
ordcom.InitArg();
Ordcom.SetArg(FALSE, ' BOOL ' ); -- SaveChanges argument
-- equivalent to WorkBookHandle.Close(False)
hresult: = ORDCOM.Invoke(CurrWorkBookHandle, ' Close ' , 0 , DummyHandle);
if hresult != 0 then
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line ( ' Error closing current workbook: ' || hresult);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
reslt : = hresult;
-- continue instead of halting
-- return reslt;
end if ;
-- Now we need to destroy the WorkBookHandle object
hresult: = ORDCOM.DestroyObject(CurrWorkBookHandle);
-- Now, to be on the safe, side, we close all workbooks
-- by invoking the Close method of the Workbooks object
-- which closes all workbooks in the collection
Ordcom.InitArg(); -- clear out arguments
dbms_output.put_line( ' Closing all workbooks in WorkBooksHandle ' );
hresult: = ORDCOM.Invoke(WorkBooksHandle, ' Close ' , 0 , DummyHandle);
if hresult != 0 then
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line ( ' Error closing all workbooks: ' || hresult);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
reslt : = hresult;
-- continue instead of halting
-- return reslt;
end if ;
-- Now we destroy the WorkBooksHandle object
hresult: = ORDCOM.DestroyObject(WorkBooksHandle);
dbms_output.put_line ( ' Quitting Excel application ' );
-- The next step is to quit the application
-- Equivalent to ApplicationHandle.Quit()
hresult: = ORDCOM.Invoke(applicationHandle, ' Quit ' , 0 , DummyHandle);
if hresult != 0 then
OrdCom.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line ( ' Error quitting application: ' || hresult);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
reslt : = hresult;
-- continue instead of halting
-- return reslt;
end if ;
-- Now we destroy all the objects we created
hresult: = OrdCom.DestroyObject(CurrSheetHandle);
hresult: = OrdCom.DestroyObject(DummySheetHandle);
hresult: = OrdCom.DestroyObject(DummyHandle);
hresult: = OrdCom.DestroyObject(ApplicationHandle);
RETURN reslt;
END QuitExcel;
FUNCTION SetCellColor(var_CellRange VARCHAR2 ,
var_ColorIndex INTEGER ) RETURN BINARY_INTEGER
IS
VAR_RESULT BINARY_INTEGER : = 0 ;
RANGEHANDLE BINARY_INTEGER : = - 1 ;
SELECTIONHANDLE BINARY_INTEGER : = - 1 ;
InteriorHandle BINARY_INTEGER : = - 1 ;
ColorIndexHandle BINARY_INTEGER : = - 1 ;
BEGIN
-- DBMS_OUTPUT.PUT_LINE('Setting Color to cell ' + var_CellRange + 'with ColorIndex ' + TO_CHAR(var_ColorIndex));
-- Setting parameters
ORDCOM.InitArg();
ORDCOM.SetArg(var_CellRange, ' BSTR ' );
-- ORDCOM.SetArg(var_ColorIndex,'I2');
VAR_RESULT : = ORDCOM.GetProperty(CurrSheetHandle, ' Range ' , 1 , RANGEHANDLE);
IF VAR_RESULT != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error inserting data: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
VAR_RESULT : = ORDCOM.Invoke(RANGEHANDLE, ' SELECT ' , 0 , DummyHandle);
IF VAR_RESULT != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error inserting data: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
DBMS_OUTPUT.PUT_LINE( ' ApplicationHandle SELECTION ' );
VAR_RESULT : = ORDCOM.GetProperty(ApplicationHandle, ' Selection ' , 0 , SELECTIONHANDLE);
IF VAR_RESULT != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error inserting data: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
DBMS_OUTPUT.PUT_LINE( ' CurrSheetHandle SELECTION ' );
VAR_RESULT : = ORDCOM.GetProperty(SELECTIONHANDLE, ' Interior ' , 0 , InteriorHandle);
IF VAR_RESULT != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error inserting data: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
VAR_RESULT : = ORDCOM.SetProperty(InteriorHandle, ' ColorIndex ' , var_ColorIndex, ' I2 ' );
IF VAR_RESULT != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
DBMS_OUTPUT.PUT_LINE( ' Error inserting data: ' || hresult);
DBMS_OUTPUT.PUT_LINE(error_src);
DBMS_OUTPUT.PUT_LINE(error_description);
DBMS_OUTPUT.PUT_LINE(error_helpfile);
END IF ;
-- destroy the RangeHandle object
VAR_RESULT : = ORDCOM.DestroyObject(RANGEHANDLE);
RETURN VAR_RESULT;
END SetCellColor;
end XLdemo;
/