Axlsx is an excellent gem for creating and formatting the excel report for your rails or just ruby application.
This blog is about the problems I have faced while using this gem and discoveries I have made while using this gem.
I was faced with issues even before I have written any code to write an excel report. It was because of version of axlsx and its runtime dependency gems. Axlsx require rubyzip to run.
First I have to deal with this error: uninitialized constant Zip::DOSTime error.
It was because of wrong version of rubyzip. It was a deprecated version of rubyzip. It internally uses zip-zip interrupted with axlsx. So, I upgraded the version of rubyzip and this error popped up: uninitialized constant Zip::OutputStream. It was again due to versions of both the gems, and to resolve this I had to again change the version of both the gems.
Finally I settled on these version of:
1.) axlsx 1.3.6
2.) rubyzip 1.1.6
Now, as soon as I was out of the version dependency issues, or at least I thought so, when I precompiled the assets I go this error: cannot load such file — zip/zip. This is because rubyzip >= 1.0.0 has required zip like this:
And rubyzip at <= 0.9.9 has code as:
So if you have some other gem in your application which is also dependent on rubyzip(<= 0.9.9), then just change your version of rubyzip to 0.9.9. For example: I was also using ‘roo’ gem which also needed rubyzip <= 0.9.9. So, to resolve the error which came up while precompiling, I finally settled at:
1.) axlsx, 1.3.6
2.) rubyzip, 0.9.9
One of the other challenges I faced was setting the height of any row. It is missing in the official examples page by the time I am writing this blog.
Here is how you can do it;
wb.add_worksheet(:name => "fixed row height") do |sheet| sheet.add_row ["This row will have a fixed height", "It will overwrite the default row height"], :height => 30 sheet.add_row ["This row can have a different height too."], :height => 10 end
It will overwrite the default row height. The default row height is 18.
And style to any row can be added along with height, like;
wb.add_worksheet(:name => "fixed row height") do |sheet| sheet.add_row ["This row can have a different height too"], :height => 10, :style => custom_style end
You can see the full example here: https://github.com/rishijain/axlsx/blob/patch-1/examples/example.rb
I have also submitted a pull-request and if you want to follow up with it, you can do it here: https://github.com/randym/axlsx/pull/354