问题描述
当使用ASP.NET生成Excel XML工作簿时，我将在一行中附加3个单元格，例如:
When generating an Excel XML workbook with ASP.NET i'm appending 3 cells in a row like:
sb.Append(String.Format(
"<Cell><Data ss:Type=\"DateTime\">{1}</Data></Cell>{0}",
Environment.NewLine,
item.DateAvailable.ToString("yyyy-MM-ddTHH:mm:ss.fffffffK")
)); // newlines added for readability;
item.DateAvailable 是C# DateTime ，必须在Excel中以单元格-DateTime"结尾.
item.DateAvailable is a C# DateTime that has to end in Excel as An cell-DateTime.
如此
在VS2010中通过组装进行调试的值:
value from debug in VS2010 in asssembly:
2011-08-15T01:16:06.8470000
excel中的值:
40761,2299142361
手动转换单元格格式后:
after manual conversion of the cell formatting:
6-08-11 5:31
但是，在转换时，它会在excel中输出为数字，并且单元格格式设置为自定义" .当我手动将单元格格式更改回DateTime时正确吗???日期以正确的格式显示.但是，当然，企业不会接受必须转换的原始数据;)
However, when converting it outputs in excel as a number, and the cell formatting is set to custom. When I manually change the cell formatting back to DateTime the correct??? date is shown in the correct format. But of course, the business would not accept such a raw data they have to convert themselves ;)
也许我使用了错误的StringFormat?
在这种情况下写入DateTime并以正确的DateTime格式在Excel中输出的正确格式是什么?
推荐答案
是的，您使用的是错误的"字符串格式，即Excel无法自动将其识别为日期的格式.放下 T .另外，Excel中的日期时间格式至少四舍五入到毫秒，如果不是整数秒，那么您也可以舍弃其中的一些小数位.
Yes, you are using a "wrong" string format, i.e. a format that Excel does not automatically recognize (or parse appropriately) as a date. Drop the T. Also, the date-time format in Excel rounds at least to milliseconds, if not to integer seconds, so you might as well drop some of those decimal places.
这应该有效:
item.DateAvailable.ToString("yyyy-MM-dd HH:mm:ss.ffff")
这篇关于在Excel XML工作表中格式化DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！