Export DataGridView to Excel

Introduction

This is how to export a DataGridView to Excel.

Background

I had an application with an 8-tab window. Each window contained 1-3 DataGridViews.

Order from boss: Add export to Excel functionality!

The code

So I created an ExcelExporter class:

Option Explicit On
Option Strict On
Imports x = Microsoft.Office.Interop.Excel
Friend Class ExcelExporter

I recommend Option Strict On.

Option Strict Off (which unfortunately is the default) = Option Slow On (Dan Appleman). Do a disassembly and you will see.

Option Strict On also gives you intellisense.

Import Office Interop

I then created  a function : Export

Friend Function Export(ByRef dgv As DataGridView, ByVal Path As String) As Boolean

It takes a DataGridView and a path where to save the Excel file.

Since a DataGridView sometimes allows a user to rearrange the columns or allows the programmer to hide columns you have to get the columns by display index:

'get all visible columns in display index order
Dim ColNames As List(Of String) = (From col As DataGridViewColumn _
                                   In dgv.Columns.Cast(Of DataGridViewColumn)() _
                                   Where (col.Visible = True) _
                                   Order By col.DisplayIndex _
                                   Select col.Name).ToList

After that, if you want the column headers:

'get the headers
Dim colcount = 0
For Each s In ColNames
    colcount += 1
    xlWorkSheet.Cells(1, colcount) = dgv.Columns.Item(s).HeaderText
Next

And then, the rows and all their columns:

'get the values
For rowcount = 0 To dgv.Rows.Count - 1  'for each row
    colcount = 0
    For Each s In ColNames 'for each column
        colcount += 1
        xlWorkSheet.Cells(rowcount + 2, colcount) = dgv.Rows(rowcount).Cells(s).Value
        'xlWorkSheet.Cells(rowcount + 2, colcount) = dgv.Rows(rowcount).Cells(s).FormattedValue
    Next
Next

If you want the unformatted value: use Value

If you want the formatted value: use FormattedValue.

Using the code

Private Sub dgData_MouseClick(ByVal sender As Object, ByVal e As MouseEventArgs) _
           Handles dgData0.MouseClick, dgData1.MouseClick, _
                   dgData3.MouseClick, dgData4.MouseClick
    If e.Button = MouseButtons.Right Then
        Dim dgData As DataGridView = DirectCast(sender, DataGridView)
        With SaveExcelFileDialog
            .Filter = "Excel|*.xlsx"
            .Title = "Save griddata in Excel"
            If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                Dim o As New ExcelExporter
                Dim b = o.Export(dgData, .FileName)
            End If
            .Dispose()
        End With
    End If
End Sub