[RESOLVED] Gridview Export to Excel

[Nitesh]

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:

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.

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

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.

[davebat]

not sure what GridViewExcelExporter is, but I assume this exports the whole datasource the gridview is bound to, rather than the filtered one used by the gridview.

[Nitesh]

Hi davebat,

you are right. It exports the whole datasource. I need the filtered one but I'm not sure how to do it

[Nitesh]

Ok I've managed something. I'm not sure if it's the best way. Gary I would really appreciate your input here.

The example project I was working off they rebound the gridview like in my first post, therefore they got all the results.

What I've done is I get my gridviews sqlDatasource. Then I get it's SelectCommand. Then I call my GetFilterCommand function and append it's contents to the SelectCommand.

Then I set the Gridviews DataSourceID to the New SQLdatasource and rebind. then call my export class and that did the trick.

            Dim sqlds As SqlDataSource = Me.GetDataSource

            Dim FilterCommand As String = GetFilterCommand()

            If (sqlds.SelectCommand.Contains(FilterCommand)) Then
                Return
            End If

            If (sqlds.SelectCommand.Contains("WHERE")) Then
                sqlds.SelectCommand += " AND " & FilterCommand
            Else
                sqlds.SelectCommand += " WHERE " & FilterCommand
            End If
           
            Me.DataSourceID = sqlds.ID
            Me.DataBind()