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

Question

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.

Answer 1

The simplest way to do this is to use the textfieldparser class in microsoft.visualbasic.fileio (msdn link). The psuedocode would be:

create a textfieldparser object, set the file to open (*.csv), and decoding.

write the column headers

loop through the datagridview or its datsource print to the text file

User can now open the file in excel.

That is my quick answer, I will look to see if there is a better way to do it.

Answer 2

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