Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
I have to export datagridview in excel. It take too time to export if data increases.
If data is above 500-700 records.
If flg = True Then
                    If txtflnm.Text <> "" Then
                    Dim xlApp As Excel.Application
                    Dim xlWorkBook As Excel.Workbook
                    Dim xlWorkSheet As Excel.Worksheet
                    Dim misValue As Object = System.Reflection.Missing.Value
                    Dim i, j As Integer
                    Dim p, q As Integer
                    xlApp = New Excel.ApplicationClass
                    xlWorkBook = xlApp.Workbooks.Add(misValue)
                    xlWorkSheet = xlWorkBook.Sheets("sheet1")
                    p = 1
                    For i = 0 To DataGridView2.RowCount - 1
                        'If DataGridView2.Item(0, i).Value = True Then
                        For j = 0 To DataGridView2.ColumnCount - 1
                            q = j + 1
                            If DataGridView2.Columns(j).Visible = True Then
                                If Convert.ToString(DataGridView2(j, i).Value)<> Nothing Then
                                    If j = 14 Or j = 18 Then
                                        If DataGridView2(j, i).Value.ToString() = True Then
                                            xlWorkSheet.Cells(p + 1, q) = "Yes"
                                        Else
                                            xlWorkSheet.Cells(p + 1, q) = "No"
                                        End If
                                    Else
                                        xlWorkSheet.Cells(p + 1, q) = DataGridView2(j, i).Value.ToString()
                                    End If
                                End If
                            End If
                        Next
                        p = p + 1
                        'End If
                    Next
 
                    xlWorkBook.SaveAs(txtflnm.Text, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
                    xlWorkBook.Close(True, misValue, misValue)
                    xlApp.Quit()
                    releaseObject(xlWorkSheet)
                    releaseObject(xlWorkBook)
                    releaseObject(xlApp)
                    MessageBox.Show("Data exported")
This is my code.
is there any shortcut to export that data????
Posted 3-Dec-12 19:42pm
dcba1890
v2
Comments
shadmehr at 4-Dec-12 3:32am
   
format of exported file, is important for you? should it be .xlsx or not?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

use this code, this works much faster.
 
For _Rows = 0 To _DSclip.Tables(T).Rows.Count - 1
                   _xlSheet1.Range("a" & XlRow).Offset(_Rows).Resize(1, _Cols).Value = _DSclip.Tables(T).Rows(_Rows).ItemArray()
               Next
 
It writes data row by row.
Please mark as answer if worked.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try to iterate in a different way :
  
For Each row As DataGridViewRow In Me. DataGridView2.Rows
 
    If row("SomeColumn").Visible = True Then
    ---do something ---
    End If
---do something
Next
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You can see this projecthttps://exportdata.codeplex.com/[^] I have tried, it can export more than 6000 records and even generate 15 types of chart when export to Excel, you can see below code:
Private Sub btnLoad_Click(sender As Object, e As EventArgs)
	Using oleDbConnection As New OleDbConnection()
		oleDbConnection.ConnectionString = Me.textBox1.Text
		Dim oleDbCommand As New OleDbCommand()
		oleDbCommand.CommandText = Me.textBox2.Text
		oleDbCommand.Connection = oleDbConnection
		Using da As New OleDbDataAdapter(oleDbCommand)
			Dim dt As New DataTable()
			da.Fill(dt)
			dataGridView1.DataSource = dt
		End Using
	End Using
End Sub
Private Sub btnRun_Click(sender As Object, e As EventArgs)
	Dim cellExport As New Spire.DataExport.XLS.CellExport()
	Dim worksheet1 As New Spire.DataExport.XLS.WorkSheet()
	worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable
	worksheet1.DataTable = TryCast(Me.dataGridView1.DataSource, DataTable)
	worksheet1.StartDataCol = CByte(0)
	cellExport.Sheets.Add(worksheet1)
	cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView
	cellExport.SaveToFile("20110223.xls")
End Sub
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100