Excel Interop cell formatting of Dates

Question

My program creates an output in Excel.

Some of the dates seem to be getting misinterpreted:

On opening the file the dates are a mixture as in the screenshot.
If I actually put the cursor in Excel's calculation box (in screenprint) and press enter the formatting of the cell reverts to the correct formatting.

I'm using Microsoft.Office.Interop.Excel to move data from a Datatable to an Excel template saved in my Solution.

Before putting the data into the cells I change each columns numberformat accordingly using a switch:

for(int i = 1; i < NumColumns + 1; i++) {
    switch(dt.Columns[i-1].DataType.ToString()) {
        case "System.Int32":
            xlWorkSheet.Columns[i].NumberFormat = "#,##0_ ;[Red]-#,##0 ";
            break;
        case "System.String":
            xlWorkSheet.Columns[i].NumberFormat = "@";
            break;
        case "System.DateTime":
            xlWorkSheet.Columns[i].NumberFormat = "[$-809]dd mmmm yyyy;@"; //"dd-mmm-yy";
            break;
        case "System.Date":
            xlWorkSheet.Columns[i].NumberFormat = "[$-809]dd mmmm yyyy;@"; //"dd-mmm-yy";
            break;
        default:
            xlWorkSheet.Columns[i].NumberFormat = "@";
            break;
    }
}

To move the values from the datatable I use nested loops:

//move header totals into the spreadsheet
for(int i = 1; i < NumColumns + 1; i++) {
    xlWorkSheet.Cells[1, i].value = dt.Columns[i - 1].ColumnName;
}

//move in the data
DataView dv = new DataView(dt);
dv.Sort = "Date ASC";

int rowCount = 2;
string theValue;
try {
    foreach(DataRowView dr in dv) {//(DataRow dr indt.Rows)
        for(int i = 1; i < NumColumns + 1; i++) {
            theValue = dr[i - 1].ToString();
            xlWorkSheet.Cells[rowCount, i].value = theValue;
        }
        rowCount += 1;
    }
} catch(Exception) {
    throw;
}

In the stored procedure when I populate the Datatable I've tried explicitly spelling out the type using DATETIME and the following:

SELECT 
"Date" = CONVERT(DATE,b.[Date])
    ...

How can I make my Date data so explicit that Excel cannot misinterpret and all the desired formatting is applied ?

---

Edit

Next (untested) attempt at the nested loop is as follows:

int rowCount = 2;
string theValue;
DateTime dtm;
DateTime d;
try {
    foreach(DataRowView dr in dv) {//(DataRow dr indt.Rows)
        for(int i = 1; i < NumColumns + 1; i++) {
            //theValue = dr[i - 1].ToString();
            //xlWorkSheet.Cells[rowCount, i].value = theValue;
            switch(dr[i - 1].GetType().ToString()) {
                case "System.DateTime":
                    dtm = Convert.ToDateTime(dr[i - 1]);
                    xlWorkSheet.Cells[rowCount, i].value = dtm.ToOADate();
                    break;
                case "System.Date":
                    d = Convert.ToDateTime(dr[i - 1]);
                    xlWorkSheet.Cells[rowCount, i].value = d.ToOADate();
                    break;
                default:
                    theValue = dr[i - 1].ToString();
                    xlWorkSheet.Cells[rowCount, i].value = theValue;
                    break;
            }

        }
        rowCount += 1;
    }
} catch(Exception) {
    throw;
}

Answer 1

The issue here is that you are writing everything to Excel as a string, and Excel will store it as such. On editing and pressing Enter, Excel will re-interpret the cell contents and may then store it differently.

When you call ToString() on a DateTime, the default formatting is in the form DD/MM/YY HH:mm:ss, which is exactly what you are seeing in the resultant file.

Dates in Excel are stored as numbers representing the number of days that have elapsed since 1900. To get this number, you can call the ToOADate() method.

See http://msdn.microsoft.com/en-gb/library/system.datetime.tooadate.aspx

With the data set as a doubleand an appropriate format string, you should get the result you're hoping for!