Export GridView to Excel within an UpdatePanel

There’s a ton of information online about exporting a DataGrid or GridView to Excel, but most variations do not consider the GridView may reside within an UpdatePanel.  It goes without saying, but I was disappointed when I recently dusted off my “Export GridView to Excel” code snippet and encountered a number of exceptions.  So I revisited a number of links and I collected a working solution.  If you need a “simple” way to export your GridView to Excel and you are using an UpdatePanel, I hope the following code finds you well:

protected void btnExport_Click(object sender, EventArgs e)
{
    // Reference your own GridView here
    if (AccountGrid.Rows.Count > 65535)
    {
        DisplayError("Export to Excel is not allowed" +
            "due to excessive number of rows.");
        return;
    }

    string filename = String.Format("Results_{0}_{1}.xls",
        DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString());

    Response.Clear();
    Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
    Response.Charset = "";

    // SetCacheability doesn't seem to make a difference (see update)
    Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);  

    Response.ContentType = "application/vnd.xls";

    System.IO.StringWriter stringWriter = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);

    // Replace all gridview controls with literals
    ClearControls(AccountGrid);

    // Throws exception: Control 'ComputerGrid' of type 'GridView'
    // must be placed inside a form tag with runat=server.
    // ComputerGrid.RenderControl(htmlWrite);

    // Alternate to ComputerGrid.RenderControl above
    System.Web.UI.HtmlControls.HtmlForm form
        = new System.Web.UI.HtmlControls.HtmlForm();
    Controls.Add(form);
    form.Controls.Add(AccountGrid);
    form.RenderControl(htmlWriter);

    Response.Write(stringWriter.ToString());
    Response.End();
}

private void ClearControls(Control control)
{
    for (int i = control.Controls.Count - 1; i >= 0; i--)
    {
        ClearControls(control.Controls[i]);
    }

    if (!(control is TableCell))
    {
        if (control.GetType().GetProperty("SelectedItem") != null)
        {
            LiteralControl literal = new LiteralControl();
            control.Parent.Controls.Add(literal);
            try
            {
                literal.Text =
                    (string)control.GetType().GetProperty("SelectedItem").
                        GetValue(control, null);
            }
            catch
            {}
            control.Parent.Controls.Remove(control);
        }
        else if (control.GetType().GetProperty("Text") != null)
        {
            LiteralControl literal = new LiteralControl();
            control.Parent.Controls.Add(literal);
            literal.Text =
                (string)control.GetType().GetProperty("Text").
                    GetValue(control, null);
            control.Parent.Controls.Remove(control);
        }
    }
    return;
}
 

Update: 7/30/2008

I previously noted that SetCacheability doesn’t seem to make a difference.  Well, I was right…until I deployed my code to a site behind SSL.  As it turns out, in order for Internet Explorer to open documents in Office (or any out-of-process, ActiveX document server), Internet Explorer must save the file to the local cache directory and ask the associated application to load the file by using IPersistFile::Load.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q316431&

If the file is not stored to disk, this operation fails. When Internet Explorer communicates with a secure Web site through SSL, Internet Explorer enforces any no-cache request. If the header or headers are present, Internet Explorer does not cache the file. Consequently, Office cannot open the file.

RESOLUTION: Web sites that want to allow this type of operation should remove the no-cache header or headers. In other words, comment out the following line of code particularly if you are running under SSL:

Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache); 

 

Additional Comments

Per Jinath Blog, if you are using an UpdatePanel, you may get a System.WebForms.PageRequestManagerParserErrorException exception.  The solution is to add a PostBackTrigger and give it’s ControlID as the excel export button’s ID or you can move your excel export button out side of the update panel.  I verified both options and they work great.  I ultimately went with the former option as such:

    ...
    </ContentTemplate>
    <Triggers>
        <asp:PostBackTrigger ControlID="btnExport" />
    </Triggers>
</asp:UpdatePanel>
...

Per ASPAlliance, you may encounter issues a number of issues which require the following solution.  Check out the link (and the comments) for more details if you get stuck.  I only encountered #1 on the list.

  1. You may get an exception which states your Control ‘Grid’ of type ‘GridView’ must be placed inside a form tag with runat=server.  I overcame this by dynamically adding a form to the page and then the GridView to the form before RenderContent().  This solution came per the aforementioned post’s comments.
  2. You may need to included the following page directive: EnableEventValidation=”false”.  I didn’t need to include this directive.
  3. You may need Override the VerifyRenderingInServerForm Method.  I didn’t need to do so because I added my GridView control to a “mocked” form.
     

Per Dipal Choksi, one can format the spreadsheet results in a generic manner by replacing all controls within the GridView with Literals.  This is reflected in the ClearControls() method above.  My prior implementation merely cleaned up the links associated with the sort functionality tied to the sortable headers.  This solution tackles all cells.

Additional Reference: GridViewGuy 

