使用 .NET 扩展 Excel
4.50/5 (5投票s)
本文演示如何使用 VS2008 自动化 Excel，而不是使用 VSTO 或 VBA。
引言
使用“Excel”作为搜索关键字会得到近 700 篇 CodeProject 文章。诚然，其中有相当一部分文章充其量只是间接地提到了 Excel，但那些真正专注于 Excel 的文章似乎都没有解决一个非常简单的问题：如何在 Excel 应用程序中创建菜单项，让用户无需借助 VSTO 或开发外接程序，即可与用 .NET 编写的代码进行交互。
本文旨在填补这一空白。我打算提供一种简单的方法来创建自定义 Excel 菜单项并与之交互，而无需借助 Microsoft 的 Visual Studio Tools for Office (VSTO) 或 Visual Basic for Applications (VBA)。这种方法利用了 .NET 平台以及 MS Excel 的 VB 和 C# 对象模型，这些在之前的许多 CodeProject 文章中都有所阐述。为了节省篇幅，我只引用了两篇我认为特别有帮助的文章。修改 MS Excel 的菜单相当简单，因为其命令栏结构相对简单。对于 Outlook 来说，它要复杂得多，我已经为此发表了一篇单独的文章。这里介绍的代码适用于 Excel2003 和 Excel2007，但在这两个版本中，添加的菜单项的位置略有不同：在 Excel2003 中，菜单项将出现在主菜单栏中，而在 Excel2007 中，它们将显示在菜单栏的“加载项”部分。
背景
自动化 MS Excel 是一个非常丰富的话题，提供了大量机会来改进内置功能。有三种方法可以完成此任务
- 使用 VSTO 开发外接程序
- 编写 VBA 代码
- 使用 Visual Studio 开发自动化可执行文件
E.Carter 和 E.Lippert 在《Visual Studio Tools for Office 2007: VSTO for Excel, Word and Outlook》（Addison-Wesley，2009 年 3 月）中很好地讨论了这三种策略的相对优点，但重点是 VSTO 外接程序的开发。外接程序的开发似乎出了名的困难：调试并非易事，并且在目标机器上正确注册往往不稳定。使用 VBA 相当受限，因为它目前无法完全访问 .NET 平台。使用 VS2008 开发自动化可执行文件似乎是最好的选择：它是一个我们熟悉且舒适的开发环境，并提供了 VS2008 集成开发环境所提供的全套调试技术。此外，最重要的是，对于我们中间的纯粹主义者来说，生成的代码在完全调试后可以相对容易地在外接程序中实现。
Using the Code
我在这里介绍的代码旨在为您提供开发自己的自动化产品所需的框架。您需要向您的 VS2008 项目添加以下引用
- Microsoft Office 11.0（或 12.0）对象库
- Microsoft Excel 11.0（或 12.0）对象库
代码分为三个独立的类。`ExcelMenuExtensions` 类（文件名：ExcelSidekickCSharp.cs）完成了以下基本步骤
- 它创建了一个新的 Excel 实例。
- 它将用户指定数量的单个菜单按钮和一个包含用户指定数量菜单项的下拉菜单项添加到 Excel 实例的“工作表菜单栏”命令栏中。
`Program` 类（文件名：Program.cs）包含一个简单示例，演示如何实现 `ExcelMenuExtensions` 类。它创建 `ExcelMenuExtensions` 类的一个实例，并实现一组简单的菜单项和一个下拉菜单，并将自定义菜单项的点击事件连接到简单的事件处理程序。当使用此框架与 Excel 配合使用时，开发人员只需将他们的自定义代码包含在这些事件处理程序中。
由于我已经包含了代码，所以我决定不在本文中重现代码列表，而是专注于演示实现 `ExcelMenuExtensions` 类的相对简单性。以下是实现的核心（完整代码请参阅 Program.cs；我稍后将解释对 API 方法 `SetForegroundWindow` 的引用的原因以及引用 LicenseGenie 的注释行的目的）
class Program
{
public class InteractiveExcel
{
[DllImport("user32.dll")]
private static extern bool SetForegroundWindow(int hWnd);
ExcelMenuExtensions xL;
private Excel.Application c_ExcelApp;
private const string CAPTION1 = "New Button 1";
private const string CAPTION2 = "New Button 2";
private const string CAPTION3 = "New Popup";
private const string CAPTION4 = "Item 1";
private const string CAPTION5 = "Item 2";
private const string CAPTION6 = "Item 3";
private object c_Missing = System.Type.Missing;
static void Main()
{
InteractiveExcel t = new InteractiveExcel();
}
public InteractiveExcel()
{
//LicenseGenie myLicenseGenie = new LicenseGenie();
//if (!myLicenseGenie.LicenseIsValid()) return;
int nMenuItems = 2;
int nPullDownMenuItems = 3;
xL = new ExcelMenuExtensions(nMenuItems, nPullDownMenuItems);
xL.c_CustomMenuItem_Captions = new string[2] { CAPTION1, CAPTION2 };
xL.c_CustomMenuItem_Tags = new string[2] { CAPTION1, CAPTION2 };
xL.c_CustomMenuPullDownItem_Caption = CAPTION3;
xL.c_CustomMenuPullDownMenuItem_Captions = new string[3] { CAPTION4,
CAPTION5, CAPTION6 };
xL.SetUpMenu();
EnableMenuItems(true);
c_ExcelApp = xL.c_ExcelApp;
c_ExcelApp.WorkbookNewSheet +=
new Excel.AppEvents_WorkbookNewSheetEventHandler(
c_ExcelApp_WorkbookNewSheet);
c_ExcelApp.WorkbookActivate +=
new Excel.AppEvents_WorkbookActivateEventHandler(
c_ExcelApp_WorkbookActivate);
c_ExcelApp.SheetSelectionChange +=
new Excel.AppEvents_SheetSelectionChangeEventHandler(
c_ExcelApp_SheetSelectionChange);
for (int i = 0; i < xL.c_CustomMenuItem_Captions.Length; i++)
xL.c_CustomMenuItem[i].Click +=
new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
CustomMenuItem_Click);
for (int i = 0; i < xL.c_CustomMenuPullDownMenuItem_Captions.Length; i++)
xL.c_CustomMenuPullDownMenuItem[i].Click +=
new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(
CustomMenuItem_Click);
WaitForEvents();
}
...
}
`WaitForEvents()` 方法的代码很简单。它只是为了确保您的应用程序监听 MS Excel 应用程序触发的事件。当 Excel 实例变得不可见时，代码终止（这可能不是确定 Excel 实例是否已终止的最佳方法，但它的效果足够好）。
private void WaitForEvents()
{
do
{
Application.DoEvents();
if (!c_ExcelApp.Visible)
{
break;
}
Thread.Sleep(500);
} while (true);
}
在示例实现中，自定义菜单项保持禁用状态，直到工作簿被打开。我只为所有自定义菜单项使用了一个事件处理程序，它根据点击项的标签切换到每个菜单项所需的方法。以下是此事件处理程序的缩写代码
private void CustomMenuItem_Click(Microsoft.Office.Core.CommandBarButton Ctrl,
ref bool CancelDefault)
{
switch (Ctrl.Tag)
{
case CAPTION1:
CustomMethod1();
break;
case CAPTION2:
CustomMethod2();
break;
...
default:
break;
}
}
在示例实现中，分配给每个自定义菜单项的自定义方法没有太多功能，仅用于演示目的
- “新按钮 1”只是向活动工作表的“A”列添加一系列数字，然后将其字体更改为斜体。
- “新按钮 2”为每个单元格创建边框，并在活动工作表中用户选择的范围内，将每隔一行着色
- 单击下拉菜单中的菜单项时，只会显示一个表单，在状态栏中指示单击了哪个菜单项，并显示“您点击了…”一秒钟。
由于我无法确定的原因，在事件处理程序中使用 `MessageBox.Show()` 方法无法可靠地将生成的 MessageBox 冒泡到窗口堆栈的顶部，至少在事件处理程序第一次被调用时（在随后的调用中它似乎工作正常）。我通过使用自定义表单的 `ShowDialog()` 方法改进了这种行为，我将其 `Activated` 事件连接到以下事件处理程序，该事件处理程序通过使用 API 函数 `SetForegroundWindow` 确保自定义表单始终冒泡到顶部
private void alert_Activated(object sender, EventArgs e)
{
SetForegroundWindow(((Form)sender).Handle.ToInt32());
}
我推荐参考资料部分中的文章和书籍，以进一步学习如何编程 Excel 对象模型。遗憾的是，Excel 的 VB 和 C# 对象模型彼此之间存在显著差异。我没有在 VB.NET 中重新创建 `ExcelMenuExtensions`，而是包含了一个 dll 版本（GenericExcelSidekick.dll），它可以在 VB.NET 项目中使用，方法是首先显式地将对 GenericExcelSidekick.dll 的引用添加到您的项目中，然后仔细阅读我的示例实现程序的 VB 版本（Program.vb）。
最后，正如所承诺的，关于引用我称之为 `LicenseGenie` 的类的注释行说几句。按照所示实现此类的应用程序将整合一个完整的用户许可管理框架，而无需编写任何额外的代码！如果您觉得这很有趣，我邀请您访问 SoarentComputing 的网站，了解更多关于 LicenseGenie 的信息。
关注点
在这个项目中我学到的主要事情是 Excel 模型的 VB 和 C# 版本彼此之间的差异有多大。即使是 VB 模型也不完全等同于老牌 VBA，但仍然足够接近，任何有 VBA 经验的人都应该相对容易上手。我还了解到，这两个版本都不像 VBA 版本那样完整。例如，我还没有找到 Selection 属性或 `ActiveSheet.Range()L` 方法的等效项。这是一个 VBA 代码示例，它在 Excel 对象模型的 VB 或 C# 版本中似乎没有完全等效的项
Dim r As Range
Set r = ActiveSheet.Range("A1:B10")
r.Select
Selection.Value = 1
在 VB 中，以下代码产生相同的结果（其中 `c_ExcelApp` 表示在 Program.vb 中实例化的 Excel 对象）
Dim r As Excel.Range = c_ExcelApp.Range("A1:B10")
r.Value = 1
在 C# 中，这将是这样的（同样，`c_ExcelApp` 表示在 Program.cs 中实例化的 Excel 对象）
Excel.Range r = c_ExcelApp.get_Range("A1", "B10");
r.Value2 = 1;
参考文献
- [2] “Excel 2003 编程：开发者手册”，J.Webb，O'Reilly，2004
- [3] “使用 VBA 和 .NET 编程 Excel”，J.Webb 和 S.Saunders，O'Reilly，2006
- [5] “Visual Studio Tools for Office 2007：适用于 Excel、Word 和 Outlook 的 VSTO”，E.Carter 和 E.Lippert，Addison-Wesley，2009
历史
初版于 2009 年 7 月发布。