Increase rows support

[brunobrgs]

It looks like the rows limit is 65,536.

Is it possible to do more than this? Current excel limit is 1,048,576

[zdavatz]

Have you tried this as well with LibreOffice 5.0.x? The Excel I know is limited at about 60'000 rows.

[brunobrgs]

http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx

Excel 2003 | 65,536 Excel 2007 | 1,048,576

[zdavatz]

Thanks for the link. Please post a script to gist.github.com so I can verify this on my Linux machine. What Ruby Version are you using? What gem version?

[brunobrgs]

I'm using ruby 2.2.3 and spreadsheet 1.0.8

[brunobrgs]

begin
  require 'bundler/inline'
rescue LoadError => e
  $stderr.puts 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'
  gem 'rails', '4.1.13', require: 'active_record'
  gem 'spreadsheet', '1.0.8'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

ActiveRecord::Base.logger = Logger.new(STDOUT)

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet

for i in 1..70000 do
  sheet.row(i).push "Line: #{i+1}"
end

book.write "test.xls"

[brunobrgs]

I set 70.000 lines, when it reaches the limit (it starts to override the lines starting from the beggining)

[zdavatz]

Thanks! Can you please create a script without using bundler, rails and activerecord? The script should only use spreadsheet and ruby, nothing else.

[brunobrgs]

require 'rubygems'
gem 'spreadsheet', '=1.0.8'
require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet

for i in 1..70000 do
  sheet.row(i).push "Line: #{i+1}"
end

book.write "test.xls"

[zdavatz]

Thanks! I tried it with for i in 1..70000 do and for i in 1..170000 do and it turns out that the file size does increase but the lines are not displayed in Libreoffice. Lines stop after 65536

[bkao]

This doesn't appear to be a libre issue. Not sure why the file size keeps increasing but it's definitely not rendering in Excel either.

[siddharthasandhu]

I am facing this problem. Is there a solution to this?

[AlfonsoUceda]

I think there isn't a solution for this, this limitation is attached to the XLSX format: http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx

[leandroalemao]

Hi @gpeng we do need a replacement for this gem on MI.. I'll search by others..

[datnguyen0606]

hi @brunobrgs I got the issue with you recently. Do we have any solution for it?

[brunobrgs]

No, no solutions for that.

[datnguyen0606]

@brunobrgs thanks. I think I'll use another gem to export to xlsx instead of xls in order to have more rows (e.g gem axlsx).

[zdavatz]

For XLSX support use this gem: https://github.com/weshatheleopard/rubyXL