如何使用VSTO加载项项目轻松创建Excel UDF

What I am trying to do is to create User Defined Functions (UDFs) for Excel using VSTO’s C# “Excel 2007 Add-in”-project type (since I just want to generate some general UDFs). As I am only trying to learn the basics (at this stage anyhow) this is what my code looks like:

我想要做的是使用VSTO的C#“Excel 2007加载项” - 项目类型为Excel创建用户定义函数(UDF)(因为我只想生成一些通用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
    }
}

It compiles fine, and when I run it, Excel pops up with a fresh spreadsheet, and when I look at the “Add-Ins”-list (in Excel options) I can see my add-in on the list (which is set to “Load at Startup). But here comes my problem, when I try to call my UDF from with-in Excel, Excel can’t find the method!

它编译得很好,当我运行它时,Excel弹出一个新的电子表格,当我查看“加载项”列表(在Excel选项中)时,我可以在列表中看到我的加载项(已设置“在启动时加载”。但是这里出现了我的问题,当我尝试从Excel中调用我的UDF时,Excel无法找到该方法!

What I would imagine is wrong, is that I have to tag my method as an Excel UDF (using the square brackets – as for instance done when coding webservices -> “[WebService]”). But I haven’t been able to track down this tag (and since I am not sure at all if my hunch is correct), which is why I’ve decided to go to you fine people here at SO.

我想象的是错误的,我必须将我的方法标记为Excel UDF(使用方括号 - 例如在编写webservices时完成 - >“[WebService]”)。但是我无法追踪这个标签(因为如果我的预感是正确的,我根本不确定),这就是为什么我决定在这里找你们好的人。

So my question basically is – from where I am with my code is there any easy way to make my UDF accessible to Excel? If yes, how?

所以我的问题基本上是 - 从我的代码到哪里,有什么简单的方法让我的UDF可以访问Excel?如果有,怎么样?

I really would like to stay within the VSTO project-types (Add-In, Workbook, Template), since my overall goal for my current project is to establish whether or not C# UDF execution with VS2010/Excel2007 works at an acceptable speed. To test this I am working on Windows7RC and with the VS2010 beta1.

我真的希望保留在VSTO项目类型(加载项,工作簿,模板)中,因为我当前项目的总体目标是确定使用VS2010 / Excel2007执行C#UDF是否以可接受的速度运行。为了测试这个,我正在使用Windows7RC和VS2010 beta1。

4 个解决方案

#1

VSTO has no support for creating Excel UDFs. Automation Add-Ins can be created in .Net, and seem to be the Microsoft approved way of doing it.

VSTO不支持创建Excel UDF。自动化加载项可以在.Net中创建,并且似乎是Microsoft批准的方式。

You should have a look at ExcelDna - http://www.codeplex.com/exceldna. ExcelDna allows managed assemblies to expose user-defined functions (UDFs) and macros to Excel through the native .xll interface. The project is open-source and freely allows commercial use. And you'll find that the performance of your .Net-based UDF is similar to native .xll add-ins for Excel. Excel 2007 features like the large sheet, long Unicode strings and multi-threaded recalculation are supported.

你应该看一下ExcelDna - http://www.codeplex.com/exceldna。 ExcelDna允许托管程序集通过本机.xll接口将用户定义的函数(UDF)和宏公开给Excel。该项目是开源的,可以*地进行商业使用。您会发现基于.Net的UDF的性能类似于Excel的原生.xll加载项。 Excel 2007的功能包括大表,长Unicode字符串和多线程重新计算。

With ExcelDna your function as posted above will be exposed to Excel with no VSTO - you can put to code into an xml-based .dna file or compile it to a .dll.

使用ExcelDna,上面发布的函数将暴露给没有VSTO的Excel - 您可以将代码放入基于xml的.dna文件或将其编译为.dll。

The .dna file exposing your UDF would look like this:

暴露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>

Update: These days, the easiest way to get started with Excel-DNA is to make a new Class Library project in Visual Studio, then add the 'ExcelDna.AddIn' package from NuGet. That makes a starter add-in - just paste your code and press F5 to run.

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

#2

Looks like Eric Carter has a winner here:

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

http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx

It's pure .NET - no dependency on third party libraries.

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

Giving it a burl now...

现在给它一个洞穴......

#3

As far as I know, you cannot directly create UDFs in VSTO.

据我所知,你无法在VSTO中直接创建UDF。

See Paul Stubbs' article How to create Excel UDFs in VSTO managed code where he uses a VBA add-in to expose VBA UDFs, which in turn call his Managed UDFs written in VSTO.

请参阅Paul Stubbs的文章如何在VSTO托管代码中创建Excel UDF,其中他使用VBA加载项来公开VBA UDF,后者又调用VSTO编写的托管UDF。

You can use managed code to create UDFs, however, when not using VSTO. See Eric Carter's article Writing user defined functions for Excel in .NET on how to do this.

但是,不使用VSTO时,可以使用托管代码创建UDF。有关如何执行此操作,请参阅Eric Carter的文章为.NET中的Excel编写用户定义的函数。

As for VSTO's execution speed, I think you'll find it fine for virtually all tasks. Looping through cells, however, which is already Excel's weak-point, might be painfully slow, depending on what you are doing. Try to execute things in batch, as much as possible. For example, instead of looping through the cells one by one, return a two dimensional array of values from an area, process the array, and then pass it back to the range.

至于VSTO的执行速度,我认为你会发现几乎所有任务都很好。然而,通过细胞循环,这已经是Excel的弱点,可能会非常缓慢,这取决于你正在做什么。尝试尽可能批量执行。例如,不是逐个循环遍历单元格,而是从区域返回二维值数组,处理数组,然后将其传递回范围。

To demonstrate, the following will return a two dimensional array of values from an area, process the values, and then pass the resulting array back to the original area in one shot:

为了演示,以下内容将返回区域中的二维值数组,处理值,然后将结果数组一次性传递回原始区域:

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

Hope this helps!

希望这可以帮助!

Mike

#4

What I've found that works well is to keep the UDF as a VB module to avoid the issues with COM objects.

我发现效果很好的是将UDF保持为VB模块以避免COM对象的问题。

I've got tons of C# code running and when I'm ready to build the release I do the following:
1. Add a module:
Developer [tab in Excel] | Visual Basic -> project window, right-click, insert module
- merely copy/paste the VB code here
2. Include the appropriate reference library (Tools in same VB window)
3. Save the Excel file as an .xlsm (ie, Macro-Enabled)

我已经运行了大量的C#代码,当我准备构建版本时,我会执行以下操作:1。添加模块:开发人员[Excel中的选项卡] | Visual Basic - >项目窗口,右键单击,插入模块 - 只需在此处复制/粘贴VB代码2.包含相应的参考库(同一VB窗口中的工具)3。将Excel文件另存为.xlsm(即宏) - 启用)

You can then delete the .xlsx file.

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

What I do is zip the entire directory (eg, "Release") and send it to our users.

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

#1