导出Excel,格式化数据,该如何处理

2012-04-23

导出Excel,格式化数据如果数值型的数据位数过长的话,就会显示比如这样的数据:1.3301E+11。已经试过以下这种

导出Excel,格式化数据
如果数值型的数据位数过长的话,就会显示比如这样的数据:1.3301E+11。
已经试过以下这种方法,没有起到作用:e.Item.Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
请问谁有比较好的方法没?

[解决办法]
你使用的方法有问题吧
你直接拷贝运行下面的代码

HTML code

<%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">  protected void Page_Load(object sender, EventArgs e)  {    if (!Page.IsPostBack)    {      GridView1.DataSource = this.CreateDataSource();      GridView1.DataBind();    }  }  System.Data.DataTable CreateDataSource()  {    System.Data.DataTable dt = new System.Data.DataTable();    System.Data.DataRow dr;    dt.Columns.Add(new System.Data.DataColumn("身份证号码", typeof(string)));    dt.Columns.Add(new System.Data.DataColumn("图书单价", typeof(decimal)));    dt.Columns.Add(new System.Data.DataColumn("购买数量", typeof(Int32)));    dt.Columns.Add(new System.Data.DataColumn("总价格", typeof(decimal)));    for (int i = 0; i < 6; i++)    {      dr = dt.NewRow();      dr[0] = "123456789123456789123456789123456789";      dr[1] = 100 * i / 3.0;      dr[2] = i + 5;      dr[3] = (decimal)dr[1] * (Int32)dr[2];      dt.Rows.Add(dr);    }    return dt;  }  protected void Button1_Click(object sender, EventArgs e)  {    Response.Clear();    Response.Buffer = true;    Response.Charset = "GB2312";    Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");    Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");    Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。     this.EnableViewState = false;    System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);    System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);    System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);    this.GridView1.RenderControl(oHtmlTextWriter);    Response.Write(oStringWriter.ToString());    Response.End();  }  public override void VerifyRenderingInServerForm(Control control)  {  }  protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)  {    if (e.Row.RowType == DataControlRowType.DataRow)    {      e.Row.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");    }  }</script><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">  <title></title></head><body>  <form id="form1" runat="server">  <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">  </asp:GridView>  <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" />  </form></body></html>
[解决办法]
你可以在导入Excel方法中加上
string style = "<style>td{mso-number-format:\"\\@\";}</style>";//这句写在开头
Response.Write(style);//这句写在Response.Write(sw.ToString())前面
[解决办法]
最好的导入方法是采用这种方法

http://dotnet.aspx.cc/file/Export-Gridview-To-Excel-With-Multi-Sheet.aspx

兼容Office 2010
[解决办法]
这一列的值 前面加空格试试。

热点排行

asp.net

导出Excel,格式化数据,该如何处理