在Oracle中COM函数操作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
   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;
/