Fast Export to Excel using transpose

Question

i used a solution posted in this link Fast Export of Large Datatable to Excel Spreadsheet in VB.Net

and worked very well, however when my code export more than 15513 lines the code fail and only appears "#N/A" on columns. like shown on image below

Error on export a big number of lines see the code used:

        'numero de linhas
        Dim Nbligne As Long = dt.Rows.Count

        'Grava o cabeçalho das colunas e os dados
        For Each dc In dt.Columns

            colIndex = colIndex + 1
            'Cabeçalhos
            oWsh.Cells(1, colIndex) = dc.ColumnName
            oWsh.Cells(2, colIndex).Resize(Nbligne, ).Value = oXls.Application.transpose(dt.Rows.OfType(Of DataRow)().[Select](Function(k) CObj(k(dc.ColumnName))).ToArray())

        Next

PS: this code continue to running in current application, because only in few cases i have more than 15000 records, but i need a definitive solution for this problem. ;-)

can somebody help me?

Answer 1

After receive a large number of records in this routine, i could verify that this error ocurred in a specific situation that not happened again. I believe this error could be caused by a caracter in a varchar field.

thus it is not necessary a correction on routine.

So, thanks for reading my question ;-)