Comments

  1. Thank you for researching when a gridview is in an UpdatePanel. After trying several things including the code above I realized that the you can work with the regular code that you find anywhere to export to excel:
    Response.Clear();
    Response.AddHeader(“content-disposition”, “attachment;filename=FileName.xls”);

    Response.Charset = “”;

    Response.ContentType = “application/vnd.ms-excel”;
    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    gv.RenderControl(htmlWrite);

    Response.Write(stringWrite.ToString());

    Response.End();

    Then you override the Server control so you don’t get the error ‘GridView’ must be placed inside a form tag with runat=server.

    public override void VerifyRenderingInServerForm(Control control)
    {

    // Confirms that an HtmlForm control is rendered for the
    //specified ASP.NET server control at run time.

    }

    The most important part if you are using a gridview inside an Update Panel is to do what Jinath mentioned. Put a PostBackTrigger in your update panel and point it to your export to excel button. That way you won’t get the System.WebForms.PageRequestManagerParserErrorException exception.

    At this point, you should be done and be able to Open the Grid in Excel. Good luck!

  2. Thanks for the comment, Ignacio. I’m happy to read that this article and others helped you come to a satisfactory solution. I appreciate you putting the time into submitting a detailed reply.

  3. great code…i was looking for exactly this code…. however anybody has any idea how to do the formatting in the rendered output…coz i render images and the images get aligned over each other…thanks.

  4. You have both ComputerGrid and AccountGrid in your code sample. What is ComputerGrid? I think that both have to be called AccountGrid. Is that the case?

  5. …hmmm… it seems to be working now for GridView… my mistake… (I think)… regardless, thank you…

  6. thank you for your codes. it’s working great. Is there anyway that I can add custom info. for header/footer so that it includes with the export excel file? Thanks

  7. Hi,
    I’m Exporting to Excel from AspxGridview which is in updatepanel. The Export button also is in update panel. when I’m Exporting the javascript exception System.WebForms.PageRequestManagerParserErrorException is throwing. I added trigger which you suggested outside the content template of a update panel. but the trigger control Id is not identifying the id.
    give suggestions. still i’m getting exception

  8. I might suggest viewing the source to verify the id. It is just a guess, but you might not be considering the id of the container being appended to the control id. Hence it can’t be found.

  9. Finally I am successful to export the data from gridview to excel. Actually i was facing problem that Murali is facing but now finally every thing is working fine. I have written a detail post for this in my blog so that people can know the solution of errors that they are facing while exporting gridview data to excel within an update panel and can successfully export the data. Please have a look over this Export gridview to excel within an update panel in asp.net using c#

  10. I have a custom gridview control derived from System.Web.UI.WebControls.GridView that contains the export button and code. This control is then placed within an UpdatePanel. When I click the export button, I get the following error:

    PageRequestManagerServerErrorException: Object reference not set to an instance of an object.

    Any ideas?

  11. Thanks a lot, after searching through several site i found the solution but i need that it will show gridline in excell too

  12. Awesome!! You really gave me a neat solution with the postbacktrigger example … thanx a ton

  13. Problem :

    Sys.WebForms.PageRequestManagerParserErrorException will occur when redirecting ur page ,lets say button click inside UpdatePanel in aspxAjax

    Soln :

    1. Add a “GoTo” button in ur aspx page where update panel is using and add it outside Update panel

    1a. In ur code assign ur just registered userID to session variable , say Session["UseridJustregistered"]= Id from DB or UsernameField

    2. respose.redirect(“regSucces.aspx?urlid=’”+Session["UseridJustregistered"]+”‘”);

    3. Check if Session["UseridJustregistered"] is null or not

    –> This is OLD Classic ASP way which can solve our problem , by the time microsoft find a solution we can tackle it this way <–

  14. How do you go about formating fields.
    Working great just my first column which is a string, actually hold a larg number so it imports as that strange number 10084 +5.
    Then in excel if I format it as a number it shows correctly. I need to format this diring the export.

  15. Thanks!! The Triggers as per Jinath’s Blog works smoothly. I’ve been working on several errors regarding this for days. Finally a clean solution which does not generate more errors.

  16. Everything is working fine, superb post, but when data is exported to excel…then at the end of the file this line is being added (which i dont want)

    Clear Filter from %FIELDDISPLAYNAME%Sort DescendingSort Ascending
    

    Can you please help me to remove this line.
    Thanks in advance.

  17. Hi Ben!

    your above code does not work if images are there in grid view…

    because in my case it is not working.. plz suggest a way out, hope u reply..

  18. this article is great and it has almost everything that I need. But in my case, my grid view is showing master child records and for that purpose I am using AJAX collapsible extender control (for hiding and showing the child records of each master record). Now the issue is when I run the above code, I get the error saying
    (“Extender control ‘cpe’ (name of my collapsible extender) is not a registered extender control. Extender controls must be registered using RegisterExtenderControl() before calling RegisterScriptDescriptors().\r\nParameter name: extenderControl”)

    how should I tackle this?? Thanks for all the help.

  19. I get the following help:
    RegisterForEventValidation can only be called during Render();

    Can you please tell me why.

  20. I get the following error:
    RegisterForEventValidation can only be called during Render();

    Can you please tell me why.

  21. Please let me know if you’re looking for a writer for your weblog. You have some really good posts and I feel I would be a good asset. If you ever want to take some of the load off, I’d really like to write some material for your blog in exchange for a link back to mine. Please blast me an email if interested. Thanks!

  22. Just what exactly i actually don’t understood is definitely if truth be told the best way you are no longer actually much more neatly -appreciated as compared with you actually might be now. You’re very bright. You know thus considerably on the subject of the following subject, made people for my part imagine them out of a lot of varied attitudes. It has the for instance women and men are not involved except it is one thing so that you can do by using Girl coo! Your individual things nice. At all times take care of them right up!

  23. Yeah, I am supporting an app that just got moved to SSL, and the code was a copy/paste of what you have. Your SSL update saved me HOURS worth of troubleshooting… Thanks!

closed