Export more than 2000 row of data from a data gridview to excel file in vb.net

Question

I did succeed to export data from a DataGridView to excel file by using a loop and writing cell by cell, but the problem is i have more than 2000 rows so the exporting process takes a lot of time. My question is : Is there anything to change so i can minimize the exportation time ? Thanks

'exporter
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    xlApp = New Excel.Application
    xlBook = xlApp.Workbooks.Open(Filename:=Path.Combine(Application.StartupPath, "EMP_.xlsx"), IgnoreReadOnlyRecommended:=True, ReadOnly:=False, Editable:=True)
    xlSheet = xlBook.Worksheets(1)
    If DataGridView1.DataSource IsNot Nothing Then
        Dim i, j As Integer
        For i = 1 To DataGridView1.RowCount - 1
            For j = 1 To DataGridView1.ColumnCount
                xlSheet.Cells(i + 1, j) = DataGridView1.Rows(i - 1).Cells(j - 1).Value
            Next
        Next
        xlApp.Visible = True
        xlApp.UserControl = True
        xlApp.Quit()
        xlApp = Nothing
    Else
        MsgBox("Le tableau est vide")
    End If
End Sub

[You could turn off screenupdating, automatic calculations etc](https://stackoverflow.com/a/47092175/1115360) - that's for VBA in Excel, but would still apply to this question. — Andrew Morton, Apr 15 '20 at 13:18

score 0 · Accepted Answer · answered Apr 15 '20 at 13:17

Not sure if this is something might be open too. By chance if you could load your DataGridView via setting the DataSource of the DataGridView to a DataTable then the following would be an option.

Using SpreadSheetLight (installed via NuGet) you can use a function like the sample below. Pass in the following, full path and file name, sheet name, the DataTable from the DataGridView e.g. Dim dt As DataTable = CType(DataGridView1.DataSource,DataTable) and the last argument if True included column headers (DataColumn names) or False exclude column names. SpreadSheetLight home page.

Public Sub SimpleExportRaw(
    pFileName As String,
    pSheetName As String,
    pDataTable As DataTable,
    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

Thanks for your answer it was very helpful but i am wondering if i can do it row per row that should minimize the exportation time to the half without any external library — Charaf eddine, Apr 16 '20 at 13:37
Row by row is always slower than all rows. Note the SpreadSheetLight does not use Excel automation, instead uses Open Office XML for Excel. Also note that there is really no downside to using an external library. — Karen Payne, Apr 16 '20 at 17:29

Export more than 2000 row of data from a data gridview to excel file in vb.net

1 Answers1