Export GridView to Excel

Users often post a requirement to export the data in an ASP.NET GridView to Excel. Here's a simple way to do so:

C#


protected void Button1_Click(object sender, EventArgs e)

{

    Response.AddHeader("content-disposition", 
       "attachment;filename=FileName.xls");

    Response.Charset = String.Empty;

    Response.ContentType = "application/vnd.xls";

    System.IO.StringWriter sw = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.RenderControl(hw);

    Response.Write(sw.ToString());

    Response.End();

}



VB.NET


    Protected Sub Button1_Click(ByVal sender As Object, _
     ByVal e As EventArgs)

        Response.AddHeader("content-disposition", _
         "attachment;filename=FileName.xls")

        Response.Charset = String.Empty

        Response.ContentType = "application/vnd.xls"

        Dim sw As System.IO.StringWriter = _
          New System.IO.StringWriter()

        Dim hw As System.Web.UI.HtmlTextWriter = _
            New HtmlTextWriter(sw)

        GridView1.RenderControl(hw)

        Response.Write(sw.ToString())

        Response.End()

    End Sub



To read about some more tips and tricks about the GridView, check GridView Tips and Tricks using ASP.NET 2.0




About The Author

Suprotim Agarwal
Suprotim Agarwal, Developer Technologies MVP (Microsoft Most Valuable Professional) is the founder and contributor for DevCurry, DotNetCurry and SQLServerCurry. He is the Chief Editor of a Developer Magazine called DNC Magazine. He has also authored two Books - 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal.

8 comments:

Unknown said...

Trying the VB code I get: System.Web.HttpException: Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.

Abhilash said...

Hi,
I doubt.
I tried the code-segment in the post. When i click 'Button_1', the page simply refreshes & no excel files are generated.
Please note, my pc doesnt have any version of MS office installed. Im using OpenOffice3.0. Is it due to that...???
Thanks.

Abhilash said...

Oh!
I solved it. Has to do some searching in MSDN gallery and solved via including the below code inot my code-behind.

---
[code-behind]
public override void VerifyRenderingInServerForm(Control control)
{}

Suprotim Agarwal said...

Glad to hear that you solved it! Can you share the complete solution for the benefit of future users.Thanks.

Abhilash said...

Thanks for your attention.
While building the sample, i encountered 2 problems. First, when i click the Button_1 for downloading, the page simply refreshes & no excel files are generated. When I build the website second time, I's getting the same error what Peggy was having.
The first error was solved automatically(i dont know how!). Peggy's error was solved when i include the 'VerifyRenderingInServerForm()' method in the code-behind.

Hence the complete code looks like
[Code-Behind]
protected void Button1_Click(object sender, EventArgs e)
{
// Same as above code
}
public override void VerifyRenderingInServerForm(Control control)
{
// Empty Method
}
---

Thankz.

Unknown said...

Here is complete details about all problems we face in this process:
http://praveenbattula.blogspot.com/2010/09/gridview-and-export-
to-excel.html

Anonymous said...

Wow Abhilash!!!! thank you! you save my life! with that response!!!!

really work!

greetings from Colombia

Anonymous said...

Thanks Abhilash!!!

I have used your code and it works