C# / Excel: Convert Excel Range to Array with Correct Data Type

Question

I opened *.htm file with Excel Application (Microsoft.Office.Interop.Excel). It was parsed excellent! So I can work with it. For more speed, I'm trying to get data from Excel Range and insert into System.Array and work with it:

Excel.Range range = ExcelWorksheet.get_Range("A1", "H1500"); // get all values
System.Array dataArray = (System.Array)(range.Cells.Value2); // insert into array

Problem is with data type. If Excel cell has time or date format, range.Cells.Value2 makes:
12.06.2012 to 41072 (Excel Cell Type - date)
14:48 to 0,616666666666667 (Excel Cell Type - time)
If I get single value form Excel Cell, I get correct value (with Cells.Text.ToString()):

ExcelWorksheet.get_Range("A1", "A1").Cells.Text.ToString()

Task: I need get values from Excel Sheet as they are, just like text, not as another type.
And don't want Excel thinks instead of me :)

Solution

Why don't you just convert the returned values to the format you need? The FromOADate method of DateTime is designed for this (see http://msdn.microsoft.com/en-us/library/1ad4d8d6(v=vs.80).aspx). For example, the time value returned represents a fractional portion of a 24 our day. Hence the following example will output "14:48":

double oleDateTime = 0.616666666666667;
DateTime dt = DateTime.FromOADate(oleDateTime);
string time = dt.ToString("H:mm");
Console.WriteLine(time);

As far as the date values, you can use the same approach. The only difference will be the fact that the value from Excel (double) would be greater than zero because it is includes a date portion (not just time). The following will yield "12.06.2012":

dt = DateTime.FromOADate(41072);
string date = dt.ToString("dd.MM.yyyy");
Console.WriteLine(date);

To further illustrate, in the case that you are dealing with date AND time (returned Excel value is greater than zero), the following will yield "6/12/2012 2:48:00 PM":

dt = DateTime.FromOADate(41072.616666666666667);
Console.WriteLine(dt.ToString());

Comments

[Chaki_Black]

Of course you are right. The problem was too, when I convert range to array System.Array dataArray = (System.Array)(range.Cells.Value2), object cells of array (the same as object[,] dataArray) stores info of data type of base excel cell. So I can know, if cell of data array is datetime type like if(dataArray.GetValue(1,1) is DateTime) I can do actions above. I solved this problem like this: object[,] dataArray = (object[,])range.get_Value(Excel.XlRangeValueDataType.xlRang‌​eValueDefault);. DataTime values represent like '2012-06-01 0:00:00'. It is possible to work with them too.

[sergeidave]

This method is reading the values cell by cell, which is very slow compared to grabbing the values from the Excel Range as an array...