Click here to Skip to main content
13,259,779 members (46,940 online)
Click here to Skip to main content
Add your own
alternative version

Stats

40.5K views
4 downloads
14 bookmarked
Posted 8 Oct 2012

Export DataGridView to Excel

, 11 Oct 2012
Rate this:
Please Sign up or sign in to vote.
How to export a datagridview to Excel
This is an old version of the currently published tip/trick.

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
        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)
                'TODO take care of false (or exception)
            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)

Share

About the Author

Michael Rosqvist
Web Developer
Sweden Sweden
No Biography provided

You may also be interested in...

Comments and Discussions

Discussions on this specific version of this article. Add your comments on how to improve this article here. These comments will not be visible on the final published version of this article.
 
GeneralMy vote of 5 Pin
RossMcPhee15-May-13 2:41
memberRossMcPhee15-May-13 2:41 
GeneralMy vote of 1 Pin
L3AP19-Feb-13 21:51
memberL3AP19-Feb-13 21:51 
QuestionHelp! Pin
L3AP19-Feb-13 21:13
memberL3AP19-Feb-13 21:13 
AnswerRe: Help! Pin
Michael Rosqvist19-Feb-13 23:20
memberMichael Rosqvist19-Feb-13 23:20 
QuestionNice Tutorial Pin
Vanlalruata4-Jan-13 4:52
memberVanlalruata4-Jan-13 4:52 
AnswerRe: Nice Tutorial Pin
Michael Rosqvist19-Feb-13 23:18
memberMichael Rosqvist19-Feb-13 23:18 
Questionplease help Pin
spacer21-Nov-12 1:46
memberspacer21-Nov-12 1:46 
AnswerRe: please help Pin
Michael Rosqvist21-Nov-12 4:16
memberMichael Rosqvist21-Nov-12 4: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 | Terms of Use | Mobile
Web02 | 2.8.171114.1 | Last Updated 11 Oct 2012
Article Copyright 2012 by Michael Rosqvist
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid