Trying to figure out how to read the contents of a reference and get same results and Interop/COM's Range.Value...namely that the object[,] returned contains string, DateTime, and doubles.
I'm using ExcelDNA (and underlying XlCall.Excel to call C API) and both...
ExcelReference.GetValue() and
XlCall.Excel( XlCall.xlfDeref, reference )
Both return an object[,] that is equivalent to Interop/COM's Range.Value2...namely that the object[,] returned contains only string and doubles.
The problem with this is that Dates are returned as double and I have no way of determining if the value should be a double or a DateTime.
ExcelReference.GetValue() will never return a DateTime, since that's never the stored value of a cell - it is just a display format applied to a numeric (double) value. It is similar for currency and percentage formatting.
You can read the "Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell's formatting." using the xlfGetCell call with the C API, using option 53. However, you then have to figure out whether the string represents a date/time yourself.
One could also read the "Number format of the cell, as text (for example, "m/d/yy" or "General")." using xlfGetCell option 7.