Conditional Formatting w/ RubyXL

I was looking to add conditional formatting to an Excel report that I was generating with RubyXL and found no documentation on how to do it. Looking at the source code wan’t much use as it looked like it was a direct translation from an XML reference.

With that dead end sufficiently explored I turned to the obvious next solution in 2023, Stackoverflow ChatGPT. It’s first attempt looked pretty good, except for the part where it use classes and methods that don’t exist. It’s second attempt was no better. It’s third try was actually correct, just not very helpful. It recommended I add documentation and submit a pull request. Thanks ChatGPT…

Turns out no documentation means nothing for ChatGPT to scrap and generate from.

Backtracking, there wasn’t documentation for conditional highlighting in RubyXL, but it’s essentially a wrapper around the XML. Turns out Microsoft has pretty good documentation.

Once I figured out what XML I needed to be generating, I was able to move forward. Checking that I was generating what I thought I was generating was also critical. To see the actual XML of the xlsx file is pretty easy, it’s just a zip file so with a single tar -xf I could open it up and look at the xl/worksheets/sheet1.xml file to see the conditional rules, and check styles.xml for the style I was setting.

The root problem I ran into: I used a bare array for the list of styles instead of the required container class. Final code sample below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
workbook = RubyXL::Workbook.new
worksheet = workbook.worksheets[0]

# Style to highlight red
style = RubyXL::DXF.new
style.font = RubyXL::Font.new
style.font.color = RubyXL::Color.new("FF0000")

# Add style to workbook
workbook.stylesheet.dxfs ||= RubyXL::DXFs.new # This was my initial problem
workbook.stylesheet.dxfs << style
style_id = workbook.stylesheet.dxfs.index(style) # need to reference this later

# Rule to highlight negative numbers
rule = RubyXL::ConditionalFormattingRule.new
rule.dxf_id = style_id
rule.type = :cellIs
rule.operator = :lessThan
rule.priority = 1
rule.formulas << RubyXL::Formula.new(expression: "0")

range = "A3:A10" # area to apply the rule
formatting = RubyXL::ConditionalFormatting.new(sgref: range)
formatting.cf_rule << rule

worksheet.conditional_formatting << formatting