VB.net DataGridView to Excel

Question

I’m using VB.net on Microsoft visual studio 2017, to create a little App and I’m having a problem with the code that I’m using to export my Datagridview to excel. It exports everything but the last row of my data. Any idea how I can fix this?

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports Microsoft.Office.Interop
Imports System.IO

Private Sub ExportToExcel()
        ' Creating a Excel object.
        Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
        Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
        Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing

        Try

            worksheet = workbook.ActiveSheet

            worksheet.Name = "ExportedFromDatGrid"

            Dim cellRowIndex As Integer = 1
            Dim cellColumnIndex As Integer = 1

            'Write headers
            For j As Integer = 0 To DataGridView_Kontakte.Columns.Count - 2
                worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView_Kontakte.Columns(j).HeaderText
                cellColumnIndex += 1
            Next
            cellColumnIndex = 1
            cellRowIndex += 1

            'Loop through each row and read value from each column.
            For i As Integer = 0 To DataGridView_Kontakte.Rows.Count - 2
                For j As Integer = 0 To DataGridView_Kontakte.Columns.Count - 1
                    ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = DataGridView_Kontakte.Rows(i).Cells(j).Value.ToString()
                    cellColumnIndex += 1
                Next
                cellColumnIndex = 1
                cellRowIndex += 1
            Next

            'Getting the location and file name of the excel to save from user.
            Dim saveDialog As New SaveFileDialog()
            saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"
            saveDialog.FilterIndex = 2

            If saveDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                workbook.SaveAs(saveDialog.FileName)
                MessageBox.Show("Export Successful")
            End If
        Catch ex As System.Exception
            MessageBox.Show(ex.Message)
        Finally
            excel.Quit()
            workbook = Nothing
            excel = Nothing
        End Try

    End Sub

Answer 1

You can try my function to export in EXCEL

Sub ExportExcel(ByVal obj As Object)
    Dim rowsTotal, colsTotal As Short
    Dim I, j, iC As Short
    System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
    Dim xlApp As New Excel.Application
    Try
        Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
        Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
        xlApp.Visible = True

        rowsTotal = obj.RowCount
        colsTotal = obj.Columns.Count - 1
        With excelWorksheet
            .Cells.Select()
            .Cells.Delete()
            For iC = 0 To colsTotal
                .Cells(1, iC + 1).Value = obj.Columns(iC).HeaderText
            Next
            For I = 0 To rowsTotal - 1
                For j = 0 To colsTotal
                    .Cells(I + 2, j + 1).value = obj.Rows(I).Cells(j).Value
                Next j
            Next I
            .Rows("1:1").Font.FontStyle = "Bold"
            .Rows("1:1").Font.Size = 12

            .Cells.Columns.AutoFit()
            .Cells.Select()
            .Cells.EntireColumn.AutoFit()
            .Cells(1, 1).Select()
        End With
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
        'RELEASE ALLOACTED RESOURCES
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
        xlApp = Nothing
    End Try
End Sub

Answer 2

From Datgridview to Excel? It should be done like this.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String

        connectionString = "data source=servername;" & _
        "initial catalog=databasename;user id=username;password=password;"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click


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

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

    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
End Class

Please see the link below for all relevant information, and several relater links as well.

http://vb.net-informations.com/excel-2007/vb.net_export_from_datagridview_to_excel.htm