DataGrid/DataSet导出到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