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 >
< 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;
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
}
{
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
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
wan