Write Array to Excel Range

Question

I'm currently trying to write data from an array of objects to a range in Excel using the following code, where `objData` is just an array of strings: ``` private object m = System.Type.Missing; object[] objData = getDataIWantToWrite(); Range rn_Temp; rn_Temp = (Range)XlApp.get_Range(RangeName, m); rn_Temp = rn_Temp.get_Resize(objData.GetUpperBound(), 1); rn_Temp.value2 = objData; ``` This very nearly works, the problem being that the range gets filled but every cell gets the value of the first item in the `objData`. The inverse works, i.e. ``` private object m = System.Type.Missing; object[] objData = new object[x,y] Range rn_Temp; rn_Temp = (Range)XlApp.get_Range(RangeName, m); rn_Temp = rn_Temp.get_Resize(objData.GetUpperBound(), 1); objData = (object[])rn_Temp.value2; ``` would return an array containing all of the values from the worksheet, so I'm not sure why reading and assignment work differently. Has anyone ever done this successfully? I'm currently writing the array cell by…

Accepted Answer

This is an excerpt from method of mine, which converts a `DataTable` (the `dt` variable) into an array and then writes the array into a `Range` on a worksheet (`wsh` var). You can also change the `topRow` variable to whatever row you want the array of strings to be placed at.

```
object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
    DataRow dr = dt.Rows[r];
    for (int c = 0; c < dt.Columns.Count; c++)
    {
        arr[r, c] = dr[c];
    }
}
Excel.Range c1 = (Excel.Range)wsh.Cells[topRow, 1];
Excel.Range c2 = (Excel.Range)wsh.Cells[topRow + dt.Rows.Count - 1, dt.Columns.Count];
Excel.Range range = wsh.get_Range(c1, c2);
range.Value = arr;
```

Of course you do not need to use an intermediate `DataTable` like I did, the code excerpt is just to demonstrate how an array can be written to worksheet in single call.

Write Array to Excel Range

Solution

Problem