[엑셀출력] Export GridView to Excel

thought this sounded like a pretty standard requirement so I figured there would be a nice snippet on msdn or in an msdn publication describing the official Microsoft solution. I couldn’t find that, or any other approach that I was happy with. I ran across a few sites that looked promising, but didn’t quite meet my requirements – I had to either override a page level method and do nothing (here) or else turn off event validation (here and here). It also generally appeared that the people using these solutions were also running into a variety of issues with grids that allow sorting and paging - all of the grids I am working with allow both.

The approach I settled on is very similar to the above links, but without the limitations of having to turn off event validation or override any page methods. This gives me the freedom to put these methods were I want them (in my case within a static GridViewExportUtil class) without having to worry about the page that hosts the GridView doing anything special.

Download code | View live demo here | View GridViewExportUtil.cs | View GridViewExportUtil.vb

[Update: 7/7/2007]

Fred van den Berg converted the sample web site to VB.NET.  You can download it here.  You can also just view the VB version of the export utility class here.  Thanks Fred!

[Update: 6/26/2007]

Shane, you can use the GridLines proeprty of the Table to show or hide the GridLines.  I have updated the demo and sample to include copying over this property to the Table before exporting.

//  Create a table to contain the grid
Table table = new Table();
//  include the gridline settings
table.GridLines = gv.GridLines;

[Update: 6/22/2007]

I updated the demo to include an option for specifing the nuber of rows that should be exported.  The available options are 'Current Page', 'All Pages', or just the 'Top 100 Rows'. 

For the case where I actually wanted all rows exported, I turned off paging and rebound the grid before sending the control to the export utility.  For exporting just the first 100 rows, I set the PageSize property to 100 and then rebound.  You should probably use care when exporting the complete GridView just in case your grid has a few more rows that you are expecting.  Here is the code for the export button click handler

/// <summary>
/// 
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
protected void BtnExportGrid_Click(object sender, EventArgs args)
{
    if (this.rdoBtnListExportOptions.SelectedIndex == 1)
    {
        //  the user wants all rows exported, turn off paging
        //  and rebing the grid before sending it to the export
        //  utility
        this.gvCustomers.AllowPaging = false;
        this.gvCustomers.DataBind();
    }
    else if (this.rdoBtnListExportOptions.SelectedIndex == 2)
    {
        //  the user wants just the first 100,
        //  adjust the PageSize and rebind
        this.gvCustomers.PageSize = 100;
        this.gvCustomers.DataBind();
    }

    //  pass the grid that for exporting ...
    GridViewExportUtil.Export("Customers.xls", this.gvCustomers);
}

Also, I moved the GridView to a content page to test terry's comment about making sure this approach works from content pages.  I haven't run into any issues yet.

** Disclaimer **

GridViewExportUtil

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
/// <summary>
/// Summary description for Class1
/// </summary>
public class GridViewExportUtil
{
    /// <summary>
    ///
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="gv"></param>
    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";

        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();
            }
        }
    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    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);
            }
        }
    }
}

http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html