在Excel XML工作表中格式化DateTime [英] Formatting DateTime in Excel XML worksheet

问题描述

当使用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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！