Merge pull request #183 from straydogstudio/master

Added support for specifying between/notBetween formula in an array.

4 files changed, 20 insertions, 7 deletions

diff --git a/examples/conditional_formatting/example_conditional_formatting.rb b/examples/conditional_formatting/example_conditional_formatting.rb
index ab49d238..f5823ab4 100644
--- a/examples/conditional_formatting/example_conditional_formatting.rb
+++ b/examples/conditional_formatting/example_conditional_formatting.rb
@@ -11,8 +11,8 @@ percent = book.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE
 money = book.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER)
 
 # define the style for conditional formatting
-profitable = book.styles.add_style( :fg_color=>"FF428751",
-                                    :type => :dxf)
+profitable = book.styles.add_style( :fg_color => "FF428751", :type => :dxf )
+unprofitable = wb.styles.add_style( :fg_color => "FF0000", :type => :dxf )
 
 book.add_worksheet(:name => "Cell Is") do |ws|
 
@@ -27,6 +27,8 @@ book.add_worksheet(:name => "Cell Is") do |ws|
 
 # Apply conditional formatting to range B3:B100 in the worksheet
   ws.add_conditional_formatting("B3:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 })
+# Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween
+  sheet.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :priority => 1 })
 end
 
 book.add_worksheet(:name => "Color Scale") do |ws|
diff --git a/examples/example.rb b/examples/example.rb
index 87814b97..31706c3d 100755
--- a/examples/example.rb
+++ b/examples/example.rb
@@ -647,8 +647,8 @@ if examples.include? :conditional_formatting
   money = wb.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER)
 
   # define the style for conditional formatting
-  profitable = wb.styles.add_style( :fg_color=>"FF428751",
-                                   :type => :dxf)
+  profitable = wb.styles.add_style( :fg_color => "FF428751", :type => :dxf )
+  unprofitable = wb.styles.add_style( :fg_color => "FF0000", :type => :dxf )
 
   wb.add_worksheet(:name => "Conditional Cell Is") do |sheet|
 
@@ -663,6 +663,8 @@ if examples.include? :conditional_formatting
 
     # Apply conditional formatting to range B3:B100 in the worksheet
     sheet.add_conditional_formatting("B3:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 })
+    # Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween
+    sheet.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :priority => 1 })
   end
 
   wb.add_worksheet(:name => "Conditional Color Scale") do |sheet|
diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
index a0ce6a41..9db091be 100644
--- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
+++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
@@ -25,7 +25,7 @@ module Axlsx
     # @option options [Integer] stdDev The number of standard deviations above or below the average to match
     # @option options [Boolean] stopIfTrue Stop evaluating rules after this rule matches
     # @option options [Symbol]  timePeriod The time period in a date occuring... rule
-    # @option options [String] formula The formula to match against in i.e. an equal rule
+    # @option options [String] formula The formula to match against in i.e. an equal rule. Use a [minimum, maximum] array for cellIs between/notBetween conditionals.
     def initialize(options={})
       @color_scale = @data_bar = @icon_set = @formula = nil
       parse_options options
@@ -36,6 +36,8 @@ module Axlsx
                             :stopIfTrue, :timePeriod
 
     # Formula
+    # The formula or value to match against (e.g. 5 with an operator of :greaterThan to specify cell_value > 5).
+    # If the operator is :between or :notBetween, use an array to specify [minimum, maximum]
     # @return [String]
     attr_reader :formula
 
@@ -180,7 +182,7 @@ module Axlsx
     # @see timePeriod
     def timePeriod=(v); Axlsx::validate_time_period_type(v); @timePeriod = v end
     # @see formula
-    def formula=(v); Axlsx::validate_string(v); @formula = v end
+    def formula=(v); [*v].each {|x| Axlsx::validate_string(x) }; @formula = v end
 
     # @see color_scale
     def color_scale=(v)
@@ -208,7 +210,7 @@ module Axlsx
       str << '<cfRule '
       serialized_attributes str
       str << '>'
-      str << '<formula>' << self.formula << '</formula>' if @formula
+      str << '<formula>' << [*self.formula].join('</formula><formula>') << '</formula>' if @formula
       @color_scale.to_xml_string(str) if @color_scale && @type == :colorScale
       @data_bar.to_xml_string(str) if @data_bar && @type == :dataBar
       @icon_set.to_xml_string(str) if @icon_set && @type == :iconSet
diff --git a/test/workbook/worksheet/tc_conditional_formatting.rb b/test/workbook/worksheet/tc_conditional_formatting.rb
index 087fd40e..42e29fa6 100644
--- a/test/workbook/worksheet/tc_conditional_formatting.rb
+++ b/test/workbook/worksheet/tc_conditional_formatting.rb
@@ -130,6 +130,13 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']//xmlns:formula='0.5'")
   end
 
+  def test_multiple_formulas
+    @ws.add_conditional_formatting "B3:B3", { :type => :cellIs, :dxfId => 0, :priority => 1, :operator => :between, :formula => ["1","5"] }
+    doc = Nokogiri::XML.parse(@ws.to_xml_string)
+    assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='between']//xmlns:formula='1'")
+    assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='between']//xmlns:formula='5'")
+  end
+
   def test_sqref
     assert_raise(ArgumentError) { @cf.sqref = 10 }
     assert_nothing_raised { @cf.sqref = "A1:A1" }