在Excel中创建自定义函数（UDF）是一项非常有用的技能，它可以帮助扩展Excel的功能，实现更复杂的数据处理。本文将介绍两种在Excel中使用C#创建UDF的方法：自动化插件方法和XLL插件方法。这两种方法各有优缺点，将通过示例项目来演示如何实现它们，并在最后进行比较。
自动化插件是一种通过COM接口从Excel工作表公式中调用.NET函数的方法。自Excel 2002起，这种方法就被支持。要创建自定义函数，需要在Visual Studio中创建一个新的C#类库项目，然后进行以下操作：
以下是创建基类的示例代码：
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace ExcelUdf.Automation
{
public abstract class UdfBase
{
[ComRegisterFunction]
public static void ComRegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetClsIdSubKeyName(type, "Programmable"));
// 注册mscoree.dll的完整路径
var key = Registry.ClassesRoot.OpenSubKey(
GetClsIdSubKeyName(type, "InprocServer32"), true);
if (key == null)
{
return;
}
key.SetValue("", String.Format("{0}\\mscoree.dll", Environment.SystemDirectory), RegistryValueKind.String);
}
[ComUnregisterFunction]
public static void ComUnregisterFunction(Type type)
{
// 添加"Programmable"注册表键
Registry.ClassesRoot.DeleteSubKey(GetClsIdSubKeyName(type, "Programmable"));
}
private static string GetClsIdSubKeyName(Type type, String subKeyName)
{
return string.Format("CLSID\\{{{0}}}\\{1}", type.GUID.ToString().ToUpper(), subKeyName);
}
// 从Excel中隐藏这些方法
[ComVisible(false)]
public override string ToString()
{
return base.ToString();
}
[ComVisible(false)]
public override bool Equals(object obj)
{
return base.Equals(obj);
}
[ComVisible(false)]
public override int GetHashCode()
{
return base.GetHashCode();
}
}
}
然后，UDF类应该继承UdfBase，如下所示：
using System.Runtime.InteropServices;
using ExcelUdf.Automation;
namespace AutomationSample
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[Guid("7a9de936-0e99-4d37-9c2b-a02a09fb371f")]
public class AutomationSample : UdfBase
{
public double AutomationSampleAdd(double a, double b)
{
return a + b;
}
public double AutomationSampleSubtract(double a, double b)
{
return a - b;
}
}
}
构建项目后，最后一步是打开Excel文件，转到“文件”>“选项”，然后选择“加载项”。在下拉列表中选择“Excel加载项”，然后点击“转到…”。选择“自动化”按钮并选择组件（在这个例子中，要选择的项目名称是AutomationSample.AutomationSample）。
在工作表单元格中输入=AutomationSampleAdd(1,2)，应该得到3。
上述方法允许Excel调用.NET，但不能反过来。如果想要有对执行.NET代码的Excel应用程序的引用，例如根据某些标准为某些工作表列上色或进行异步回调，需要实现IDTExtensibility2接口。
要实现这种方法，需要引用右侧显示的程序集，继承UdfBase抽象类并实现IDTExtensibility2接口。以下是实现IDTExtensibility2接口的示例代码：
using System;
using ExcelUdf.Automation;
using Extensibility;
using Microsoft.Office.Interop.Excel;
namespace ExcelUdf.ExtensibilityAutomation
{
public abstract class UdfExtensibilityBase : UdfBase, IDTExtensibility2
{
protected Application ExcelApplication { get; set; }
public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
{
ExcelApplication = application as Application;
}
public void OnDisconnection(ext_DisconnectMode removeMode, ref Array custom)
{
}
public void OnAddInsUpdate(ref Array custom)
{
}
public void OnStartupComplete(ref Array custom)
{
}
public void OnBeginShutdown(ref Array custom)
{
}
}
}
在下载项目中，在一个独立的项目中实现了这个类，而不是与现有的项目结合。原因是这种方法需要引用特定版本的Excel互操作组件。一旦有了这些引用，部署项目就会变得更加复杂，因为现在需要管理更多的依赖项，并确保目标机器上安装了正确版本的Excel（如果想避免这个问题，请查看NetOffice）。
以下是创建UDF方法并获取当前Excel实例引用的示例代码：
using System.Runtime.InteropServices;
using ExcelUdf.ExtensibilityAutomation;
namespace ExtensibilitySample
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[Guid("7a9de936-0e99-4d38-9c2b-a02a09fb371f")]
public class ExtensibilitySample : UdfExtensibilityBase
{
public double ExtensibilitySampleAdd(double a, double b)
{
return a + b;
}
public string WhoAreYou()
{
string name = ExcelApplication.Application.InputBox("Who are you?");
if (string.IsNullOrWhiteSpace(name))
{
return string.Empty;
}
return "Hello " + name;
}
}
}
按照上述方法使用这个项目与Excel。
XLL是Excel的插件，可以使用任何支持构建原生DLL（动态链接库）的编译器来构建它，自Excel 97起就被支持。它比自动化插件更快，并且具有更多的功能，但XLL组件通常是由C/C++构建的。
幸运的是，对于.NET来说，有一个名为Excel DNA的开源组件，它允许.NET轻松构建XLL插件。要构建XLL组件，创建一个新项目，下载Excel DNA并引用ExcelDna.Integration.dll，然后编写函数：
using ExcelDna.Integration;
namespace XllSample
{
public class XllSample
{
[ExcelFunction(Description = "Adds two numbers", Category = "XLL with .NET Sample Function")]
public static double XllSampleAdd(double a, double b)
{
return a + b;
}
}
}
构建后，创建一个名为YourDllName.dna的文件，在本例中为XllSample.dna，内容如下：
<DnaLibrary RuntimeVersion="v4.0">
<ExternalLibrary Path="XllSample.dll" />
</DnaLibrary>
将其放在DLL旁边，然后将ExcelDna.xll或ExcelDna64.xll复制到DLL旁边，并将其重命名为与DLL名称匹配的名称，在本例中为XllSample.xll。
构建项目后，最后一步是打开Excel文件，转到“文件”>“选项”，然后选择“加载项”。在下拉列表中选择“Excel加载项”，然后点击“浏览”按钮并选择XllSample.xll。
在Excel单元格中开始输入XllSampleAdd，将通过Excel的自动完成功能获得函数的其余部分。
以下是两种方法的比较表：
| 自动化插件 | XLL插件 | |
|---|---|---|
| 最低支持版本 | Excel 2002 | Excel 97 |
| 性能 | 较慢 | 更快 |
| UDF自动完成 | 不支持 | 支持 |
| UDF文档提示 | 不支持 | 支持 |
| 在.NET中构建 | 更容易 | 更难（没有第三方组件） |
自动化插件支持更像是为VB6而不是.NET设计的，并且缺少一些重要功能，如在单元格中输入时的自动完成和描述。XLL插件从.NET开发的角度来看比较复杂，但是Excel DNA在使接口透明和抽象所有复杂细节方面做得非常出色。