DataGrid/DataSet导出到excle和csv文

http://www.cnblogs.com/gaoxiang/articles/346951.html

 

#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 }