原文发布时间为:2008-10-11 —— 来源于本人的百度文章 [由搬家工具导入]
这个例子是从gridview中导出到Excel,可以举一反三,可以直接从数据库中取值放在DataSet中,然后再从DataSet中导出到Excel,原理是一样的。。。。
例子:
网站文件夹中的所有内容:
excel中的内容:[具有固定的Excel表头]
后台代码:
using System;
using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.IO;
using System.Data.OleDb;public partial class _Default : System.Web.UI.Page
{ protected void Page_Load(object sender, EventArgs e) {}
protected void Button1_Click(object sender, EventArgs e) { string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; NewFileName = Server.MapPath(NewFileName) ; //根据模板正式生成该Excel文件 File.Copy(Server.MapPath("~/Module01.xls"), NewFileName,true); //建立指向该Excel文件的数据库连接 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'"; OleDbConnection Conn = new OleDbConnection(strConn); Conn.Open(); OleDbCommand Cmd = new OleDbCommand("", Conn); foreach (GridViewRow gr in GridView1.Rows) { string sql = "insert into [Sheet1$]"; sql += "([课程编号],[课程名字]) values("; sql += "'" + gr.Cells[0].Text + "','" + gr.Cells[1].Text + "')"; Cmd.CommandText = sql; Cmd.ExecuteNonQuery(); } Conn.Close(); //打开要下载的文件,并把该文件存放在FileStream中 FileStream fs = File.OpenRead(NewFileName); //文件传送的剩余字节数:初始值为文件的总大小 long length = fs.Length;Response.Buffer = false;
Response.AddHeader("Connection", "Keep-Alive"); Response.ContentType = "application/octet-stream"; Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("课程.xls")); Response.AddHeader("Content-Length", length.ToString());//存放欲发送数据的缓冲区
byte[] buf = new byte[1000];//每次实际读取的字节数
int ByteToRead; //剩余字节数不为零,继续传送 while (length > 0) { //客户端浏览器还打开着,继续传送 if (Response.IsClientConnected) { //往缓冲区读入数据 ByteToRead = fs.Read(buf, 0, 1000); //把缓冲区的数据写入客户端浏览器 Response.OutputStream.Write(buf, 0, ByteToRead); //立即写入客户端 Response.Flush(); //剩余字节数减少 length -= ByteToRead; } else { //客户端浏览器已经断开,阻止继续循环 length = -1; } } fs.Close(); File.Delete(NewFileName); }}
第二种方法:
protected void Button2_Click(object sender, EventArgs e)
{ Response.Clear(); Response.Buffer = true; Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("课程.xls")); //定义输出文件和文件名 Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "Excel";//设置输出文件类型为excel文件。 this.EnableViewState =false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true); StringWriter oStringWriter = new StringWriter(myCItrad); HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter); this.GridView1.RenderControl(oHtmlTextWriter); //要导出的DataGrid Response.Write(oStringWriter.ToString()); Response.End(); } //上面的方法 还要在这个页面中重载这个方法 不然会出以下的异常 // 类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内。 public override void VerifyRenderingInServerForm(Control control) { }