在Oracle中COM函数操作Excel文件

最新推荐文章于 2021-04-06 06:26:12 发布

原创最新推荐文章于 2021-04-06 06:26:12 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#excel #oracle #integer #function #parameters #vba

本文介绍如何使用PL/SQL调用Excel COM对象，实现Excel文件的创建、编辑与保存等功能。涉及的关键步骤包括启动Excel应用程序、创建新的工作簿及工作表、向单元格插入数据并设置颜色等。

早就知道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;

以下是具体的实现

CREATE OR REPLACE PACKAGE BODY Oracle_Excel_b IS

-- ====================================

-- 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

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

-- 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

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

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

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

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;