-
Dec 9th, 2009, 03:16 PM
#1
Thread Starter
Frenzied Member
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...
-
Dec 9th, 2009, 03:25 PM
#2
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
-
Dec 9th, 2009, 03:32 PM
#3
Thread Starter
Frenzied Member
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?
-
Dec 9th, 2009, 03:36 PM
#4
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
-
Dec 9th, 2009, 03:41 PM
#5
Thread Starter
Frenzied Member
Re: Exporting to Excel faster
What you say makes sense. Unfortunately my knowledge of Excel leaves much to be desired. I will keep searching...
-
Dec 9th, 2009, 03:53 PM
#6
Re: Exporting to Excel faster
This seems that you are looking for (follow it) --->
http://www.codeproject.com/KB/vb/Sen...ware_Eng_.aspx
-
Dec 9th, 2009, 03:57 PM
#7
Thread Starter
Frenzied Member
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.
-
Dec 9th, 2009, 04:41 PM
#8
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
-
Dec 9th, 2009, 06:12 PM
#9
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
-
Dec 9th, 2009, 06:56 PM
#10
Re: Exporting to Excel faster
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
-
Dec 10th, 2009, 10:57 AM
#11
Thread Starter
Frenzied Member
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.
-
Dec 10th, 2009, 12:19 PM
#12
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.
-
Jul 18th, 2017, 02:43 AM
#13
New Member
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
-
Jul 18th, 2017, 03:05 AM
#14
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
-
Jul 18th, 2017, 07:42 AM
#15
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
-
Jul 18th, 2017, 04:08 PM
#16
New Member
Re: Exporting to Excel faster
Originally Posted by ChrisE
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
-
Jul 19th, 2017, 01:23 PM
#17
Addicted Member
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
-
Jul 21st, 2017, 02:42 AM
#18
Re: Exporting to Excel faster
Originally Posted by ForumAccount
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|