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
