How can I export the data from datagrid to Excel using Visual Basic?

[Johnny Wong]

Hi, I am new to visual basic.
In my vb system, there is a dynamic datagrid to represent different reocrds in our database. Today I am gonna export the data to Excel file. I found most examples code in Google but all didn't work. Does anyone can help me? I am using visual basic 2010. I dont know how can get the value of the cells in the datagrid and the number of rows of the datagrid. Thanks everyone!

[NeoPa]

Access can export its objects directly into Excel format. There doesn't appear to be any reason to know what rows and columns are included from your question.

[marcellus7]

A simple way would be exporting it to a tab-delimited text file, and just importing that into excel. Here's some code that will export to tab or csv depending on parameters.

   Sub DataTable2Tab(ByVal table As System.Data.DataTable, ByVal filename As String, _
    ByVal sepChar As String)
        Dim writer As System.IO.StreamWriter
        Try
            writer = New System.IO.StreamWriter(filename)
 
            ' first write a line with the columns name
            Dim sep As String = ""
            Dim builder As New System.Text.StringBuilder
            For Each col As DataColumn In table.Columns
                builder.Append(sep).Append(col.ColumnName)
                sep = sepChar
            Next
            writer.WriteLine(builder.ToString())
 
            ' then write all the rows
            For Each row As DataRow In table.Rows
                sep = ""
                builder = New System.Text.StringBuilder
 
                For Each col As DataColumn In table.Columns
                    builder.Append(sep).Append(row(col.ColumnName))
                    sep = sepChar
                Next
                writer.WriteLine(builder.ToString())
            Next
        Finally
            If Not writer Is Nothing Then writer.Close()
        End Try
    End Sub
 

You can call it like this:

DataTable2Tab(finalTable, filePath, vbTab)