Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Write Array to Excel Range

Tags:

c#

excel

interop

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.

like image 379
Jon Artus Avatar asked Feb 11 '09 12:02

Jon Artus


People also ask

How do you convert a range to an array in Excel?

Steps to Add a Range into an Array in VBA First, you need to declare a dynamic array using the variant data type. Next, you need to declare one more variable to store the count of the cells from the range and use that counter for the loop as well. After that, assign the range where you have value to the array.

How do I populate an array in Excel VBA?

To Fill a Dynamic ArrayOn the Tools menu, point to Macro and then click Macros. In the Macro dialog box, click fill_array, and then click Run.

How do you write an array to a worksheet in VBA?

The array may be 1 or 2 dimensional. To write a one dimensional array back to the worksheet, you must create a Range object, resize that range to the size of your array, and then write to the range. This code will write the values of Arr to range that is one row tall by UBound(Arr) columns wide, starting at range K1.


1 Answers

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.

like image 191
petr k. Avatar answered Sep 18 '22 22:09

petr k.