ExcelPackage and 98 thousand rows

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.C开发者_StackOverflow中文版ell(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.

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.

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

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.