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.


42 thoughts on “Exporting GridView Data to Excel

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

  • Gunnar says:

    Thank you very much 🙂

  • Sanjay Gupta says:

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

  • Girish Khadke says:

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

  • Bartek says:

    Great post Gunnar!

    I’ve written a similar article that lets you select which rows to export here:

    http://blog.evonet.com.au/post/2008/06/17/Gridview-that-exports-selected-rows-to-Excel.aspx

    Bartek

  • srikanth says:

    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

  • Gunnar says:

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

  • MaestroDabla says:

    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

  • ram says:

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

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

  • Sandhya says:

    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

  • Gunnar says:

    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.

  • Godly Mathew says:

    Excellent Code.

    You are great.

  • ash says:

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

  • Nikita Somaiya says:

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

  • Rick says:

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

  • umamahesh2020 says:

    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

  • Asif Iqbal says:

    Excellent its working fine, thanks a lot.

  • Gunnar says:

    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 🙂

  • Kaniel says:

    Thank you for the code.

  • sama salim says:

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

  • Ahmed says:

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

  • Rey says:

    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.

  • rey says:

    Good afternoon Guyz,

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

    Thank you in advance.

  • Aschalew says:

    Thank you for posting this life saving article

  • Bishwajeet says:

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

  • Jhon says:

    Please remove the

    <asp:UpdatePanel>

    and

    <ContentTemplate>

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

  • Vinayak says:

    Thanks you very much working fine.

  • ashutosh thakur says:

    Thanks.its working fine

  • Pradeep Kumar says:

    Great code.

  • Pradeep kumar says:

    Filtering is not working after this.

  • Pradeep kumar says:

    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.

  • Warren P. says:

    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.

  • Mohammed Basheer says:

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

  • Kristen M. says:

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

  • Prasanthi says:

    Excellent code..it helped me..

  • ronin47 says:

    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

  • ronin47 says:

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

  • ronin47 says:

    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

  • Pinky says:

    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 *