• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

How to save the contents of a GridView to an excel spreadsheet

I am trying to save my GridView data as an Excel file.


Here's my code



 Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=Report.xls");
           // Response.ContentType = "application/octet-stream";

            // If you want the option to open the Excel file without saving then
            // comment out the line below
            //Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.xls";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
           
            GridView3.RenderControl(htmlWrite);
         
            Response.Write(stringWrite.ToString());
            Response.End();

It doesn't work and I get a I get an error about Managed Pipelines. After changing it to Integrated, I still cant get it to work.

Regards.
0
fantasylan
Asked:
fantasylan
1 Solution
 
penndellCommented:
Try:


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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now