Changing gridview to excel using button

    Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        Return
    End Sub

Select allOpen in new window

Partial Public Class _Default
    Inherits System.Web.UI.Page

    Public lNames As List(Of String)

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        lNames = New List(Of String)
        lNames.Add("test")
        lNames.Add("test1")
        lNames.Add("test2")
        lNames.Add("test3")
        lNames.Add("test4")
        lNames.Add("test5")

        NamesGrid.DataSource = lNames
        NamesGrid.DataBind()
    End Sub

    Protected Sub submitExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitExcel.Click
        If NamesGrid.HasControls = True Then
            '// Disable paging
            NamesGrid.AllowPaging = False
            NamesGrid.DataBind()
            '// exluded columns arraylist
            Dim defaultExcludedColumns As New ArrayList()

            '// Always exclude these columns
            defaultExcludedColumns.Add("") '// Removes columns without headers from the output.

            '// Send to base Excel export method 
            ExportGridView(NamesGrid, "ExcelFileName", defaultExcludedColumns)

            '// Rebind with paging enabled
            NamesGrid.AllowPaging = True
            NamesGrid.DataBind()
        End If

    End Sub


    ' This will export the gridview...
    '/// <summary>
    '/// Export GridView data to Excel.
    '/// </summary>
    '/// <param name="grdView">GridView control to export.</param>
    '/// <param name="filename">Filename of excel spreadsheet.</param>
    '/// <param name="excludedColumnList">ArrayList of columns to exlude.</param>
    Sub ExportGridView(ByVal grdView As GridView, ByVal filename As String, ByVal excludedColumnList As ArrayList)

        '// Clear response content & headers
        Response.Clear()
        Response.ClearContent()
        Response.ClearHeaders()

        '// Add header
        Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls")
        Response.Charset = String.Empty
        Response.Cache.SetCacheability(System.Web.HttpCacheability.Public)
        Response.ContentType = "application/vnd.xls"
        '// Start Excel Write
        '// Create stringWriter
        Dim stringWrite As New System.IO.StringWriter()

        '// Create HtmlTextWriter
        Dim htmlWrite As New HtmlTextWriter(stringWrite)

        '// Remove controls from Column Headers
        If (grdView.HeaderRow IsNot Nothing And grdView.HeaderRow.Cells IsNot Nothing) Then
            Dim ct As Integer
            For ct = 0 To grdView.HeaderRow.Cells.Count - 1 Step ct + 1
                ' Save initial text if found
                Dim headerText As String = grdView.HeaderRow.Cells(ct).Text

                ' Check for controls in header
                If grdView.HeaderRow.Cells(ct).HasControls() Then
                    ' Check for link button
                    If grdView.HeaderRow.Cells(ct).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.DataControlLinkButton" Then
                        ' link button found, get text
                        headerText = (CType(grdView.HeaderRow.Cells(ct).Controls(0), LinkButton)).Text
                    End If

                    ' Remove controls from header
                    grdView.HeaderRow.Cells(ct).Controls.Clear()
                End If

                ' Reassign header text
                grdView.HeaderRow.Cells(ct).Text = headerText
            Next

        End If
        '// Remove footer
        If (grdView.FooterRow IsNot DBNull.Value) Then

            grdView.FooterRow.Visible = False
        End If

        '// Remove unwanted columns (header text listed in removeColumnList arraylist)
        Dim field As DataControlField
        For Each field In grdView.Columns

            If (excludedColumnList.Contains(field.HeaderText)) Then

                field.Visible = False
            End If
        Next

        '// Call gridview's renderControl
        grdView.RenderControl(htmlWrite)

        '// Write Response to browser
        Response.Write(stringWrite.ToString())

        Response.End()
    End Sub

    Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        Return
    End Sub
End Class

Select allOpen in new window

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="test1._Default" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="NamesGrid" runat="server" AutoGenerateColumns="True" 
                ShowHeader="False">
        </asp:GridView>        
        <asp:Button ID="submitExcel" runat="server" Text="export" />
    </div>
    </form>
</body>
</html>

Select allOpen in new window

Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        Return
    End Sub

Select allOpen in new window