Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Max rows count #268

Open
jameswangz opened this issue Aug 21, 2022 · 10 comments
Open

Max rows count #268

jameswangz opened this issue Aug 21, 2022 · 10 comments

Comments

@jameswangz
Copy link

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
Copy link
Owner

zdavatz commented Aug 22, 2022

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

@jameswangz
Copy link
Author

jameswangz commented Jan 30, 2023

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

@zdavatz
Copy link
Owner

zdavatz commented Jan 30, 2023

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
Copy link
Author

I used ruby 2.6.5p114.

@zdavatz
Copy link
Owner

zdavatz commented Jan 30, 2023

I used 3.2.0 but same result.

@kmckinley
Copy link

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
Copy link
Owner

zdavatz commented Feb 2, 2023

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
Copy link

kmckinley commented Feb 2, 2023

@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

Screen Shot 2023-02-02 at 7 49 21 AM

@zdavatz
Copy link
Owner

zdavatz commented Feb 2, 2023

Thank you!

@kmckinley
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants