none
Fastest way to export a Datagridview (VB.NET 2015) to MS Excel (2007)

    Question

  • Hi. Firstly thanks for your time.
    I am searching the way fastest to export a datagridview control to an MS Excel document.
    I'm Use  usea a method but this is too slow because my datagridiview cointains many columns and rows to traver recursively.
    If i select the contain of the datagridview directly (with the properties multiselect=true and ClipBoarCopyMode=EnableWithAutoHeaderText) and paste this on a excel Worksheet the copy is soo fast. 
    Someone knows why?

    This is the method that i am using

    Thank's for you time, again.

     Public Function GridAExcel(ByVal grd As DataGridView)
            Dim fichero As New SaveFileDialog()
            fichero.Filter = "Excel (*.xls)|*.xls"
    
            Dim aplicacion As Microsoft.Office.Interop.Excel.Application
            Dim libros_trabajo As Microsoft.Office.Interop.Excel.Workbook
            Dim hoja_trabajo As Microsoft.Office.Interop.Excel.Worksheet
            aplicacion = New Microsoft.Office.Interop.Excel.Application()
            libros_trabajo = aplicacion.Workbooks.Add()
    
    
    
            Dim Visibles As Integer = 0
            For x = 0 To grd.Columns.Count - 1
                If grd.Columns(x).Visible = True Then
                    Visibles = Visibles + 1
                End If
            Next
            Dim NCol As Integer = grd.Columns.Count
            Dim NRow As Integer = grd.RowCount
    
    
            hoja_trabajo = DirectCast(libros_trabajo.Worksheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
            Dim Count = 1
            For i As Integer = 1 To NCol
                If grd.Columns(i - 1).Visible = True Then
                    hoja_trabajo.Cells.Item(1, Count) = grd.Columns(i - 1).Name
                    Count = Count + 1
                End If
    
            Next
    
            hoja_trabajo.Rows.Item(1).Font.Bold = 1
            hoja_trabajo.Rows.Item(1).HorizontalAlignment = 3
            hoja_trabajo.Columns.AutoFit()
    
            For i As Integer = 0 To grd.Rows.Count - 1
                Dim cant As Integer = 1
                For j As Integer = 0 To grd.Columns.Count - 1
                    If grd.Columns(j).Visible = True Then
                        hoja_trabajo.Cells(i + 2, cant) = grd.Rows(i).Cells(j).Value
                        cant = cant + 1
                    End If
                Next
            Next
            aplicacion.Application.Visible = True
    
            Return True
        End Function

    Tuesday, February 6, 2018 8:03 PM

Answers

  • Hi CM16,

    Here is the article about comparing the speed of importing data into Excel, you can take a look:

    https://www.codeproject.com/articles/26303/export-data-to-excel-much-faster

    In the basic method, copying the data takes a lot of time. Using the FileSteamWriter or the object pasting method, copying data is much faster as compared to the basic method.

    The following code is about objext past, you can take a look:

     Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            Dim sheetIndex As Integer
            Dim Ex As Object
            Dim Wb As Object
            Dim Ws As Object
            Ex = CreateObject("Excel.Application")
            Wb = Ex.workbooks.add
    
            ' Copy each DataTable as a new Sheet
    
            'On Error Resume Next
            Dim col, row As Integer
            ' Copy the DataTable to an object array
            Dim rawData(DataGridView1.Rows.Count, DataGridView1.Columns.Count - 1) As Object
    
            ' Copy the column names to the first row of the object array
    
            For col = 0 To DataGridView1.Columns.Count - 1
                rawData(0, col) = DataGridView1.Columns(col).HeaderText.ToUpper
    
            Next
    
            For col = 0 To DataGridView1.Columns.Count - 1
                For row = 0 To DataGridView1.Rows.Count - 1
                    rawData(row + 1, col) = DataGridView1.Rows(row).Cells(col).Value
    
                Next
            Next
            ' Calculate the final column letter
            Dim finalColLetter As String = String.Empty
                finalColLetter = ExcelColName(DataGridView1.Columns.Count) 'Generate Excel Column Name (Column ID)
    
    
                sheetIndex += 1
                Ws = Wb.Worksheets(sheetIndex)
                'Ws.name = "Test10"
                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, DataGridView1.Rows.Count + 1)
    
                Ws.Range(excelRange, Type.Missing).Value2 = rawData
                Ws = Nothing
    
    
                Wb.SaveAs("D:\TestField\Test10.xlsx", Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            Wb.Close(True, Type.Missing, Type.Missing)
            Wb = Nothing
            ' Release the Application object
            Ex.Quit()
            Ex = Nothing
            ' Collect the unreferenced objects
            GC.Collect()
            MsgBox("Exported Successfully.", MsgBoxStyle.Information)
        End Sub
    
        Public Function ExcelColName(ByVal Col As Integer) As String
            If Col < 0 And Col > 256 Then
                MsgBox("Invalid Argument", MsgBoxStyle.Critical)
                Return Nothing
                Exit Function
            End If
            Dim i As Int16
            Dim r As Int16
            Dim S As String
            If Col <= 26 Then
                S = Chr(Col + 64)
            Else
                r = Col Mod 26
                i = System.Math.Floor(Col / 26)
                If r = 0 Then
                    r = 26
                    i = i - 1
                End If
                S = Chr(i + 64) & Chr(r + 64)
            End If
            ExcelColName = S
        End Function

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 9:08 AM
    Moderator

All replies

  • Yea we have something on our website. 

    However, Karen has made a sticky in top of this forum which covers your question and she describes everything.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/5243f42d-1796-4359-b461-3fd5088da470/export-datagridview-unbound-to-excel?forum=vbgeneral


    Success Cor

    Tuesday, February 6, 2018 8:12 PM
  • As Cor has pointed you to my code sample there is one caveat, they only work for .xlsx not .xls. If you were using .xlsx then a simply example that is quick would be (taken from my code sample)

    Public Sub Export(ByVal pFileName As String, ByVal pDataTable As DataTable)
        Using doc As New SLDocument()
            doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, True)
            doc.SaveAs(pFileName)
        End Using
    End Sub

    The idea is that the method above imports a DataTable which is obtained from a language extension method (also included in the code sample) 

    Dim dt As DataTable = DataGridView1.GetDataTable()
    The entire process is very fast and if you want to do simple or complex styling like bolding the column headers or formatting dates that will not slow down the import into 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

    Tuesday, February 6, 2018 8:58 PM
    Moderator
  • Hi CM16,

    Here is the article about comparing the speed of importing data into Excel, you can take a look:

    https://www.codeproject.com/articles/26303/export-data-to-excel-much-faster

    In the basic method, copying the data takes a lot of time. Using the FileSteamWriter or the object pasting method, copying data is much faster as compared to the basic method.

    The following code is about objext past, you can take a look:

     Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    
            Dim sheetIndex As Integer
            Dim Ex As Object
            Dim Wb As Object
            Dim Ws As Object
            Ex = CreateObject("Excel.Application")
            Wb = Ex.workbooks.add
    
            ' Copy each DataTable as a new Sheet
    
            'On Error Resume Next
            Dim col, row As Integer
            ' Copy the DataTable to an object array
            Dim rawData(DataGridView1.Rows.Count, DataGridView1.Columns.Count - 1) As Object
    
            ' Copy the column names to the first row of the object array
    
            For col = 0 To DataGridView1.Columns.Count - 1
                rawData(0, col) = DataGridView1.Columns(col).HeaderText.ToUpper
    
            Next
    
            For col = 0 To DataGridView1.Columns.Count - 1
                For row = 0 To DataGridView1.Rows.Count - 1
                    rawData(row + 1, col) = DataGridView1.Rows(row).Cells(col).Value
    
                Next
            Next
            ' Calculate the final column letter
            Dim finalColLetter As String = String.Empty
                finalColLetter = ExcelColName(DataGridView1.Columns.Count) 'Generate Excel Column Name (Column ID)
    
    
                sheetIndex += 1
                Ws = Wb.Worksheets(sheetIndex)
                'Ws.name = "Test10"
                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, DataGridView1.Rows.Count + 1)
    
                Ws.Range(excelRange, Type.Missing).Value2 = rawData
                Ws = Nothing
    
    
                Wb.SaveAs("D:\TestField\Test10.xlsx", Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            Wb.Close(True, Type.Missing, Type.Missing)
            Wb = Nothing
            ' Release the Application object
            Ex.Quit()
            Ex = Nothing
            ' Collect the unreferenced objects
            GC.Collect()
            MsgBox("Exported Successfully.", MsgBoxStyle.Information)
        End Sub
    
        Public Function ExcelColName(ByVal Col As Integer) As String
            If Col < 0 And Col > 256 Then
                MsgBox("Invalid Argument", MsgBoxStyle.Critical)
                Return Nothing
                Exit Function
            End If
            Dim i As Int16
            Dim r As Int16
            Dim S As String
            If Col <= 26 Then
                S = Chr(Col + 64)
            Else
                r = Col Mod 26
                i = System.Math.Floor(Col / 26)
                If r = 0 Then
                    r = 26
                    i = i - 1
                End If
                S = Chr(i + 64) & Chr(r + 64)
            End If
            ExcelColName = S
        End Function

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 9:08 AM
    Moderator
  • Thanks to everybody for your help. I have solved my trouble.
    i paste the code in case that this helps to somebody.

    Dim sheetIndex As Integer
                Dim Ex As Object
                Dim Wb As Object
                Dim Ws As Object
                Ex = CreateObject("Excel.Application")
                Wb = Ex.workbooks.add
    
                Dim col, row As Integer
                Dim rawData(tb_registros.Rows.Count, tb_registros.Columns.Count - 1) As Object
    
    
                For col = 0 To tb_registros.Columns.Count - 1
                    rawData(0, col) = tb_registros.Columns(col).ColumnName.ToUpper
                Next
    
    
                Dim NroCol As Integer = 0
                For col = 0 To tb_registros.Columns.Count - 1
                    fila = 0
                    For row = 0 To tb_registros.Rows.Count - 1
                        rawData(row + 1, col) = tb_registros.Rows(row).ItemArray(col)
                        fila = fila + 1
                    Next
                    BackgroundWorker1.ReportProgress(NroCol * 100 / tb_registros.Columns.Count)
                    NroCol = NroCol + 1
    
                    columna = columna + 1
                Next
    
                Dim finalColLetter As String = String.Empty
                finalColLetter = ExcelColName(tb_registros.Columns.Count)
    
    
                sheetIndex = sheetIndex + 1
                Ws = Wb.Worksheets(sheetIndex)
                'Ws.name = lb_vista.Text
                Dim excelRange As String = String.Format("A1:{0}{1}", finalColLetter, tb_registros.Rows.Count + 1)
    
                Ws.Range(excelRange, Type.Missing).Value2 = rawData
                Ws = Nothing
    
    
                Ex.Application.Visible = True
                GC.Collect()

    Thursday, February 8, 2018 11:43 AM