Use Ruby to Generate Formatted Excel Documents
The latest version of Ruby comes standard now with Comma Separated Value support built right in via the CSV library written by one of our very own alumni, James Edward Gray II. You might know CSV as the extremely portable format file used for everything from Excel Documents, to Numbers Spreadsheets, to lists of emails, to even generic data files. The CSV library is quite generic and useful by itself, but sometimes, you really need the expanded capabilities that only an Excel or Numbers document can support. Read on to find out how to generate Excel and Numbers compatible .xlsx files with Ruby.
Another Highgroove alumnus, Emily Price, recently introduced me to axlsx, a Ruby gem which generates “Office Open XML Spreadsheet documents.” axlsx provides support for charts, images (with links), automated and fixed column widths, customized styles, functions, merged cells, and more.
It is super simple to get started. Let’s check out a few of the features.
First, if you plan to use the generated file with Numbers, make sure to set “use_shared_strings” to true.
Next, after creating a workbook via a block, we’ll set up some styles to use later.
Next, we’ll create a worksheet for and add a few rows. Note that the worksheet name is limited to 31 characters.
Finally, we add a few rows using custom styles for each column, merge some of the title cells, and then serialize the file out to disk!
Here’s the completed example.
If you’re an auditory learner, you can check out my tech-talk on axlsx.
To learn more, check out the axlsx blog.
What are some other Ruby gems you love to use?