ExcelPackage and 98 thousand rows

Question

I want to write an excel file (2007) with the codeplex excelpackage but it is taking a lot of time to write the excel file. I didn't find any method that it could accept a datasource.

the piece of my code:

var newFile = new FileInfo(GlobalVariables.Compare2007Path);

using (var excelpackage = new ExcelPackage(newFile))
{
  var myWorkbook = excelpackage.Workbook;
  myWorkbook.Worksheets.Add("sheetname");

  var xlWorkSheet = xlWorkBook.Worksheets["sheetname"];

  //loop the data and fill the columns
  var rowCount = 2;
  foreach (var compare in objCompare)
  {
    xlWorkSheet.Cell(rowCount, 1).Value = compare.adserverIdSite.ToString();
    xlWorkSheet.Cell(rowCount, 2).Value = compare.site;
    xlWorkSheet.Cell(rowCount, 3).Value = compare.adserverIdZone.ToString();
    xlWorkSheet.Cell(rowCount, 4).Value = compare.zone;
    xlWorkSheet.Cell(rowCount, 5).Value = compare.position;
    xlWorkSheet.Cell(rowCount, 6).Value = compare.weekday;
    xlWorkSheet.Cell(rowCount, 7).Value = compare.oldimps.ToString();
    xlWorkSheet.Cell(rowCount, 8).Value = compare.olduu.ToString();
    xlWorkSheet.Cell(rowCount, 9).Value = compare.oldimpsuu.ToString();
    xlWorkSheet.Cell(rowCount, 10).Value = compare.newimps.ToString();
    xlWorkSheet.Cell(rowCount, 11).Value = compare.newuu.ToString();
    xlWorkSheet.Cell(rowCount, 12).Value = compare.newimpsuu.ToString();
    xlWorkSheet.Cell(rowCount, 13).Value = compare.diffimps.ToString();
    xlWorkSheet.Cell(rowCount, 14).Value = compare.diffimpsperc.ToString();
    rowCount++;
  }
  excelpackage.Save();
}

Or are there other options besides excelpackage.

Answer 1

I have used (and bought) SmartXL for creating Excel files. It's not free but it saved me a lot of time. You can try it for free for 30 days.

Answer 2

SpreadsheetGear for .NET will do it.

You might want to look at the SpreadsheetGear Explorer Sample Solution (for C# or VB) which is installed with SpreadsheetGear. There is a sample under Advanced -> Performance which shows the fast way to populate cells. On my machine (overclocked Intel QX6850) it creates 50,000 rows by 4 columns in 0.05 seconds.

You can download a free trial here which will install the SpreadsheetGear component and the SpreadsheetGear Explorer sample mentioned above.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

I found my solution for the performance of the excel package. It's a patch you need to apply on the excelPackage. The patch can be found here. Look for the id : 1042 or for the update 1233 (more features in that patch).

With the patch, you are able to add a datatable on an empty sheet. The adding of 98000 records with 14 columns was done in seconds.