Export GridView to Excel without losing grid lines in Excel

Question

I have a GridView that I want to export to Excel. When I use the sample code I find online, it exports the content to Excel just fine, but for some reason it also clears out all grid lines outside of my exported table.

For your average excel user this is easy enough to fix, but I need this solution to work for everyone.

So then is there a way to export the data in a GridView into an Excel Workbook so that it looks like it was just typed into Excel? I've pasted the code I am using below, assume that a GridView called toPrint exists and has accurate data.

Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + name + "_Registration_Forms.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
Page.EnableViewState = false;

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

toPrint.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());
Response.End();

EDIT: Found one partial solution. If I export as a comma-delimited list and set the header to be a CSV file, it opens fine and all grid lines (even those outside of the exported data) are showing. The only problem with this of course is having to strip out every comma and newline character from my values before exporting them.

Answer 1

I have used the helper function below in the past. I just gave the user a checkbox that they could select to include grid lines or not. Obviously you could change this to always include the grid lines.

namespace Helpers
{
    public class GridViewExportUtil
    {
        public static void Export(string fileName, GridView gv, bool includeGridLines)
        {
           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 form to contain the grid
               Table table = new Table();

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

This is an example of how you would call it:

GridViewExportUtil.Export("QueryResults.xls", GridView1, includeGridLines);

Answer 2

Thanks..........

While exporting gridview using this code invisible columns of gridview also exporting.