I use the fantastic Axslx ruby library to generate xlsx reports. While it has a good amount of examples, given that it generates Excel which is a beast its just a fact that there isn’t always an example for what you’re trying to do.

However, since Excel is really just a series of XML files packaged as a Zip, it means we can generate an Excel doc representing our desired output and then view the XML representation. Which in turn gives us the set the of method calls and attributes we need to manipulate in Axlsx.

Example: I wanted to freeze Column A so when the user scrolls to the right it remains fixed. No example in Axlsx has this exact scenario.

Solution:

  1. Generate an Excel doc with your desired output - in my case I froze Column A. 
  2. Change the file extension from .xlsx to .zip and un-compress it.
  3. Find sheet1.xml which represents your first worksheet and open it in a text editor.
  4. Find the pane freezing code which in my case looks like: 

At this point you just need to translate the XML tags to the Axlsx equivalent, which looks like:

And thats it!