protected void btExcel_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds = Session["myDataSet"] as DataSet;
DataTable DT = ds.Tables[0];
//生成将要存放结果的Excel文件的名称
string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//转换为物理路径
NewFileName = Server.MapPath("~/Temp/" + NewFileName);
//根据模板正式生成该Excel文件
File.Copy(Server.MapPath("~/Temp/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 (DataRow DR in DT.Rows)
{
string XSqlString = "insert into [Sheet1$]";
XSqlString += "([举办年度],[名称],[主题],[举办时间],[举办地点],[承办单位],[预算],[实际规模],[评价]) values(";
XSqlString += "'" + DR["Year"] + "',";
XSqlString += "'" + DR["CampName"] + "',";
XSqlString += "'" + DR["Topic"] + "',";
XSqlString += "'" + DR["HeldTime"] + "',";
XSqlString += "'" + DR["HeldPlace"] + "',";
XSqlString += "'" + DR["UndertakeDep"] + "',";
XSqlString += "'" + DR["Budget"] + "',";
XSqlString += "'" + DR["ActualScale"] + "',";
XSqlString += "'" + DR["Remarks"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
}
//操作结束，关闭连接
Conn.Close();
//打开要下载的文件，并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
//文件传送的剩余字节数：初始值为文件的总大小
long Length = Reader.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[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区
int ByteToRead; //每次实际读取的字节数
while (Length > 0)
{
//剩余字节数不为零，继续传送
if (Response.IsClientConnected)
{
//客户端浏览器还打开着，继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件
Reader.Close();
//删除该Excel文件
File.Delete(NewFileName);
}
方法2：
private void Export(GridView gvUser, string Style, string FileType, string FileName)
{
Response.ClearContent();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
Response.ContentType = FileType;
//page.Response.ContentType = "application/ms-excel";
Page.EnableViewState = false;
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
//turn off paging
gvUser.AllowPaging = false;
BindGrid(gvUser);
gvUser.RenderControl(hw);
Response.Write(Style);
Response.Write(sw.ToString());
//page.Response.Flush();
Response.End();
//turn the paging on again
gvUser.AllowPaging = true;
BindGrid(gvUser);
}
private void BindGrid(GridView gvUser)
{
DataSet ds = new DataSet();
ds = Session["myDataSet"] as DataSet;
gvUser.DataSource = ds;
gvUser.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
protected void GridViewCamp_RowDataBound(object sender, GridViewRowEventArgs e)
{
LinkButton deleteLB = (LinkButton)e.Row.FindControl("lbDelete");
if (deleteLB != null)
{
deleteLB.Attributes.Add("onclick", "return confirm('您确实要删除所选择的数据项吗？');");
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[0].Attributes["class"] = "text";
}
}
protected void btExcel_Click(object sender, EventArgs e)
{
string strStyle = @"<style> .text { mso-number-format:\@; }</style>";
//string strStyle = "";
Export(GridViewCamp,strStyle,"application/ms-excel", "myExcel.xls");
}
前台界面设置：EnableEventValidation="false"
<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="CampInfo.aspx.cs" Inherits="Default3" %>
