GridView - Export to Excel (VB.Net)

Posted by David Wier on 03/4/10 | Code Samples

This sample uses the Products table of the Northwind Database to show how to export a Gridview's rows to an Excel Spreadsheet, using VB.Net.

There are a couple of 'tricks' used here, in order to get this done. First, we dynamically create a form and populate the Controls collection with it, and then populate the form with the GridView. Before, in the DataGrid sample, this wasn't necessary, as we could use the Rendercontrol method directly.

Next, in order to get the full results in the spreadsheet, we temporarily 'turn off' paging (also necessary if sorting was on), otherwise, it would return errors. Lastly, [FieldList] is used in place of the actual list of fields in the SelectCommand, specifically for saving horizontal screen real estate.

Code Behind:

Imports System.IO

Protected Sub doExcel(Source as Object, E as EventArgs)
If gv.Rows.Count.ToString + 1 < 65536 Then
    gv.AllowPaging="False"
    gv.DataBind
    Dim tw As New StringWriter()
    Dim hw As New System.Web.UI.HtmlTextWriter(tw)
    Dim frm As HtmlForm = new HtmlForm()
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("content-disposition", "attachment;filename=" & txtFile.text & ".xls")
    Response.Charset = ""
    EnableViewState = False
    Controls.Add(frm)
    frm.Controls.Add(gv)
    frm.RenderControl(hw)
    Response.Write(tw.ToString())
    Response.End()
    gv.AllowPaging="True"
    gv.Databind
Else
    LblError.Text="Too many rows - Export to Excel not possible"
End If
End Sub

ASP.Net page:
<html>
    <head>
        <meta name="GENERATOR" Content="ASP Express 5.0"/>
        <title>Export GridView to Excel</title>
    </head>
    <body>
        <form id="fm1" runat="server">
        Enter File Name:
        <asp:TextBox id="txtFile" Text="MyExcelFile" Runat="server" />
<asp:GridView Runat="server"
    Id="gv"
    GridLines="None"
    cellpadding="0"
    cellspacing="1"
    Headerstyle-BackColor="#7988B7"
    Headerstyle-Forecolor="#FFFFFF"
    Headerstyle-Font-Names="Arial"
    Headerstyle-Font-Bold="True"
    Headerstyle-Font-Size="11"
    BackColor="#E0E0F6"
    Font-Names="Arial"
    Font-Size="10"
    AlternatingRowStyle-BackColor="#EFEFEF"
    AlternatingRowStyle-Font-Names="Arial"
    AlternatingRowStyle-Font-Size="10"
    BorderColor="Black"
    DataKeyNames="ProductID"
    DataSourceID="sqlProducts"
    AllowPaging="True"
    AutogenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="ProductID" HeaderText="ID" ReadOnly="True"/>
        <asp:BoundField DataField="ProductName" HeaderText="Name"/>
        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID"/>
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID"/>
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity"/>
        <asp:BoundField DataField="UnitPrice" HTMLEncode="False" DataformatString="{0:c}" HeaderText="Price"/>
        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"/>
    </Columns>
</asp:GridView>
    <asp:Button id="btn" Text="Get It" onclick="doExcel" Runat="server" />
    <p>
    <asp:Label ID="LblError" Runat="server" />
    </p>
<asp:SQLDataSource ID="sqlProducts"
    Runat="Server"
    SelectCommand = "SELECT [FieldList] From NWProducts"
    ConnectionString="<%$ ConnectionStrings: %>">
</asp:SQLDataSource>
        </form>
    </body>
</html>

0 Comments

COMMENTS

Name:
URL:
Comment:
Comments are disabled for this article.