sticky
Export DataGridView unbound to Excel

    General discussion

  • There are countless questions and replies on how to export a DataGridView that was loaded without a DataSource to an Excel file. The majority use Excel automation which when not careful one can have issues releasing used objects. Also the common solutions have a developer iterate each row and cell and insert into an Excel WorkSheet. This all leads up to a lot of code and error prone if Excel is not installed.

    Another solution that still requires a data source (and that can easily be remedied) is from Beth Massi on this page. I like Beth's idea yet one needs to understand the underlying xml structure to create an xml file which when opened by double clicking in Windows Explorer will open in Excel.

    So what I did was create a language extension method that converts a bound or unBound DataGridView to a DataTable.

    There is still iterating data but much better than iterating rows/cells of a DataGridView in tangent with writing to Excel.

    Before going any farther here is the code sample DataGridView unbound to either Excel or text file.

    The extension method

    <Runtime.CompilerServices.Extension>
    Public Function GetDataTable(ByVal pDataGridView As DataGridView, Optional ByVal pColumnNames As Boolean = True) As DataTable
        Dim dt As DataTable = New DataTable()
        For Each column As DataGridViewColumn In pDataGridView.Columns
            If column.Visible Then
                If pColumnNames Then
                    dt.Columns.Add(New DataColumn() With {.ColumnName = column.Name})
                Else
                    dt.Columns.Add()
                End If
            End If
        Next
    
        Dim cellValues(pDataGridView.Columns.Count - 1) As Object
    
        For Each row As DataGridViewRow In pDataGridView.Rows
            If Not row.IsNewRow Then
                For i As Integer = 0 To row.Cells.Count - 1
                    cellValues(i) = row.Cells(i).Value
                Next
                dt.Rows.Add(cellValues)
            End If
        Next
    
        Return dt
    
    End Function

    We can the use SpreadSheetLight library to import the DataTable to Excel.

        Public Sub SimpleExportRaw(
        ByVal pFileName As String,
        ByVal pSheetName As String,
        ByVal pDataTable As DataTable,
        ByVal pColumnHeaders As Boolean)
    
            Using doc As New SLDocument()
                doc.SelectWorksheet(pSheetName)
                doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
                doc.SaveAs(pFileName)
            End Using
    
        End Sub

    The code sample does formatting of cells too by creating a style and applying the style to the first row in this case.

    Included in the solution

    Note I've made this a sticky for a limited time ending 03/1/2018


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, December 04, 2017 12:05 AM
    Moderator

All replies

  • I just finished a revised version of this solution in C# to include Gembox Spreadhsheet library. Will include this into the VB.NET code sample this weekend.

    In short, Gembox spread sheet library is very powerful and with that comes like any good library time to learn. On the upside, they have great documentation and good code samples on their web site.

    What this does is provide yet another option for working with Excel.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 08, 2017 10:29 PM
    Moderator