I am using Microsoft.Office.Interop.Excel to open a Workbook. I then find the Worksheet that I need, and find a named cell in the Worksheet. I want to be able to set the value of the cell to an array, so that those values “spill” (an Excel term).

If I set the value of the cell in Excel by hand like this

={1;2;3}

I will get a spill where the first cell has 1, then the one below it has 2, and then 3 below that. Same for

={"foo";"bar";"fizz"}

I’d like to have a function in C# that can take in a dynamic array, and write that to the cell. I could use a StringBuilder to concat all the values and then set the formula of the cell. But if the dynamic array is strings then I have to worry about the quotes.

Is there a simpler way to achieve the desired behavior? Here is what I have so far. This method is one for a class that handles the opening of the Excel file. Its also part of an AutoCAD plugin, which is the transaction object, but thats not really important.

public void testMethod3(Transaction tr)//AutoCAD object
{
    var pages = Workbook.Worksheets.Cast<Worksheet>()
        .Where(sheet => sheet.Name.Contains("Export"))
        .ToList();

    if (pages.Count != 1) return;

    if (!Save)
    {
        pages.ForEach(p =>
        {
            p.Columns.ClearFormats();
            p.Rows.ClearFormats();
        });
    }

    dynamic range = pages[0]?.Names?.Item("LoopRoom")?.RefersToRange?.Cells;

    List<MyObject> myObjects = //data objects with string property RoomName

    object[,] data = new object[myObjects.Count+1,1];

    for (int i = 0; i < loops.Count; i++)
    {
        data[i, 0] = myObjects[i].RoomName;
    }

    


    range.Value2 = data;

}