[ASP.NET] GridView轉出Excel檔案 完整版

公司系統需要有將畫面上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.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx?ArticleID=000c64fb-8a22-414a-8247-984335aaa0eb

http://www.dotblogs.com.tw/jeff377/archive/2008/05/16/4006.aspx