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


advertise here

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

emo-but-icon

Currency Converter

Built using AngularJS and ASP.NET Web API

SUBSCRIBE


item