首页 > 代码库 > .net解决数据导出excel时的格式问题

.net解决数据导出excel时的格式问题

2024-07-17 14:51:55   220人阅读

在项目中一般都需要将报表数据导出到EXCEL中，但经常出现导出长串数据(如身份证)到EXCEL中后显示为科学计数法的格式，或者报表中显示为001的数据导出到Excel后成了1的格式。

     下面简单介绍一下以上问题的解决方法：

     1、首先，了解一下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中呢？下面以datagrid控件为例，介绍其格式化代码。

  2、实例

 1 <html xmlns="http://www.w3.org/1999/xhtml" > 2 <head runat="server"> 3     <title>导出数据到Excel</title> 4 </head> 5 <body> 6     <form id="form1" runat="server"> 7     <div> 8      9         <asp:DataGrid ID="DataGrid1" runat="server" 10             onitemdatabound="DataGrid1_ItemDataBound" >11         </asp:DataGrid>12         <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="导出数据到Excel" />13     14     </div>15     </form>16 </body>17 </html>
前台
 1 using System; 2 using System.Collections; 3 using System.Configuration; 4 using System.Data; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.HtmlControls; 9 using System.Web.UI.WebControls;10 using System.Web.UI.WebControls.WebParts;11 12 namespace FLX.ComplexQuery13 {14     public partial class OutPutExcel : System.Web.UI.Page15     {16         protected void Page_Load(object sender, EventArgs e)17         {18             DataGrid1.DataSource = CreateDataSource();19             DataGrid1.DataBind();20         }21 22         protected void Button1_Click(object sender, EventArgs e)23         {24             Response.Clear();25             Response.Buffer = true;26             Response.Charset = "GB2312";27             Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");28             Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文29             Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 30             this.EnableViewState = false;31             System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);32             System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);33             System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);34             this.DataGrid1.RenderControl(oHtmlTextWriter);35             Response.Write(oStringWriter.ToString());36             Response.End();37         }38 39         ICollection CreateDataSource()40         {41 42             DataTable dt = new DataTable();43             DataRow dr;44             dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));45             dt.Columns.Add(new DataColumn("图书单价", typeof(decimal)));46             dt.Columns.Add(new DataColumn("购买数量", typeof(Int32)));47             dt.Columns.Add(new DataColumn("总价格", typeof(decimal)));48             for (int i = 0; i < 30; i++)49             {50                 dr = dt.NewRow();51 52                 dr[0] = "123456789123456789";53                 dr[1] = 100 * i / 3.0;54                 dr[2] = i + 5;55                 dr[3] = (decimal)dr[1] * (Int32)dr[2];56                 dt.Rows.Add(dr);57             }58             DataView dv = new DataView(dt);59             return dv;60         }61 62         protected void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)63         {64             if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)65             {66                 //将整个datagrid都格式化为文本格式67                 int datagridcolumns; //datagrid显示列的数量,68                 //获取显示列的数量可以从数据源那里进行获取69                 //比如绑定DataGrid的数据源是DataSet                   70                 //datagrid显示列的数量 = ds.tables[0].Columns.Count;  71                 for (int i = 0; i < datagridcolumns; i++)72                 {73                     e.Item.Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@");74                 }75 76                 //对需要格式化的列进行格式化77                 //e.Item.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");78                 //e.Item.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat::#,##0.00");79                // e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");          80             }81         }82     }83 }
后台

3、动态绑定数据源示例

 //导出        function GetExcel() {            var model = {};            model.CompanyName = mini.get("CompanyName").getValue();            model.DateFrom = mini.get("DateFrom").getValue();            model.DateTo = mini.get("DateTo").getValue();            var json = mini.encode(model);            window.open("EntReport/SoftwareRP/Export?model=" + json);        }
前台js方法
 1       /// <summary> 2         /// 导出Excel 3         /// </summary> 4         /// <param name="model"></param> 5         public void Export(string model) 6         { 7             DataTable table = new DataTable(); 8             Dictionary<string, string> dic = new Dictionary<string, string>(); 9             JavaScriptSerializer js = new JavaScriptSerializer();10             dic = js.Deserialize<Dictionary<string, string>>(model);11             System.Data.DataSet ds = new ZhishiPatent_BLL().QueryDataSet(dic);12             table = ds.Tables[0];13 14             Response.Clear();15             Response.Buffer = true;16             Response.Charset = "GB2312";//UTF-817             Response.AppendHeader("Content-Disposition", "attachment;filename=" + "知识专利统计.xls");18             Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");19             Response.ContentType = "application/ms-excel";20             Response.Write(ImportToExcel(table));21             Response.End();22         }23         /// <summary>24         /// 设置样式25         /// </summary>26         /// <param name="table"></param>27         /// <returns></returns>28         private string ImportToExcel(DataTable table)29         {30             System.Text.StringBuilder sb = new System.Text.StringBuilder();31             DataGrid gv = new DataGrid();32             gv.ItemDataBound += new DataGridItemEventHandler(gv_ItemDataBound);33             gv.AlternatingItemStyle.BackColor = System.Drawing.Color.White;34             gv.AlternatingItemStyle.ForeColor = System.Drawing.Color.FromArgb(40, 71, 117);35             gv.HeaderStyle.BackColor = System.Drawing.Color.FromArgb(93, 123, 157);36             gv.HeaderStyle.Font.Bold = true;37             gv.HeaderStyle.ForeColor = System.Drawing.Color.White;38             gv.HeaderStyle.VerticalAlign = VerticalAlign.Middle;39             gv.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;40             gv.ItemStyle.BackColor = System.Drawing.Color.FromArgb(247, 246, 233);41             gv.ItemStyle.ForeColor = System.Drawing.Color.FromArgb(51, 51, 51);42             gv.DataSource = table;43             gv.DataBind();44             System.IO.StringWriter oStringWriter = new System.IO.StringWriter(new System.Globalization.CultureInfo("zh-CN"));45             System.Web.UI.HtmlTextWriter ht = new System.Web.UI.HtmlTextWriter(oStringWriter);46             gv.RenderControl(ht);47             return oStringWriter.ToString();48         }49 50         private void gv_ItemDataBound(object sender, DataGridItemEventArgs e)51         {52             if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)53             {54                 foreach (TableCell item in e.Item.Cells)55                 {56                     item.Attributes.Add("style", "vnd.ms-excel.numberformat: @");57 58                 }59                 e.Item.Cells[5].HorizontalAlign = HorizontalAlign.Right;60             }61 62         }
后台

 

 


联系
我们