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.