Implement Conditional Formatting for worksheet

[randym]

18.3.1.10 cfRule (Conditional Formatting Rule)

<conditionalFormatting sqref="E3:E9">
  <cfRule type="cellIs" dxfId="0" priority="1" operator="greaterThan">
  <formula>0.5</formula> </cfRule>
</conditionalFormatting>

[scpike]

I'm interested in this feature. I started working on it in my fork (progress so far), but I have a couple of questions.

1. Generally speaking, I added ConditionalFormatting and ConditionalFormattingRule classes, and a add_conditional_formatting function to Worksheet. Is that in line with what you'd do?

2. I tried to add validators to check types of things against what's in the spec. Have you just been going off of MSDN? http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule.aspx

3. I got the conditionalFormatting blocks to generate correctly, but I can't get the styling piece added to the stylesheet. I'm not entirely sure what a dxfId is - it seems like it's the index into the <dxfs> block in the stylesheet. Is there any way for me to create styles that get put inside that block with the current style code? You have a @dxfs variable but 1) doesn't seem like it ever gets set and 2) The to_xml code wouldn't seem to generate sub-blocks of <dxf></dxf> from it.

As an example, here's what Excel on Mac generates (closing </cellStyles> tag just to show position)

</cellStyles><dxfs count="3"><dxf><font><color rgb="FF9C0006"/></font>
    <fill><patternFill><bgColor rgb="FFFFFFFF"/></patternFill></fill>
</dxf></dxfs>

[randym]

@scpike woot!
That is a big chunk of love!

RE: 1) Absolutely perfect.

2. I always use the Office Open XML spec
   http://dl.dropbox.com/u/52469356/ECMA-376.1.zip

3. You are quite correct that we have not implemented xdf yet. That is the missing piece!
   18.8.14 dxf (Formatting) in the spec.

It is basically a container node for
alignment (Alignment) (§18.8.1)
border (Border) (§18.8.4)
fill (Fill)(§18.8.20)
font (Font)(§18.8.22)
numFmt (Number Format)(§18.8.30)
protection (Protection Properties)(§18.8.33)

Models for all of those objects are already in the stylesheet directory, I just never got around to making a dxf class :(

The last step would be adding adding an option for add_style to mark the style as differential and return the dxfs index so when you create your conditional_formatting_rule, you have an dxfId.

In the mean time, please send in a pull request for your stuff!
I'll merge it in and add you to the contributors list so you can code directly against master.

[scpike]

I did some more work on this today, and Dxf elements now exist. I think all the commits got automatically added to the pull request I sent earlier.

I did a bunch of messing around with Styles#add_style so that styles for Xf elements continue to get put inside @numFmts, @fills, @fonts, etc. while Dxf styles don't (they only get put inline in the <dxf> chunks. It still passes all your tests and I added some for dxfs, but you should take a look at the changes there before you merge it in in case we want to do some re-factoring.

With the most recent commits I'm able to create .xlsx files with working conditional formatting (see examples/example_conditional_formatting.rb). We're still missing support for dataBar, colorScale, and iconSet child elements of ConditionalFormattingRule, which aren't that important to me.

[randym]

@scpike This is really great work mate. You now have full access to the repo so when you run into something you want, please don't hesitate to add it in. I am really impressed with the quality of your work. I've set you up with direct access to the repo. If you ever have any questions you can usually find me lurking in #axlsx (the ci environment for this gem reports there) or #travisci on freenode.

I just plugged in colorScale and will knock off dataBar and iconSet today as well so we can call it done (until someone reports a bug of course!)

That Style#add_style method does indeed need a bit of love - but I am so impressed with this I think I'll release it next week with a blog post! Let me know your twitter handle if you have one.

[randym]

It is all implemented - but we need some testing help!

After spending a few hours to build in icon set, data bar and color scale - only to find out that my crappy 2007 Mac version tells me very politely that it does not support those conditional formats. Any out there have a windows version handy to do some testing? No validation errors or fix errors so the data should be clean - just cant verify if it actually works...

[scpike]

@randym Thanks for the compliment, you really know who to make a guy want to contribute!

I have Excel for Mac 2011, which supports icon set, data bar, and color scale. They worked with no errors, but the Icon set showed all green lights. I modified IconSet#initial_value_objects to have three based on percentages, so our default now matches the example in 18.3.1.49, and got a much nicer looking thing.

[randym]

That is AWESOME!