Fastest way to write cells to Excel

Question

I am writing a function to export data from a gridview to Excel this method on VB .NET:

     Call consultaPlanilha()
    Dim i, j As Integer  'CONTADORES
    ReDim vetPontos(GridPontos.Rows.Count - 2, 1)


    For i = 0 To GridPontos.Rows.Count - 2
        For j = 0 To 1
            vetPontos(i, j) = CDbl(GridPontos.Item(j, i).Value)
            xlw.Application.Cells(i + 3, j + 2).Value = vetPontos(i, j)
        Next
    Next

    xlw.Save()

This is taking a long time for large amounts of data. Is there a faster way to write a lot of data to Excel at once? Would doing something with ranges be faster?

Answer 1

You know you can do this:

Dim vetPontos() as Variant
ReDim vetPontos(1 to 100, 1 to 20)

For i=1 to 100
  For j=1 to 20
   // fill in vetPontos(i,j)
  Next j
Next i

Range("A1").Resize(100,20).Value = vetPontos