vb.net export (DataGridView) to excel very slow.

Question

I have this code the works but is very slow. one line at a time update and if a large DataGridView it really does not work.

 If ComboBoxExport.Text = "Export to Excel" Then

            Dim rowsTotal, colsTotal As Short
            Dim I, j, iC As Short
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
            Dim xlApp As New Excel.Application

            Try
                Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
                Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
                xlApp.Visible = True
                rowsTotal = DataGridView1.RowCount - 1
                colsTotal = DataGridView1.Columns.Count - 1

                With excelWorksheet
                    .Cells.Select()
                    .Cells.Delete()
                    For iC = 0 To colsTotal
                        .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
                    Next


                    For I = 0 To rowsTotal - 1
                        For j = 0 To colsTotal
                            .Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value
                        Next j
                    Next I

                    .Rows("1:1").Font.FontStyle = "Bold"
                    .Rows("1:1").Font.Size = 10
                    .Cells.Columns.AutoFit()
                    .Cells.Select()
                    .Cells.EntireColumn.AutoFit()
                    .Cells(1, 1).Select()
                End With

            Catch ex As Exception
                MsgBox("Export to Excel Error: " & ex.Message)
            Finally
                'RELEASE ALLOACTED RESOURCES
                System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
                xlApp = Nothing

            End Try

        End If

Below Code does not work: Error "Unable to cast object of type System.Data.DataTable' to type System.Data.DataSet'."

 If ComboBoxExport.Text = "Export to Excel" Then

            Dim sfd As SaveFileDialog = New SaveFileDialog()

            sfd.Title = "Save File Dialog"
            sfd.InitialDirectory = "C:\"
            sfd.Filter = "Excel file (*.xlsx)|*.xlsx|All files (*.*)|*.*"
            sfd.FilterIndex = 1
            'sfd.RestoreDirectory = True

            Try
                'Dim dt = DirectCast(DataGridView1.DataSource, DataTable)
                Dim ds As DataSet = DirectCast(DataGridView1.DataSource, DataSet)

                'Export to Excel process
                Dim Excel As Object = CreateObject("Excel.Application")

                With Excel
                    .SheetsInNewWorkbook = 1
                    .Workbooks.Add()
                    .Worksheets(1).Select()
                    .WorkSheets(1).Name = "Sheet1"


                    Dim i As Integer = 1
                    For col = 0 To ds.Tables(0).Columns.Count - 1
                        .Cells(1, i).value = ds.Tables(0).Columns(col).ColumnName
                        .Cells(1, i).EntireRow.Font.Bold = True
                        i += 1
                    Next
                    i = 2
                    Dim j As Integer = 1
                    For col = 0 To ds.Tables(0).Columns.Count - 1
                        i = 2
                        For row = 0 To ds.Tables(0).Rows.Count - 1
                            .Cells(i, j).Value = ds.Tables(0).Rows(row).ItemArray(col)
                            i += 1
                        Next
                        j += 1
                    Next

                    .Application.DisplayAlerts = False
                    .ActiveCell.Worksheet.SaveAs(sfd.FileName)

                    .Workbooks.Close()
                    .Excel.Quit()
                End With
                'cn.Close()

            Catch ex As Exception
                MsgBox("Export to Excel Error: " & ex.Message)
            Finally
                'RELEASE ALLOACTED RESOURCES
                System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
                'xlApp = Nothing

            End Try


End If

Thoughts on the best way to rewrite this to work and be faster. thanks any help is greatly appreciated.

Answer 1

Hello @jim brown

I would suggest using spreadsheetlight available from NuGet and DocumentFormat.OpenXml. Both are free libraries.

Why?

Because these libraries don't use Excel automation but OpenXML for Excel which is many times faster than Excel automation.

Some helpers

After creating and instance of SLDocument you can (where doc is a SLDocument)

• AutoFit columns e.g. doc.AutoFitColumn("A")
• Create and apply styles via doc.CreateStyle which in turn allows you to do things like text alignment, bold, italics etc.

   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  
  

Extras

Public Function SheetNames(ByVal pFileName As String) As List(Of String)  
	Using doc = New SLDocument(pFileName)  
		Return doc.GetSheetNames(False)  
	End Using  
End Function  

Public Function AddNewSheet(ByVal pFileName As String, ByVal pSheetName As String) As Boolean  
	Using doc = New SLDocument(pFileName)  
		If Not (doc.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower() = pSheetName.ToLower())) Then  
			doc.AddWorksheet(pSheetName)  
			doc.Save()  
			Return True  
		Else  
			Return False  
		End If  
	End Using  
End Function