Excel Report using axlsx gem

Axlsx is an excellent gem for creating and formatting the excel report for your rails or just ruby application.

This not a RTFM blog. If you are looking for basic setup, you can refer the official examples page or read this blog written by a friend of mine.

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:

require 'zip'

And rubyzip at <= 0.9.9 has code as:

require 'zip/zip'

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

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

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s