Jul

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.

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.
You may need to included the following page directive: EnableEventValidation=”false”. I didn’t need to include this directive.
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

Posted by Ben Griswold ASP.NET, C#, Gridview, Samples Subscribe to RSS feed

22 Responses to “Export GridView to Excel within an UpdatePanel”

Ignacio Fuentes
September 4th, 2008 at 5:43 am

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!
bgriswold
September 4th, 2008 at 8:25 am

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.
Prenidha
September 18th, 2008 at 12:57 am

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.
Prenidha
September 18th, 2008 at 9:11 pm

I have image buttons and they are not getting displayed properly
bgriswold
September 19th, 2008 at 6:30 am

Hi Prenidha. Can you provide a snippet of your code please?
Mark Kamoski
November 11th, 2008 at 1:18 pm

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?
Mark Kamoski
November 11th, 2008 at 1:18 pm

I ran your code and when I export a GridView I get an empty Excel sheet. Can you help?
Mark Kamoski
November 11th, 2008 at 1:45 pm

…hmmm… it seems to be working now for GridView… my mistake… (I think)… regardless, thank you…
flyby
November 24th, 2008 at 7:17 am

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
Lakshya
December 8th, 2008 at 3:45 am

Hi, Could you share the code to export images from gridview to excel.
Thanks in advance,
Lakshya.
Murali
January 5th, 2009 at 9:43 pm

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
Ben Griswold
January 6th, 2009 at 1:23 am

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.
Braj
May 1st, 2009 at 10:48 pm

It was awesome, I was indeed looking for the same code…

Thanks a TON!!!
Adeel Fakhar
May 2nd, 2009 at 12:25 am

I am facing same problem that Murali is facing.

Please provide its solution
Adeel Fakhar
June 17th, 2009 at 11:17 pm

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#
Charles Welch
June 18th, 2009 at 7:01 am

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?
CP Fouche
July 2nd, 2009 at 4:04 am

Thanks a lot, completely solved my problem
Hemant
July 15th, 2009 at 11:00 pm

Thanks a lot, after searching through several site i found the solution but i need that it will show gridline in excell too
Ben Griswold
August 18th, 2009 at 12:06 pm

@CP Fouche @Hemant - Happy to help.
Aniruddha Singh Tomar
February 2nd, 2010 at 5:07 am

Thanks man your code below worked ultimate….
…

thanks alot
mayank
February 8th, 2010 at 11:30 pm

Thanks a lot buddy
Ben Griswold
February 9th, 2010 at 10:33 am

Happy to help