Excel日期单元格格式的日期格式

My program creates an output in Excel.

我的程序在Excel中创建输出。

Some of the dates seem to be getting misinterpreted: Excel日期单元格格式的日期格式

有些日期似乎被误解了：

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.

在打开文件时，日期是截图中的混合。如果我实际将光标放在Excel的计算框中（在丝网印刷中）并按Enter键，则单元格的格式将恢复为正确的格式。

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

我正在使用Microsoft.Office.Interop.Excel将数据从Datatable移动到我的解决方案中保存的Excel模板。

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:

在我填充Datatable的存储过程中，我尝试使用DATETIME和以下内容明确地拼写出类型：

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 ?

如何使我的日期数据如此明确，以至于Excel无法解释并且应用了所有所需的格式？

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;
}

2 个解决方案

#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.

这里的问题是您将所有内容写入Excel作为字符串，Excel将这样存储它。在编辑并按Enter键时，Excel将重新解释单元格内容，然后可能以不同方式存储它。

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.

在DateTime上调用ToString（）时，默认格式为DD / MM / YY HH：mm：ss格式，这正是您在结果文件中看到的内容。

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.

Excel中的日期存储为表示自1900年以来经过的天数的数字。要获取此数字，可以调用ToOADate（）方法。

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

请参阅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!

将数据设置为double和适当的格式字符串，您应该得到您希望的结果！

#2

-3

Just replace (to string) with (formated value)

只需用（格式化值）替换（字符串）

#1