Click here to Skip to main content
12,244,664 members (53,768 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB 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 20:42pm
dcba1894
v2
Comments
shadmehr 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  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 22 May 2015
Copyright © CodeProject, 1999-2016
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