Interop Excel - Range Text to multidimensional array

Question

I am reading excel sheet with following code:

  Excel.Range uRange = xlWorkSheet.UsedRange;  
  dynamic data = uRange.Value2;

As you can see I copy used range cell values into dynamic 2D array. One column of selected range is custom formatted and with above technic I am getting just a bunch of numbers instead of nice formatted value when accessing this custom formatted cell (data[x,y]).

If I am accessing only certain cell directly with following code:

 (Excel.Range)xlWorkSheet.Cells[x, y].Text

... I can see the formatted value I need. By accessing each cell separately I am loosing a lot of time because it is slower to access cell by cell inside a loop.

Is it possible to get all Text values from selected range. Something like this:

 Excel.Range uRange =xlWorkSheet.UsedRange; 
 dynamic data = uRange.Text;

When I use above code I am getting empty variable.

Answer 1

Note that if you are using COM interop in Silverlight application to access excel files that approach will not work.

Here's the solution, basically you need to iterate through columns, and get 1-dimensional array for each column. (this approach is very fast)

 foreach (var column in fullRange.Columns)
 {
   // vals will be a one-dimensional array 
   // of all values in that column!
   var vals = column.Value;

   foreach (var val in vals)
   {
       if (val == null) continue;
       string cellValue = val.ToString();
       //Debug.WriteLine(" Value read: " + cellValue);
       //do something with the value...

   }
 }