In the following example, I will demonstrate how to export GridView Data into MS-Excel using VB.NET. It uses the same business object to select data from database. I have simply formatted the GridView control from previous example.
1: <%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation = "False" CodeFile="ExportGridViewToExcel.aspx.vb"
2: Inherits="ExportGridViewToExcel" %>
3:
4: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
5:
6: <html xmlns="http://www.w3.org/1999/xhtml" >
7: <head runat="server">
8: <title>Export Customers Data</title>
9: </head>
10: <body>
11: <form id="frmCustomer" runat="server">
12: <div style="text-align:center">
13: <asp:Button ID="btnExportToExcel" runat="server" Text="Export to Excel" /><br />
14: <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label> <br />
15: <asp:GridView ID="gdvCustomer" DataKeyNames="CustID" runat="server" AllowPaging="True" DataSourceID="odsCustomer"
16: AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None"
17: BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical"
18: RowStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left">
19: <Columns>
20: <asp:BoundField DataField="CustID" HeaderText="Cust ID" />
21: <asp:BoundField DataField="CustName" HeaderText="Name" SortExpression="CustName" />
22: <asp:BoundField DataField="CustAddress" HeaderText="Address" SortExpression="CustAddress"/>
23: </Columns>
24: <EmptyDataTemplate>
25: No Customer Record Found.
26: </EmptyDataTemplate>
27: <FooterStyle BackColor="#CCCC99" />
28: <RowStyle BackColor="#F7F7DE" />
29: <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
30: <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
31: <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
32: <AlternatingRowStyle BackColor="White" />
33: </asp:GridView>
34:
35: <asp:ObjectDataSource ID="odsCustomer" runat="server"
36: SelectMethod="GetCustomers" TypeName="Vishwa.Example.Business.BIZCustomer" >
37: </asp:ObjectDataSource>
38: </div>
39: </form>
40: </body>
41: </html>
1: Option Explicit On
2: Option Strict On
3: Imports System.IO
4: ' Author : Vishwa@VishwaMohan.com
5: ' Date : 12/15/2006
6: ' Class : ExportCustomer
7: ' Purpose: To Export Customers Record into Excel
8: Partial Class ExportGridViewToExcel
9: Inherits System.Web.UI.Page
10: Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
11: If gdvCustomer.Rows.Count + 1 < 65536 Then
12: Me.lblMessage.Text = ""
13: Me.gdvCustomer.AllowPaging = False
14: 'If you are binding GridView under Code Behind
15: 'Me.gdvCustomer.DataSource = GetCustomer
16:
17: Me.gdvCustomer.DataBind()
18: Dim tw As New StringWriter()
19: Dim hw As New System.Web.UI.HtmlTextWriter(tw)
20: Dim frm As HtmlForm = New HtmlForm()
21: Page.Response.ContentType = "application/vnd.ms-excel"
22: Page.Response.AddHeader("content-disposition", "attachment;Customer.xls")
23: Page.Response.Charset = ""
24: Page.EnableViewState = False
25: frm.Attributes("runat") = "server"
26: Controls.Add(frm)
27:
28: frm.Controls.Add(gdvCustomer)
29: frm.RenderControl(hw)
30: Response.Write(tw.ToString())
31: Response.End()
32: Me.gdvCustomer.AllowPaging = True
33:
34: 'If you are binding GridView under Code Behind
35: 'Me.gdvCustomer.DataBind()
36: Else
37: Me.lblMessage.Text = "Too many rows - Export to Excel not possible"
38: End If
39: End Sub
40: End Class
Page at the run Time