在 Excel 中使用 C# .NET 用户定义函数 (UDF)

• Excel UDF with C# 示例 (203 KB)

注意：在本文中，我使用的是 Excel 2010 和 Visual Studio 2010。

在 VBA 中编写 UDF 以公开给 Excel 单元格非常直接，只需在 VBA 模块中编写函数即可。然而，使用托管语言（如 C# 或 F#）公开函数到 Excel 则稍微棘手一些。

本质上，有两种方法可以实现这一点，每种方法都有其优缺点。

1. 自动化加载项方法
2. XLL 加载项方法

我将演示每种方法的实现，然后讨论我的结论。我已经为每种方法创建了一个示例项目；您可以在本文末尾下载。

自动化加载项方法

自动化加载项是通过 COM 函数实现的，可以从 Excel 工作表的公式调用，自 Excel 2002 起就已支持。其思想是 .NET 可以公开一个 COM 接口，该接口可以通过自动化加载项支持从 Excel 中使用。

要创建自定义函数，您需要从 Visual Studio 创建一个新的 C# 代码库项目，然后转到
右键单击项目 > 属性 > 生成 > 注册为 COM 互操作并启用它。

然后转到 Assembly.cs 并将 ComVisible 设置为 true。之后，您需要创建一个基类，稍后将继承它来创建 UDF。

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"));

    // Solves an intermittent issue where Excel
    // reports that it cannot find mscoree.dll
    // Register the full path to 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)
{
    // Adds the "Programmable" registry key under CLSID
    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);
}

// Hiding these methods from 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 引用的自动化加载项方法

上述方法允许 Excel 调用 .NET，而不是反之。如果您想引用正在执行 .NET 代码的 Excel 应用程序怎么办？例如，根据某些条件为特定的工作表列着色，或者进行异步回调。在这种情况下，您需要实现 IDTExtensibility2 接口。

要实现此方法，您需要引用右侧显示的程序集，继承 UdfBase 抽象类并实现 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 加载项方法

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 在使接口透明化和为 .NET 开发人员抽象掉所有精巧的细节方面做得非常出色。

下载

我创建了一个包含上述所有示例代码的 Visual Studio 2010 项目，您可以随意使用和分发它。

• Excel UDF with C# 示例 (203 KB)