How to Export DataGridView Items to Excel VB.NET

by
Avatar of Sana Modi
Latest posts by Sana Modi (see all)

Last Updated on November 10, 2022 by Humera hallari

Before you can exporting data from DataGridViewto excel in VB.NET, you need to install Microsoft Office, especially Microsoft Excel. We need it to add some libraries from there. And here the source code how to export data from DataGridView to Excel Format.

1 Open Visual Studio, Create Project, 1 Form, 1 Buttons, and 1 DataGridView.
2 Paste this code in button 1

 Try
            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
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
            xlWorkSheet.Columns.AutoFit()
            For i = 0 To DataGridView1.RowCount - 2
                For j = 0 To DataGridView1.ColumnCount - 1
                    For k As Integer = 1 To DataGridView1.Columns.Count
                        xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                        xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                    Next
                Next
            Next
            Dim fName As String = "DataBuku"
            Using sfd As New SaveFileDialog
                sfd.Title = "Save As"
                sfd.OverwritePrompt = True
                sfd.FileName = fName
                sfd.DefaultExt = ".xlsx"
                sfd.Filter = "Excel Workbook(*.xlsx)|"
                sfd.AddExtension = True
                If sfd.ShowDialog() = DialogResult.OK Then
                    xlWorkSheet.SaveAs(sfd.FileName)
                    xlWorkBook.Close()
                    xlApp.Quit()
                    releaseObject(xlApp)
                    releaseObject(xlWorkBook)
                    releaseObject(xlWorkSheet)
                    MsgBox("Export Successfully !", MsgBoxStyle.Information, "== Notice ==")
                End If
            End Using
        Catch ex As Exception
            myconnection.Close()
            MsgBox("Error ! " & vbCrLf & "Error Code: " & ex.Message)
        End Try

3 Paste this code in range of Form1 Code.

  Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

4 Add Microsoft Excel Object Library via Add Reference menu in your Visual Studio.

And here for useful video.

Leave a Comment