如何将对象从 UDF 参数转换为 Excel-DNA 中的 Excel 范围？

Question

我正在使用 Excel-DNA 开发一个简单的插件。我写了一个下面的函数，但我发现将它转换为 Range 对象时遇到了困难。尝试谷歌搜索，无法弄清楚。有人可以帮帮我吗

[ExcelFunction(Description = "Excel Range")]

public static string Concat2([ExcelArgument(AllowReference = true)] object rng)

{

    try

    {

       // Assuming i am calling this from Excel cell A5 as =Concat2(A1:A2)

        var app = (Excel.Application)ExcelDnaUtil.Application;

        var r = app.Range[rng, Type.Missing];

        return r.Cells[1,1] + r.Cells[2,2]

    }

    catch (Exception e)

    {

        return "Error";

    }

}

Answer 1

您应该直接从输入参数中获取值，而不是获取 Range COM 对象。这样做也更有效率。您的简单函数可能如下所示：&nbsp; &nbsp; public static object Concat2(object[,] values)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; string result = "";&nbsp; &nbsp; &nbsp; &nbsp; int rows = values.GetLength(0);&nbsp; &nbsp; &nbsp; &nbsp; int cols = values.GetLength(1);&nbsp; &nbsp; &nbsp; &nbsp; for (int i = 0; i < rows; i++)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (int j = 0; j < cols; j++)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; object value = values[i, j];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; result += value.ToString();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return result;&nbsp; &nbsp; }通常，您希望检查value对象的类型，并基于此做一些不同的事情。object[,]从 Excel-DNA 传递的数组可以包含以下类型的项目：doublestringboolExcelDna.Integration.ExcelErrorExcelDna.Integration.ExcelEmptyExcelDna.Integration.ExcelMissing（如果不带参数调用该函数，则为=Concat2()）。如果您将签名更改为具有单个类型的参数object（而不是object[,]），如下所示：&nbsp;&nbsp;&nbsp;&nbsp;public&nbsp;static&nbsp;object&nbsp;Concat2(object&nbsp;value)然后，根据函数的调用方式，您可能会获得上述类型之一作为 the ，value或者您可能获得一个object[,]数组作为 the&nbsp;value，因此在进行迭代之前，您的类型检查看起来会有所不同。

Answer 2

在我的 F# 插件中，我有一个执行此操作的函数（我使用此函数主要是为了提取日期的显示值）：[<ExcelFunction(Description="Returns what is currently displayed as text.", IsMacroType=true)>]let DISPLAYEDTEXT ([<ExcelArgument(Description="Cell", AllowReference=true)>] rng : obj) =&nbsp; &nbsp; app().get_Range(XlCall.Excel(XlCall.xlfReftext, rng, true)).Text应用程序在哪里：let app()= ExcelDnaUtil.Application :?> Excel.Application

Answer 3

这个怎么样？[ExcelFunction(IsMacroType = true)]public static double GetBackColor([ExcelArgument(AllowReference=true)] object cell){&nbsp; &nbsp; ExcelReference rng = (ExcelReference)cell;&nbsp; &nbsp; Excel.Range refrng = ReferenceToRange(rng);&nbsp; &nbsp; return refrng.Interior.Color;}这是辅助函数private static Excel.Range ReferenceToRange(ExcelReference xlRef){&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;&nbsp; &nbsp; string strAddress = XlCall.Excel(XlCall.xlfReftext, xlRef, true).ToString();&nbsp; &nbsp; return app.Range[strAddress];}