我们可以将自定义对象从Excel传递给C＃ExcelDNA吗？

I have an object:

我有一个对象:

public class Shampoo
{
public string Name {get;set;}
public string Id {get;set;}
public string PriceRegion {get;set;}
}

Can I pass this object to a method in C# via Excel DNA:

我可以通过Excel DNA将此对象传递给C#中的方法:

C# method is:

C#方法是:

public static string PassShampoo(Shampoo shampoo)
{
//Some Code...
}

Is this possible from VBA to C# via Excel DNA?

这是否可以通过Excel DNA从VBA到C#?

1 个解决方案

#1

Your .NET library can export and register the Shampoo class as a COM visible type, which you can then instantiate from VBA as Dim sh = New Shampoo(). Similarly other types could be define, like

您的.NET库可以将Shampoo类导出并注册为COM可见类型,然后您可以从VBA实例化为Dim sh = New Shampoo()。类似地,可以定义其他类型

public class Shopper()
{
    public string ReadLabel(Shampoo shampoo)
    {
        return shampoo.Name;
    }
}

and then a New Shopper() could be passed the shampoo:

然后新购物者()可以通过洗发水:

Dim anne As Shopper = New Shopper()
Dim dove As Shampoo = New Shampoo()

Dim label As String
label = anne.ReadLabel(dove)

The method using Shampoo (Shopper.ReadLabel above) would not be static and would not be available to Excel as a UDF or anything - just as a method on a .NET object onvoked via COM interop from VBA.

使用Shampoo(上面的Shopper.ReadLabel)的方法不是静态的,并且不能作为UDF或任何东西用于Excel - 就像通过VBA的COM互操作上的.NET对象上的方法一样。

So far - no Excel-DNA involved. You could do all of this with a standard .NET assembly that is compiled with the right flags and attributes and registered for COM interop on your machine.

到目前为止 - 没有涉及Excel-DNA。您可以使用标准.NET程序集完成所有这些操作,该程序集使用正确的标志和属性进行编译,并在计算机上注册COM interop。

However, Excel-DNA also allows your add-in to be a COM Server. This means that the .xll can host the COM classes you've defined in your library, your add-in can do the COM registration (instead of an installer) without requiring Administrator access, and your COM Objects will live in the same AppDomain as the rest of your add-in. So Excel-DNA helps a bit in gluing things up, but the actual interaction between the VBA code and your .NET assembly is the standard .NET-to-COM interop, which works very well once you've climbed the learning curve a bit.

但是,Excel-DNA还允许您的加载项成为COM服务器。这意味着.xll可以托管您在库中定义的COM类,您的加载项可以在不需要管理员访问权限的情况下进行COM注册(而不是安装程序),并且您的COM对象将存在于同一个AppDomain中。你的加载项的其余部分。所以Excel-DNA有点混淆了,但VBA代码和.NET程序集之间的实际交互是标准的.NET-to-COM互操作,一旦你爬上学习曲线,它就能很好地工作。

#1