[Edit, Sept 5, 2008: nothing incorrect in the following post; however, if I had Google'd first, I would have found that DateTime date = DateTime.FromOADate(d), where d is a double, does exactly the job...]
The project I am currently working on requires reading some data from an Excel workbook into a .NET calculation engine written in C#. Most of my reads follow this pattern: read a named range into an array of objects, then convert the object to the appropriate .NET type.
public static object[,] GetRangeAsArray(Excel.Worksheet sheet, string rangeName)
{
Excel.Range range = sheet.get_Range(rangeName, Missing.Value);
object[,] rangeAsArray = range.Value2 as object[,];
return rangeAsArray;
}
However, I ran into an issue reading dates. Excel stores dates as doubles, which encode the number of days elapsed since January 0, 1900 (Yes, January 0). As a result, the object stored in the array is a double, and the Convert.ToDateTime(double) method throws an InvalidCastExpression, so standard conversion doesn’t work.
If you look a bit deeper into it (here is a very comprehensive page on the topic), you will discover some interesting idiosyncrasies of the date encoding in Excel. For instance, back in the days, the Excel team knowingly implemented a bug to replicate a known bug of Lotus, for the sake of backwards compatibility.
Here is the quick method I wrote to perform that conversion, addressing these issues:
public static DateTime ConvertToDateTime(double excelDate)
{
if (excelDate < 1)
{
throw new ArgumentException("Excel dates cannot be smaller than 0.");
}
DateTime dateOfReference = new DateTime(1900, 1, 1);
if (excelDate > 60d)
{
excelDate = excelDate - 2;
}
else
{
excelDate = excelDate - 1;
}
return dateOfReference.AddDays(excelDate);
}
More...
Subscribe