Odd Behavior when exceed 65k rows

I have a datatable that I have inserted into a worksheet. There are 68,188 rows in the table. I have a loop that adds a formula. The iterator


for (int i = 3; i < dt.Rows.Count; i++)
	wsData.Cells[i, 11].Formula = string.Format("=K{0}Rates!$B$1", (i + 1).ToString());
There is more within the loop that this should get the point across. Everything is good up to row 65,536. I get =K65536Rates!$B$1
The next tow should be row 65,537 has a formula of =K1*Rates!$B$1
It is as if the an int is being treated as an ushort type. It can’t be the loop variable i since it populates the right cell.
Is there a way around this problem?
Thanks
Randy

Hi,

Thanks for your posting and using Aspose.Cells.

Actually, you are using XLS format which is an older format, it deals with only 65536 rows. You should use XLSX format which is a newer format and it deals with 1048576 rows.

When you create a workbook like this, then workbook is created in XLS format

C#

//Here workbook will be created in XLS older format
Workbook workbook = new Workbook();

Therefore, you should create workbook object like this. It will then be created in XLSX format and your formulas will be correct even after 65536 rows.

C#

//Here workbook will be created in XLSX newer format
Workbook workbook = new Workbook(FileFormatType.Xlsx);

Thanks, I was opening a template, but converting the template from XLS to XLSX fixed it. The file was always saved as XLSX, but since the template was XLS I guess it followed those rules.


Thanks for the quick reply.

Randy

Hi,

Thanks for your feedback and using Aspose.Cells.

Yes, the issue was occurring because your were loading XLS file. It is good to know that you were able to sort out this issue. Let us know if you encounter any other issue, we will be glad to look into it and help you further.