如何使用VSTO外接程序项目轻松创建Excel UDF [英] How to easily create an Excel UDF with VSTO Add-in project

问题描述

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

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无法找到该方法！

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 UDF(使用方括号，例如在编码Web服务时执行->"[WebService]").但是我一直无法追踪到这个标签(而且由于我完全不确定我的直觉是否正确)，所以这就是为什么我决定在这里找你的好人.

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.

所以我的问题基本上是–从我的代码所在位置开始，有什么简单的方法可以使UDF对Excel可用?如果是，怎么办?

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?

我真的很想呆在VSTO项目类型(加载项，工作簿，模板)中，因为我当前项目的总体目标是确定VS2010/Excel2007的C#UDF执行是否可以接受速度.为了对此进行测试，我正在使用Windows7RC和VS2010 beta1.

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不支持创建Excel UDF.自动化加载项可以在.Net中创建，并且似乎是Microsoft认可的方法.

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.

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

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，上面发布的功能将在没有VSTO的情况下暴露给Excel-您可以将代码放入基于xml的.dna文件中，也可以将其编译为.dll.

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.

暴露您的UDF的.dna文件如下所示:

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

<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键即可运行.

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.

这篇关于如何使用VSTO外接程序项目轻松创建Excel UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！