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.
You don’t have to set the ‘frm.Attributes[“runat”] = “server”;’ thing… that’s only for telling the compliler to do this:
Thank you very much :)
This is a very fantastic code. I am 100% satisfied with the code. Thanks a ton.
I Searched for Gridview export to excel problem’s solution for atleast 1 hr, But this solution is 100% perfect.
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
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
You mean header row of grid is not visible or what?
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.
Excellent Code.
You are great.
still i m getting this error’ A page can have only one server-side Form tag’
It worked great ..Thanks for the Article …its cool…My problems got sort ..
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
Excellent its working fine, thanks a lot.
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 :)
Thank you for the code.
Thanks
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.
Good afternoon Guyz,
Please help me how to do it using ASP.NET MVC3..
Thank you in advance.
Thank you for posting this life saving article
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..
Please remove the
<asp:UpdatePanel>
and
<ContentTemplate>
It works for me..! Thanks a lot…!
Thanks you very much working fine.
Thanks.its working fine
Great code.
Filtering is not working after this.
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 code is really fantastic , thank you very much dear. It helped me a lot.
This was the perfect solution and was easy to adapt to my current project. Thank you SO much!!
Excellent code..it helped me..
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?