xlsx issue: create excel sheet with large number of rows

axlsx, export-to-excel, ruby-on-rails, ruby-on-rails-3

Solution

Looks like I need to start paying attention to SO! This is randym (the author of axlsx)

There are a couple of things I'd like to point out that should help you get what you need done, well... done!

If you are writing to a file, consider Package#serialize - not because it is faster, but because it is less code for you to maintain.

p.serialize 'filename.xlsx'

Major performance improvements have been made over the last few weeks. Please upgrade to 1.1.1 The gem no longer depends on RMagic, and use_autowidth = false is no longer required.

https://github.com/randym/axlsx

Benchmarks for master:

Benchmarks w/40k rows:
                            user     system      total        real
axlsx_noautowidth      68.130000   1.690000  69.820000 ( 80.257108)
axlsx                  61.520000   2.290000  63.810000 ( 78.187423)
axlsx_shared           53.280000   1.170000  54.450000 ( 62.880780)
axlsx_stream           52.110000   1.360000  53.470000 ( 61.980672)
csv                    10.670000   0.930000  11.600000 ( 14.901387)

Benchmarks w/4k rows:
                            user     system      total        real
axlsx_noautowidth       4.880000   0.120000   5.000000 (  5.314383)
axlsx                   5.470000   0.110000   5.580000 (  5.853739)
axlsx_shared            5.720000   0.080000   5.800000 (  6.135263)
axlsx_stream            4.840000   0.090000   4.930000 (  5.194801)
csv                     1.090000   0.090000   1.180000 (  1.484763)

Here is the benchmarking file:

https://gist.github.com/2411144

Hope this helps

Problem

I am using Axlsx to create an excel file. For a small dataset, it works fine. But once the dataset gets big, it just hangs. I ran strace on the process, it was doing a lot brk. ``` a = Axlsx::Package.new book = a.workbook book.add_worksheet(:name => "test") do |sheet| input_array.each do |input_data| ...# covert input_data to row_data sheet.add_row(row_data) end end File.open("testfile", 'w') { |f| f.write(p.to_stream().read) } ``` My input_array size is about 400,000, so the worksheet has 400,000 rows, quite large. It got stuck at `p.to_stream().read`. Any help would be great. Thanks.

Original source