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 DataGridView
s.
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:
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:
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:
For rowcount = 0 To dgv.Rows.Count - 1
colcount = 0
For Each s In ColNames
colcount += 1
xlWorkSheet.Cells(rowcount + 2, colcount) = dgv.Rows(rowcount).Cells(s).Value
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