Click here to Skip to main content
12,760,377 members (32,904 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"
                                            xlWorkSheet.Cells(p + 1, q) = "No"
                                        End If
                                        xlWorkSheet.Cells(p + 1, q) = DataGridView2(j, i).Value.ToString()
                                    End If
                                End If
                            End If
                        p = p + 1
                        'End If

                    xlWorkBook.SaveAs(txtflnm.Text, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
                    xlWorkBook.Close(True, misValue, misValue)
                    MessageBox.Show("Data exported")

This is my code.
is there any shortcut to export that data????
Posted 3-Dec-12 20:42pm
shadmehr 4-Dec-12 3:32am
format of exported file, is important for you? should it be .xlsx or not?
Rate this: bad
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()

It writes data row by row.
Please mark as answer if worked.
Rate this: bad
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

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.170217.1 | Last Updated 22 May 2015
Copyright © CodeProject, 1999-2017
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