C#:ExcelDNA在Excel中显示一个数组 [英] c#: ExcelDNA display an array in excel

问题描述

我正在为excel创建一个插件，我想知道如何创建新行以显示列表或数组

I'm creating a plugin for excel, i would like to know how is it possible to create new rows to display a list or array

我创建了一个示例函数:

I created a sample function :

[ExcelFunction(Description = "Split string", Category = "STRING")]
    public static object[] StringSplit([ExcelArgument(Name = "TEXT", Description = "text to split")] string text)
    {
        return text.Split(' ').ToArray();
    }

在此示例中，该函数返回带有不同关键字的数组.但是当我通过excel使用它时，它仅显示我单元格中的第一个单词. 我想知道的是创建新闻列或新闻行，并在其中显示数组的每个元素. 谢谢

In this example, the function returns an array with the different keywords. but when i use it via excel, it displays only the first word in my cell. what i want to know is to create news columns or rows and display each element of my array in it. thanks

修改 我尝试了@C发布的解决方案. Augusto Proiete波纹管，因此​​可以在以下代码中正常工作:

Edit I tried the solution posted by @C. Augusto Proiete bellow, so it works fine with this code :

Page page = new Page();
        WebResponse resp = page.Download(url);
        List<string> res = page.GetAllFromXpath(resp.content, xpath);
        object[,] result = new object[1, res.Count];

        for (int j = 0; j < res.Count; j++)
        {
            result[0, j] = res.ElementAt(j);
        }
       return Resizer.Resize(result);

但是当我尝试使用asyncrohous方法时，这会抛出一个无法处理的异常:

but when i try the asyncrohous method, this throw an exeption not handled :

return ExcelAsyncUtil.Run("DownloadAsync", url,
           delegate
           {
               Page page = new Page();
               WebResponse resp = page.Download(url);
               List<string> res = page.GetAllFromXpath(resp.content, xpath);
               object[,] result = new object[1, res.Count];

               for (int j = 0; j < res.Count; j++)
               {
                   result[0, j] = res.ElementAt(j);
               }

               return Resizer.ResizeMe(result);
           });

抛出的异常是:

ExcelDna.Integration.XlCallException' occurred in ExcelDna.Integration.dll but was not handled in user code

这是Reizer类中抛出它的行:

And here is the line in the Reizer class that throw it:

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

推荐答案

考虑到您要返回一个数组，可以通过在数组模式下输入公式来将结果跨列.

Considering you're returning an array, you can span the results across columns by entering the formula in array mode.

水平选择单元格，键入公式，然后按CTRL + SHIFT + ENTER.

Select the cells horizontally, type in the formula, and press CTRL+SHIFT+ENTER.

例如，选择A1:C1并键入=StringSplit("a b c")作为数组公式，将在所选的三个单元格中显示数组的三个元素.

For example, selecting A1:C1 and typing =StringSplit("a b c") as an array formula, will display the three elements of the array in the three cells selected.

您需要预先知道将返回多少个元素，并适当地选择正确的单元格数量.

You need to know upfront how many elements will be returned and select the correct number of cells appropriately.

如果您不知道将返回多少个元素，那么您可能需要查看

If you don't know how many elements will be returned, then you might want to look at Resizing Excel UDF result arrays, but it's a hack and should be avoided if possible.

这是示例实现一个辅助宏，它将您的结果数组调整为正确的大小.

Here is an example implementation of a helper macro that will resize your result array to the right size.

如果要返回行和列，只需返回object[,]

If you wanted to return rows and columns, you just need to return an object[,]

[ExcelFunction(Description = "Returns 2x2 matrix")]
public static object GetValues()
{
    return new object[,]
    {
        {1, 2},
        {3, 4},
    };
}

其他所有内容保持不变.除非使用**

Everything else stays the same. You should return the number of elements that correspond to the number of cells selected by the user, unless you go with **resizing result arrays as per above.

这篇关于C#:ExcelDNA在Excel中显示一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！