ASP.NET: How to export a GridView to Excel
From Wiki
Summary: A simple look at how you can easily add "Export To Excel" functionality to your GridView
Data that is shown to users in a GridView can be very useful, however, there are times when a user needs to save this information to a more permanent state, or they need to do some manipulation to it. This is where an "Export To Excel" feature would come in handy. Luckily for us, it's fairly easy to accomplish and you don't need to change the way you populated your GridView originally.
Let's start by creating a simple page with just a GridView and a Button on it:
- <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default1.aspx.vb" Inherits="Default1" %>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml" >
- <head runat="server">
- <title>Export To Excel example</title>
- </head>
- <body>
- <form id="form1" runat="server">
- <div>
- <asp:GridView ID="GridView1" runat="server">
- </asp:GridView>
- <asp:Button ID="Button1" runat="server" Text="Button" />
- </div>
- </form>
- </body>
- </html>
Now, we need to:
1) Populate the GridView
2) Add the code to export the data to excel
Here's how this is done:
- Imports System.Data
- Partial Class Default1
- Inherits System.Web.UI.Page
- Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
- ' Check for a postback
- If Not Page.IsPostBack Then
- ' Bind the Gridview with some sample data
- GridView1.DataSource = GetData()
- GridView1.DataBind()
- End If
- End Sub
- Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
- ' Clear the response
- Response.Clear()
- ' Set the type and filename
- Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
- Response.Charset = ""
- Response.ContentType = "application/vnd.xls"
- ' Add the HTML from the GridView to a StringWriter so we can write it out later
- Dim sw As System.IO.StringWriter = New System.IO.StringWriter
- Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
- GridView1.RenderControl(hw)
- ' Write out the data
- Response.Write(sw.ToString)
- Response.End()
- End Sub
- Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
- End Sub
- Private Function GetData() As DataTable
- ' Declarations
- Dim dt As New DataTable
- Dim dr As DataRow
- ' Add some columns
- dt.Columns.Add("Column1")
- dt.Columns.Add("Column2")
- ' Add some test data
- For i As Integer = 0 To 10
- dr = dt.NewRow
- dr("Column1") = i
- dr("Column2") = "Some Text " & (i * 5)
- dt.Rows.Add(dr)
- Next
- ' Return the DataTable
- Return dt
- End Function
- End Class
As you'll see from above, we populate the GridView on the Page Load event with some sample data and the Button1.Click method handles the export to excel.
Hopefully the comments in the code are fairly self-explanatory so I won't have to explain the process here. The only thing that may be confusing is the empty "VerifyRenderingInServerForm" procedure. The only reason this is included is without it, the ASP.NET Page will complain about the GridView not being inside form tags. By overriding this method, we get rid of the error.
This Hack is part of the ASP.NET Hacks collection
