导出最简单方法:
const string style = @"<style> .text { mso-number-format:\@; } </style> "; Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(Name) + ".xls"); Response.ContentType = "application/excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); //htw.WriteLine(Name); System.Web.UI.LiteralControl lt = new LiteralControl(); lt.Text = "<div style=\"width:100%;font-bold:true;text-align:center;\">" +Name + "</div>"; if(Name!="")lt.RenderControl(htw); gvdata.RenderControl(htw); // Style is added dynamically Response.Write(style); Response.Write(sw.ToString()); Response.End();
导出出现科学计数解决办法一:
GridView gvdata = new GridView(); gvdata.HeaderStyle.BackColor = System.Drawing.Color.Silver; gvdata.HeaderStyle.Font.Bold = true; gvdata.HeaderStyle.HorizontalAlign = HorizontalAlign.Center; gvdata.RowDataBound += new GridViewRowEventHandler(gvdataRowDataBound); gvdata.DataSource = dtData; gvdata.DataBind(); const string style = @"<style> .text {mso-number-format:\@; } </style> "; Response.ClearContent(); Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(strFileName) + ".xls"); Response.ContentType = "application/excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); //htw.WriteLine(Name); System.Web.UI.LiteralControl lt = new LiteralControl(); lt.Text = "<div style=\"width:100%;font-bold:true;text-align:center;\">" + strFileName + "</div>"; if (strFileName != "") lt.RenderControl(htw); gvdata.RenderControl(htw); // Style is added dynamically Response.Write(style); Response.Write(sw.ToString()); Response.End(); public void gvdataRowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); e.Row.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); } }
另注: //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%
//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%
DataTable导出到Excel时身份证号信息自动转换为科学计数法的解决方案
做项目的时候是否遇到过这种问题,我今天遇到了就给大家分享下,在网上找的答案都是针对gridview控件的,还有就是该DataTable的身份证号列的类型为string,再有就是前面加单引号什么的,但是我都没有试验成功不知道是我人品的问题还是真不行,但是今天我整的这个方案完美避开了科学计数法 呵呵 废话少说上代码
1 DataTable dt = new DataTable();
2 dt.Columns.Add(new DataColumn("身份证号"));
3 DataRow dr = dt.NewRow();
4 StringBuilder sb = new StringBuilder("=\"");
5 sb.Append("1234567890123456789").Append("\"");
6 dr[0] = sb.ToString();
7 dt.Rows.Add(dr);
8 ImportAndExport.CreateExcel(dt, "1", "11.xls");
嘿嘿 够简单吧!原理就是Excel会自动把="1234567890123456789"认为计算字符串的公式 嘿嘿,不信的请新建个Exel文件试试