Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

ASP.NET: How to export a GridView to Excel

From Wiki

Jump to: navigation, search

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:

aspLine number Off | Hide | Select all
  1. <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default1.aspx.vb" Inherits="Default1" %>  
  2.  
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4.  
  5. <html xmlns="http://www.w3.org/1999/xhtml" >  
  6. <head runat="server">  
  7.     <title>Export To Excel example</title>  
  8. </head>  
  9. <body>  
  10.     <form id="form1" runat="server">  
  11.     <div>  
  12.         <asp:GridView ID="GridView1" runat="server">  
  13.         </asp:GridView>  
  14.         <asp:Button ID="Button1" runat="server" Text="Button" />  
  15.     </div>  
  16.     </form>  
  17. </body>  
  18. </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:

vbnetLine number Off | Hide | Select all
  1. Imports System.Data  
  2. Partial Class Default1
  3.     Inherits System.Web.UI.Page
  4.  
  5.     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  6.         ' Check for a postback  
  7.         If Not Page.IsPostBack Then
  8.             ' Bind the Gridview with some sample data  
  9.             GridView1.DataSource = GetData()
  10.             GridView1.DataBind()
  11.         End If
  12.     End Sub
  13.  
  14.     Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
  15.         ' Clear the response  
  16.         Response.Clear()
  17.  
  18.         ' Set the type and filename  
  19.         Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
  20.         Response.Charset = ""
  21.         Response.ContentType = "application/vnd.xls"
  22.  
  23.         ' Add the HTML from the GridView to a StringWriter so we can write it out later  
  24.         Dim sw As System.IO.StringWriter = New System.IO.StringWriter
  25.         Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
  26.         GridView1.RenderControl(hw)
  27.  
  28.         ' Write out the data  
  29.         Response.Write(sw.ToString)
  30.         Response.End()
  31.     End Sub
  32.  
  33.     Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
  34.  
  35.     End Sub
  36.  
  37.     Private Function GetData() As DataTable
  38.         ' Declarations  
  39.         Dim dt As New DataTable
  40.         Dim dr As DataRow
  41.  
  42.         ' Add some columns  
  43.         dt.Columns.Add("Column1")
  44.         dt.Columns.Add("Column2")
  45.  
  46.         ' Add some test data  
  47.         For i As Integer = 0 To 10
  48.             dr = dt.NewRow
  49.             dr("Column1") = i
  50.             dr("Column2") = "Some Text " & (i * 5)
  51.             dt.Rows.Add(dr)
  52.         Next
  53.  
  54.         ' Return the DataTable  
  55.         Return dt
  56.     End Function
  57.  
  58. 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

445 Rating: 2.8/5 (6 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/ASP.NET:_How_to_export_a_GridView_to_Excel"

Powered by MediaWiki GNU Free Documentation License 1.2
Valid XHTML | Valid CSS