Excel – How to export a DataGridView to Excel format in VB.NET

datagridexceloledbvb.net

I'm using OLE to connect to a database using VB.NET, and show the results in a DataGridView.
I want to export the data that is in the DataGridView to an Excel format file, i.e., the user can save the content of the DataGridView as MS Excel file.

Best Solution

I found that copyfromrecordset is the fastest way.

Dim xlApp As New Excel.Application
    Dim xlWBook As Excel.Workbook = xlApp.Workbooks.Add
    Dim XlSheet As Excel.Worksheet = CType(xlWBook.Worksheets("Sheet1"), Excel.Worksheet)
    With XlSheet
         'insert column names
        For i = 2 To dt.Columns.Count - 1
            .Cells(1, i).value = dt.Columns(i - 1).ColumnName
        Next
        'insert the actual data
        .Range("A2").CopyFromRecordset(datset)

    End With