ExcelPackage and 98 thousand lines

Question

I want to write an excel (2007) file with excelpackage codeplex, but it takes a lot of time to write the excel file. I did not find any method so that it could accept the data source.

part 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 there are other options besides excelpackage.

Answer 1

I found my solution for excel package performance. This is the patch you need to apply on excelPackage. The patch can be found here . Look for the identifier: 1042 or for update 1233 (more features in this patch).

Using the patch, you can add data to a blank sheet. Adding 98,000 records with 14 columns was done in seconds.

Answer 2

SpreadsheetGear for .NET will do this.

Perhaps you should take a look at the trial solution SpreadsheetGear Explorer (for C # or VB), which is installed using SpreadsheetGear. There is a sample in Advanced → Performance that shows a quick way to fill cells. On my computer (overclocked by Intel QX6850) it creates 50,000 rows of 4 columns in 0.05 seconds.

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

Disclaimer: I have SpreadsheetGear LLC

Answer 3

I used (and bought) SmartXL to create Excel files. It is not free, but it saved me a lot of time. You can try it for free for 30 days.