点晴MIS内部交流论坛 加入收藏
新手上路
论坛搜索
 您的位置:点晴MIS系统问题答疑『 经验分享&问题答疑 』浏览当前帖子  
登 陆注 册论坛帮助  

  网站搜索
  搜索范围: 搜索方式: 关键词(可用空格分开)  

  作者及文章信息: 本文热度:125 % 
admin

积分:73145
等级:网站管理员
文数:13811
注册:2004-7-20

 信息   留言   主页   编辑   引用    

楼 顶 

 Excel导出数据格式设置


公司系统新增加一个新的功能,在页面生成报表时允许用户选择时间格式,有下面四种时间格式可供选择:
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 style="vnd.ms-excel.numberformat:@">410522198402161833</td>

  • 1

同样,我们也可以给添加样式,也可以给<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>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

对,当我们在父标签对和子标签对都添加样式时,数据会以哪一个样式呈现呢?经过测试,会以离数据最近的样式呈现,这也是符合我们的意愿的(好像也符合一句俗话:县官不如现管)。这样我们就可以通过改变样式而改变数据在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:@"); }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

如果在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());

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

下面开始说我处理这个问题的过程:
首先,根据上面那篇文章中说的那样,在<td></td>标签上加上style属性,并根据用户选择的时间格式设定:

<TD VALIGN=TOP style="vnd.ms-excel.numberformat:<%=DateFormat%>">...</td>

  • 1

然后开始测试,结果前三种时间格式都没问题,最后一个: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时它就可以分辨出来了,同时,由于我指定了的 style 属性,excle会严格地按照那种格式来进行显示(不管Excel上显示的时间格式是什么样的,当你编辑时间是,你会看到,你可以编辑的格式永远是 mm/dd/yyyy)。
至此,问题圆满得到解决。


该文章在 2022/8/31 17:19:26 编辑过

  离 线  2022/8/31 17:19:25 
  本文章共有 0 页, 0 张回文,每页有 10 张回文 >> [ ]
页码:  
Copyright 2003-2022 ClickSun All Rights Reserved