C# 日期的 Excel 互操作单元格格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15603098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Interop cell formatting of Dates
提问by whytheq
My program creates an output in Excel.
我的程序在 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.
打开文件时，日期是混合的，如屏幕截图所示。
如果我实际上将光标放在 Excel 的计算框中（在屏幕打印中）并按 Enter，单元格的格式将恢复为正确的格式。
I'm using Microsoft.Office.Interop.Excelto move data from a Datatableto an Exceltemplate saved in my Solution.
我正在使用Microsoft.Office.Interop.Excel将数据从 a 移动Datatable到Excel我的解决方案中保存的模板。
Before putting the data into the cells I change each columns numberformat accordingly using a switch:
在将数据放入单元格之前，我使用以下命令相应地更改每一列的数字格式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 DATETIMEand the following:
在存储过程中，当我填充数据表时，我尝试使用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;
}
采纳答案by Charles Mager
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.
当您调用ToString()a 时DateTime，默认格式为 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!
将数据集作为 adouble和适当的格式字符串，您应该会得到您希望的结果！
回答by Mohand GK
Just replace (to string) with (formated value)
只需将（到字符串）替换为（格式化的值）
