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?

You can drop a 2-d array directly into a Range in one operation. http://support.microsoft.com/kb/302094 — Tim Williams, May 06 '14 at 16:30

score 0 · Answer 1 · answered May 06 '14 at 19:28

0

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

answered May 06 '14 at 19:28

John Alexiou

28,472
11
77
133

Fastest way to write cells to Excel

1 Answers1

Linked