如何使用VSTO Add-in项目轻松创建Excel UDF

Question

我想做的是使用VSTO的C#“Excel 2007 Add-in”项目类型为Excel创建用户定义函数（UDFs）（因为我只想生成一些通用UDF）。由于我只想学习基础知识（无论在这个阶段），所以我的代码如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;
using System.Runtime.InteropServices;

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {}

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {}

        //My UDF
        public static double HeronicCal(int a, int b, int c)
        {
            //first compute S = (a+b+c)/2
            double S = (a + b + c) / 2;    
            double area = Math.Sqrt(S * (S - a) * (S - b) * (S - c));
            return area;
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }            
        #endregion
    }
}

代码编译通过，运行后Excel弹出一个新的电子表格，在Excel选项中查看“加载项”列表时，可以看到我的加载项（设置为“启动时加载”）。但是问题来了，当我尝试从Excel内部调用我的UDF时，Excel找不到该方法！

我想可能是因为我需要将我的方法标记为Excel UDF（使用方括号，就像编写Web服务时所做的那样 - “[WebService]”）。但我一直没有找到这个标记（而且由于我根本不确定我的猜测是否正确），这就是为什么我决定向SO等优秀的人寻求帮助。

所以我的问题基本上是 - 从我的代码位置出发，是否有任何简单的方法使我的UDF可供Excel使用？如果有，如何实现？

我真的很想坚持使用VSTO项目类型（Add-In、Workbook、Template），因为我的当前项目的总体目标是确定使用VS2010/Excel2007执行C# UDF是否具有可接受的速度。为了测试这一点，我正在Windows7RC上工作，并使用VS2010 beta1。

- user118193

Answer 1

VSTO不支持创建Excel用户定义函数(UDFs)。可以使用.NET创建Automation Add-Ins，这似乎是微软推荐的方式。

您可以查看ExcelDna-http://www.codeplex.com/exceldna。 ExcelDna允许托管程序集通过本地的.xll接口向Excel公开用户定义函数(UDFs)和宏。该项目是开源的，并允许商业使用。而且您会发现，基于.NET的UDF的性能与Excel的本地.xll添加程序类似。支持Excel 2007功能，例如大型工作表、长的Unicode字符串和多线程重新计算。

使用ExcelDna，您所发布的代码将在没有VSTO的情况下暴露给Excel-您可以将代码放入基于xml的.dna文件中，也可以将其编译为.dll文件。

暴露您的UDF的.dna文件如下：

<DnaLibrary Language="C#">
   using System;
   using ExcelDna.Integration;
   
   public class MyFunctions
   {
      [ExcelFunction(Description="Calculate Stuff", Category="Cool Functions")]
      public static double HeronicCal(int a, int b, int c)
      {
         //first compute S = (a+b+c)/2
         double S = (a + b + c) / 2;
         double area = Math.Sqrt(S * (S - a) * (S - b) * (S - c));
         return area;        
      }
   }
</DnaLibrary>

更新：现在，使用Excel-DNA最简单的方法是在Visual Studio中创建一个新的类库项目，然后从NuGet添加“ExcelDna.AddIn”软件包。这将创建一个入门级别的插件-只需粘贴您的代码并按F5运行即可。

更新2: 可以查看完整的VSTO + ExcelDna工作示例。

- Govert

Answer 2

看起来Eric Carter这里有一个得胜者:

https://learn.microsoft.com/zh-cn/archive/blogs/eric_carter/3-vsto-bug-tracker-a-udf

它是纯.NET的 - 不依赖第三方库。

现在正在试用中...

- harvest316

---

在我看来，这个答案应该排名更高。没有第三方库和非常少的样板代码是很大的优点。 - Sebastiaan

链接已损坏。 - AgostinoX

我相信这是@harvest316所提到的文章。https://learn.microsoft.com/en-us/archive/blogs/eric_carter/3-vsto-bug-tracker-a-udf. - maoyang

Answer 3

据我所知，您不能直接在VSTO中创建UDF。请参见Paul Stubbs的文章How to create Excel UDFs in VSTO managed code，他使用VBA插件来公开VBA UDF，然后调用他在VSTO中编写的托管UDF。

但是，在不使用VSTO时，您可以使用托管代码来创建UDF。请参见Eric Carter的文章Writing user defined functions for Excel in .NET以了解如何实现。

至于VSTO的执行速度，我认为对于几乎所有任务，您都会发现它很好。但是，遍历单元格（这已经是Excel的弱点）可能会非常缓慢，具体取决于您正在执行的操作。尽可能批量执行操作。例如，不要逐个遍历单元格，而是从区域返回一个二维值数组，处理该数组，然后将其传回范围。

为了演示，以下代码将返回一个二维数组，其中包含区域内的值，处理这些值，然后一次性将结果数组传回原始区域：

Excel.Range rng = myWorksheet.get_Range("A1:D4", Type.Missing);

//Get a 2D Array of values from the range in one shot:
object[,] myArray = (object[,])rng.get_Value(Type.Missing);

// Process 'myArray' however you want here.
// Note that the Array returned from Excel is base 1, not base 0.
// To be safe, use GetLowerBound() and GetUpperBound:
for (int row = myArray.GetLowerBound(0); row <= myArray.GetUpperBound(0); row++)
{
    for (int column = myArray.GetLowerBound(1); column <= myArray.GetUpperBound(1); column++)
    {
        if (myArray[row, column] is double)
        {
            myArray[row, column] = (double)myArray[row, column] * 2;
        }
    }
}

// Pass back the results in one shot:
rng.set_Value(Type.Missing, myArray);

希望这有所帮助！Mike

- Mike Rosenblum

Answer 4

我发现保持UDF作为VB模块可以避免与COM对象的问题，这种方法效果很好。

我有大量的C#代码正在运行，当我准备构建发布版本时，我会执行以下操作：
1. 添加一个模块：
   开发人员[Excel中的选项卡] | Visual Basic -> 项目窗口，右键单击，插入模块
   - 只需将VB代码复制/粘贴到此处
2. 包括适当的引用库（在同一VB窗口中的工具）
3. 将Excel文件保存为.xlsm（即启用宏）

然后您可以删除.xlsx文件。

我所做的是压缩整个目录（例如，“发布”），并将其发送给我们的用户。

- Gary Huckabone