C# 如何使用 VSTO 插件项目轻松创建 Excel UDF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/957575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to easily create an Excel UDF with VSTO Add-in project
提问by
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（使用方括号 - 例如在编码 web 服务时所做的 ->“[WebService]”）。但是我一直无法找到这个标签（因为我完全不确定我的预感是否正确），这就是为什么我决定在 SO 去找你们这些优秀的人。
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?
所以我的问题基本上是 - 从我的代码所在的位置，是否有任何简单的方法可以让 Excel 访问我的 UDF？如果是，如何？
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 项目类型（加载项、工作簿、模板）中，因为我当前项目的总体目标是确定 C# UDF 与 VS2010/Excel2007 的执行是否以可接受的速度运行。为了测试这一点，我正在使用 Windows7RC 和 VS2010 beta1。
回答by Mike Rosenblum
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 codewhere 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 .NETon 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
麦克风
回答by Govert
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 接口向 Excel 公开用户定义的函数 (UDF) 和宏。该项目是开源的，可以自由地用于商业用途。您会发现基于 .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 即可运行。
回答by harvest316
Looks like Eric Carter has a winner here:
看起来埃里克卡特在这里有一个赢家：
http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx
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...
现在给它一个节...
回答by Gary Huckabone
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. 添加一个模块：
Developer [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.
我所做的是压缩整个目录（例如，“Release”）并将其发送给我们的用户。
