DataGridDataSet导出到excle和csv文件

1.html代码 < HTML >      < HEAD >          < title > WriteToCVS </ title >          < meta  content ="False"  name ="vs_snapToGrid" >          < meta  content ="Microsoft Visual Studio .NET 7.1"  name ="GENERATOR" >          < meta  content ="C#"  name ="CODE_LANGUAGE" >          < meta  content ="JavaScript"  name ="vs_defaultClientScript" >          < meta  content ="http://schemas.microsoft.com/intellisense/ie5"  name ="vs_targetSchema" >      </ HEAD >      < body  MS_POSITIONING ="GridLayout" >          < form  id ="Form1"  method ="post"  runat ="server" >              < asp:DataGrid  id ="DataGrid1"  style ="Z-INDEX: 101; LEFT: 14px; POSITION: absolute; TOP: 109px"                 runat ="server"  BorderColor ="#CC9966"  BorderStyle ="None"  BorderWidth ="1px"  BackColor ="White"                 CellPadding ="4" >                  < FooterStyle  ForeColor ="#330099"  BackColor ="#FFFFCC" ></ FooterStyle >                  < SelectedItemStyle  Font-Bold ="True"  ForeColor ="#663399"  BackColor ="#FFCC66" ></ SelectedItemStyle >                  < ItemStyle  ForeColor ="#330099"  BackColor ="White" ></ ItemStyle >                  < HeaderStyle  Font-Bold ="True"  ForeColor ="#FFFFCC"  BackColor ="#990000" ></ HeaderStyle >                  < PagerStyle  HorizontalAlign ="Center"  ForeColor ="#330099"  BackColor ="#FFFFCC" ></ PagerStyle >              </ asp:DataGrid >              < asp:Button  id ="Button1"  style ="Z-INDEX: 102; LEFT: 18px; POSITION: absolute; TOP: 11px"  runat ="server"                 Text ="DataSet导出到csv文件"  Width ="148px" ></ asp:Button >              < asp:Button  id ="Button2"  style ="Z-INDEX: 103; LEFT: 19px; POSITION: absolute; TOP: 42px"  runat ="server"                 Text ="DataGrid导出到csv文件"  Width ="157px" ></ asp:Button >              < asp:Button  id ="Button3"  style ="Z-INDEX: 104; LEFT: 18px; POSITION: absolute; TOP: 73px"  runat ="server"                 Text ="DataGrid导出到Excel"  Width ="149px" ></ asp:Button >          </ form >      </ body > </ HTML > 2.cs代码 using  System.IO; using  System.Data.SqlClient; public   class  WriteToCVS : System.Web.UI.Page     {        protected System.Web.UI.WebControls.DataGrid DataGrid1;        protected System.Web.UI.WebControls.Button Button2;        protected System.Web.UI.WebControls.Button Button3;        protected System.Web.UI.WebControls.Button Button1;            private void Page_Load(object sender, System.EventArgs e)        {            if(!Page.IsPostBack)            {                string sql="select * from TestGrid";                ds=GetDataSet(sql);                this.DataGrid1.DataSource=ds;                this.DataGrid1.DataBind();            }        }        WriteDSToCsv 传入dataset生成csv文件#region WriteDSToCsv 传入dataset生成csv文件        public void WriteDSToCsv(DataSet ds)        {            string strFile="";            string path="";                        DataTable dt=ds.Tables[0];                        //文件信息设置            strFile=strFile+"LogBackUp";            strFile=strFile+DateTime.Now.ToString("yyyyMMddhhmmss");            strFile=strFile+".csv";            path=Server.MapPath(strFile);            //string[] strHead={"使用者姓名","员工编号","所属分行别","作业时间","使用功能","作业说明"};            System.IO.FileStream fs=new FileStream(path,System.IO.FileMode.Create,System.IO.FileAccess.Write);            StreamWriter sw=new StreamWriter(fs,new System.Text.UnicodeEncoding());            //画表头            for(int i=0;i<dt.Columns.Count;i++)            {                sw.Write(dt.Columns[i].ColumnName);                sw.Write("/t");            }            sw.WriteLine("");            //画表体            for(int i=0;i<dt.Rows.Count;i++)            {                sw.Write(DelQuota(dt.Rows[i]["UserID"].ToString()));                sw.Write("/t");                sw.Write(DelQuota(dt.Rows[i]["UserName"].ToString()));                sw.Write("/t");                                        sw.Write(DelQuota(dt.Rows[i]["provinceID"].ToString()));                sw.Write("/t");                sw.Write(DelQuota(dt.Rows[i]["cityID"].ToString()));                sw.Write("/t");                sw.Write(DelQuota(dt.Rows[i]["areaID"].ToString()));                sw.Write("/t");                sw.Write(DelQuota(dt.Rows[i]["Enabled"].ToString()));                                sw.WriteLine("");            }            sw.Flush();            sw.Close();                        }        #endregion        WriteDatagridToCsv 传入datagrid生成csv文件#region WriteDatagridToCsv 传入datagrid生成csv文件        public void WriteDatagridToCsv(System.Web.UI.WebControls.DataGrid grid)        {            string strFile="";            string path="";                        DataTable dt=ds.Tables[0];                        //文件信息设置            strFile=strFile+"LogBackUp";            strFile=strFile+DateTime.Now.ToString("yyyyMMddhhmmss");            strFile=strFile+".csv";            path=Server.MapPath(strFile);            string[] strHead={"UserID","UserName","provinceID","cityID","areaID","Enabled"};            System.IO.FileStream fs=new FileStream(path,System.IO.FileMode.Create,System.IO.FileAccess.Write);            StreamWriter sw=new StreamWriter(fs,new System.Text.UnicodeEncoding());            //画表头            for(int i=0;i<strHead.Length;i++)            {                sw.Write(strHead[i]);                sw.Write("/t");            }            sw.WriteLine("");            //画表体            for(int i=0;i<grid.Items.Count;i++)            {                sw.Write(DelQuota(grid.Items[i].Cells[0].Text.ToString()));                sw.Write("/t");                sw.Write(DelQuota(grid.Items[i].Cells[1].Text.ToString()));                sw.Write("/t");                                        sw.Write(DelQuota(grid.Items[i].Cells[2].Text.ToString()));                sw.Write("/t");                sw.Write(DelQuota(grid.Items[i].Cells[3].Text.ToString()));                sw.Write("/t");                sw.Write(DelQuota(grid.Items[i].Cells[4].Text.ToString()));                sw.Write("/t");                sw.Write(DelQuota(grid.Items[i].Cells[5].Text.ToString()));                                sw.WriteLine("");            }            sw.Flush();            sw.Close();                        }        #endregion        ToExcel#region ToExcel        public static void ToExcel(System.Web.UI.Control ctl,string FileName)        {            HttpContext.Current.Response.Charset ="UTF-8";            HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;            HttpContext.Current.Response.ContentType ="application/ms-excel";            HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls");            ctl.Page.EnableViewState =false;            System.IO.StringWriter  tw = new System.IO.StringWriter();            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);            ctl.RenderControl(hw);            HttpContext.Current.Response.Write(tw.ToString());            HttpContext.Current.Response.End();        }                #endregion        DelQuota#region DelQuota        public string DelQuota(string str)//删除特殊字符        {            string result=str;            string[] strQuota={"~","!","@","#","$","%","^","&","*","(",")","`",";","'",",",".","/",":","/,","<",">","?"};            for(int i=0;i<strQuota.Length;i++)            {                if(result.IndexOf(strQuota[i])>-1)                    result=result.Replace(strQuota[i],"");            }            return result;        }        #endregion        GetDataSet#region GetDataSet        public static DataSet GetDataSet(string sql)        {            string ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];            SqlDataAdapter    sda =new SqlDataAdapter(sql,ConnectionString);            DataSet ds=new DataSet();            sda.Fill(ds);            return ds;        }        #endregion        Web Form Designer generated code#region Web Form Designer generated code        override protected void OnInit(EventArgs e)        {            //            // CODEGEN: This call is required by the ASP.NET Web Form Designer.            //            InitializeComponent();            base.OnInit(e);        }                /**//// <summary>        /// Required method for Designer support - do not modify        /// the contents of this method with the code editor.        /// </summary>        private void InitializeComponent()        {                this.Button1.Click += new System.EventHandler(this.Button1_Click);            this.Button2.Click += new System.EventHandler(this.Button2_Click);            this.Button3.Click += new System.EventHandler(this.Button3_Click);            this.Load += new System.EventHandler(this.Page_Load);        }        #endregion        private void Button1_Click(object sender, System.EventArgs e)        {            WriteDSToCsv(ds);        }        private void Button2_Click(object sender, System.EventArgs e)        {            WriteDatagridToCsv(this.DataGrid1);                }        private void Button3_Click(object sender, System.EventArgs e)        {            ToExcel(this.DataGrid1,"meng");        }        property#region property        private DataSet ds        {            get            {                if(ViewState["ds"]!=null)                {                    return (DataSet)ViewState["ds"];                }                else                {                    return null;                }            }            set            {                ViewState["ds"]=value;            }        }                #endregion    } 3.数据库脚本 if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[TestGrid] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 ) drop   table   [ dbo ] . [ TestGrid ] GO CREATE   TABLE   [ dbo ] . [ TestGrid ]  (     [ UserID ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,     [ UserName ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,     [ provinceID ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,     [ cityID ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,     [ areaID ]   [ nvarchar ]  ( 50 ) COLLATE Chinese_PRC_CI_AS  NULL  ,     [ Enabled ]   [ bit ]   NULL  )  ON   [ PRIMARY ] GO