Click here to Skip to main content
15,867,141 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick

Export DataGridView to Excel

Rate me:
Please Sign up or sign in to vote.
4.00/5 (4 votes)
22 Nov 2012CPOL 54.1K   5   15   8
How to export a 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:

VB
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.

VB
Import Office Interop

I then created  a function : Export

VB
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:

VB
'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:

VB.NET
'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:

VB
'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

VB.NET
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

License

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


Written By
Web Developer
Sweden Sweden
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
RAMcPh15-May-13 1:41
RAMcPh15-May-13 1:41 
GeneralMy vote of 1 Pin
L3AP19-Feb-13 20:51
L3AP19-Feb-13 20:51 
QuestionHelp! Pin
L3AP19-Feb-13 20:13
L3AP19-Feb-13 20:13 
AnswerRe: Help! Pin
Michael Rosqvist19-Feb-13 22:20
Michael Rosqvist19-Feb-13 22:20 
QuestionNice Tutorial Pin
Vanlalruata_Hnamte4-Jan-13 3:52
professionalVanlalruata_Hnamte4-Jan-13 3:52 
AnswerRe: Nice Tutorial Pin
Michael Rosqvist19-Feb-13 22:18
Michael Rosqvist19-Feb-13 22:18 
Questionplease help Pin
spacer21-Nov-12 0:46
spacer21-Nov-12 0:46 
AnswerRe: please help Pin
Michael Rosqvist21-Nov-12 3:16
Michael 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.