Write Array to Excel Range

Question

Hi All,

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 cell, but it needs to cope with lots (>50,000) of rows and this is therefore very time consuming.

Many thanks,
Jon

Answer 1

You could put your data into a recordset and use Excel's CopyFromRecordset Method - it's much faster than populating cell-by-cell.

You can create a recordset from a dataset using this code. You will have to do some trials to see if using this method is faster than what you are currently doing.

Answer 2

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.

Answer 3

Thanks for the pointers guys - the Value vs Value2 argument got me a different set of search results which helped me realise what the answer is. Incidentally, the Value property is a parametrized property, which must be accessed through an accessor in C#. These are called get_Value and set_Value, and take an optional enum value. If anyone's interested, this explains it nicely.

It's possible to make the assignment via the Value2 property however, which is preferable as the interop documentation recommends against the use use of the get_Value and set_Value methods, for reasons beyond my understanding.

The key seems to be the dimension of the array of objects. For the call to work the array must be declared as two-dimensional, even if you're only assigning one-dimensional data.

I declared my data array as an object[NumberofRows,1] and the assignment call worked.