This project has moved. For the latest updates, please go here.

Any ways to increase speed when creating ExcelPackage from Dataset, or saving package to file?

Jul 24, 2014 at 6:50 PM
Edited Jul 24, 2014 at 7:01 PM
I recently updated my .NET application to stop using the Microsoft Excel interop library because it's slow for some actions, and now it's using EPPlus.

Using EPPlus improved the speed of creating an Excel file by about 50%. All the actions involving editing worksheets are much faster, but is there any way to improve the speed of generating the ExcelPackage, or saving to a file? (In some cases, saving the ExcelPackage to a file is slower than saving an Excel file using MS Excel interop - but overall performance is still better).

The spreadsheet has 32 worksheets, with about 1.2 million cells between them (average of about 60 columns per worksheet, with about 20,000 rows split between the worksheets). Excel file size is about 6.2 MB.

After getting the Dataset, saving the Excel file takes ~16 seconds. This is about ~4 seconds to create the ExcelPackage from the DataSet, ~1 second for formatting (comments, dropdown formulas, worksheet protection, etc), and ~11 seconds for saving the Excel file.

Here is the code for creating the ExcelPackage:
Public Shared Function CreateExcelPackageFromDS(ByVal ds As DataSet) As ExcelPackage
    Dim xlPackage As New ExcelPackage()
    Dim oBook As ExcelWorkbook = xlPackage.Workbook

    'Load each table into worksheet
    For Each dt As System.Data.DataTable In ds.Tables
        Dim ws As ExcelWorksheet = oBook.Worksheets.Add(dt.TableName)
        ws.Cells.LoadFromDataTable(dt, True)
        ws.Cells.Style.Locked = False
    Next

    Return xlPackage
End Function

And the code for saving the Excel file, after all the formatting is done, is just:
        Dim excelFile As New FileInfo(tempFilesPath & "\Export" & userID & ".xlsx")
        xlPackage.SaveAs(excelFile)

Is anyone aware of any things that could be changed, in order to improve the performance of creating the Excel package, or for saving the Excel file? Or is this level of performance about what should be expected, given this amount of worksheets, rows, cells, etc.?

(Edit: This is running on my local machine. File path for saving Excel file is just a folder on C: drive. Computer has Intel Xeon dual core processor and 6 GB RAM.)
Jan 27, 2015 at 3:45 PM
I also have the same problem. Writing a data table with 3500 rows is taking almost 10 seconds a row. I would like to find an answer. I also posted a similar questions.