ASP.NET: Export Grid View to Excel

Exporting contents to excel spreadsheet is a much required functionality for almost every data driven website. Especially in asp.net...


Exporting contents to excel spreadsheet is a much required functionality for almost every data driven website. Especially in asp.net websites we may want to export contents of gridview to excel sheets more often. It can be done in a simple way by setting the mime type of a response to fit the format of excel sheet and writing the contents of gridview to the response stream. In this post I am going to explain in detail on how to export gridview contents to an excel sheet.



To get started, I am having a gridview filled with data from employee table. The gridview looks like this with its contents and styling,



Now to export the contents of the above gridview to excel sheet, write the code below in "Export to Excel" button's click event.


protected void Button1_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            string filename="GridViewExport_"+DateTime.Now.ToString()+".xls";
            Response.AddHeader("content-disposition",
            "attachment;filename="+filename);
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            GridView1.RenderControl(hw);
            //style to format numbers to string
            string style = @"";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }


Now if you run this code and click on "Export To Excel" button, you will get the following error "Control GridView1 must be placed inside a form tag with runat=server".




This error occurs whenever we try to render a control to response. This can be rectified in two ways.

1. Adding gridview to HtmlForm Object Programmatically and render the form. Use the below code to do that,




        protected void Button1_Click(object sender, EventArgs e)
        {
            HtmlForm form = new HtmlForm();
            Response.Clear();
            Response.Buffer = true;
            string filename="GridViewExport_"+DateTime.Now.ToString()+".xls";
 
            Response.AddHeader("content-disposition",
            "attachment;filename="+filename);
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            form.Controls.Add(GridView1);
            this.Controls.Add(form);
            form.RenderControl(hw);
      
            //style to format numbers to string
            string style = @"";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }


2. Overriding VerifyRenderingInServerForm Event in the code behind page. This ensures that HtmlForm Control is rendered for the specific ASP.NET control at runtime. Just add the below event handler code beneath the Page_Load event handler in the code behind page.


     public override void VerifyRenderingInServerForm(Control control)
        {

        }


One last thing one should do is add EnableEventValidation="false" property to the page directive or to the web.config file. This avoids one more exception from arising that will say "RegisterEventValidation can only be called during Render()". See below code for reference,


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DatabaseTestDemo.Default"  EnableEventValidation="false"%>

The output excel file will have contents that look like this. Note that the styling that is applied to gridview is also exported along with the content. 




If you wants to add custom styling to the contents of the excel sheet, then you can add it dynamically by adding custom styles to the gridview and render it. For example,


//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
 
//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "white");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "white");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "white");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "white");  
 
for (int i = 0; i < GridView1.Rows.Count;i++ )
{
    GridViewRow row = GridView1.Rows[i];
 
    //Change Color back to white
    row.BackColor = System.Drawing.Color.White;
 
    //Apply text style to each Row
    row.Attributes.Add("class", "textmode");
 
    //Apply style to Individual Cells of Alternating Row
    if (i % 2 != 0)
    {
        row.Cells[0].Style.Add("background-color", "#C2D69B");
        row.Cells[1].Style.Add("background-color", "#C2D69B");
        row.Cells[2].Style.Add("background-color", "#C2D69B");
        row.Cells[3].Style.Add("background-color", "#C2D69B");  
    }
}
GridView1.RenderControl(hw);

The output excel sheet that is exported from the above custom styled gridview look like this,



Please leave your comments and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading this!!


Subscribe to GET LATEST ARTICLES!


Related

GridView 1858674927118128053

