How to export a DataGridView to Excel format in VB

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

I tested it and its work for me.

Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    Try

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlApp.Application.DisplayAlerts = False
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets.Add()
        xlWorkSheet.Name = "MysqlSheet"

        For i = 0 To Form2.DataGridView2.RowCount - 1
            For j = 0 To Form2.DataGridView2.ColumnCount - 1
                For k As Integer = 1 To Form2.DataGridView2.Columns.Count
                    xlWorkSheet.Cells(1, k) = Form2.DataGridView2.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = Form2.DataGridView2(j, i).Value
                Next
            Next
        Next

        xlWorkSheet.SaveAs("c:\")  'Where u want to save
        xlWorkBook.Close()
        xlApp.Quit()




    Catch ex As Exception
        MsgBox(ex.Message)
    Finally

    End Try

Answer 2

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click DATAGRIDVIEW_TO_EXCEL((DataGridView1)) ' PARAMETER: YOUR DATAGRIDVIEW End Sub

Private Sub DATAGRIDVIEW_TO_EXCEL(ByVal DGV As DataGridView) Try Dim DTB = New DataTable, RWS As Integer, CLS As Integer

    For CLS = 0 To DGV.ColumnCount - 1 ' COLUMNS OF DTB
        DTB.Columns.Add(DGV.Columns(CLS).Name.ToString)
    Next

    Dim DRW As DataRow

    For RWS = 0 To DGV.Rows.Count - 1 ' FILL DTB WITH DATAGRIDVIEW
        DRW = DTB.NewRow

        For CLS = 0 To DGV.ColumnCount - 1
            Try
                DRW(DTB.Columns(CLS).ColumnName.ToString) = DGV.Rows(RWS).Cells(CLS).Value.ToString
            Catch ex As Exception

            End Try
        Next

        DTB.Rows.Add(DRW)
    Next

    DTB.AcceptChanges()

    Dim DST As New DataSet
    DST.Tables.Add(DTB)
    Dim FLE As String = "" ' PATH AND FILE NAME WHERE THE XML WIL BE CREATED (EXEMPLE: C:\REPS\XML.xml)
    DTB.WriteXml(FLE)
    Dim EXL As String = "" ' PATH OF/ EXCEL.EXE IN YOUR MICROSOFT OFFICE
    Shell(Chr(34) & EXL & Chr(34) & " " & Chr(34) & FLE & Chr(34), vbNormalFocus) ' OPEN XML WITH EXCEL

Catch ex As Exception
    MsgBox(ex.ToString)
End Try

End Sub

Answer 3

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 4

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