Four Key Reasons to Learn Markdown
Back-End Leveling UpWriting documentation is fun—really, really fun. I know some engineers may disagree with me, but as a technical writer, creating quality documentation that will...
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| require 'axlsx' | |
| p = Axlsx::Package.new | |
| # Required for use with numbers | |
| p.use_shared_strings = true |
Next, after creating a workbook via a block, we’ll set up some styles to use later.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| p.workbook do |wb| | |
| # define your regular styles | |
| styles = wb.styles | |
| title = styles.add_style :sz => 15, :b => true, :u => true | |
| default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER | |
| pascal_colors = { :bg_color => '567DCC', :fg_color => 'FFFF00' } | |
| pascal = styles.add_style pascal_colors.merge({ :border => Axlsx::STYLE_THIN_BORDER, :b => true }) | |
| header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true | |
| money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER | |
| money_pascal = styles.add_style pascal_colors.merge({ :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER }) | |
| percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER | |
| percent_pascal = styles.add_style pascal_colors.merge({ :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER }) |
Next, we’ll create a worksheet for and add a few rows. Note that the worksheet name is limited to 31 characters.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| wb.add_worksheet(:name => 'Data Bar Conditional Formatting') do |ws| | |
| ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title | |
| ws.add_row | |
| ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header | |
| # Passing one style applies the style to all columns | |
| ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => pascal |
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!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Otherwise you can specify a style for each column. | |
| ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [pascal, money_pascal, percent_pascal] | |
| ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent] | |
| ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent] | |
| # You can merge cells! | |
| ws.merge_cells 'A1:C1' | |
| end | |
| end | |
| p.serialize 'getting_barred.xlsx' |
Here’s the completed example.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # adapted from http://axlsx.blogspot.com/, https://gist.github.com/2484520 | |
| require 'axlsx' | |
| p = Axlsx::Package.new | |
| # Required for use with numbers | |
| p.use_shared_strings = true | |
| p.workbook do |wb| | |
| # define your regular styles | |
| styles = wb.styles | |
| title = styles.add_style :sz => 15, :b => true, :u => true | |
| default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER | |
| pascal_colors = { :bg_color => '567DCC', :fg_color => 'FFFF00' } | |
| pascal = styles.add_style pascal_colors.merge({ :border => Axlsx::STYLE_THIN_BORDER, :b => true }) | |
| header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true | |
| money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER | |
| money_pascal = styles.add_style pascal_colors.merge({ :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER }) | |
| percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER | |
| percent_pascal = styles.add_style pascal_colors.merge({ :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER }) | |
| wb.add_worksheet(:name => 'Data Bar Conditional Formatting') do |ws| | |
| ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title | |
| ws.add_row | |
| ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header | |
| # Passing one style applies the style to all columns | |
| ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => pascal | |
| # Otherwise you can specify a style for each column. | |
| ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [pascal, money_pascal, percent_pascal] | |
| ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent] | |
| ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent] | |
| # You can merge cells! | |
| ws.merge_cells 'A1:C1' | |
| end | |
| end | |
| p.serialize 'getting_barred.xlsx' |
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?
