本文介绍了一种从DataSet和DataGrid导出数据到CSV文件的方法，并提供了将DataGrid数据导出为Excel文件的功能。通过具体示例展示了如何创建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>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
}if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestGrid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[TestGrid]GOCREATE 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
扫一扫
1182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
