Axlsx 1.1.3 - Release Announcement: Conditional Formatting Excel with Ruby and Ruby on Rails

Contributors Rock

 This will be the first in a series of "Contributors Rock" posts where I hope I will be able to express my gratitude for those that have stepped up and added functionality as well as used the gem.

@scpike of #mom (hashtagmom)  and other things I am not allowed to talk about is one of those people.

A couple of months ago, after releasing the first version of axlsx, I started thinking about what else was really needed on the sharp edge. One of the things that came up was conditional formatting, so I tossed an issue into the repo: Implement Conditional Formatting for worksheet figuring I'd get around to it sometime in June. What came later was totally unexpected. @scpike piped in saying that he was interested in the feature and posted: "I started working on it in my fork (progress so far), but I have a couple of questions. "

I was mind blown. The code was clean, thoroughly documented, well tested and in line with the style of the rest of the gem. Hats off mate - I would have done a worse job of it myself.

 

The Business End

 So what is conditional formatting? In essence, it allows you to change the style for your cells based on the content of the worksheet. Show your rising profitability in smashing green, or your spiking operations costs in a jarring red. Let have a look at a simple example:

Hitting the high notes

The code for this example is available here

require 'axlsx' 
p = Axlsx::Package.new
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)
  header = styles.add_style(:bg_color => '00', :fg_color => 'FF', :b => true)
  money = styles.add_style(:format_code => '#,###,##0', 
                           :border => Axlsx::STYLE_THIN_BORDER)
  percent = styles.add_style(:num_fmt => Axlsx::NUM_FMT_PERCENT,   
                             :border => Axlsx::STYLE_THIN_BORDER)


  # define the style for conditional formatting - its the :dxf bit that counts!
  profitable =  styles.add_style(:fg_color=> 'FF428751',  
                                 :sz => 12, :type => :dxf, :b => true)


  wb.add_worksheet('the high notes') 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
    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'],  
                :style => [default, money, percent]
    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'],  
                :style => [default, money, percent]
    ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'],  
                :style => [default, money, percent]
    ws.add_row ['Q1-2013(est)', '72000000000', '=B7/SUM(B4:B7)'],  
                :style => [default, money, percent]


    ws.merge_cells 'A1:C1'

    # Apply conditional formatting to range B4:B7 in the worksheet

    ws.add_conditional_formatting('B4:B7', { :type => :cellIs,
                                    :operator => :greaterThan,
                                    :formula => '27000000',
                                    :dxfId => profitable,
                                    :priority => 1 })
  end
end
p.serialize 'the_high_notes.xlsx'

But there is so much more! The previous example used :cellIs as the conditional format type but the specification supports 16 other types of conditional formatting:

colorScale, dataBar, iconSet, top10, uniqueValues, duplicateValues, containsText, notContainsText, beginsWith, endsWith, containsBlanks, notContainsBlanks, containsErrors, notContainsErrors, timePeriod and aboveAverage so get Creative, but keep in mind that users of older versions like 2008 on Mac will receive a 'sorry, you version does not support this!' message and render the worksheet normally for some of the more funky stuff.

Scaled Colors

The code for this example is available here

    color_scale = Axlsx::ColorScale.new

    ws.add_conditional_formatting('B4:B7', { :type => :colorScale,
                                             :operator => :greaterThan,
                                             :formula => 27000000000,
                                             :dxfId => profitable,

                                             :priority => 1,

                                             :color_scale => color_scale })

Stop and Go

The code for this example is available here

     icon_set = Axlsx::IconSet.new

    ws.add_conditional_formatting('B4:B7', { :type => :iconSet,

                                             :dxfId => profitable,

                                             :priority => 1,

                                             :icon_set => icon_set })

Getting Barred

The code for this example is available here

    data_bar = Axlsx::DataBar.new

    ws.add_conditional_formatting('B4:B7', { :type => :dataBar,

                                             :dxfId => profitable,

                                             :priority => 1,

                                             :icon_set => data_bar })

Triple unicorn rainbow <3 <3 <3 to @scpike for adding this in.