Click here to Skip to main content
Click here to Skip to main content

Export DataGridView to Excel

, 22 Nov 2012
Rate this:
Please Sign up or sign in to vote.
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:

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

License

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

Comments and Discussions

 
GeneralMy vote of 5 PinmemberRossMcPhee15-May-13 1:41 
GeneralMy vote of 1 PinmemberL3AP19-Feb-13 20:51 
QuestionHelp! PinmemberL3AP19-Feb-13 20:13 
AnswerRe: Help! PinmemberMichael Rosqvist19-Feb-13 22:20 
QuestionNice Tutorial PinmemberVanlalruata4-Jan-13 3:52 
AnswerRe: Nice Tutorial PinmemberMichael Rosqvist19-Feb-13 22:18 
Questionplease help Pinmemberspacer21-Nov-12 0:46 
AnswerRe: please help PinmemberMichael Rosqvist21-Nov-12 3:16 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 22 Nov 2012
Article Copyright 2012 by Michael Rosqvist
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid