导出以在 VB.Net 中脱颖而出

问：

我正在尝试将数据导出到 excel 中，但对于大数据，它失败了 错误 - “Range 类的 PasteSpecial 方法失败” 对于更少的数据，我能够成功导出。

代码是这样的：

私有子 ProcessVisibleColumns（）

    With Me._ExcelWrks

            .Range("A1").Activate()


            For Each c As Colonna In _Colonne
                _ExcelApp.ActiveCell.Value = c.Label & ""
                _ExcelApp.ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate()
            Next

        .Range("A1").Select()



        _ExcelApp.Selection.NumberFormat = "@"

            Dim sb As New System.Text.StringBuilder(25000)
            Dim strFormat As New System.Text.StringBuilder(200)
            For i As Integer = 0 To _Colonne.Count - 1
                strFormat.Append("{")
                strFormat.Append(i.ToString)
                strFormat.Append("}")
                strFormat.Append(ControlChars.Tab)
            Next
            strFormat.Remove(strFormat.Length - 1, 1)
            Dim strF As String = strFormat.ToString

            Dim k As Integer = 1
            Dim j As Integer = 0
            Dim CurRow As Integer = 2

            Dim ColValues As ArrayList = New ArrayList(_Colonne.Count)

            For Each r As DataRowView In _Rv
                ColValues.Clear()
                For Each c In _Colonne
                    ColValues.Add(r(c.DataField))
                Next
                sb.AppendFormat(strF, CType(ColValues.ToArray(GetType(Object)), Object()))

            sb.Append(vbNewLine)
            If k = 100 OrElse j = _Rv.Count - 1 Then
                    System.Windows.Forms.Clipboard.SetDataObject(sb.ToString, True)
                    Me._ExcelWrks.Range("A" & CurRow).Select()
                _ExcelApp.ActiveSheet.PasteSpecial()
                CurRow += k
                    k = 0
                    sb.Remove(0, sb.Length)
                End If
                j += 1 : k += 1
                If j = 65536 Then
                    j = 0
                    Me._ExcelWrks.Range("A1").Activate()

                    _ExcelWrks = _ExcelWrkb.Sheets.Add(After:=_ExcelWrks)
                    _ExcelWrks.Activate()

                    Me._ExcelWrks.Range("A1").Activate()
                    For Each c As DataColumn In _Rv.Table.Columns
                        _ExcelApp.ActiveCell.Value = c.ColumnName & ""
                        _ExcelApp.ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Activate()
                    Next

                    CurRow = 2
               
                    Me._ExcelWrks.Range("A:A;N:N;AC:AC;AE:AE").Select()
                    _ExcelApp.Selection.NumberFormat = "@"

                End If
            Next


            Me._ExcelWrks.Range("A1").Activate()
            _ExcelApp.Visible = True
        End With

End Sub

注意 - 如果记录计数小于 400/500，我能够成功导出。

我正在尝试导出计数大于 1000/2000 的数据........