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.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    41 thoughts on “Exporting GridView Data to Excel

    • September 17, 2007 at 4:20 am
      Permalink

      You don’t have to set the ‘frm.Attributes[“runat”] = “server”;’ thing… that’s only for telling the compliler to do this:

    • September 21, 2007 at 1:23 am
      Permalink

      Thank you very much :)

    • July 3, 2008 at 11:55 am
      Permalink

      This is a very fantastic code. I am 100% satisfied with the code. Thanks a ton.

    • July 14, 2008 at 2:55 pm
      Permalink

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

    • February 12, 2009 at 1:03 pm
      Permalink

      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

    • February 12, 2009 at 1:23 pm
      Permalink

      You mean header row of grid is not visible or what?

    • March 25, 2009 at 7:10 am
      Permalink

      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.

    • July 2, 2009 at 11:18 am
      Permalink

      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

    • August 19, 2009 at 8:25 am
      Permalink

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

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

    • May 3, 2010 at 1:41 pm
      Permalink

      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

    • May 4, 2010 at 12:23 pm
      Permalink

      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.

    • October 24, 2010 at 11:44 am
      Permalink

      still i m getting this error’ A page can have only one server-side Form tag’

    • December 2, 2010 at 8:58 am
      Permalink

      It worked great ..Thanks for the Article …its cool…My problems got sort ..

    • January 17, 2011 at 4:48 pm
      Permalink

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

    • February 1, 2011 at 12:59 pm
      Permalink

      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

    • February 3, 2011 at 6:59 pm
      Permalink

      Excellent its working fine, thanks a lot.

    • February 13, 2011 at 12:17 pm
      Permalink

      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 :)

    • April 25, 2011 at 8:29 pm
      Permalink

      Thank you for the code.

    • July 13, 2011 at 2:34 pm
      Permalink

      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??

    • August 11, 2011 at 7:40 am
      Permalink

      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????

    • August 26, 2011 at 11:02 am
      Permalink

      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.

    • September 1, 2011 at 8:23 am
      Permalink

      Good afternoon Guyz,

      Please help me how to do it using ASP.NET MVC3..

      Thank you in advance.

    • October 25, 2011 at 12:41 pm
      Permalink

      Thank you for posting this life saving article

    • December 16, 2011 at 10:48 am
      Permalink

      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..

    • December 22, 2011 at 10:40 am
      Permalink

      Please remove the

      <asp:UpdatePanel>

      and

      <ContentTemplate>

      It works for me..! Thanks a lot…!

    • January 4, 2012 at 10:41 am
      Permalink

      Thanks you very much working fine.

    • February 4, 2012 at 7:23 am
      Permalink

      Thanks.its working fine

    • February 8, 2012 at 8:17 am
      Permalink

      Great code.

    • February 8, 2012 at 9:58 am
      Permalink

      Filtering is not working after this.

    • February 8, 2012 at 9:59 am
      Permalink

      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.

    • February 28, 2012 at 3:04 pm
      Permalink

      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.

    • March 19, 2012 at 3:47 pm
      Permalink

      This code is really fantastic , thank you very much dear. It helped me a lot.

    • April 3, 2012 at 3:44 pm
      Permalink

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

    • June 1, 2012 at 2:16 pm
      Permalink

      Excellent code..it helped me..

    • July 17, 2012 at 6:10 pm
      Permalink

      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

    • July 19, 2012 at 1:52 pm
      Permalink

      @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!!

    • July 19, 2012 at 2:42 pm
      Permalink

      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

    • January 9, 2013 at 11:15 pm
      Permalink

      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?

    Leave a Reply

    Your email address will not be published. Required fields are marked *