CODEWITHSUNDEEP
vb.net
RedZ
RedZ

Reputation: 408

How to export DatagridView to Excel

I have a form with a button that exports the data within the datagridview to an excel workbook.

I have gotten it to work but it doesn't add the data. only the headers of each column. Also I am not sure how to tell it to ignore the ID column.

I have looked over this code like 100 times and i cannot find the fault

UPDATE: It seems to work when i enter a few rows of data but it ignores the first row of data in the datagridview.

Code:

Private Sub PictureBox2_Click(sender As Object, e As EventArgs) Handles 
PictureBox2.Click
    ExportToExcel()
End Sub

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

        'Loop through each row and read value from each column.
        For i As Integer = 0 To FleetDataGridView.Rows.Count - 2
            For j As Integer = 0 To FleetDataGridView.Columns.Count - 1
                ' Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check.
                If cellRowIndex = 1 Then
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = FleetDataGridView.Columns(j).HeaderText
                Else
                    worksheet.Cells(cellRowIndex, cellColumnIndex) = FleetDataGridView.Rows(i).Cells(j).Value.ToString()
                End If
                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

Upvotes: 1

Views: 514

Answers (1)

Sir Rufo
Sir Rufo

Reputation: 19106

Write the headers in a separate loop

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

    'Write headers
    For j As Integer = 0 To FleetDataGridView.Columns.Count - 1
        worksheet.Cells(cellRowIndex, cellColumnIndex) = FleetDataGridView.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 FleetDataGridView.Rows.Count - 2
        For j As Integer = 0 To FleetDataGridView.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) = FleetDataGridView.Rows(i).Cells(j).Value.ToString()
            cellColumnIndex += 1
        Next
        cellColumnIndex = 1
        cellRowIndex += 1
    Next

Upvotes: 2

Related Questions