公司系統需要有將畫面上Grid直接轉成Excel的功能,因此我就Google了一些文章,中途遇到一些小困難,所以在此分享我的小心得,經過測試保證可以順利執行。
公司系統需要有將畫面上Grid直接轉成Excel的功能,因此我就Google了一些文章,中途遇到一些小困難,所以在此分享我的小心得,經過測試保證可以順利執行。
1.先套用轉出Excel的CLASS,注意檔案編碼最好為UTF8,轉出的表格是無格式的,可在轉出的TABLE語法加上CSS,或者在前面對整個TABLE套上CSS。
///
/// GridView轉出Excel
///
public class GridViewExportUtil
{
public static void Export(string fileName, GridView gv)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(
"content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.UTF8;
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
// Create a table to contain the grid
Table table = new Table();
// include the gridline settings
table.GridLines = gv.GridLines;
// add the header row to the table
if (gv.HeaderRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
table.Rows.Add(gv.HeaderRow);
}
// add each of the data rows to the table
foreach (GridViewRow row in gv.Rows)
{
GridViewExportUtil.PrepareControlForExport(row);
table.Rows.Add(row);
}
// add the footer row to the table
if (gv.FooterRow != null)
{
GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
table.Rows.Add(gv.FooterRow);
}
// render the table into the htmlwriter
table.RenderControl(htw);
// render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}
}
}
///
/// Replace any of the contained controls with literals
///
private static void PrepareControlForExport(Control control)
{
for (int i = 0; i < control.Controls.Count; i++)
{
Control current = control.Controls[i];
if (current is LinkButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
}
else if (current is ImageButton)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
}
else if (current is HyperLink)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
}
else if (current is DropDownList)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
}
else if (current is CheckBox)
{
control.Controls.Remove(current);
control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
}
if (current.HasControls())
{
GridViewExportUtil.PrepareControlForExport(current);
}
}
}
}
2.覆寫VerifyRenderingInServerForm,切換到 aspx 程式碼中,在 <%@ Page %> 中加入 EnableEventValidation="false" ,以解決無法正常Render出來的問題。
public override void VerifyRenderingInServerForm(System.Web.UI.Control Control)
{
//覆寫,不執行 MyBase.VerifyRenderingInServerForm 方法,解決執行 RenderControl 產生的錯誤
}
<%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
3.如果有使用UpdatePanel,須將轉出Excel的事件改為PostBack,不然會無法將匯出的檔案提供客戶端下載。
相信有了以上步驟,您可以順利轉出Excel檔案了,感謝觀賞。
參考來源:
http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
http://www.dotblogs.com.tw/jeff377/archive/2008/05/16/4006.aspx