c#: ExcelDNA display an array in excel

Question

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();
    }

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

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);

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);
           });

the exception thrown is :

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

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

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

See also stackoverflow.com/questions/15903055/…

Ralph Willgoss
– Ralph Willgoss

2019-03-22 12:49:22 +00:00
Commented Mar 22, 2019 at 12:49 — Ralph Willgoss
– Ralph Willgoss, Commented Mar 22, 2019 at 12:49

C. Augusto Proiete · Accepted Answer · 2018-01-26 14:47:19Z

3

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

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

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.

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.

edited Jan 26, 2018 at 14:47

answered Jan 17, 2018 at 17:10

C. Augusto Proiete

28.6k3 gold badges79 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Dany M Over a year ago

humm, not really what i need, i'll check the hack but still think there is something native to do

C. Augusto Proiete Over a year ago

@DanyM In that case, edit your question and try to express what you're looking for in a better way, including what you mean by "something native"

Dany M Over a year ago

I don't know how many elements the string will return. i would like excel to create as many cells as needed

C. Augusto Proiete Over a year ago

Excel does not allow you to modify the sheet from within a user-defined function. Doing this breaks Excel's calculation model. The resizing approach linked above would be the way to do what you're after, but as it says, it's a hack and should be avoided.

C. Augusto Proiete Over a year ago

Instead of using a function/formula, consider creating a button on a Ribbon. That way you can access Excel's object model via COM, and do whatever you want...

|

Collectives™ on Stack Overflow

c#: ExcelDNA display an array in excel

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related