Post a Comment

  1. awsome solution. thanks

    ReplyDelete
  2. Nice post.. helped me a lot

    ReplyDelete
  3. wow ! ultimate .. very easy to understand ,, thanks a lot :)

    ReplyDelete
  4. Hello thank you
    string filename="GridViewExport_"+DateTime.Now.ToString()+".xls";
    //excel 2003
    I want export excel .xlsx excel 2007
    I do not know.

    ReplyDelete
    Replies
    1. Hello Bundit,

      There is no straightforward way to create .xlsx file, you have to use Open XML SDK for this.

      Try this,

      http://blogs.msdn.com/b/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx

      Hope this helps!

      Thanks,
      Priya

      Delete
    2. To export in .xlsx format, simply change 'Response.ContentType = "application/vnd.ms-excel";' to 'Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

      Source: http://en.wikipedia.org/wiki/Internet_media_type#Prefix_vnd

      Delete
  5. Hi Priya,

    Nice work!
    but how can i add a page number so that when i print it, page number is visible like: Page 1 of 3.
    I tried using pageindex and pagecount but could not get the result correctly.
    is this possible?

    thanks,
    areef

    ReplyDelete
    Replies
    1. Hi Priya,

      Thank you for your reply.

      My data result sometimes spans more than a page, hence i have to display the page number.

      In the Gridview, i have made to allow paging which works well, showing "Prev" & "Next" links.

      My concern is when i export the Gridview to Excel, i want the excel file to have the Page number when i print it.

      I have tried putting Pageindex and Page count to show something like this: "Page 1 of 4" on the excel file. But it doesnt work as expected.

      thank you,
      areef

      Delete
    2. Areef,

      If you use the above code all the records in your gridview, irrespective of your front end paging will be exported to the first sheet of excel.

      For example,say you have 100 rows in your gridview and you are showing only 10 records at a time with paging, when you export this to excel all the hundred records will be exported to the first sheet of excel file. So you actually need not have paging values in your excel sheet.

      Thanks,
      Priya

      Delete
    3. Hi Priya,

      Thanks for your reply.

      You have a point, my reason was just to have a page number whenever i print the excel file.

      thanks,
      areef

      Delete

  6. Thanks a lot Priya Darshini,

    I need one more help ..

    how to import n number of grid view pages to excel ..

    Please help me ..

    ReplyDelete
  7. Awesome work priya, became a geek :-)

    ReplyDelete
  8. IS it possible by using this methos to create spread sheets and paste grid view in that

    ReplyDelete
  9. Hi priya
    im having a prblm cn u plz help me in solving it.'System.IO.StreamWriter' does not contain a constructor that takes 0 arguments thx for help

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Hello Priya,

    how can I format the excel file where the gridview is exactly the same as the gridview?

    Because I have this GridView which has this type of text : "201303060009"
    Then when I download the excel file the text becomes "2.01302E+11"?? But when I click the cell it becomes the text in the GridView.
    How can I get the exact format?? I don't want it to change.

    Thank you.

    ReplyDelete
  12. Hi Priya,
    very nice post.
    Its very helpful to us.
    thank u.

    ReplyDelete
  13. Hi priya..
    Nice post.i have a query.
    I have 3 buttons: view button, reset button and export excel button with in a date range(start date and end date),
    In view button the gridview will shown within a date range.
    In reset button the gridview will reset
    i used,
    gvrecord.datasource=null;
    gvrecord.databind();
    but, After reset, the gridview record will shown in export excel sheet..
    guide me..
    Thanks

    ReplyDelete
  14. Hi,

    How to Freeze the First Row while Exporting the Grid to Excel.

    ReplyDelete
  15. when i export the Gridview to Excel, i want the excel file to have the Page number when i print it as it was in Gridview.

    ReplyDelete
  16. Hi Priya,

    Could you please help me in exporting multiple gridview in excel, now what i mean by multiple grid view is one of my developer has added gridview within a gridview so basically there are 3 gridviews within the same gridview, this was done for getting the reports of users. We are really cracking our heads out to find a solution for this however I have searched on the internet there are no solutions for exporting to excel within the same multiple gridview.

    Thanks in advance.

    ReplyDelete
  17. Hi Priya, though it might seem dumb as vast majority is getting results from your post but when i try to export using above code, upon file opening it gives me a message that file is not in correct format and then instead of data,i get this.

    style .textmode { mso-number-format:\@; } /style form method="post" action="Cart.aspx" id="ctl01"
    div class="aspNetHidden"
    input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="" /
    /div
    div

    /div /form

    any suggestions ?

    ReplyDelete
  18. Please note i have removed all "< >" characters from above.

    ReplyDelete
    Replies
    1. You defintely have to place those angular brackets to get this work!

      Hope this helps!

      Thanks,
      Priya

      Delete
    2. It has nothing to do with the angular brackets...
      http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/03/11/excel-2007-extension-warning.aspx

      It's a security feature in Excel 2007 called Extension Hardening.
      As far as I know there is no solution to it but change register key on the client machine which is not a good solution...

      Delete
    3. This could be a solution to the above problem though...
      http://www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format-than-specified-by-the-file-extension.aspx

      Delete
  19. Hi Priya,
    I used above code. But when i try to 'Save As' downloded file, 'File Type' option displays as 'Html page'. And if i save file as excel file, new file size is very small as compared to actual downloaded file. Please suggest.

    ReplyDelete
  20. Yess its working. Thank you very very much :)

    ReplyDelete
  21. Great article.
    if you use a custom DataSource be sure to load it again!

    ReplyDelete
  22. ive ot an error in this line.
    public override void VerifyRenderingInServerForm(Control control)
    {

    }

    pls help..
    tnx in advance.

    ReplyDelete
    Replies
    1. What is the error message you are getting? Also, did you set EnableEventValidation="false" in Page Directive ?

      Delete
  23. Hello Priya ........I have had several pages that export gridview controls to Excel working for many months now. All of a sudden today, none are working. Strange. I have been modifying one page but what about the several other pages I haven't touched in months, they no longer work! When I click the export to Excel button, I immedately get a Windows Internet Explorer message box, asking, 'What do you want to do with main.aspx?' - the page name

    When I step through the code while debugging, I do get the "unable to evaluate expression because the code is optimized or a native frame is on the top of the call stack". But how to explain these errors on pages I haven't changed in months? So Strange.

    ReplyDelete
  24. Hi,

    I have two gridviews in different pages namely gridview1 and gridview2
    I used a hyperlink in gridview1 to show the result in gridview2
    This is working fine
    coming to the Export
    1.pdf is downloading and showing records
    2.Excel is downloading and it doesn't show any records.
    I tried with different aspects but it doesn,t show any records
    please do this needful.

    ReplyDelete
  25. Just how do dog shows work? There's CBD oil for dogs to a dog show than what we see on TV. This article explains how dog shows work.

    ReplyDelete
  26. We often highlight some of the world's newest and most bizarre buy salvia but one of the older ones in our society is probably more of a necessity today than when it was invented. In regards to some of our modern marvels there are some life-spans that have been ingrained into our psyche without us probably even realising it.

    ReplyDelete
  27. If you have an occasion coming up that is going to require you, and your children, to be dressed very nicely, then you may be dreading the shopping for the baby dresses. cheap flower girl dresses can be difficult to find that are appropriate for the age of the child, the weather conditions, and the event they are attending. Many mothers would rather have root canal work performed than to have to go shopping for baby dresses for a special occasion.

    ReplyDelete
  28. Through new services, there is a way to save a great deal of money by opting to rent private CBD parking spaces. Through opting to rent a private parking cbd wax you can negotiate not only what price you want to pay, but also location and convenience, making this a great option for those who want to take control of their own commute.

    ReplyDelete
  29. As the process and costs of buying real estate in Kenya can vary by location and property type, this scenario helps explain one of the most popular real-estate investment options: a 3 bedroom apartment in the CBD of Nairobi. Buying any real estate in Kenya is an easy process guided by Kenyan property laws and regulations. Be it as it may, the process will often differ slightly based on the type of property you want to buy and how you are going to finance the property investment. https://royalcbd.com/

    ReplyDelete
  30. online dispensary shipping usa marijuana store that finds a local bank or credit union to work with may still have to charge a convenience fee, which is not appreciated by marijuana users. Although there are still no statistics on whether oil cartridges for sale consumers are more sensitive to COVID-19, greenstore dispensary

    ReplyDelete
  31. Greenstore Dispensary is for anyone who is looking to get 420 mail order delivered  to their home address for both medical and recreational use, here you can definitely have medical marijuana  sale online delivered to you at the comfort of your home at the very best choice with just a single click buy weed online now. Buy Glo extracts vape carts

    ReplyDelete
  32. This article helps past, current, and future prospective patients in Washington State, choose the right clinic for their authorization. Please avoid getting your authorization from a dispensary, or a clinic that exists solely for giving authorizations. These kind of operations will soon be illegal. This article shows why. Buy 710 King pen Cartridges

    ReplyDelete
  33. Nice post, keep it up.
    Hope you are well in the current situation.
    seo agency
    marketingcom.my

    ReplyDelete
  34. Good article! Check out
    https://www.tnect.com.my/website-design/
    https://www.maxis-broadband.com/

    ReplyDelete
  35. Patients answer patient satisfaction survey questions based on their perception, and yet there is limited context for the healthcare provider. It leaves one asking the questions - who were they interacting with, what was said, when did it happen, and how capable and reliable was the patient to make those interpretations? So instead of convening a committee to explore the reasons for poor scores, healthcare mystery shopping provides healthcare clients with the research intelligence needed to make real-time improvements. purchase weed online

    ReplyDelete
  36. Marijuana laws throughout the country are being reformed, and some states have even voted to allow the use of cannabis for medical and recreational uses. However, states like Utah continue to outlaw the drug, although it offers some exceptions. curepen carts

    ReplyDelete
  37. https://www.lokataste.com/mcdonalds-menu-price-malaysia
    https://www.lokaeats.com/whataburger-menu-price-usa/

    ReplyDelete
  38. https://psychallucin.com/2019/12/30/buy-psychedelic-mushroom/

    ReplyDelete
  39. Usually tenants have to sign a bond to rent a property on lease. They have to sign an agreement with the landlord to ensure the security of the property and its assets. Further, they have to assure the owner that their property will be kept in the same way as it was when the lease was initiated. And to ensure this, tenants have to take care of the property and keep it clean. To get this job done, tenants need to avail end of lease cleaning services that is a very convenient and budget-friendly option to help them handover the place in its original condition. Carpet Cleaners Melbourne

    ReplyDelete
  40. Mental, Emotional, Physical Wellness versus Mental, Emotional, Physical Illness is a positive way to talk about Mental Health Challenges. I also like to talk about the difference between mental health vs. emotional health vs. wholesale CBD flower

    ReplyDelete
  41. There are many things that each home needs that people will often neglect. Cleaning is one of those things. Figuring out how to choose a house cleaning service is not always easy. cleaning hero pte ltd

    ReplyDelete
  42. There a few fascinating points in time in this post but I don’t know if I see these center to heart. There may be some validity but I’ll take hold opinion until I explore it further. Excellent article , thanks and then we want a lot more! Put into FeedBurner too sa casino

    ReplyDelete
  43. Hello! I just now want to supply a massive thumbs up to the excellent info you may have here during this post. We are returning to your blog post to get more soon. slot

    ReplyDelete
  44. Free online internet gaming has exploded over the past 10 years and now comprises of a large collection of game developers, publishers, web portals and millions of casual game players. Talented game developers are in more limited supply than publishers and portals, yet even they are steadily growing in supply as more companies and brands look for sponsorship deals with high quality upcoming free games. The rapid growth of casual games has seen the emergence of many talented free game developers in the flash market, find out about some of the most exciting game developers and their highest rated titles. slot228

    ReplyDelete
  45. Let's face it - a lot of eco-friendly cleaning companies have been stealing the show over the past couple of years. Basically, people are more eager to choose an environmentally friendly service than ever before. That has a lot to do with the fact that such services are free of toxic and chemicals and therefore do not pose a threat to our health. best office cleaning

    ReplyDelete
  46. hello, your website is really good. We do appreciate your give good results bitmain antminer s19

    ReplyDelete
  47. hi there! Nice stuff, do maintain me published whilst you submit again some thing like this! Extraordinary put up.. Happy i got here throughout this looking forward to proportion this with every body here thank you for sharing . You've got achieved a awesome activity on this article. I've simply stumbled upon your blog and enjoyed analyzing your weblog posts very a good deal. Thank you for the beneficial data. First-rate article with splendid idea! Thanks for any such precious article. I definitely appreciate for this terrific statistics.. I’m extremely impressed together with your writing capabilities and also with the layout on your blog. 먹튀폴리스

    ReplyDelete
  48. satisfactory article ithe best work! Thanks for the valuable information and insights you have so supplied right here.. 안전놀이터

    ReplyDelete
  49. youre so cool! I dont think ive examine anything just like this previous to. So quality to discover any person with some original mind in this concern. Realy appreciation for beginning this up. This internet website online is one area that is needed at the net, any individual if we do originality. Useful job for bringing new stuff for the internet! I'm extremely joyful that i observed this net blog , just the proper information that i was searching out! . It's miles without a doubt a nice and useful piece of statistics. I’m glad that you just shared this beneficial tidbit with us. Please stay us updated like this. Thanks for sharing. This is the proper blog for each person who hopes to find out about this subject matter. You recognize an entire lot its nearly hard to argue alongside (no longer that i genuinely would need…haha). You really put an entire new spin for a topic thats been written approximately for years. Outstanding stuff, just remarkable! The internet website online is lovingly serviced and saved as an awful lot as date. So it need to be, thank you for sharing this with us. This net web page is called a stroll-with the aid of for all of the facts you wanted approximately this and didn’t recognize who to ask. Glimpse proper right here, and you’ll undoubtedly discover it. Proper publish and a pleasing summation of the hassle. My best trouble with the analysis is given that lots of the populace joined the refrain of deregulatory mythology, given vested hobby is inclined toward perpetuation of the cutting-edge system and given a loss of a famous cheerleader to your arguments, i’m now not seeing a good deal within the way of exchange. I might absolutely love to visitor publish in your weblog . A few certainly first-class stuff in this net web page , i love it. Im no professional, but i remember you simply made the excellent factor. You clearly know what youre talking about, and i can truly get behind that. Thanks for being so prematurely and so honest. 온라인카지노

    ReplyDelete
  50. It’s used for cell devices along with cameras and smartphones, as an example, and it’s capable of being a lifesaver in sure situations. That is I can make sure to be reading your blog more. You made a good point but i can't help but marvel, what approximately the alternative aspect? !!!!!! Thank you 안전놀이터

    ReplyDelete
  51. this become sincerely an interesting topic and i kinda believe lights, maintain doing more and additional dazzling fantastic site, wherein did u give you the facts in this posting? I'm pleased i found it though, ill be checking lower back quickly to discover what extra posts you encompass 스포츠토토

    ReplyDelete
  52. i just couldn’t go awat will be a great deal extra beneficial than ever before. This website changed into… how do i say it? Applicable!! Finally i have located some thing which helped me. Thank you lots! 메이저놀이터

    ReplyDelete


  53. will be praised anywhere. I am a columnist This post is really the best on this valuable topic 검증카지노

    ReplyDelete
  54. I had a great time reading your thoughtful blog post. I could relate to your thoughts on a few points raised in the article, and it's obvious that you have a great deal of experience in the relevant subject. Please have a look at our finance assignment help service.

    ReplyDelete

emo-but-icon

SUBSCRIBE


item