public void ExportExcel(DataTable dtData, string filename)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// 设置编码和附件格式
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename) + ".xls");//加HttpUtility.UrlEncode()方法处理文件名乱码
curContext.Response.Charset = "gb2312";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
//HttpContext.Current.Response.ContentType=".xls/.txt/.doc";image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
curContext.Response.ContentType = ".xls";
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string paths = Server.MapPath("db1.mdb");
string path = this.paths(paths);
string sql = "select userNum as 编号 ,userName as 姓名,accountPlace as 开户行所在地,bankName as 开户行详细信息,cardID as 账号, mobilTelephone as 电话号码, demo as 备注 from member where loginTime between format$( dateadd (/"d/",-Weekday(now()),DateValue(now())),/"yyyy-MM-dd/") and format$( dateadd(/"d/",-Weekday(now())+6,DateValue(now())),/"yyyy-MM-dd/") and thread=1 and state='1'";
SQLDAL.DBhelper db = new SQLDAL.DBhelper(path);
DataTable da = db.getTable(sql, path);
for (int i = 0; i < da.Rows.Count; i++)
{
da.Rows[i]["账号"] = da.Rows[i]["账号"].ToString() + "'";
}
ExportExcel(da, "aaa");
}