Reputation: 5434
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.
Upvotes: 4
Views: 3773
Reputation: 5434
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.
Upvotes: 7
Reputation: 7217
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
Upvotes: 1