How to export DataGridView to Excel format in VB.NET

Question

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

Answer 1

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

Answer 2

The easiest way to do this is to use the textfieldparser class in microsoft.visualbasic.fileio ( msdn link ). The pseudo code will be:

textfieldparser, (*.csv) .

datagridview datsource

excel.

, , .

Answer 3

Private Sub Button1_Click ( ByVal As System.Object, ByVal e As System.EventArgs) Button1.Click   DATAGRIDVIEW_TO_EXCEL ((DataGridView1)) ": End Sub

Sub DATAGRIDVIEW_TO_EXCEL (ByVal DGV As DataGridView)          Dim DTB = 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

Sub

Answer 4

.

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