asp.net导出excel及科学计数问题

 

导出最简单方法：

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%

 

 

 

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文件试试