Steve Holstad's "the bright lights"

Gridview Export to Excel

Exporting a gridview control to Excel isn't hard, but I did run into some problems with Response Headers when deploying this functionality to our production site.  Turns out the header settings were set at a global level, so I needed to explicitly set Response.Cache.SetCacheability to 'Public'.  I also noticed that some sites are publishing a version of this code, but it needed a few tweaks to work in some environments. I've upgraded it to work, and have added a few features I needed:

- The aforementioned SetCacheability property is set
- Added the event click code to show how you can export this gridview when AllowPaging is set to true.  Just turn paging off, rebind to data, export, then set paging on and bind again.
- Removed the footer row
- The gridview's headers contained hyperlinks, so I've replaced the cell with the link text after clearing the controls
- I've created an ArrayList of column names to exclude, and passed this to the Export method. 

protected void lnkExport_Click(object sender, EventArgs e)
        {
            // Disable paging
            grdMyData.AllowPaging = false;
            LoadGridData();

            // exluded columns arraylist
            ArrayList defaultExcludedColumns = new ArrayList();

            // Always exclude these columns
            defaultExcludedColumns.Add("MyHiddenFieldName");

            // Send to base Excel export method 
            ExportGridView(grdMyData, "defaultFileName", defaultExcludedColumns);

            // Rebind with paging enabled
            grdMyData.AllowPaging = true;
            LoadGridData();
        }

        /// <summary>
        /// Export GridView data to Excel.
        /// </summary>
        /// <param name="grdView">GridView control to export.</param>
        /// <param name="filename">Filename of excel spreadsheet.</param>
        /// <param name="excludedColumnList">ArrayList of columns to exlude.</param>
     protected void ExportGridView(GridView grdView, string filename, ArrayList excludedColumnList)
        {
            // Clear response content & headers
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();

            // Add header
            Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");

            Response.Charset = string.Empty;
            Response.Cache.SetCacheability(System.Web.HttpCacheability.Public);
            Response.ContentType = "application/vnd.xls";

            // Create stringWriter
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();

             // Create HtmlTextWriter
            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

            // Remove controls from Column Headers
            if (grdView.HeaderRow != null && grdView.HeaderRow.Cells != null)
            {
                for (int ct = 0; ct < grdView.HeaderRow.Cells.Count; ct++)
                {
                    // Save initial text if found
                    string headerText = grdView.HeaderRow.Cells[ct].Text;

                    // Check for controls in header
                    if (grdView.HeaderRow.Cells[ct].HasControls())
                    {
                        // Check for link button
                        if (grdView.HeaderRow.Cells[ct].Controls[0].GetType().ToString() == "System.Web.UI.WebControls.DataControlLinkButton")
                        {
                            // link button found, get text
                            headerText = ((LinkButton)grdView.HeaderRow.Cells[ct].Controls[0]).Text;
                        }

                        // Remove controls from header
                        grdView.HeaderRow.Cells[ct].Controls.Clear();
                    }

                    // Reassign header text
                    grdView.HeaderRow.Cells[ct].Text = headerText;
                }
            }

            // Remove footer
            if (grdView.FooterRow != null)
            {
                grdView.FooterRow.Visible = false;
            }

            // Remove unwanted columns (header text listed in removeColumnList arraylist)
            foreach (DataControlField field in grdView.Columns)
            {             
                if (excludedColumnList.Contains(field.HeaderText))
                {
                    field.Visible = false;
                }
            }

            // Call gridview's renderControl
            grdView.RenderControl(htmlWrite);

            // Write Response to browser
            Response.Write(stringWrite.ToString());

            Response.End();
        }

         /// <summary>
         /// This allows Excel Exporting to function correctly, notifying Export function that control resides in server form.
        /// </summary>
        /// <param name="control"></param>
        public override void VerifyRenderingInServerForm(Control control)
        {
            // Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
            // Required for Excel exporting
        }