VSTO：使用C#開發Excel、Word【11】

標籤：路徑   get   div   多個   cell   擷取   string   工作表   static   

編程使用者定義的功能
Excel可以建立可在Excel公式中使用的使用者定義的函數。 開發人員必須建立一種稱為XLL的特殊類型的DLL。 Excel還允許您在VBA中編寫可在Excel公式中使用的自訂函數。 不幸的是，Excel不支援或建議編寫使用Managed 程式碼的XLL。

構建提供使用者定義函數的管理型自動化附加元件
幸運的是，建立一個不需要建立XLL的使用者定義函數有一個更簡單的方法。 Excel 2003支援稱為自動化附加元件的定製技術，可以輕鬆地在C＃或Visual Basic中建立。

首先，啟動Visual Studio並建立一個新的C＃類庫項目。將項目命名為AutomationAddin。在您在新項目中為您建立的Class1.cs檔案中，輸入如清單3-1所示的代碼。該代碼定義了一個名為MyFunctions的類，它實現了一個名為MultiplyNTimes的函數。我們將使用此函數作為自訂公式。我們的類還實現了RegisterFunction和UnregisterFunction，它們分別歸因於ComRegisterFunction屬性和ComUnregisterFunction屬性。當COM程式集註冊為COM互操作時，RegisterFunction將被調用。當彙編未註冊COM互操作時，UnregisterFunction將被調用。這些功能在註冊表中放置了一個必要的密鑰，允許Excel知道該類可以用作自動化附加元件。

清單3-1 一個C＃類被稱為MyFunction，它暴露了一個使用者定義的函數乘法

using System;using System.Runtime.InteropServices;using Microsoft.Win32;namespace AutomationAddin{  [ClassInterface(ClassInterfaceType.AutoDual)]  public class MyFunctions  {    public MyFunctions()    {    }    public double MultiplyNTimes(double number1, double number2, double timesToMultiply)    {      double result = number1;      for (double i = 0; i < timesToMultiply; i++)      {        result = result * number2;      }      return result;    }    [ComRegisterFunctionAttribute]    public static void RegisterFunction(Type type)    {      Registry.ClassesRoot.CreateSubKey( GetSubKeyName(type));    }    [ComUnregisterFunctionAttribute]    public static void UnregisterFunction(Type type)    {      Registry.ClassesRoot.DeleteSubKey（GetSubKeyName(type),false);    }    private static string GetSubKeyName(Type type)    {      System.Text.StringBuilder s = new System.Text.StringBuilder();      s.Append(@"CLSID\{");      s.Append(type.GUID.ToString().ToUpper());      s.Append(@"}\Programmable");      return s.ToString();    }  }}

使用這段代碼編寫，您需要修改項目，以便在構建COM互操作時自動註冊該類。 首先，通過雙擊“方案總管”中項目節點下的“屬性”節點來顯示項目的屬性。 在出現的屬性設計器中，單擊“構建”選項卡，然後選中“註冊COM Interop”複選框，3-7所示。 然後從Build菜單中選擇Build Solution來構建類庫項目。 您的操作將導致您的類庫項目正在構建以及在註冊表中作為自動化附加元件註冊。 Excel現在可以看到你的C＃類並使用它。

圖3-7 設定構建選項以註冊COM互操作

在Excel中使用管理的自動化附加元件
啟動Excel並從工具菜單中選擇附加元件以顯示附加元件對話方塊。 在附加元件對話方塊中，單擊自動化按鈕。 您可以通過在Automation 伺服程式列表中尋找AutomationAddin.MyFunctions找到您建立的類，3-8所示。

圖3-8  從“Automation 伺服程式”對話方塊中選擇“AutomationAddin.MyFunction”

單擊此對話方塊中的確定，您已將AutomationAddin.MyFunctions類添加到已安裝的自動化附加元件列表中，3-9所示。

圖3-9  AutomationAddin.MyFunction已安裝 

現在，嘗試在Excel公式中使用函數MultiplyNTimes。 首先建立一個簡單的試算表，其中包含一個數字，第二個數字乘以第一個數字，第三個數字用於將第一個數字乘以第二個數位次數。 試算表3-10所示。

圖3-10  一個簡單的試算表來測試自訂公式

單擊數字下方活頁簿中的空儲存格，然後單擊公式欄中的插入函數按鈕（帶有“fx”標籤的按鈕）。 從可用公式的對話方塊中，下拉“或選擇類別”下拉框，然後選擇AutomationAddin.MyFunction。 然後單擊“Multiplyntimes”功能，3-11所示。

圖3-11  從“插入函數”對話方塊中選取“乘數”

單擊“確定”按鈕後，Excel彈出對話方塊，協助從試算表中的儲存格中選擇功能參數，3-12所示。

圖3-12  設定函數參數

從相應的儲存格中選擇功能參數後，單擊“確定”建立最終的試算表，3-13所示，儲存格C5中的自訂公式。

圖3-13 最終的試算表

一些其他使用者定義的函數
您可以嘗試Excel公式中可以使用的其他功能。 例如，清單3-2顯示了可以添加到MyFunctions類的其他幾個函數。 要使用清單3-2，您必須添加對Excel 11.0物件程式庫的引用，並將代碼使用Excel = Microsoft.Off-ice.Interop.Excel添加到類檔案的頂部。 請注意，當您將參數聲明為對象時，Excel會傳遞一個Range對象。 還要注意AddNumbers函數支援選擇性參數。 當省略一個參數時，System.Type.Missing作為參數的值傳遞。

清單3-2  可以添加到MyFunctions類的其他使用者定義的函數

