网页中 将GridView中的内容导出到excel

[传奇3]

如题 网上看了些 都不行啊

[by_封爱]

说说你都看了什么? 说的好像你看了很多例子一样... 不过 这个你一定没看.... 否则就不会问这样的问题了.

[insus]

引用 楼主 u010529467 的回复:

如题 网上看了些 都不行啊

try it: http://www.cnblogs.com/insus/archive/2009/02/28/1400266.html

[泛小白]

public override bool EnableEventValidation {
	get { return false; }
	set { base.EnableEventValidation = false; }
}



public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
{
}


public static void ExportCommon(string XlsTittle, System.Web.UI.WebControls.GridView gridView, int[] hideColumns, string fileExt)
{
	HttpContext context = HttpContext.Current;
	System.Web.UI.WebControls.Label TextTittle = new System.Web.UI.WebControls.Label();
	System.Web.UI.WebControls.Label TextTimeFrame = new System.Web.UI.WebControls.Label();
	System.Web.UI.WebControls.Label TextNowTime = new System.Web.UI.WebControls.Label();

	TextTittle.Text = XlsTittle + "<br style='mso-data-placement:same-cell;'/>";
	TextNowTime.Text = "导出时间:" + DateAndTime.Now + "<br style='mso-data-placement:same-cell;'/>";
	TextTittle.ControlStyle.Font.Size = 15;
	TextTittle.ControlStyle.Font.Name = "微软雅黑";
	TextTittle.ControlStyle.ForeColor = Color.Black;
	context.Response.ClearContent();
	context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
	context.Response.AddHeader("content-disposition", string.Format("attachment; filename=Page{0}.{1}", gridView.PageIndex + 1, fileExt));
	switch (fileExt.ToLower()) {
		case "xls":
			context.Response.ContentType = "application/excel";
			break; // TODO: might not be correct. Was : Exit Select

			break;
		case "doc":
			context.Response.ContentType = "application/word";
			break; // TODO: might not be correct. Was : Exit Select

			break;
	}

	System.IO.StringWriter sw = new System.IO.StringWriter();
	HtmlTextWriter htw = new HtmlTextWriter(sw);

	Page page = new Page();
	HtmlForm form = new HtmlForm();
	gridView.EnableViewState = false;

	page.EnableEventValidation = false;

	page.DesignerInitialize();

	// 隐藏指定的列
	if (hideColumns != null) {
		foreach (int columnIndex in hideColumns) {
			if (columnIndex < gridView.Columns.Count) {
				gridView.Columns[columnIndex].Visible = false;
			}
		}
	}

	// 如果HeaderRow里的控件是button的话，则把它替换成文本
	foreach (TableCell tc in gridView.HeaderRow.Cells) {
		// TableCell里的每个Control
		foreach (Control c in tc.Controls) {
			// 如果控件继承自接口IButtonControl
			if (c.GetType().GetInterface("IButtonControl") != null && c.GetType().GetInterface("IButtonControl").Equals(typeof(IButtonControl))) {
				// 如果该控件不是“导出Excel”按钮则把button转换成文本
				tc.Controls.Clear();
				tc.Text = ((IButtonControl)c).Text;
			}
		}
	}


	// 将服务器控件的内容输出到所提供的 System.Web.UI.HtmlTextWriter 对象中
	//  gridView.RenderControl(htw)
	page.Controls.Add(TextTittle);
	page.Controls.Add(TextNowTime);
	page.Controls.Add(form);
	//把form放到page内

	form.Controls.Add(gridView);
	//再把gridview放到form中

	page.RenderControl(htw);
	//输出page
	HttpContext.Current.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=UTF-8>");
	context.Response.Write(sw.ToString());
	context.Response.Flush();
	context.Response.End();
}

[dongshaohua]

/// <summary>
/// 会员列表导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
string key = this.txtKey.Text.Trim();
string startTime = this.txtStartTime.Text.Trim();
string endTime = this.txtEndTime.Text.Trim();

string strWhere = " 1=1";
if (star == "1")
strWhere += " and userType=1";
else if (star == "2")
strWhere += " and userType=2";
else if (star == "3")
strWhere += " and a.uid in(select uid from user_CrmInfo where isactive=1)";

