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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Thread ID:

Created:

Updated:

Platform:

Replies:

143564 Mar 26,2019 08:56 PM UTC Mar 29,2019 03:48 PM UTC ASP.NET MVC - EJ 2 3
loading
Tags: XlsIO
Paul Meems
Asked On March 26, 2019 08:56 PM UTC

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

Dilli Babu Nandha Gopal [Syncfusion]
Replied On 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. 

Regards, 
Dilli babu. 


Paul Meems
Replied On 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.


Sivaneswaran Amsu [Syncfusion]
Replied On 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.


Regards,
Sivaneswaran . A

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;