一、附加元件簡介
Office提供了多種用于擴充Office應用程式功能的模式,常見的有:
1、Office 自動化程式(Automation Executables)
2、Office附加元件(COM or Excel Add-In)
3、Office文檔代碼或模闆(Code Behind an Office Document or Template)
4、Office 智能标簽(Smart Tags)
本次我們将學習使用VSTO 4.0編寫一個簡單的Office COM 附加元件,建構于Office 2010 x64.
<a href="http://files.cnblogs.com/brooks-dotnet/T-SQL/2010.03.01_VSTO_3_ExcelCOMAddInDemo.rar" target="_blank">示例代碼下載下傳</a>
本系列所有示例代碼均在 Visual Studio 2010 Ultimate RC 和 Office 2010 Professional Plus Beta x64 中測試通過。
二、為什麼要使用附加元件
Office附加元件提供了一種擴充應用程式核心功能的機制,由此添加的功能可以在整個應用程式或單個應用程式中使用,極大的擴充了Office的應用領域:
1、擴充現有功能:對于特定需求,特别是和業務緊密相關的需求,如果能夠以附加元件的方式在Office中解決,那麼将節省軟體成本、教育訓練成本等。
2、資料整合:随着Office由辦公平台正式轉變為一個計算平台之後,将Office與其他平台進行整合的需求也變得愈來愈頻繁。如将Web伺服器中資料導入到Excel中,将SAP系統中資料導入到Excel中,生成日報表等。
三、COM附加元件的工作原理
要使用COM附加元件,必須要在系統資料庫中寫入相應的資訊。Office 要判斷使用哪些附加元件時,以Excel為例,需要檢視系統資料庫中的兩個位置:
1、HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\AddIns
這個位置是針對于特定使用者的,也是推薦的位置。在該位置注冊的COM附加元件将出現在Office中的COM附加元件對話框中:
在該位置,有幾項是必備的。
1)FriendlyName:字元串值,包含了顯示在COM對話框中的COM附加元件的名稱;
2)Description:字元串值,包含了COM附加元件的簡短描述資訊;
3)LoadBehavior:DWORD類型,用于描述COM附加元件的加載方式,通常設定為3(1 + 2).
值
描述
斷開,不加載COM附加元件
1
連接配接,加載 COM附加元件
2
啟動時加載,主應用程式啟動時加載并連接配接COM附加元件
8
需要時加載,主應用程式需要(觸發加載事件)時加載并連接配接COM附加元件
16
首次連接配接,使用者注冊附加元件後,首次運作主應用程式時加載并連接配接COM附加元件
除了上面的三個,還需要在HKEY_CLASSES_ROOT\CLSID下建立幾個系統資料庫項:
2、HKEY_LOCAL_MACHINE\Software\Microsoft\Office\Excel\AddIns
這個位置是針對于所有使用者的,但是這些附加元件對使用者隐藏,即不會出現在COM附加元件對話框中。
這些系統資料庫項如果純手工寫将會非常麻煩,而且容易出錯,幸運的是,Visual Studio提供了一組模闆來友善的建立Office附加元件,但是我們還是應該了解這些系統資料庫項及其代表含義。
四、了解IDTExtensibility2接口
所有Office應用程式都是用IDTExtensibility2接口與COM附加元件進行通信,該接口提供了一種通用的初始化機制,并具有在Office應用程式的對象模型中傳遞資料的能力,是以COM附加元件可以與Office應用程式通信。
IDTExtensibility2接口并不複雜,但是對于标注COM附加元件的加載順序,以及它在何處影響我們編寫的代碼來說,此接口至關重要。Office在對COM附加元件進行執行個體化時,會建立主Connect類,注意不能用Connect的構造函數建立類的執行個體,應該用OnConnection方法;類似的,附加元件的關閉不能調用析構函數,而要用OnDisconnection方法。
五、ExcelCOMAddInDemo
現在我們來動手開發一個簡單的Excel COM Add-In.
1、建立一個Share Add-In項目:
2、在彈出的項目向導中,點選【Next】:
3、選擇你所熟悉的語言,點選【Next】:
4、選擇COM 附加元件的目标宿主,點選【Next】:
5、輸入Add-In的名稱和描述,點選【Next】:
6、選擇"啟動時加載",點選【Next】:
7、點選【Finish】:
8、項目建立完成後會自動添加一個安裝項目,它會負責安裝生成的COM附加元件,處理系統資料庫的資訊等,無需我們手動參與。
Excel workbooks can be freely opened and edited in both 32- and 64-bit Excel; there is nothing architecture specific in a saved workbook. For custom code solutions, however, 64-bit Excel introduces some challenges:
ActiveX controls need to be ported – they need a 64-bit version to work in a 64-bit process. This includes Microsoft's own controls, of which many have been ported. We are evaluating the popularity and criticality of the remaining ones for possible porting.
COM add-ins, similarly, need to be compiled for 64-bit in order to work in 64-bit Excel.
VBA: Embedded VBA code gets re-compiled when the containing Excel workbook is opened on a new platform; 64-bit Excel now includes a 64-bit version of VBA. So most VBA code just works in 64-bit. However, a subset of VBA solutions needs some tweaking. It has to do with declarations and calls to external APIs with pointers/handles in the parameter list. VBA7, the new version of VBA that ships with Office 2010, supports the development of code that can run in both 32- and 64-bit Office.
摘自Excel 2010官方部落格,可以看出,在Office 2010 x64中,必須編寫64位的COM Add-In.
這不是問題,稍後我們将看到如何編譯64位的COM Add-In和生成64位的安裝檔案。
9、設定項目屬性:
在【Build】标簽中,将【Platform】設定為:Active(x64),取消【Register for COM】的選擇:
在【Debug】标簽中,将【Platform】設定為:Active(x64),同時将【Start Action】設定為Excel的安裝路徑,這樣做可以啟動Excel來調試COM Add-In:
配置編譯選項:
配置安裝項目屬性:
10、我們要做的是在Excel 的Ribbon中添加一個按鈕,點選後彈出一則歡迎資訊。
首先添加引用:
Microsoft.Office.Interop.Excel
System.Windows.Forms
打開Connect.cs,添加如下代碼:
代碼
namespace ExcelCOMAddInDemo
{
using System;
using Extensibility;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Windows.Forms;
#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the ExcelCOMAddInDemoSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion
/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("DDC49E0C-03FE-4134-9829-65EF0351CECE"), ProgId("ExcelCOMAddInDemo.Connect")]
public class Connect : Object, Extensibility.IDTExtensibility2
{
private Microsoft.Office.Interop.Excel.Application applicationObject;
private Microsoft.Office.Core.COMAddIn addInInstance;
private CommandBarButton simpleButton;
/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}
/// Implements the OnConnection method of the IDTExtensibility2 interface.
/// Receives notification that the Add-in is being loaded.
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// <param term='addInInst'>
/// Object representing this Add-in.
/// <seealso class='IDTExtensibility2' />
public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
applicationObject = application as Microsoft.Office.Interop.Excel.Application;
addInInstance = addInInst as Microsoft.Office.Core.COMAddIn;
/// Implements the OnDisconnection method of the IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// <param term='custom'>
/// Array of parameters that are host application specific.
public void OnDisconnection(Extensibility.ext_DisconnectMode disconnectMode, ref System.Array custom)
/// Implements the OnAddInsUpdate method of the IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has changed.
public void OnAddInsUpdate(ref System.Array custom)
/// Implements the OnStartupComplete method of the IDTExtensibility2 interface.
/// Receives notification that the host application has completed loading.
public void OnStartupComplete(ref System.Array custom)
CommandBars commandBars;
CommandBar standardBar;
commandBars = applicationObject.CommandBars;
// Get the standard CommandBar from Word
standardBar = commandBars["Standard"];
try
{
// try to reuse the button is hasn't already been deleted
simpleButton = (CommandBarButton)standardBar.Controls["Excel COM Addin"];
}
catch (System.Exception)
// If it's not there add a new button
simpleButton = (CommandBarButton)standardBar.Controls.Add(1);
simpleButton.Caption = "Excel COM Addin";
simpleButton.Style = MsoButtonStyle.msoButtonCaption;
// Make sure the button is visible
simpleButton.Visible = true;
simpleButton.Click += new _CommandBarButtonEvents_ClickEventHandler(simpleButton_Click);
standardBar = null;
commandBars = null;
/// Implements the OnBeginShutdown method of the IDTExtensibility2 interface.
/// Receives notification that the host application is being unloaded.
public void OnBeginShutdown(ref System.Array custom)
void simpleButton_Click(CommandBarButton ctrl, ref bool cancelDefault)
MessageBox.Show("Welcome to COM Add In");
}
}
聲明了一個CommandBarButton的執行個體,然後将其添加到CommandBar中,并關聯了一個Click事件,輸出一則歡迎資訊。
11、編譯成功後,安裝生成的setup.exe,執行安裝:
12、安裝成功後,打開Excel,會在【Add-In】中發現我們新添加的按鈕:
六、調試COM附加元件
1、通過前面的設定為Excel啟動來進行調試,這也是首先的方法,很友善。
2、可以通過附加Excel程序的方式來進行調試:
七、小結
本次主要學習了COM 附加元件的開發流程,對使用托管代碼開發COM元件有了初步的認識,尤其是IDTExtensibility2接口,在VSTO開發中占據重要地位,了解其與COM互動的過程是很有必要的。其次是64位COM附加元件的部署問題,與32位傳統附加元件有些差別,請注意差別對待。後續篇章會繼續深入介紹VSTO開發的内容,及其與其他技術的整合。