Question Interop Excel performance issues

[Adagio]

Is there a way to increase performance when exporting to Excell from Vb.net using Interop?

I'm using code that looks a bit like this

Private excelApp As New Excel.Application
Private excelBook As Excel.Workbook
Private worksheet As Excel.Worksheet

excelBook = excelApp.Workbooks.Add
worksheet = CType(excelBook.Worksheets.Add(), Excel.Worksheet)

With worksheet
  For each cell as MyCustomObject in listCell
     .Range(cell.cellPosition).Value = cell.Value
     
     ' Here we also set vertial/horizontal alignment, wraptext = false, back and front color, etc
  Next
End With

The code itself works great, as in: we get the result as we want it... the problem is that it takes a long time. When we have e.g. 1000 rows it could easily take 10 minutes (depending on number of columns) before it's done. Considering that we "often" end up with 5000+ rows, time starts becoming a real problem

Is there a way to speed up this code? Is there an alternative to Interop Excel when exporting to Excel?

 

[MattP]

Take a look here: How Do I: Transfer Data Into an Excel Worksheet?. Should be able to speed things up considerably.

Description:

In this video, Steve Hansen from VSTOTips.com demonstrates how to write data out to an Excel worksheet in an efficient manner. Writing data to a worksheet is a common task when programming on top of Excel and depending on the amount of data you need to write, you can dramatically improve the performance of your code by putting the data into an array and then assigning the array to a range.

 

[Adagio]

Thank you, I'll have a look at that, see if I can make it work here