导出Excel,格式化数据

Z_Lacey 2012-04-11 11:20:56

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

...全文

116 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

孟子E章 2012-04-11

打赏
举报

最好的导入方法是采用这种方法

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

兼容Office 2010

feiying102743 2012-04-11

打赏
举报

你可以在导入Excel方法中加上
string style = "<style>td{mso-number-format:\"\\@\";}</style>";//这句写在开头
Response.Write(style);//这句写在Response.Write(sw.ToString())前面

孟子E章 2012-04-11

打赏
举报

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

<%@ 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>