//数据表导出为txt private void ExportTXT()
{
string str_File=""; SqlConnection cn = new SqlConnection("server=.; database=test;uid=sa;pwd="); string s_temp="select * from login"; string SaveFileName="test.txt"; try
{ cn.Open(); SqlCommand cmtemp=new SqlCommand(s_temp,cn); SqlDataReader drtemp=cmtemp.ExecuteReader(); while(drtemp.Read()) { str_File=str_File+drtemp["ID"].ToString().Trim()+","; str_File=str_File+drtemp["uid"].ToString().Trim()+","; str_File=str_File+drtemp["pwd"].ToString().Trim()+","; str_File=str_File+"\r\n";
} if(File.Exists("c:\\"+SaveFileName)) { Response.Write("该文件已经存在!已经被删除,请重新再次导出即可"); File.Delete("c:\\"+SaveFileName); return; } else { StreamWriter sw=File.CreateText("c:\\"+SaveFileName); // //创建文本文件 sw.WriteLine(str_File); sw.Close(); Response.Write("写入文件成功!"); } } catch(Exception ex) { Response.Write("错误"+ex.Message); } finally { cn.Close(); }
} //DataGrid导出为Excel private void ExportExcel() { if(this.DataGrid1.Items.Count==0) { Response.Write("<script>alert('对不起,你没有查询到任何记录,不能导出数据')</script>"); } else { Response.Clear(); Response.Buffer= true; Response.Charset="GB2312"; Response.AppendHeader("Content-Disposition","attachment;filename=report.xls"); Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 this.EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.DataGrid1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } }
OutPutExcel.aspx
<%@ Page language="c#" Codebehind="OutPutExcel.aspx.cs"
AutoEventWireup="false" Inherits="eMeng.Exam.OutPutExcel" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>OutPutExcel</title>
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
<asp:Button id="Button1" runat="server" Text="输出到Excel"></asp:Button>
</form>
</body>
</HTML>
OutPutExcel.aspx.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace eMeng.Exam
{
/// <summary>
/// OutPutExcel 的摘要说明。
/// </summary>
public class OutPutExcel : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
DataGrid1.DataSource=CreateDataSource();
DataGrid1.DataBind();
}
/// <summary>
/// 创建数据源
/// </summary>
/// <returns>DataView</returns>
ICollection CreateDataSource()
{
DataTable dt = new DataTable();
DataRow dr;
dt.Columns.Add(new DataColumn("身份证号码", typeof(string)));
dt.Columns.Add(new DataColumn("图书单价",typeof(decimal)));
dt.Columns.Add(new DataColumn("购买数量",typeof(Int32)));
dt.Columns.Add(new DataColumn("总价格",typeof(decimal)));
for (int i = 0; i < 30; i++)
{
dr = dt.NewRow();
dr[0] = "123456789123456789";
dr[1] = 100 * i /3.0;
dr[2] = i + 5;
dr[3] = (decimal)dr[1] * (Int32)dr[2];
dt.Rows.Add(dr);
}
DataView dv = new DataView(dt);
return dv;
}
/// <summary>
/// 输出到Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Button1_Click(object sender, System.EventArgs e)
{
Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:￥#,###.00");
}
}
}
}
//出处孟子E章http://dotnet.aspx.cc/
datagrid数据导出到excel文件给客户端下载方法一：导出到csv文件，存放在服务器端任一路径，然后给客户下载 优点： 1、可以进行身份认证后给客户下载，如果放到非web目录就没有对应的url，客户无法随时下载。 2、也是因为生成了文件，所以占用了服务器的空间，但是可以把文件名存放到数据库，再次给客户下载的时候不需要重复生成文件。 3、csv文件是文本文件，逗号隔开字段，回车隔开行，易于数据导入导出。 实现方法： SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]); SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn); DataSet ds=new DataSet(); da.Fill(ds,"table1"); DataTable dt=ds.Tables["table1"]; string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数 FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312")); sw.WriteLine("自动编号,姓名,年龄"); foreach(DataRow dr in dt.Rows) { sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); } sw.Close(); Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name)); Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载 Response.WriteFile(name); // 把文件流发送到客户端 Response.End(); 方法二：导出到csv文件，不存放到服务器，直接给浏览器输出文件流 优点： 1、随时生成，不需要占用资源 2、可以结合身份认证 3、同样利于数据交换 实现方法： SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]); SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn); DataSet ds=new DataSet(); da.Fill(ds,"table1"); DataTable dt=ds.Tables["table1"]; StringWriter sw=new StringWriter(); sw.WriteLine("自动编号,姓名,年龄"); foreach(DataRow dr in dt.Rows) { sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); } sw.Close(); Response.AddHeader("Content-Disposition", "attachment; filename=test.csv"); Response.ContentType = "application/ms-excel"; Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); Response.Write(sw); Response.End(); 对方法一，二补充一点，如果你希望导出的是xls文件分隔符用\t就可以了，不要用逗号 代码修改如下： sw.WriteLine("自动编号\t姓名\t年龄"); foreach(DataRow dr in dt.Rows) { sw.WriteLine(dr["ID"]+"\t"+dr["vName"]+"\t"+dr["iAge"]); } 另外，修改输出的文件扩展名为xls即可。 方法三：从datagrid导出html代码，生成excel文件，给客户端下载 优点： 1、有固定的格式，样子好看（datagrid的样子） 局限性： 1、不适合数据交换，里面有html代码，比较乱，没有固定格式 2、datagrid不能有分页、排序等，否则出错 实现方法： Response.Clear(); Response.Buffer= false; Response.Charset="GB2312"; Response.AppendHeader("Content-Disposition","attachment;filename=test.xls"); Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); Response.ContentType = "application/ms-excel"; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.DataGrid1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); 在这里说明一点：有的网友反映代码出现“没有dr["id"]”之类的错误，这个代码是按照我的数据结构来写的，到时候相关的字段要换成你自己的才是。 还有就是如果文件名需要中文的话，这么修改Response.AddHeader("Content-Disposition", "attachment; filename="+System.Web.HttpUtility.UrlEncode("中文",System.Text.Encoding.UTF8)+".xls"); 摘自lovecherry.cnblogs.com/