Export Gridview to CSV in excel file / VB.NET

Question

Following examples found on the web and even stackoverflow I filled the button btnGerarCSV thus.

Protected Sub btnGerarCSV_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnGerarCSV.Click
     Dim sb As New StringBuilder
     Dim gv As New GridView
            Try
                Page.Response.Clear()
                Page.Response.Buffer = True
                Page.Response.AddHeader("content-disposition", "attachment;Pedido_" & Session("ddlPedido") & ".csv")
                Page.Response.Charset = ""
                Page.Response.ContentType = "application/vnd.ms-excel"                

                grdPedido.DataSource = Session("dtPedidosColetados")

                For i As Integer = 0 To grdPedido.Columns.Count - 1
                    'Adiciona separador por vírgula
                    sb.Append(grdPedido.Columns(i).HeaderText + ","c)
                Next
                'Adiciona nova linha
                sb.Append(vbCr & vbLf)

                For i As Integer = 0 To grdPedido.Rows.Count - 1
                    For j As Integer = 0 To grdPedido.Columns.Count - 1
                        'Adiciona separador por  vírgula
                        sb.Append(grdPedido.Rows(i).Cells(j).Text + ","c)
                    Next
                    'Adiciona nova linha
                    sb.Append(vbCr & vbLf)
                Next

                Response.Output.Write(sb.ToString())
                Response.Flush()
                Response.End()

            Catch ef As ThreadAbortException
                Exit Try
            Catch ex As Exception

            End Try

        End Sub

My page works as follows: The user chooses Start Date and End Date and selects the search button. Then the grdPedido is filled and displayed on screen. After that two buttons are displayed, one to export to excel in spreadsheet, which is working properly and one button to CSV.

But what happens to me is that when I get to the line For i As Integer = 0 To grdPedido.Columns.Count - 1 the number of columns is = 0, even the grid returning the query normally.

I’ve changed the code many ways, but I always fall into the same problem.

Answer 1

After much fighting with this code, I found a way to solve the problem of the columns always return 0

I inserted a Directcast to convert the gv.DataSource in one object and then use another Directcast to transform the object into a DataTable

For i As Integer = 0 To DirectCast(DirectCast(gv.DataSource, System.Object), System.Data.DataTable).Columns.Count - 1
                'Adiciona separador por vírgula
                'sw.Write(gv.Columns(i).HeaderText + ","c) Como estava antes
                sw.Write(DirectCast(DirectCast(gv.DataSource, System.Object), System.Data.DataTable).Columns(i).ColumnName + ","c)
            Next
            'Adiciona nova linha
            sw.Write(vbCr & vbLf)

            For i As Integer = 0 To gv.Rows.Count - 1
                'For j As Integer = 0 To gv.Columns.Count - 1 Como estava antes
                For j As Integer = 0 To DirectCast(DirectCast(gv.DataSource, System.Object), System.Data.DataTable).Columns.Count - 1
                    'Adiciona separador por  vírgula
                    sw.Write(gv.Rows(i).Cells(j).Text + ","c)
                Next
                'Adiciona nova linha
                sw.Write(vbCr & vbLf)
            Next

I don’t know if it’s the simplest way, but it’s the only way I can get back the columns.