public string GetStars(double number){  System.Text.StringBuilder s =  new System.Text.StringBuilder();  s.Append(‘*‘, number);  return s.ToString();}public double AddNumbers(double number1,  [Optional] object number2, [Optional] object number3){  double result = number1;  if (number2 != System.Type.Missing)  {    Excel.Range r2 = number2 as Excel.Range;    double d2 = Convert.ToDouble(r2.Value2);    result += d2;  }  if (number3 != System.Type.Missing)  {    Excel.Range r3 = number3 as Excel.Range;    double d3 = Convert.ToDouble(r3.Value2);    result += d3;  }  return result;}public double CalculateArea(object range){  Excel.Range r = range as Excel.Range;  return Convert.ToDouble(r.Width) +  Convert.ToDouble(r.Height);}public double NumberOfCells(object range){  Excel.Range r = range as Excel.Range;  return r.Cells.Count;}public string ToUpperCase(string input){  return input.ToUpper();}

在管理型自動化附加元件中調試使用者定義的函數
您可以通過將Excel設定為您的類庫項目在調試時啟動的程式來調試作為自動化附加元件的C＃類庫項目。 通過雙擊“方案總管”中項目節點下的“屬性”節點來顯示項目的屬性。 在出現的屬性設計器中，單擊“調試”選項卡，在“啟動外部程式”文字框中，鍵入Excel.exe的完整路徑，3-14所示。 現在，在您的一個使用者功能上設定斷點，按F5，然後使用試算表中的功能。 調試器將停止執行斷點設定的使用者函數。

圖3-14 設定調試選項以啟動Excel

部署管理型自動化附加元件
要部署自動化附加元件，請在方案總管中按右鍵解決方案，然後從“添加”菜單中選擇“建立項目”。 從“添加新項目”對話方塊中，在“項目類型”樹中選擇“從其他項目類型\安裝和部署”中的“安裝項目”。

按右鍵方案總管中添加的安裝項目，然後從添加菜單中選擇項目輸出。 從“添加項目輸出組”對話方塊中，選擇“AutomationAddin”項目，選擇“主要輸出”，3-15所示。

圖3-15  將Automation Addin項目的主輸出添加到安裝項目中

 

因為我們告訴項目註冊我們用於COM互操作的託管對象，所以安裝項目應該已經被正確設定，以便在安裝時註冊COM互操作的託管對象。 要驗證此，請單擊安裝項目中AutomationAddin節點的主輸出。 在主輸出的屬性視窗（我們的C＃DLL）中，確保將Register設定為vsdrpCOM。

 

Excel物件模型簡介
無論您選擇將代碼與Excel整合在一起，您最終都需要與Excel物件模型進行交流，以完成任務。 在本書中完全描述Excel物件模型是不可能的，但我們嘗試讓您熟悉Excel物件模型中最重要的對象，並顯示這些對象上最常用的方法，屬性和事件。

對象階層
學習Excel物件模型的第一步是擷取物件模型階層的基本結構。 圖3-16顯示了Excel物件模型中最關鍵的對象及其層次關係。

 圖3-16   Excel物件模型的基本階層

一個Workbook對象有一個名為Sheets的集合。 “表”集合可以包含“工作表”或“圖表”類型的對象。 圖表有時被稱為圖表，因為它涵蓋了工作表將涵蓋的整個地區。 您可以通過按右鍵Excel活頁簿左下角的工作表選項卡並選擇“插入”，將工作表插入到活頁簿中。 圖3-17顯示出現的對話方塊。 請注意，在Sheets集合中還有兩個附加對象：MS Excel 4.0宏表和MS Excel 5.0對話方塊。 如果將宏表單或對話方塊插入到Excel活頁簿中，則將其視為特殊類型的工作表，而不是與宏表或對話方塊對應的特殊物件模型類型。

圖3-17  將各種“工作表”插入到Excel活頁簿中

因為活頁簿可以包含這些各種對象，Excel會從Workbook對象中提供多個集合。 Worksheets集合僅包含活頁簿中的Worksheet對象。 “圖表”集合僅包含活頁簿中的圖表。 Sheets集合是兩者的混合集合。 Sheets集合將集合的成員作為類型對象返回，您必須將返回的對象轉換為工作表或圖表。在這本書中，當我們談論一個可能是工作表或圖表的對象時，我們將其稱為工作表。

圖3-18顯示了一個更完整的階層樹，其主要對象與圖3-16中的對象相關聯。這將開始讓您瞭解Excel物件模型中對象的廣泛階層，特別是當您意識到該圖顯示的可用對象不足一半時。以灰色顯示的對象來自Microsoft.Office.Core命名空間，它與Microsoft Office 11.0 PIA（office.dll）相關聯。這些對象由所有Office應用程式共用。

圖3-18  Excel物件模型中某些主要對象的詳細階層

 

圖3-19顯示了與Range相關聯的對象階層，這是Excel中非常重要的一個對象，代表您要在代碼中處理的儲存格範圍。 我們已經使用了清單3-2中的RangXe對象。

圖3-19  與Excel物件模型中的Range相關聯的對象的更詳細階層

圖3-20顯示了與Shapea Shape相關聯的對象階層，表示浮動在工作表上不是儲存格的東西，例如嵌入的按鈕，圖形，注釋氣泡等。

圖3-20 在Excel物件模型中與Shape相關聯的對象的更詳細階層

結論
本章介紹了將代碼整合到Excel中的各種方法。 本章介紹如何構建自動化附加元件來為Excel建立使用者定義的函數。 您還學習了Excel物件模型的基本階層。 第4章“使用Excel事件”討論Excel物件模型中的事件。 第5章“使用Excel對象”涵蓋了Excel物件模型中最重要的對象。

 

VSTO：使用C#開發Excel、Word【11】