原文发布时间为:2008-10-11 —— 来源于本人的百度文章 [由搬家工具导入]
这个例子是从gridview中导出到Excel,可以举一反三,可以直接从数据库中取值放在DataSet中,然后再从DataSet中导出到Excel,原理是一样的。。。。
例子:
网站文件夹中的所有内容:
![f5c8f3161012815020a4e91b.jpg](https://i-blog.csdnimg.cn/blog_migrate/9336f530b0650debfed8fe66f37776e2.jpeg)
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)
{
}