interop.excel convert Range Object(,) to Double(,)

Question

I have a Microsoft.Office.Interop.Excel.Range Object obtained as shown:

Dim maxRow As Integer = currentSheet.UsedRange.Rows.Count
currentSheet.Range("A2").Resize(maxRow-1, 2).Value

and I'm trying to convert that to a 2 dimensional array of doubles, but if I do:

Dim arrayData(,) As Double = currentSheet.Range("A2").Resize(maxRow - 1, 2).Value

I get the following error:

Additional information: Unable to cast object of type 'System.Object[,]' to type 'System.Double[,]'.

Is there a simple way to obtain the data from a spreadsheet as an array of doubles? I could loop over each element in the array, but it seems like that shouldn't be necessary.

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

Use Array.Copy()

Dim arrayData(,) As Double
Dim objData(,) As Object = currentSheet.Range("A2").Resize(maxRow - 1, 2).Value
Array.Copy(objData, arrayData, objData.Length)

By the way I found this by putting convert 2d object double into a search engine... How can I quickly up-cast object[,] into double[,]?

Edit

I just tried it on my own Excel sheet and it works. One thing I should add is that I needed to specify the bounds of the destination array when dimensioning it.

'Dim objData(,) As Object = xlSheet.Range("C6:E14").Value ' either way works
Dim objData(,) As Object = xlSheet.Range("C6").Resize(9, 3).Value
Dim dblData(objData.GetUpperBound(0) - 1, objData.GetUpperBound(1) - 1) As Double
Array.Copy(objData, dblData, objData.Length)

Can you inspect your Object(,) at runtime to make sure there is data inside it? Also, everything needs to be castable to Double.

edited May 23 '17 at 12:33

Community

1
1

answered Dec 05 '13 at 18:51

djv

15,168
7
48
72

1

Well, apparently you are better at searching than I am because I spent at least 4 hours without finding anything that actually worked. – Matt Dec 05 '13 at 18:58
Upon further investigation I have discovered that while this will compile, it does not copy the data as intended. Many values at the end of the array are 0 when they should not be. In fact, the only values that I was able to find that were correct are arrayData(0,0) and arrayData(0,1). – Matt Dec 05 '13 at 23:29
I am specifying the bounds of my destination array, and I have verified that all my data is castable to Double. I have successfully copied the data into a 2D array of doubles by using nested for loops and `Double.Parse()` but for some reason when switching back to this method I still have some cells that are filled with '0' instead of the actual values, and the rest (except the first 2) are wrong. My array of Object is apparently 1 indexed and my array of Double is 0 indexed, could this be causing the problem? – Matt Dec 06 '13 at 16:05
Maybe you have some hidden cells? Otherwise I have no idea, sorry. – djv Dec 06 '13 at 16:19
I think I'm going to go with the nested loops anyway as there are now some calculations I want to do on each element – Matt Dec 06 '13 at 16:27

score -1 · Answer 2 · answered Dec 05 '13 at 16:22

-1

This is a C# sample, but you shouldn't have problems converting it to vb.

object[,] arrWks = (object[,])enuWorksheet.UsedRange.get_Value(XLS.XlRangeValueDataType.xlRangeValueDefault);

So int your case, instead of .Value, use get_Value method.

Then when iterating through array, remember to check for nulls:

object objKom = arrWks[intRow, intCol];
string strKom = objKom == null ? "" : objKom.ToString();

Hope this helps.

answered Dec 05 '13 at 16:22

AdamL

12,421
5
50
74

`but you shouldn't have problems converting it to vb` - then you shouldn't either. – djv Dec 05 '13 at 16:32

interop.excel convert Range Object(,) to Double(,)

2 Answers2