页面导出为Excel的时间格式的问题
2020-01-18 01:29:53
供稿：网友
现象：
是这样的，公司的一个系统，新增加一个新的功能，在页面生成报表时允许用户选择他prefer的时间格式，有下面四种时间格式可供选择：
Default Option: yyyy-mm-dd
Descriptive (1-Jan-07)
Short Date: mm/dd/yy
Short Date: dd/mm/yy
另外，用户可以选择报表是页面形式还是导出为Excel文件。原有的处理在页面上显示时间格式没有任何问题，但是昨天发现，如果导出Excel，那么时间格式就出错了:(
原因：
经过这半天的摸索，终于有了一些头绪，下面首先贴出我搜到的关于这方面的资料：
---------------------------偶就是传说中华丽的分割线啦拉拉~~~----------------------------
当我们把web页面上的数据导成excel形式时，有时候我们的数据需要以特定的格式呈现出来，这时候我们就需要给cell添加一些样式规格信息。
首先，我们了解一下excel从web页面上导出的原理。当我们把这些数据发送到客户端时，我们想让客户端程序（浏览器）以excel的格式读取它，所以把 mime类型设为：application/vnd.ms-excel，当excel读取文件时会以每个cell的格式呈现数据，如果cell没有规定的格式，则excel会以默认的格式去呈现该cell的数据。这样就给我们提供了自定义数据格式的空间，当然我们必须使用excel支持的格式。下面就列出常用的一些格式：
1） 文本：vnd.ms-excel.numberformat:@
2） 日期：vnd.ms-excel.numberformat:yyyy/mm/dd
3） 数字：vnd.ms-excel.numberformat:#,##0.00
4） 货币：vnd.ms-excel.numberformat:￥#,##0.00
5） 百分比：vnd.ms-excel.numberformat: #0.00%
这些格式你也可以自定义，比如年月你可以定义为：yy-mm等等。那么知道了这些格式，怎么去把这些格式添加到cell中呢？很简单，我们只需要把样式添加到对应的标签对（即闭合标签）即可。如<td></td>，给标签对<td></td>添加样式，如下：
<td style="vnd.ms-excel.numberformat:@">410522198402161833</td>
同样，我们也可以给<div></div>添加样式，也可以给<tr></tr>，<table></table>添加样式，这样就会引入一个问题，你注意到了吗？先看如下的代码：
<table style='vnd.ms-excel.numberformat:#,##0.00'>
<tr>
<td>542</td>
<td style='vnd.ms-excel.numberformat: #0.00%'>0.25</td>
</tr>
</table>
对，当我们在父标签对和子标签对都添加样式时，数据会以哪一个样式呈现呢？经过测试，会以离数据最近的样式呈现，这也是符合我们的意愿的（好像也符合一句俗话：县官不如现管）。这样我们就可以通过改变样式而改变数据在excel中呈现的方式（这些样式规格你可以在前台页面上添加也可以在后台代码里给相应的控件如：DataGrid等添加这些样式）。如果你的应用比较简单，那么这已经足够满足你的需求。但如果你的应用比较复杂，那么你也可以采取一种方式来达到不同的数据呈现效果。下面，我就举一个稍微复杂一点的应用。
例如：你的数据要呈现给不同国家和地区的用户查看，这样数据的呈现的格式就会不一样，那么我们怎么解决这个问题呢？当然了，你可以手工把这些数据处理好，但这毕竟不是最好的方法，因为如果我们每增加一个其他国家或地区的用户，那么我们就需要把所有的数据以客户要求的格式处理一遍，当数据量很大时，这无疑是一件很沉重且无聊的工作。那么我们究竟应该怎样解决类似这样的问题呢？下面我说一下，我自己的看法：把这些格式化的信息抽取到一个xml文件中，程序运行时根据不同的客户读取不同的格式化信息，然后把这些格式化信息动态的添加到我们的数据上，这样，当我们每增加一个其他国家或地区的用户时，我们只需要多增加一个xml文件，把对应的格式化信息写入这个xml文件，然后当这个国家或地区的用户查看时，就把对应的格式化信息读取出来应用到数据上即可。
以上这个例子是我突然想到的，相信跨国企业的公司会遇到类似的问题，解决方法只是提供给大家一个思路，希望可以起到抛砖引玉的效果。
DataGrid：
Asp.Net WebForm中DataGrid导出的时候，在ItemDataBound内
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
}
如果在WinForm内则可以
Excel.Range range = (Excel.Range)worksheet.Cells[1,1];
range.NumberFormat = Excel.XlParameterDataType.xlParamTypeUnknown;
VB:
If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
e.Item.Cells(1).Attributes.Add("style", "vnd.ms-excel.numberformat:@")
End If
如果是Label，则在<td>的style中加上：
<td style="height: 22px; vnd.ms-excel.numberformat:@" bgcolor="#eff3fb">
<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label></td>
如果是Gridview，(07.12.13 Update)
则需要在后台打印click事件代码中加两句话：
// More Codes
string style = @"<style> .text { mso-number-format:/@; } </script> ";
Response.Write(style);
Response.Output.Write(oStringWriter.ToString());
---------------------------------------------------------------
下面开始说我处理这个问题的过程：
首先，根据上面那篇文章中说的那样，在<td></td>标签上加上style属性，并根据用户选择的时间格式设定：
<TD VALIGN=TOP style="vnd.ms-excel.numberformat:<%=DateFormat%>">...</td>
然后开始测试，结果前三种时间格式都没问题，最后一个：dd/mm/yy，有的时间正确的，有的是错误的，错误的时间均为dd>12的那些。为什么会这样呢，经过反复的测试琢磨，最后终于弄清楚了：原来，在我们使用导出数据到Excel中去时，Excel会从中作梗！不只是这样，我们可以试着在Excel中输入时间，不管你输入的是什么格式，它都会把这个时间强行转化为 mm/dd/yyyy 格式。我传过去的日期（例如：30/10/08）到它那边，它就傻了，因为它不认识了，然后在cell里显示时，就老老实实地显示为30/10/08，也算歪打正着吧（其实是错的）。但是如果是 1/10/08 (这个是我已经转换好的 dd/mm/yy 格式)呢？它可不管，认为这个（1/10/08）是 mm/dd/yy 格式的，不管三七二十一，转换为 1/10/2008，注意，这个（ 1/10/2008）的意思是 2008年1月10日，已经完全背离了我的意愿了:(
忘了说一个东西了，我们从数据库中取出日期数据时，会根据用户选择的格式（上面列出的4种格式）来进行format。这时我想到一个trick，就是如果用户选择的是dd/mm/yy这种格式，我先不把date格式化为 dd/mm/yy，而是mm/dd/yy，这样数据到达Excel时它就可以分辨出来了，同时，由于我指定了<td>的 style 属性，excle会严格地按照那种格式来进行显示（不管Excel上显示的时间格式是什么样的，当你编辑时间是，你会看到，你可以编辑的格式永远是 mm/dd/yyyy）。
至此，问题圆满得到解决。
