Ruby - Generate an Excel Spreadsheet

September 22, 2014

In the financial world, there are always Excel spreadsheets; spreadsheets and Excel dominate most Fortune 500 Companies. Many times a business person will ask that a bunch of data is put in an Excel spreadsheet for them to manipulate. There are many ways to do this in the C# and .NET world, but how do we skin this cat in Ruby?

In Ruby we can use the Axlsx library, this is a cool library. With Axlsx you can create excel worksheets with charts, images (with links), automated and fixed column widths, customized styles, functions, tables, conditional formatting, print options, comments, merged cells, auto filters, file and stream serialization as well as full schema validation.

Enough talk, let’s dive into the code:

Axlsx has a concept of Package, which holds the Workbook that in turns hold the different Worksheets. In the code above we create a package and from the package variable we retrieve the Workbook. To add a Worksheet you call the ‘add_worksheet’ method on the workbook, passing in a has to for the name. The ‘add_worksheet’ method takes a code block and from the code example you see you can call ‘add_row’ to start adding items into the rows of the worksheet. To save the spreadsheet to disk you call the ‘serialize’ method on the ‘package’ variable passing in the filename. Opening this Excel spreadsheet, you will see a column for income and a column for expense with values below these columns. How easy was that?

There are a bunch of examples available on the GitHub repository for Axlsx; I can see myself using this library quite often going forward.

Discussion, links, and tweets

My name is Deon Heyns and I am a developer learning things and documenting them in realtime. Python, Ruby, Scala, .NET, and Groovy are all languages I have written code in. I appeared in the New York Post once. I host my code up at GitHub and Bitbucket so have a look at my code, fork it and send those pull requests.

comments powered by Disqus