Max rows count

[jameswangz]

Hi guys, it seems the max rows count of the xls file created by this gem is 65535? Not sure whether I missed some configuration, below is my test script:

require 'spreadsheet'

Spreadsheet.client_encoding = 'UTF-8'
book = Spreadsheet::Workbook.new
sheet = book.create_worksheet name: 'Testing sheet'

100000.times do |index|
 sheet.row(index).push(
  "a",
  index
 )
end

book.write "#{Dir.home}/temp/exported.xls"

[zdavatz]

Did you try to open the file with LibreOffice or with Microsoft Office?

[jameswangz]

Yes, I tried Microsoft Office on both Windows and MacOS, the last row is 65535, you can run my script to verify it.

[zdavatz]

Yes, I can verify this. Seems to be an XLS limit. Please let me know if you come up with a patch. Which Ruby version did you test?

[jameswangz]

I used ruby 2.6.5p114.

[zdavatz]

I used 3.2.0 but same result.

[kmckinley]

The .xls format is limited to only handle a max of 65,536 rows. So there isn't a way to get around this because Excel itself will not allow it. However, the .xlsx format allows over 1 million rows, so you may need to find a different gem that allows you to create that file type.

[zdavatz]

The .xls format is limited to only handle a max of 65,536 rows. So there isn't a way to get around this because Excel itself will not allow it. However, the .xlsx format allows over 1 million rows, so you may need to find a different gem that allows you to create that file type.

Thank you @kmckinley for the insigths. Can you please paste a link here that confirms your point?

[kmckinley]

@zdavatz Yes, though it has become much more difficult to find this information than it was in the past. Below is the best link I've been able to find regarding the .xls limitations. I've also included a screenshot of the "More about the limits of Excel file formats" section.

I've started using the 'caxlsx' gem (formally 'axlsx') to handle the creation of .xlsx files. It's a rather powerful, though initially, I was running into file format issues where when opening the file in excel, it would go into recovery mode. The issue was that I had text data within cells that included '=' or '-'. This caused excel to think it was a formula, which would fail and cause the recovery mode. I found that specifying the cell type to be :string fixed this issue. So I always just default every cell to :string, and if I need it to be something different, I override that specific cell, row, or column.

https://support.microsoft.com/en-us/office/what-to-do-if-a-data-set-is-too-large-for-the-excel-grid-976e6a34-9756-48f4-828c-ca80b3d0e15c

[zdavatz]

Thank you!

[kmckinley]

@zdavatz I just updated my previous comment to include info about another gem I use for .xlsx formatting. Just incase it might help.