X

Exporting GridView Data to Excel

My current project required a way for exporting data to Excel. I could of course write a separate export method for every data set but in my opinion it would be a pointless waste of time. I would also like to utilise the existing functionality as much as possible – as it is, the lists going to Excel are displayed to the user in the browser.

Solution

The solution – the simplest and least painful at that – is to render GridView into HTML, and to present the resulting HTML to Excel that can also read the HTML format.

protected void btnExpExcel_Click(object sender, ImageClickEventArgs e)
{
    // Let's hide all unwanted stuffing
    this.gdvList.AllowPaging = false;
    this.gdvList.AllowSorting = false;
    this.gdvList.EditIndex = -1;

    // Let's bind data to GridView
    this.BindList();

    // Let's output HTML of GridView
    Response.Clear();
    Response.ContentType = "application/vnd.xls";
    Response.AddHeader("content-disposition", "attachment;filename=contacts.xls");

    StringWriter swriter = new StringWriter();
    HtmlTextWriter hwriter = new HtmlTextWriter(swriter);

    HtmlForm frm = new HtmlForm();
    this.gdvList.Parent.Controls.Add(frm);
    frm.Attributes["runat"] = "server";
    frm.Controls.Add(this.gdvList);
    frm.RenderControl(hwriter);

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

There is one more thing to be done before we can start the export. The page (not the user control) hosting GridView must not handle event validation. To ensure that, let’s add the following definition to the header, into the Page section of the declaration: EnableEventValidation="false"

Extension Possibilities

This functionality can also be extended to other controllers, e.g. DataList, Repeater and other controllers that can be associated with data. One could create a separate class for exporting, and even extend it to other formats besides Excel.

Liked this post? Empower your friends by sharing it!
Categories: ASP.NET

View Comments (41)

  • You don't have to set the 'frm.Attributes["runat"] = "server";' thing... that's only for telling the compliler to do this:

  • I Searched for Gridview export to excel problem's solution for atleast 1 hr, But this solution is 100% perfect.

  • hi,

    i used the above code it is working but after opening the excel sheet the grid lines which are bound to the data are visible all the other grid lines of the excel sheet are not visible

    please help me out in that

    im using studio 2005 and office 2007

    Srikanth

  • This is the greatest post i ever met on the net relative to Exporting Gridview Data To Excel. It works Fineeeeeeeee !!!

    But if your aspnet page contents a Ajax UpdatePanel, you will receive an error cause you use Response.Write in code behind. I don't have the fix for that error like this : "“Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled.”" But This code works fine if you don't use Ajax UpdatePanel. Thanks you so much Gunnar.

  • Nice article.

    Especially the htmlform thing.

    this helped me to get rid of other things on my page and only export the gridview data

    Thanks

  • The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>).

    i got this error while using this code,,,,

  • Nice article. I want to insert new sheet in excel while exporting from grid view using asp.net in c#. Actually i have two grid views while exporting to excel i want gridview1 in sheet1 and gridview in sheet2. Please do the needful

  • Hi Sandhya! If you need more than one sheet you have to use some other technique to output data. One thing I can suggest is using Excel XML-data format. But you have to write data out differently because this format doesn't accept direct HTML like I used here.

  • Man - GREAT bit of code, solved a need that I have had for a day or so now on my project, clean and neat

  • Thanks for Code.

    But,It shows the Entire data of Gridview.

    Please do me a favour that click on the export button it will show only the data in the page1 except the entire data and also without paging Numbers

  • umamahesh2020, it is very easy to modify the code like you want. Usually there is need to export out all the rows in result set not only the current page. If you need only current page and less rows it takes you about 5 minutes to change my code and make it work this way. Take it as one of your programming lessons to make my example work like you want :)

  • good post... but what if i also want to save the same excel sheet (as a backup) on the server as well without the end user knowing about it??

    any ideas??

  • Thanks a lot....it has worked for single gridview...

    but it fails for multiple grid views....

    can you suggest relevant changes in the above code such that it exports multiple grid views????

  • Good afternoon. I need some help with you guys. do you have idea how to export the data from razorview to excel ? The concept is I want to click an action link from the  page to download the data to excel.

    PS. I am using ASP.NET MVC3.

    Thank you in advance.

  • No doubt its a really nice piece of code..

    but it is not generating borders of cell in excel sheet.. plz help to short out the drawback..

  • Is there any way to call filtering again?

    I need filtering and sorting after export the data from grid. It is not refreshing the page.

  • This solved my problem in few minutes.

    Additionally, if your're getting this error:

    "Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled"

    You may be working inside an updatepanel. One great work around is to make sure to add the updatepanel triggers that will do the postback...works like magic.

  • This was the perfect solution and was easy to adapt to my current project. Thank you SO much!!

  • sorry it didn't work for me, I got this:

    'bindlist' is not a member of 'Project1.ExcelExport

    on this line:

    this.BindList(); ' your original c# code

    Me.BindList() ' my vb.net code

    could anybody help me?

    thx in avance

  • @Warren P. - I tried using Triggers but it didn't work, it WONT'T WORK if you have a UpdatePanel on your page, even using Triggers!!

  • Sorry guys! It DOES WORK! But only if you use a PostBackTrigger to trigger the Button (it doesn't work when using AsyncPostBackTrigger). My (simplified) code:

    <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">

           <ContentTemplate>

               <%-- (GridView --%>

           </ContentTemplate>

           <Triggers>

               <%--PostBackTrigger for Downloadbutton-->

               <asp:PostBackTrigger ControlID="btnDownload"/>

           </Triggers>

    </asp:UpdatePanel>

    learn more about triggers here:

    http://www.asp.net/web-forms/tutorials/aspnet-ajax/understanding-asp-net-ajax-updatepanel-triggers

  • Excellent Code.... But I have question that when I export data to the Excel my two Columns data shown as ASCII code like 781.+000. How can I solve that error?

Related Post