[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);
}
The exercise was interesting to me, because it was a perfect case to try out the [RowTest] and [Row] attributes which now ship with NUnit.
The classic NUnit version of the test would look something like this:
[Test]
public void ConvertExcelDateToDateTimeClassic()
{
double excelDate = 1.00;
DateTime expectedDate = new DateTime(1900, 1, 1);
DateTime date = ExcelTools.ConvertToDateTime(excelDate);
Assert.AreEqual(expectedDate, date);
}
However, in order to replicate the multiple border cases, you would have to write the same test over and over again. [RowTest] allows to do this in a very compact form, with this syntax:
[RowTest]
[Row(1.0, 1900, 1, 1)]
[Row(59.0, 1900, 2, 28)]
[Row(60.0, 1900, 3, 1)]
[Row(61.0, 1900, 3, 1)]
[Row(36526.0, 2000, 1, 1)]
[Row(401769.0, 3000, 1, 1)]
public void ConvertExcelDateToSimpleDateTime(double excelDate, int year, int month, int day)
{
DateTime expectedDate = new DateTime(year, month, day);
DateTime date = ExcelTools.ConvertToDateTime(excelDate);
Assert.AreEqual(expectedDate, date);
}
I struggled a bit with getting it to work initially, because it seemed that I was missing a reference and the attributes were not recognized. Thanks to Donn Felker for the walkthrough on what to include to "make it work"!
Subscribe