用C＃创建新的Excel公式/函数 [英] Creating new Excel formulas/functions with C#

问题描述

我们希望能够以编程方式创建一个可以从单元格内调用自定义代码的Excel工作簿。单元格看起来像：

  = MyCode（A1：A10）
  pre> 
 
 
我的第一个想法是使用VBA，但是由于算法是专有的，希望它受到保护的权力。我可以给它添加一个密码，但是它已经很好地记录了（在StackOverflow上）如何解决这样的密码。
 
 
 
我的第二个想法是创建一个Excel 2013 Visual Studio中的工作簿项目，但是我没有发现任何有用的C＃中如何公开函数，所以可以像我所描述的那样调用。
 
 
 
接下来我想到让VBA调用C＃，并在 https://msdn.microsoft.com上找到说明/en-us/library/bb608613.aspx 。我按照这些说明写信，但是当我尝试运行VBA代码时，我会发现GetManagedClass函数出现错误：对象库功能不支持。
 
 
 
有没有很好的参考如何做这样的事情？
解决方案
你正在寻找 Excel-DNA 。 
这个开放源代码库允许您创建管理的Excel加载项，并支持使用用户定义的函数，还支持宏，实时RTD数据源等。
 
 
 
在C＃中创建一个Excel UDF就像：
  [ExcelFunction（Description =My first .NET函数）] 
 public static string SayHello（string name）
 {
 returnHello+ name; 
} 
  
，您可以从单元格调用：
  = SayHello（Walter）
  
对于使用.NET的代码保护，您需要使用混淆器 - 可以使用各种免费和付费方式。
 
We are looking to be able to programmatically create an Excel workbook which would call custom code from within a cell.  Cells would look something like:
=MyCode(A1:A10)
My first thought was to use VBA, but since the algorithm is proprietary the powers that be want it to be protected.  I can put a password on it, but it is well documented (here on StackOverflow) on how to get around such passwords.

My second thought was to create an Excel 2013 Workbook project in Visual Studio, but I haven't found anything useful on how to expose a function in C# so it can be called like I described.

Next I thought about having VBA call the C#, and found instructions at https://msdn.microsoft.com/en-us/library/bb608613.aspx.  I followed those instructions to the letter, but when I try to run the VBA code I get an error with the GetManagedClass function: Object Library Feature not Supported.

Are there any good references on how to do something like this?
解决方案
You're looking for Excel-DNA.
This open-source library allows you to create managed Excel add-ins, and supports making user-defined functions, but also macros, real-time RTD data sources etc.

Creating an Excel UDF in C# is then as simple as:
[ExcelFunction(Description = "My first .NET function")]
public static string SayHello(string name)
{
    return "Hello " + name;
}
and you can call from a cell as:
=SayHello("Walter")
For code protection with .NET, you'd need to use an obfuscator - there are a variety of free and paid-for ones available.

                        
这篇关于用C＃创建新的Excel公式/函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！