如何将不连续的单元格范围从Excel传递到ExcelDNA函数 [英] How to pass discontinuous range of cells from Excel to ExcelDNA function

问题描述

考虑这样的ExcelDNA函数定义:

Consider such ExcelDNA function definition:

[ExcelFunction(Name = "Fnc1", Description = "Fnc1")]
public static object Fnc1(
    [ExcelArgument(Name = "Arg1", Description = "Arg1", AllowReference = true)]
    object rng)
{
    // ...
}

• 在像这样的单个单元格=Fnc1(A1)或像这样的连续范围内的=Fnc1(A1:A3)上调用时，它都可以正常工作.
• 但是当以 discontinuous 范围的单元格(例如， =Fnc1(A1,A5,A10).返回错误#VALUE!.
• It works fine when called with a single cell like this =Fnc1(A1) or with continuous range of cells like this =Fnc1(A1:A3).
• But it doesn't work when called with discontinuous range of cells e.g. =Fnc1(A1,A5,A10). The error #VALUE! is returned.

有没有一种方法可以调用 unknown 数量不连续的 discontinuous 范围内的ExcelDNA函数?

Is there a way how to call ExcelDNA function with discontinuous range of unknown amount of cells?

我试图像这样params object[] rng声明参数，但也没有运气.

I have tryied to declare the paramter like this params object[] rng but no luck as well.

推荐答案

为了拥有一个Excel-DNA函数，该函数允许在运行时传递 unknown 个参数，在函数参数中使用params object[].

In order to have an Excel-DNA function that allows passing in an unknown number of arguments at run-time, you need to use params object[] in your function arguments.

public static class MyFunctions
{
    [ExcelFunction]
    public static object Hello(params object[] values)
    {
        return "Hello " + DateTime.Now;
    }
}

然后用硬编码的值调用它并不重要，例如=Hello(10, 20)或使用单元格引用(例如) =Hello(A1,A5,A10).

Then it doesn't matter if you call it with hard-coded values e.g. =Hello(10, 20) or if you use cell references e.g. =Hello(A1,A5,A10).

但是，Excel-DNA不立即支持可变数量的参数，因此，您必须使用

However, variable number of arguments is not supported out-of-the-box by Excel-DNA, and as such you'll have to use the ExcelDna.Registration helper library in order to register your functions.

安装 ExcelDna.Registration NuGet软件包，然后将其安装在文件，将您的插件程序集引用标记为使用ExplicitRegistration，例如:

Install the ExcelDna.Registration NuGet package, then inside of your .dna file, mark your add-in assembly reference to use ExplicitRegistration e.g.:

<?xml version="1.0" encoding="utf-8"?>
<DnaLibrary Name="My Add-In" (...)>
  <ExternalLibrary Path="MyAddIn.dll" ExplicitRegistration="true" (...) />
</DnaLibrary>

然后，在AutoOpen中，通过ProcessParamsRegistrations调用注册函数...例如

Then, in your AutoOpen, you register the functions with a ProcessParamsRegistrations call... e.g.

public class AddIn : IExcelAddIn
{
    public void AutoOpen()
    {
        ExcelRegistration
            .GetExcelFunctions()
            .ProcessParamsRegistrations()
            .RegisterFunctions();

        // ...
    }

    public void AutoClose()
    {
        // ...
    }
}

---

隐式与显式注册功能

Implicit vs Explicit Registration of functions

默认情况下，Excel-DNA会搜索程序集中的每个public static方法，并将它们注册为Excel中的函数.这就是隐式注册过程.

By default, Excel-DNA searches for every public static method in your assembly and registers them as functions with Excel. That's the implicit registration process.

ExplicitRegistration="true"关闭 off 隐式注册，因此不会自动注册任何内容-您必须自己完成-这是我在上面的AutoOpen中使用... RegisterFunctions()所做的事情称呼. 如果您没有关闭隐式注册，那么功能最终将被注册两次(一次通过隐式过程，然后再次通过您的代码)您会收到错误消息

ExplicitRegistration="true" turns off the implicit registration and thus nothing gets registered automatically - you have to do it yourself - which is what I'm doing in the AutoOpen above with the ... RegisterFunctions() call. If you don't turn off the implicit registration, then functions end-up being registered twice (once by the implicit process, then again by your code) and you get error messages

这篇关于如何将不连续的单元格范围从Excel传递到ExcelDNA函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！