if (!string.IsNullOrEmpty(key))
{
strWhere += " and (username like '%" + key + "%' or account like '%" + key + "%' or a.uid in(select a1.uid from user_User a1,user_Memberdetail b1,user_CrmInfo c1 where a1.uid=b1.uid and a1.uid=c1.uid and b1.mobilePhone like '%" + key + "%'))";
}
if (!string.IsNullOrEmpty(startTime))
{
strWhere += " and regDate>='" + startTime + "'";
}
if (!string.IsNullOrEmpty(endTime))
{
strWhere += " and regDate<='" + endTime + "'";
}


DataTable data = new DataTable();

data.Columns.Add("昵称", Type.GetType("System.String"));
data.Columns.Add("真实姓名", Type.GetType("System.String"));
data.Columns.Add("注册时间", Type.GetType("System.String"));
data.Columns.Add("最后登录日期", Type.GetType("System.String"));
data.Columns.Add("身份证", Type.GetType("System.String"));

data.Columns.Add("电话号码", Type.GetType("System.String"));
data.Columns.Add("积分", Type.GetType("System.String"));
data.Columns.Add("邮箱是否验证", Type.GetType("System.String"));
data.Columns.Add("手机是否验证", Type.GetType("System.String"));
data.Columns.Add("上次登陆方式", Type.GetType("System.String"));
data.Columns.Add("出生日期", Type.GetType("System.String"));
data.Columns.Add("性别", Type.GetType("System.String"));

DataAccess dm = new DataAccess();
string sql = "select a.uid,a.ustate,a.userType,a.username,b.trueName,a.regDate,b.idNumber,b.mobilePhone,b.gender,b.birthYear,b.birthMonth,b.birthDay from user_User a inner join user_Memberdetail b on a.uid=b.uid where " + strWhere;
DataTable dt = dm.QueryTableData(sql);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
DataRow datadr = data.NewRow();
datadr["昵称"] = dr["username"].ToString();
datadr["真实姓名"] = dr["trueName"].ToString();
datadr["注册时间"] = dr["regDate"].ToString();
datadr["最后登录日期"] = GetLastTime(dr["uid"].ToString());
if (!string.IsNullOrEmpty(dr["idNumber"].ToString()))
{
StringBuilder sb = new StringBuilder("=\"");
sb.Append(dr["idNumber"].ToString()).Append("\"");
datadr["身份证"] = sb.ToString();
}
else
datadr["身份证"] = "";
datadr["电话号码"] = dr["mobilePhone"].ToString();
if (TS.Web.Com.CrmInfoControl.IsActiveCrm(int.Parse(dr["uid"].ToString())))
datadr["积分"] = TS.Web.Com.CrmManager.Get(dr["mobilePhone"].ToString(), "coodoo").New_currentscore;
else
datadr["积分"] = "0";
datadr["邮箱是否验证"] = GetIsMail(dr["userType"].ToString(), dr["ustate"].ToString());
datadr["手机是否验证"] = GetIsPhone(dr["userType"].ToString(), dr["ustate"].ToString());
datadr["上次登陆方式"] = GetUserType(dr["userType"].ToString());
datadr["出生日期"] = dr["birthYear"].ToString() + "-" + dr["birthMonth"].ToString() + "-" + dr["birthDay"].ToString();
datadr["性别"] = HelperClass.ParseAdminType(dr["gender"].ToString(), "Sex");
data.Rows.Add(datadr);
}
}


Response.Clear();
Response.ContentType = "application/octet-stream";
//使用UTF-8对文件名进行编码
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode("CoofansUserList.xls", System.Text.Encoding.UTF8) + "\"");
Response.ContentType = "application/ms-excel;";

using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
GridView gv = new GridView();
gv.DataSource = data.DefaultView;
gv.AllowPaging = false;
gv.DataBind();
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
}

}





数据源就是GridView里面的数据，重新到数据库查询一遍，where条件就是搜索拼接起来的，这个代码是完整的

[dongshaohua]

copy过去改下表之类的就可以了，成功的截图都出来了，如果这样还不会。。。。。。。。。。。。。。。。。。

[object-null]

两种思路： 1.服务器生成提供下载，写后端代码 2.客户端允许ActiveX下载，写JS代码

[babala_2804225217]

把GridView中的内容导出到Excel文档示例 private void Export(string FileType, string FileName) { Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF7; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString()); Response.ContentType = FileType; this.EnableViewState = false; StringWriter tw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); GridView1.RenderControl(hw); Response.Write(tw.ToString()); Response.End(); }

[邹琼俊]

参考我的博文：http://blog.csdn.net/zouyujie1127/article/details/12002177