I have a List<Dto> of 196,550 items.
I need to export to Excel.
I use this code to create am Excel2017 workbook, at least that is what I want to do.
using (var excelEngine = new ExcelEngine())
{
var application = excelEngine.Excel;
// SubAdvies uses Excel 2007
application.DefaultVersion = ExcelVersion.Excel2013;
application.EnableIncrementalFormula = true;
excelEngine.ThrowNotSavedOnDestroy = true;
// Create a workbook with a worksheet.
var workbook = application.Workbooks.Create(1);
// Template was made with Dutch Excel:
workbook.SetSeparators(',', ';');
//Access first worksheet from the workbook instance.
var worksheet = workbook.Worksheets[0];
//Use migrantrange to improve performance and reduce memory consumption.
var migrantRange = worksheet.MigrantRange
row++;
foreach (var dto in data)
{
// Skip if no WTL:
// Comment for debugging if (dto.Wtl <= 0) continue;
headerColumn = 0;
foreach (var headerName in headerNames)
{
migrantRange.ResetRowColumn(row, ++headerColumn);
PutHeadervalueInMigrationRange(headerName, dto, ref migrantRange);
}
// Next row:
row++;
}
workbook.SaveAs(saveAsFilename);
}
The above code runs perfectly, without any warnings.
Except the resulting Excelsheet only has 65,536 rows, the magic number of old Excel versions.
What do I need to do the let XlsIO create a proper Excelfile which can contain more than 65,536 rows?
I'm using 16.4.0.42
