Results 1 to 18 of 18

Thread: Exporting to Excel faster

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,965

    Exporting to Excel faster

    I am currently exporting from a DataGridView to Excel by looping through all the rows & columns in the DGV, which is very slow when you have a lot of rows. Here is the code I currently use:

    Code:
            For i = 0 To Me.DataGridView1.Rows.Count - 1
                For j = 0 To Me.DataGridView1.Columns.Count - 1
                    xlWorkSheet.Cells(i + 2, j + 1) = Me.DataGridView1(j, i).Value.ToString().Trim
                Next
            Next
    Is it possible to export an entire row to excel, instead of having to do it one cell at a time? I'm just looking for ways to make the process faster. Thanks for any ideas...

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Exporting to Excel faster

    You can probably improve performance by getting rid of the .ToString.Trim portion. I wrote something to dump data into Excel that is similar to what you have, and the performance is reasonable for a fairly high number of records. Unfortunately, what I wrote (there's a class in the CodeBank) is quite similar to what you wrote, so it wouldn't be a new technique. I was writing either a datatable or a datareader into an Excel spreadsheet. Whether those are faster than writing from a DataGridView I do not know. However, I just wrote the row.Item, rather than converting it in any way.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,965

    Re: Exporting to Excel faster

    I see that the Excel object has a .Rows property, but I don't know what to feed it. I tried this code:

    Code:
           Dim row As DataGridViewRow
            For i = 0 To Me.DataGridView1.Rows.Count - 1
                row = Me.DataGridView1.Rows(i)
                xlWorkSheet.Rows(i) = row
            Next
    but it errors out. Any ideas?

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Exporting to Excel faster

    I seem to remember that you could query an Excel spreadsheet using ADO.NET. I wonder if you could run an INSERT query against an Excel spreadsheet.

    Since a row in Excel is a different concept from a row in a datagrid, I would not expect that you could copy one to the next directly. If something like that were possible, I would expect that it would have to do with the Range object in Excel, much the way you can select a range for copying and pasting.
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,965

    Re: Exporting to Excel faster

    What you say makes sense. Unfortunately my knowledge of Excel leaves much to be desired. I will keep searching...

  6. #6
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: Exporting to Excel faster

    This seems that you are looking for (follow it) --->
    http://www.codeproject.com/KB/vb/Sen...ware_Eng_.aspx

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,965

    Re: Exporting to Excel faster

    jggtz ... I actually found that page too in my searching. As near as I can tell he is still exporting the data to Excel one cell at a time, just from a DataSet instead of a DataGridView.

  8. #8
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: Exporting to Excel faster

    Here's a method we use at our shop, hope it helps:
    Code:
        Public Sub ExportToExcel(ByVal ds As DataSet, ByVal sPath As String) 
    
            Dim oConnection As System.Data.OleDb.OleDbConnection
            Try
    
    
                If System.IO.File.Exists(sPath) Then
                    System.IO.File.Delete(sPath)
                End If
    
                Dim sConnectionString As String
                sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & sPath & _
                    ";Extended Properties=Excel 8.0;"
                oConnection = New System.Data.OleDb.OleDbConnection(sConnectionString)
                oConnection.Open()
    
                Dim oCommand As New System.Data.OleDb.OleDbCommand
                oCommand.Connection = oConnection
    
                For Each dtTable As DataTable In ds.Tables
                    Dim dtInsert As New DataTable
    
                    dtInsert = dtTable.Clone
                    For Each drData As DataRow In dtTable.Rows
                        Dim dr As DataRow = dtInsert.NewRow
                        dr.ItemArray = drData.ItemArray
                        dtInsert.Rows.Add(dr)
                    Next
    
                    Dim sCreateColumns As String = String.Empty
                    Dim sInsertColumns As String = String.Empty
                    Dim sQuestionMarks As String = String.Empty
                    For Each dcColumn As DataColumn In dtInsert.Columns
    
                        sInsertColumns = sInsertColumns & "[" & dcColumn.ColumnName & "],"
                        sQuestionMarks = sQuestionMarks & "?,"
    
                        If dcColumn.DataType Is GetType(Date) Then
                            sCreateColumns = sCreateColumns & "[" & dcColumn.ColumnName & "] DateTime,"
                        Else
                            sCreateColumns = sCreateColumns & "[" & dcColumn.ColumnName & "] Varchar(255),"
                        End If
    
                    Next
    
                    sCreateColumns = sCreateColumns.Substring(0, sCreateColumns.Length - 1)
                    sInsertColumns = sInsertColumns.Substring(0, sInsertColumns.Length - 1)
                    sQuestionMarks = sQuestionMarks.Substring(0, sQuestionMarks.Length - 1)
                    oCommand.CommandText = "Create Table [" & dtInsert.TableName & "] (" & sCreateColumns & ")"
                    oCommand.ExecuteNonQuery()
    
                    oCommand.CommandText = "Insert Into [" & dtInsert.TableName & "] (" & sInsertColumns & ") Values (" & sQuestionMarks & ")"
                    oCommand.Parameters.Clear()
                    For Each dcColumn As DataColumn In dtInsert.Columns
                        If dcColumn.DataType Is GetType(Date) Then
                            sCreateColumns = sCreateColumns & "[" & dcColumn.ColumnName & "] DateTime,"
                            oCommand.Parameters.Add("@" & dcColumn.ColumnName, System.Data.OleDb.OleDbType.Date, 4, dcColumn.ColumnName)
                        Else
                            oCommand.Parameters.Add("@" & dcColumn.ColumnName, System.Data.OleDb.OleDbType.VarChar, 255, dcColumn.ColumnName)
                        End If
                    Next
    
                    Dim oDataAdapter As New System.Data.OleDb.OleDbDataAdapter
                    oDataAdapter.InsertCommand = oCommand
                    oDataAdapter.Update(dtInsert)
                Next
    
            Catch ex As Exception
                'error handling
            Finally
    
                oConnection.Close()
    
            End Try
    End Sub
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Exporting to Excel faster

    You can fill an entire range of data by using an array - which tends to be about 5 times faster than doing the equivalent cells.

    This is untested, and based on my VB6/Excel tutorial:
    Code:
          Dim lMyArray(2, 1) As Long
            For i = 0 To Me.DataGridView1.Rows.Count - 1
                For j = 0 To Me.DataGridView1.Columns.Count - 1
                    lMyArray(i,j) = Me.DataGridView1(j, i).Value.ToString().Trim
                Next
            Next
            xlWorkSheet.Range("A2:E" & (Me.DataGridView1.Rows.Count)).Value = lMyArray
    I've hard-coded the final column name, but you can calculate it by using the equivalent of this VB6 function:
    Code:
    Function xl_Col(ByRef Col_No as Long) As String
    'returns Excel column name from numeric position (e.g.: col_no 27 returns "AA")
    'by Si_the_geek (VBForums.com)
    
                                          'Only allow valid columns
      If Col_No < 1 Or Col_No > 256 Then Exit Function
    
      If Col_No < 27 Then                  'Single letter
        xl_Col = Chr(Col_No + 64)
      Else                                 'Two letters
        xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & _
                 Chr(((Col_No - 1) Mod 26) + 1 + 64)
      End If
    
    End Function

  10. #10
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    Re: Exporting to Excel faster

    You might want to take a look at this as well: http://www.codeproject.com/KB/office/OpenXML.aspx
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Jul 2006
    Location
    MI
    Posts
    1,965

    Re: Exporting to Excel faster

    si ... thanks for the code, that is exactly what I was looking for. It is literally hundreds of times faster than going cell by cell. A DataGridView with approx. 53000 rows & 21 columns takes about 20 seconds to export. Thanks again.

  12. #12
    Master Of Orion ForumAccount's Avatar
    Join Date
    Jan 2009
    Location
    Canada
    Posts
    2,802

    Re: Exporting to Excel faster

    1) The WorkSheet might be recalculating after you change each cell - you can probably disable that.
    2) You could instead export to a .csv which would open by default in Excel. That way you don't even need Excel to run the code.

  13. #13
    New Member
    Join Date
    Apr 2017
    Posts
    5

    Re: Exporting to Excel faster

    Its been a while !
    Bills code is supper fast

    but does anyone know how to turn off the headers when using direct select into the Excel spreadsheet

    "HDR=No" seems to kill the main data not the headers, they seem to persist


    Thanks D

  14. #14
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Exporting to Excel faster

    Hi ,

    if the Data is in a Database you can use a Sql-Statement.

    you will have to adjust the Excelversion.

    Code:
      Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
            Dim sSql As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            Dim Filename As String
            Filename = "C:\TestExcel.xls"
            sSql = "Select CustomerID, CompanyName, ContactName " & _
                 "Into [Sheetname] In '" & Filename & "' 'EXCEL 8.0;' " & _
                 "From Customers Order By CustomerID"
            'change EXCEL 8.0 <- to your Excelversion
    
            con.Open()
            ExecuteSQL(con, sSql)
            con.Close()
            con = Nothing
        End Sub
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                          ByVal sSQL As String, _
                                          Optional ByRef ErrMessage As String = Nothing, _
                                          Optional ByVal TransAction As  _
                                          OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    regards
    Chris

  15. #15
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Exporting to Excel faster

    If your DataGridView was populated from a DataTable you could use SpreadSheetLight library.

    Basic code
    Code:
    Public Function ExportDataTable(ByVal table As DataTable) As Boolean 
        Try 
            Using sl As New SLDocument(ExportFileName) 
                Dim startRow As Integer = 1 
                Dim startColumn As Integer = 1 
                sl.SelectWorksheet("Sheet1") 
                ' import DataTable with column headers 
                sl.ImportDataTable(startRow, startColumn, table, True) 
                sl.Save() 
            End Using 
            Return True 
        Catch ex As Exception 
            Return False 
        End Try 
    End Function
    Full working example can be found here
    https://code.msdn.microsoft.com/Alte...-work-4c52c4a2

  16. #16
    New Member
    Join Date
    Apr 2017
    Posts
    5

    Re: Exporting to Excel faster

    Quote Originally Posted by ChrisE View Post
    Hi ,

    if the Data is in a Database you can use a Sql-Statement.

    you will have to adjust the Excelversion.

    Code:
      Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
            Dim sSql As String
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            Dim Filename As String
            Filename = "C:\TestExcel.xls"
            sSql = "Select CustomerID, CompanyName, ContactName " & _
                 "Into [Sheetname] In '" & Filename & "' 'EXCEL 8.0;' " & _
                 "From Customers Order By CustomerID"
            'change EXCEL 8.0 <- to your Excelversion
    
            con.Open()
            ExecuteSQL(con, sSql)
            con.Close()
            con = Nothing
        End Sub
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                          ByVal sSQL As String, _
                                          Optional ByRef ErrMessage As String = Nothing, _
                                          Optional ByVal TransAction As  _
                                          OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    regards
    Chris


    Yes I'm loading data from a DB

    I will have a look and see if it helps remove the headers

    Thanks D

  17. #17
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    173

    Re: Exporting to Excel faster

    I use some thing very similar.

    Code:
                        'Add Rows
                        For i = 0 To DGVConfigurationFile.RowCount - 2
    
                            For j = 0 To DGVConfigurationFile.ColumnCount - 1
    
                                Try
                                    xlWorkSheetConfigFile.Cells(i + 2, j + 1) = DGVConfigurationFile(j, i).Value.ToString()
    Difference is I use RowCount and ColumnCount. Also there is no Trim function. Not sure if this makes a difference.
    I can fix what stupid does, but, I cannot fix stupid

  18. #18
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Exporting to Excel faster

    Quote Originally Posted by ForumAccount View Post
    1) The WorkSheet might be recalculating after you change each cell - you can probably disable that.
    2) You could instead export to a .csv which would open by default in Excel. That way you don't even need Excel to run the code.
    nice Idea, I just tried that...


    Copy Data from Datagridview to CSV:
    Code:
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim DGV As DataGridView = DataGridView1
    
            Using FW As New IO.StreamWriter("c:\testData.csv")
    
    
                'Save with Header
                'Using SW As New IO.StringWriter
                '    For i As Integer = 0 To DGV.Columns.Count - 1
                '        If i < (DGV.Columns.Count - 1) Then
                '            SW.Write(DGV.Columns(i).HeaderText & ";") 'or ","
                '        Else
                '            SW.Write(DGV.Columns(i).HeaderText)
                '        End If
                '    Next
                '    FW.WriteLine(SW.ToString)
                'End Using
    
                'Save only Data:
                For i As Integer = 0 To DGV.Rows.Count - 1
                    Using SW As New IO.StringWriter
                        For j As Integer = 0 To DGV.Columns.Count - 1
                            SW.Write(DGV.Rows(i).Cells(j).Value.ToString)
                            If DGV.Rows(i).Cells(j).Value Is Nothing Then
                                SW.Write(DGV.Rows(i).Cells(j).Value.ToString)
                            End If
                            If j < (DGV.Columns.Count - 1) Then
                                SW.Write(",") 'or ";"
                            End If
                        Next
                        FW.WriteLine(SW.ToString)
                    End Using
                Next
                FW.Close()
            End Using
        End Sub
    if you want to save with Header just uncomment the Lines

    regards
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width