hi Guys,

I have a custom control gridview:

I have a filter row in the header of each column with a textbox and a button to filter. when the user clicks the filter button this is one of the blocks of code that get executed:

Code:
Protected Overridable Sub ApplyFilterCommand(ByVal filterCommand As String)
            Dim dsv As DataSourceView = Me.GetData

            If (TypeOf dsv Is SqlDataSourceView) Then
                Dim selectCommand As String = DirectCast(dsv, SqlDataSourceView).SelectCommand

                If (selectCommand.Contains(filterCommand)) Then
                    Return
                End If

                If (selectCommand.Contains("WHERE")) Then
                    selectCommand += " AND " & filterCommand
                Else
                    selectCommand += " WHERE " & filterCommand
                End If

                DirectCast(dsv, SqlDataSourceView).SelectCommand = selectCommand
            End If
        End Sub
my gridview is then filtered accordingly.

Now I have the following code to export to Excel.

Code:
Private Sub lnkExport_Click(ByVal sender As Object, ByVal e As EventArgs)
            Me.AllowMultiColumnSorting = False
            Me.AllowPaging = False
            Me.AllowSorting = False
            Me.ShowFooter = False
            Me.EnableViewState = False

            Me.PageIndex = 0
            Me.PageSize = Me.RecordsCount
            Me.DataSourceID = Me.DataSourceID

            Me.DataBind()

            Dim exp As GridViewExcelExporter = New GridViewExcelExporter()
            exp.Export(m_excelExportFileName, Me.Page, Me)
        End Sub
Code:
Public Class GridViewExcelExporter

        Public Sub New()

        End Sub

        Public Overridable Sub Export(ByVal reportName As String, ByVal CurrentPage As Page, ByVal NtGridView As Control)
            Dim htmlForm As HtmlForm = New HtmlForm
            CurrentPage.Controls.Add(htmlForm)
            htmlForm.Controls.Add(NtGridView)

            ClearChildControls(DirectCast(NtGridView, GridView))

            CurrentPage.Response.Clear()
            CurrentPage.Response.Buffer = True


            CurrentPage.Response.AddHeader("Content-Disposition", "attachment; filename=" + reportName)
            CurrentPage.Response.ContentType = "application/vnd.ms-excel"
            CurrentPage.Response.ContentEncoding = System.Text.Encoding.UTF8
            CurrentPage.Response.Charset = ""
            CurrentPage.EnableViewState = False

            Using stringWriter As StringWriter = New StringWriter
                Dim htmlWriter As HtmlTextWriter = New HtmlTextWriter(stringWriter)
                htmlForm.RenderControl(htmlWriter)
                htmlWriter.Flush()

                CurrentPage.Response.Write(stringWriter.ToString())
                CurrentPage.Response.End()
            End Using

        End Sub
        Private Sub RecursiveClear(ByVal control As Control)

            For i As Integer = control.Controls.Count - 1 To 0 Step -1
                RecursiveClear(control.Controls(i))
            Next

            If TypeOf control Is Repeater Then
                ' TODO: handle repeater control displayed item when used with EntityDataSourceFilter control
                control.Parent.Controls.Remove(control)
                'If it is a LinkButton, convert it to a LiteralControl
            ElseIf TypeOf control Is LinkButton Then
                Dim literal As New LiteralControl()
                control.Parent.Controls.Add(literal)
                literal.Text = DirectCast(control, LinkButton).Text
                control.Parent.Controls.Remove(control)
                'We don't need a button in the excel sheet, so simply delete it
            ElseIf TypeOf control Is Button Then
                control.Parent.Controls.Remove(control)

            ElseIf TypeOf control Is Image Then
                If DirectCast(control, Image).Visible Then
                    control.Parent.Controls.Add(New LiteralControl("<span style='font-size:8px;'>o</span>"))
                End If
                control.Parent.Controls.Remove(control)
                'If it is a ListControl, copy the text to a new LiteralControl
            ElseIf TypeOf control Is ListControl Then
                Dim literal As New LiteralControl()
                control.Parent.Controls.Add(literal)
                Try
                    literal.Text = DirectCast(control, ListControl).SelectedItem.Text
                Catch
                End Try

                control.Parent.Controls.Remove(control)
            ElseIf TypeOf control Is AjaxControlToolkit.CalendarExtender Then
                control.Parent.Controls.Remove(control)
            ElseIf TypeOf control Is TextBox Then
                control.Parent.Controls.Remove(control)
            End If

            Return
        End Sub

        Protected Sub ClearChildControls(ByVal dg As GridView)
            For i As Integer = dg.Columns.Count - 1 To 0 Step -1
                If dg.Columns(i).[GetType]().Name = "ButtonColumn" OrElse dg.Columns(i).[GetType]().Name = "CheckBoxField" OrElse dg.Columns(i).[GetType]().Name = "CommandField" Then
                    dg.Columns(i).Visible = False
                End If

            Next

            RecursiveClear(dg)
        End Sub
    End Class
this works. However, all rows from the datasource are exported. say the initial number of rows where 54. I then filter a column, the number of rows in the gridview are now 29.

I then export to excel, and all 54 rows are exported. please advise me on where I am going wrong. I really need help here. thanks.