How to improve performance in Creating and populating Excel

Question

I am generating an Excel document dynamically and populating it with more than 200,000 records. Then I need to upload the file to another server.

For the above functionality, I just wrote the code in the code behind. It is taking 20-30 mininutes to see the populated excel document though, and the same while uploading.

How can I speed up this process? Please help.

HERE is my CODE

Private Sub FillDataRows(ByVal ws As Worksheet) Dim rowcount As Integer = 7 Dim colcount As Integer = 1 Dim rowscount As Integer

    For Each dr As DataRow In dsCostUsage.Tables(0).Rows
        colcount = 1
        Dim items As Object() = dr.ItemArray
        For Each item As Object In items
            Dim nextItem As String = ""
             nextItem = item.ToString()
             DirectCast(ws.Cells(rowcount, colcount), Range).Value2 = nextItem
            colcount += 1
        Next
        rowcount += 1
    Next
End Sub

Thank you.

Yahia Yahia · Accepted Answer · 2011-08-26T14:57:00

Your use of Excel automation from ASP.NET is NOT supported according to Microsoft .

For generating Excel in ASP.NET you can use a free library called OpenXML 2.0 from Microsoft.

How to improve performance in Creating and populating Excel

1 Answers