Vishwa Mohan's Blog: Export GridView Data in MS-Excel

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.

Page Name: ExportGridViewToExcel.Aspx

------------------------------------

Note: You will be required to add - EnableEventValidation = "False" at Page Directive in order to Export to work.

-------------------------------------

Code Snippet

<%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation = "False" CodeFile="ExportGridViewToExcel.aspx.vb"

        Inherits="ExportGridViewToExcel" %>

<!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 Customers Data</title>

</head>

<body>

    <form id="frmCustomer" runat="server">

    <div  style="text-align:center">

        <asp:Button ID="btnExportToExcel" runat="server" Text="Export to Excel" /><br />

        <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label> <br />

        <asp:GridView ID="gdvCustomer" DataKeyNames="CustID" runat="server" AllowPaging="True" DataSourceID="odsCustomer"

            AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None"

            BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical"

            RowStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left">

            <Columns>

                <asp:BoundField DataField="CustID" HeaderText="Cust ID"  />

                <asp:BoundField DataField="CustName" HeaderText="Name" SortExpression="CustName" />

                <asp:BoundField DataField="CustAddress" HeaderText="Address" SortExpression="CustAddress"/>

            </Columns>

            <EmptyDataTemplate>

                No Customer Record Found.

            </EmptyDataTemplate>

            <FooterStyle BackColor="#CCCC99" />

            <RowStyle BackColor="#F7F7DE" />

            <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />

            <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

            <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

            <AlternatingRowStyle BackColor="White" />

        </asp:GridView>



          <asp:ObjectDataSource ID="odsCustomer" runat="server"

              SelectMethod="GetCustomers"

            TypeName="Vishwa.Example.Business.CustomerFac" >

         </asp:ObjectDataSource>

        </div>

    </form>

</body>

</html>

Code Behind : ExportGridViewToExcel.Aspx.vb

Code Snippet

Option Explicit On

Option Strict On

Imports System.IO

' Author : Vishwa Mohan

' Date : 12/15/2006

' Class : ExportCustomer

' Purpose: To Export Customers Record into Excel

Partial Class ExportGridViewToExcel

    Inherits System.Web.UI.Page

    Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click

        If gdvCustomer.Rows.Count + 1 < 65536 Then

            Me.lblMessage.Text = ""

            Me.gdvCustomer.AllowPaging = False

            'If you are binding GridView under Code Behind

            'Me.gdvCustomer.DataSource = GetCustomer

            Me.gdvCustomer.DataBind()

            Dim tw As New StringWriter()

            Dim hw As New System.Web.UI.HtmlTextWriter(tw)

            Dim frm As HtmlForm = New HtmlForm()

            Page.Response.ContentType = "application/vnd.ms-excel"

            Page.Response.AddHeader("content-disposition", "attachment;Customer.xls")

            Page.Response.Charset = ""

            Page.EnableViewState = False

            frm.Attributes("runat") = "server"

            Controls.Add(frm)

            frm.Controls.Add(gdvCustomer)

            frm.RenderControl(hw)

            Response.Write(tw.ToString())

            Response.End()

            Me.gdvCustomer.AllowPaging = True

            'If you are binding GridView under Code Behind

            'Me.gdvCustomer.DataBind()

        Else

            Me.lblMessage.Text = "Too many rows - Export to Excel not possible"

        End If

    End Sub

End Class

Page at the run Time

Vishwa Mohan's Blog

Sunday, December 17, 2006

Export GridView Data in MS-Excel

1 comment:

Blog Archive