Click here to Skip to main content
13,594,513 members
Click here to Skip to main content
Add your own
alternative version


15 bookmarked
Posted 8 Oct 2012
Licenced CPOL

Export DataGridView to Excel

, 22 Nov 2012
Rate this:
Please Sign up or sign in to vote.
How to export a DataGridView to Excel.


This is how to export a DataGridView to Excel.


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

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

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
        End With
    End If
End Sub


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Michael Rosqvist
Web Developer
Sweden Sweden
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
RossMcPhee15-May-13 1:41
memberRossMcPhee15-May-13 1:41 
GeneralMy vote of 1 Pin
L3AP19-Feb-13 20:51
memberL3AP19-Feb-13 20:51 
QuestionHelp! Pin
L3AP19-Feb-13 20:13
memberL3AP19-Feb-13 20:13 
AnswerRe: Help! Pin
Michael Rosqvist19-Feb-13 22:20
memberMichael Rosqvist19-Feb-13 22:20 
QuestionNice Tutorial Pin
Vanlalruata4-Jan-13 3:52
memberVanlalruata4-Jan-13 3:52 
AnswerRe: Nice Tutorial Pin
Michael Rosqvist19-Feb-13 22:18
memberMichael Rosqvist19-Feb-13 22:18 
Questionplease help Pin
spacer21-Nov-12 0:46
memberspacer21-Nov-12 0:46 
AnswerRe: please help Pin
Michael Rosqvist21-Nov-12 3:16
memberMichael Rosqvist21-Nov-12 3:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04-2016 | 2.8.180621.3 | Last Updated 22 Nov 2012
Article Copyright 2012 by Michael Rosqvist
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid