We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Can't put more than 65,536 rows in a Excel2017 workbook

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
                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:

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

3 Replies

DB Dilli Babu Nandha Gopal Syncfusion Team March 27, 2019 09:12 AM UTC

Hi Paul, 

Thank you for contacting Syncfusion support. 

We suspect that the reported problem might be caused due to file extension of the saved Excel file. If the Workbook is saved in .xls format then, 65,536 by 256 rows and columns will be preserved. So, we request you to save the Workbook in .xlsx format to resolve the issue. 

To know about maximum range of Rows and Columns in different Workbook format, please refer the following link. 

Dilli babu. 

PM Paul Meems March 27, 2019 10:43 AM UTC

Thanks for the quick response.
Indeed changing the extension to '*.xlsx' solved my problem.

Perhaps it is a good idea to log a warning when the default version is explicitly set to Excel2016 but the file is saved as Excel97 due to the extension.

SA Sivaneswaran Amsu Syncfusion Team March 29, 2019 03:48 PM UTC

Hi Paul,

Thanks you for notifying us.

We will implement warning support in any one of the upcoming release.

Sivaneswaran . A

Live Chat Icon For mobile
Up arrow icon