有没有什么将Datagrid结果导出到Excel的通用方法？

[ynduanlian]

查询的结果都用DataGrid来展示，但现有多个表格，不知用没有什么通用点的方法能让这些Datagrid都能导出为Excel文件？
也就是不管我的Datagrid的行列怎么变，都能将其导为Excel文件的方法？
如何编程实现？

[wuyq11]

打开excel模板，通过数据源赋值给单元格

[femg93]

我有一个这样的例子，不知是否可以帮得到你，如果你觉得可以的话，就到我的资料中下载一下吧

[行者无疆-Kevin]

有一个非常好的GridView导出Excel类

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

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.Text.RegularExpressions;

namespace Dealeasy.ExportDataSet
{
public class ExportDataSet
{
/// <summary>
/// 导出DataSet到客户端
/// </summary>
/// <param name="dsResults">源DataSet</param>
/// <param name="enExport">导出类型</param>
/// <param name="strColDelim">列字符</param>
/// <param name="strRowDelim">行字符</param>
/// <param name="strFileName">导出文件名</param>
public static void WebExportDataSet(DataSet dsResults , ExportFormat enExport,string strColDelim, string strRowDelim, string strFileName)
{
GridView dgExport = new GridView();
//DataGrid dgExport = new DataGrid();
dgExport.AllowPaging =false;
dgExport.DataSource =dsResults;
dsResults.DataSetName ="NewDataSet";
dgExport.DataMember = dsResults.Tables[0].TableName;
dgExport.DataBind();
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Buffer= true;
System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("gb2312");
System.Web.HttpContext.Current.Response.Charset = "";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +strFileName );
switch(enExport.ToString().ToLower())
{
case "xls":
{
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
break;
}
case "custom":
{
string strText;
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);

break;
}
case "csv":
{
string strText;
strRowDelim = System.Environment.NewLine;
strColDelim = ",";
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "tsv":
{
string strText;
strRowDelim = System.Environment.NewLine;
strColDelim = "\t";
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "xml":
{
System.Web.HttpContext.Current.Response.ContentType = "text/xml";
System.Web.HttpContext.Current.Response.Write(dsResults.GetXml());
break;
}
case "htm":
{
System.Web.HttpContext.Current.Response.ContentType = "text/html";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
break;
}
}
System.Web.HttpContext.Current.Response.End ();
}

//"Export To a Delim Format"
#region "Export To a Delim Format"
private static string ParseToDelim(string strText, string strRowDelim , string strColDelim)
{
Regex objReg = new Regex(@"(>\s+<)",RegexOptions.IgnoreCase);
strText = objReg.Replace(strText,"><");
strText = strText.Replace(System.Environment.NewLine,"");
strText = strText.Replace("</td></tr><tr><td>",strRowDelim);
strText = strText.Replace("</td><td>",strColDelim);
objReg = new Regex(@"<[^>]*>",RegexOptions.IgnoreCase);
strText = objReg.Replace(strText,"");
strText = System.Web.HttpUtility.HtmlDecode(strText);
return strText;
}
#endregion
}



public enum ExportFormat
{
XML,
XLS,
HTML,
CSV,
CUSTOM,
TSV
}
}

[阿非]

http://www.codeproject.com/KB/webforms/DAtaGridExportToExcel.aspx

[阿非]

http://www.cnblogs.com/yangbin1005/archive/2007/12/26/1016014.html

[ynduanlian]

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

myDataGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

用这个的确非常ＮＢ，不得不说用asp.net编程真是种享受，但是上面的代码却有点问题：
这个导出的数据始终是页面第一次加载时Datagrid中的数据，而当页面PostBack后，条件变了，Datagrid中的数据也变了，这始再用上面的代码，导出的数据仍是页面Postback之前的，哪位能再指点下要如何解决这个问题？

[陆仁]

我常用的就是6楼这个
哈哈
好用

[ynduanlian]

[Quote=引用 7 楼 z_kikyou 的回复:]
我常用的就是6楼这个
哈哈
好用
[/Quote]
这个导出的数据始终是页面第一次加载时Datagrid中的数据，而当页面PostBack后，条件变了，Datagrid中的数据也变了，这始再用上面的代码，导出的数据仍是页面Postback之前的，哪位能再指点下要如何解决这个问题？

[major]

在需要导出Excel表格的页面加入：
public override void VerifyRenderingInServerForm(Control control)
{

//base.VerifyRenderingInServerForm(control);
}
例如：页面中DataGrid的名称为DataGrid1
调用方法：ReportToExcel(DataGrid1,"测试EXcel");
public static void ReportToExcel(System.Web.UI.Control ctl, string fileName)
{
//HttpContext.Current.Response.Clear();
//HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Charset = "gb2312";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", Encoding.UTF8).ToString());
ClearChildControls(ctl);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
ctl.RenderControl(htw);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
private static void RecursiveClear(Control control)
{

for (int i = control.Controls.Count - 1; i >= 0; i--)
{
RecursiveClear(control.Controls[i]);
}

if (control is Repeater)
{
control.Parent.Controls.Remove(control);
}
else if (control is LinkButton)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = ((LinkButton)control).Text;
control.Parent.Controls.Remove(control);
}
else if (control is Button)
{
control.Parent.Controls.Remove(control);
}

else if (control is System.Web.UI.WebControls.Image)
{
if (((System.Web.UI.WebControls.Image)control).Visible)
{
control.Parent.Controls.Add(new LiteralControl("<span style='font-size:8px;'>o</span>"));
}
control.Parent.Controls.Remove(control);
}
else if (control is ListControl)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = ((ListControl)control).SelectedItem.Text;
}
catch
{
}
control.Parent.Controls.Remove(control);

}

return;
}
protected static void ClearChildControls(Control dg1)
{
if (dg1 is GridView)
{
GridView dg = (GridView)dg1;
for (int i = dg.Columns.Count - 1; i >= 0; i--)
{
if (dg.Columns[i].GetType().Name == "ButtonColumn"
|| dg.Columns[i].GetType().Name == "CheckBoxField"
|| dg.Columns[i].GetType().Name == "CommandField")
{
dg.Columns[i].Visible = false;
}
}
RecursiveClear(dg1);
}
else
{
RecursiveClear(dg1);
}
}

[insus]

参考:
http://www.cnblogs.com/insus/articles/1400266.html

[lchh0917]

收藏先~

[ynduanlian]

顶!

[llsen]

这个可以，只是有点慢
告别excel操作烦恼

[llsen]

这个可以，只是有点慢
告别excel操作烦恼

[llsen]

这个可以，只是有点慢
告别excel操作烦恼

[ynduanlian]

Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

myDataGrid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();

这个导出的数据始终是页面第一次加载时Datagrid中的数据，而当页面PostBack后，条件变了，Datagrid中的数据也变了，这始再用上面的代码，导出的数据仍是页面Postback之前的，哪位能再指点下要如何解决这个问题？

[baobei7758]

在这段代码前,按你的查询条件再查询一次

[baobei7758]

在这段代码前,按你的查询条件再查询一次