If you would like to hire my services, you can now do so by visiting the following link:
Website Design Darlington
Article Statistics
Code Bank Statistics
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 GridView2) Add the code to export the data to excelHere'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.
Posted on 16/03/2007 06:42:30
1. Marco 13/04/2007 06:24:59
Great job, guy!This will be useful!
2. Helge 26/04/2007 02:42:10
Worked like charmRight to the point!
3. Dimitar Voynov 07/06/2007 00:32:30
Nice work!
4. John 12/07/2007 07:28:45
This code is great. However, when I try to export a large file (over 6800 lines, 30 columns) the error message "The page you are looking for is currently unavailable..." What should I do?
5. Mark Smith 14/07/2007 04:31:57
John,I imagine that is an issue with the page timeout. You may want to check you setting in IIS and increase the timeout if necessary.Thanks,Mark
6. John 02/11/2007 09:13:42
It works beautifully... just have to modify a little. Solved my problem and meet what I am looking for.
7. Gau 08/01/2008 00:00:36
This is great but how do I include it in a class in the App_Code folder?Thanks!
8. Jet 03/04/2008 05:14:18
this is cool !!! I've tried this technique and it workshope you could post more trivia's in .NET
9. Pawan Gupta 17/06/2008 02:56:18
It's Really fantastic and helpful code.
Please keep your comments relevant to this page. Any inappropriate or purely promotional comments may be removed. Email addresses are never displayed but are required so you can confirm your comments.