提高速度以将datagridview导出为ex​​cel [英] increase speed to export datagridview to excel

问题描述

我必须在excel中导出datagridview。如果数据增加，则导出时间太长。

如果数据超过500-700条记录。

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????

推荐答案

使用此代码，这工作得多更快。

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.

尝试以不同的方式迭代：

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

这篇关于提高速度以将datagridview导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！