Saturday, September 25, 2010

GridView and export to excel

This is very simple to implement in ASP.NET. But, there are possibilities to get problems in exporting to excel from grid view. When you bind data to gridview and write some logic to export to excel then it will not be enough. We have to check or write some additional logic which will help us to solve the problems. Below is the explanation for all problems we may get in the complete process along with detailed solution.

You may encounter below errors when you try to implement the export to excel for gridview.
1. Control of type "GridView" must be placed inside of the form tag with runat="server"
This is very well known error to ASP.NET developers and by seeing it, we think that the control is not inside the form with runat server. But this is not correct. This error will come even if we put the GridView inside form with runat server. The reason is, in the export to excel logic we are calling RenderControl() method of GridView. So, to render it without any issues we have to override the "VerifyRenderingInServerForm" in our code.  Below is the syntax of the event. Add this to the c# code in code behind file. Remember this event is a Page event means this method you should place in ASPX page. If you are using user control to implement this export to excel logic then below are the ways to go.
1. If your user control is using by less than 3-4 pages then go to each and every page and add this event to the page.
2. If your user control is using by more than 5 pages then the best solution is to create a base page [Which inherits from System.Web.UI.Page class] and all your ASPX pages should inherit from this base page.

public override void VerifyRenderingInServerForm(Control control)
{
    //Confirms that an HtmlForm control is rendered for the specified ASP.NET 
    //server control at run time.
}
Now, after we added the event to page, the error will go away for sure.

2. Even when you add the above code/event to the page, this is not enough if you have the paging, sorting enabled on gridview. If you enable paging or sorting then you encounter the below error.

"RegisterForEventValidation can only be called during Render();"

This error is coming because we are doing paging and sorting. If no paging or sorting enabled this error will not come. To resolve this error, please follow below steps.
1. In your export to excel button click event, first disable the paging, sorting on gridview and do data bind.
2. Call export to excel logic.
3. Re-enable paging, sorting on gridview and databind.
Below is the logic we have to use in export to excel button click event.
gvReport.AllowPaging = false;
    gvReport.AllowSorting = false;
    gvReport.DataBind();
    ExportToExcel();//Method to use export to excel.
    gvReport.AllowPaging = true;
    gvReport.AllowSorting = false;
    gvReport.DataBind(); 
Now, you are clear with all errors and the logic will export all data in gridview to excel.
FYI, I am placing a sample of ExportToExcel() functionality here.
private void ExportToExcel()
    {
        Response.Clear();
        Response.AddHeader("content-disposition", string.Format("attachment;filename=excel_report.xls"));
        Response.Charset = "";
        
        // 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);
        gvReport.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
Note: gvReport is the gridview control name in my example. And there are lot of posts in internet guide you incorrect to resolve the above error. For example, they will say set EnableEventValidation="false" in the <%@ Page directive Or disable validation in web.config level to resolve the error. Please do not set it.

Hope this helps to solve all problems you are facing.

21 comments:

  1. I was in search of this code since from many days, and glad to have found it here. Thanks a lot for sharing this code. I like your post and would like to share it with others.

    ReplyDelete
  2. I am glad to hear this. Thank you very much for such a good comment.

    ReplyDelete
  3. Thanks a lot for sharing this code, but after using your code i got again error..

    Error 1 VerifyRenderingInServerForm(System.Web.UI.Control)': no suitable method found to override....

    ReplyDelete
  4. Seems like you forgot to put the code mentioned in #1. Read complete post and add the code mentioned. Your error will go away.

    ReplyDelete
  5. Praveen, I tried your code. But it doesn't give any error. All I see in the excel file that got created is only div element.

    Any idea why this could be happening?

    ReplyDelete
  6. Hi Basam,
    Seems like the data source is coming empty. Please check once the data source you are using have the data by the time your Excel click logic is executing. Debug and let me know.

    ReplyDelete
  7. i tried your code.. no errors.. but still not opening the excel sheet.. where i went wrong,,
    plese suggest me..

    ReplyDelete
  8. What the error you are seeing? Are you able to download EXCEL? or The excel sheet is not opening at all?

    ReplyDelete
  9. Hi,
    I need to generate an excel sheet which has to be compatible with Open Office Excel as well.

    Does your code support this feature.

    ReplyDelete
  10. You meant to say Excel in native format?

    ReplyDelete
  11. YES,with a open office support.

    ReplyDelete
  12. I tried your code and the errors disappeared but no Excel appears.
    Do you know why?
    NB: I'm using Google Chrome

    ReplyDelete
  13. Hi Praveen i have try this code but it showing the error because i am using this code in webusercontrl.ascx page
    Error:
    VerifyRenderingInServerForm(System.Web.UI.Control)': no suitable method found to override....

    ReplyDelete
  14. Hi Praveen i have try this code but it showing the error because i am using this code in webusercontrl.ascx page
    Error:
    VerifyRenderingInServerForm(System.Web.UI.Control)': no suitable method found to override....

    ReplyDelete
  15. Hi Vicky,
    Yes this is expected issue. The problem is, the method VerifyRenderingInServerForm is page method. So, you should override that in your ASPX page. Below are the scenarios considered:
    1. If user control is in less number of pages[Means less than 5 pages] then please add the method "VerifyRenderingInServerForm" in each and every page.
    2. If user control is in use more than 4-5 pages then I always suggest you to create a base page [A class which inherits from System.Web.UI.Page] and in that add this method "VerifyRenderingInServerForm". And all your ASPX pages will inherit this base page and problem resolved.

    Let me know if you have any more questions.

    ReplyDelete
  16. YEAH THIS IS THE CORRECT ANSWER, IT EXPORTS NOW MY PARENT AND CHILD GRIDVIEW TO MSEXCEL. NICE CODES THANK U (^^,)

    ReplyDelete
  17. Hi Praveen, Thanks for creating such a detailed article on all the possible errors. Literally I faced all the errors one by one.
    I am now faced with the problem what Basam faced earlier. The excel contains empty divs. Not sure what you meant by comment "data source is coming empty"? Since I am able to view the datagrid.
    Thank you

    ReplyDelete
  18. Hi Shalini,
    Data Source is the data grid only. check its binding correctly..

    ReplyDelete
  19. Error:
    VerifyRenderingInServerForm(System.Web.UI.Control)': no suitable method found to override....

    Fix #1: as mentioned above.

    Fix #2: in the usercontrol just create a form with runat attribute for your grid to export using the following:

    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    // Create a form to contain the grid
    HtmlForm frm = new HtmlForm();
    frm.Attributes["runat"] = "server";

    gv_to_export.Parent.Controls.Add(frm);
    frm.Controls.Add(gv_to_export);
    frm.RenderControl(htw);

    Response.Write(RemoveSpecialTags(sw));

    Followoing is the RemoveSpecialTags Method:

    private string RemoveSpecialTags(StringWriter sw)
    {
    string ret = "";
    ret = Regex.Replace(sw.ToString(), @"", "", RegexOptions.IgnoreCase);
    ret = Regex.Replace(ret, @"[\s\S]*?", "", RegexOptions.IgnoreCase);
    ret = Regex.Replace(ret, @"[\s\S]*?", "", RegexOptions.IgnoreCase);
    ret = Regex.Replace(ret, @"<[^>]+?/[ ]*>", "", RegexOptions.IgnoreCase);

    return ret;
    }

    ReplyDelete
  20. hi,

    how to add verifyrenering method to aspx page as of first solution..........

    ReplyDelete