DataGrid输出到Excel并进行格式化处理

 在我们把 DataGrid上的数据导入到Excel的时候，如果遇到比较长的数字字符串，比如身份证号码，就会在Excel里当成数字看待，并转换成科学计数法的格式，造成数据的丢失，下面这个方法就解决了这个问题，并示例如何进行其它的 格式化。

查看例子
http://dotnet.aspx.cc/Exam/OutPutExcel.aspx

OutPutExcel.aspx 

<% @ Page language = " c# "  Codebehind = " OutPutExcel.aspx.cs "
 AutoEventWireup = " false "  Inherits = " eMeng.Exam.OutPutExcel "   %>
<! DOCTYPE HTML PUBLIC  " -//W3C//DTD HTML 4.0 Transitional//EN "   >
< HTML >
< HEAD >
< title > OutPutExcel </ title >     
</ HEAD >
< body >
< form id = " Form1 "  method = " post "  runat = " server " >
< asp:DataGrid id = " DataGrid1 "  runat = " server " ></ asp:DataGrid >
< asp:Button id = " Button1 "  runat = " server "  Text = " 输出到Excel " ></ asp:Button >
</ form >
</ body >
</ HTML >

OutPutExcel.aspx.cs

 

using  System;
using  System.Collections;
using  System.ComponentModel;
using  System.Data;
using  System.Drawing;
using  System.Web;
using  System.Web.SessionState;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.HtmlControls;

namespace  eMeng.Exam
... {
    /**//// <summary>

    /// OutPutExcel 的摘要说明。

    /// </summary>

    public class OutPutExcel : System.Web.UI.Page
    ...{
        protected System.Web.UI.WebControls.Button Button1;
        protected System.Web.UI.WebControls.DataGrid DataGrid1;

        private void Page_Load(object sender, System.EventArgs e)
        ...{
            // 在此处放置用户代码以初始化页面

            DataGrid1.DataSource = CreateDataSource();
            DataGrid1.DataBind();
        }
        /**//// <summary>

        /// 创建数据源

        /// </summary>

        /// <returns>DataView</returns>

        ICollection CreateDataSource()
        ...{

            DataTable dt = new DataTable();
            DataRow dr;
            dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
            dt.Columns.Add(new DataColumn("图书单价", typeof(decimal)));
            dt.Columns.Add(new DataColumn("购买数量", typeof(Int32)));
            dt.Columns.Add(new DataColumn("总价格", typeof(decimal)));


            for (int i = 0; i < 30; i++)
            ...{
                dr = dt.NewRow();

                dr[0] = "123456789123456789";
                dr[1] = 100 * i / 3.0;
                dr[2] = i + 5;
                dr[3] = (decimal) dr[1] * (Int32) dr[2];
                dt.Rows.Add(dr);
            }
            DataView dv = new DataView(dt);
            return dv;
        }
        /**//// <summary>

        /// 输出到Excel

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void Button1_Click(object sender, System.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.DataGrid1.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();
        }

        Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码

        override protected void OnInit(EventArgs e)
        ...{
            //

            // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。

            //

            InitializeComponent();
            base.OnInit(e);
        }

        /**//// <summary>

        /// 设计器支持所需的方法 - 不要使用代码编辑器修改

        /// 此方法的内容。

        /// </summary>

        private void InitializeComponent()
        ...{
            this.Button1.Click += new System.EventHandler(this.Button1_Click);
            this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
            this.Load += new System.EventHandler(this.Page_Load);

        }
        #endregion


        private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
        ...{
            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            ...{
                e.Item.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
                e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");
            }
        }
    }
}