adding in color scale for conditional formatting

8 files changed, 266 insertions, 38 deletions

diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index 14d34d36..555bc038 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -101,21 +101,27 @@ module Axlsx
   # thisMonth, lastMonth, nextMonth, thisWeek, lastWeek, nextWeek
   def self.validate_time_period_type(v)
     RestrictionValidator.validate :time_period_type, [:today, :yesterday, :tomorrow, :last7Days, :thisMonth, :lastMonth, :nextMonth, :thisWeek, :lastWeek, :nextWeek], v
-  
-    
+
+
   end
-  
+
   # Requires that the value is valid conditional formatting type.
   # valid types must be one of expression, cellIs, colorScale,
   # dataBar, iconSet, top10, uniqueValues, duplicateValues,
   # containsText, notContainsText, beginsWith, endsWith,
   # containsBlanks, notContainsBlanks, containsErrors,
-  # notContainsErrors, timePeriod, aboveAverage   
+  # notContainsErrors, timePeriod, aboveAverage
   # @param [Any] v The value validated
   def self.validate_conditional_formatting_type(v)
     RestrictionValidator.validate :conditional_formatting_type, [:expression, :cellIs, :colorScale, :dataBar, :iconSet, :top10, :uniqueValues, :duplicateValues, :containsText, :notContainsText, :beginsWith, :endsWith, :containsBlanks, :notContainsBlanks, :containsErrors, :notContainsErrors, :timePeriod, :aboveAverage], v
   end
 
+  # Requires thatt he value is a valid conditional formatting value object type.
+  # valid types must be one of num, percent, max, min, formula and percentile
+  def self.validate_conditional_formatting_value_object_type(v)
+    RestrictionValidator.validate :conditional_formatting_value_object_type, [:num, :percent, :max, :min, :formula, :percentile], v
+  end
+
   # Requires that the value is valid conditional formatting operator.
   # valid operators must be one of lessThan, lessThanOrEqual, equal,
   # notEqual, greaterThanOrEqual, greaterThan, between, notBetween,
@@ -124,7 +130,7 @@ module Axlsx
   def self.validate_conditional_formatting_operator(v)
     RestrictionValidator.validate :conditional_formatting_type, [:lessThan, :lessThanOrEqual, :equal, :notEqual, :greaterThanOrEqual, :greaterThan, :between, :notBetween, :containsText, :notContains, :beginsWith, :endsWith], v
   end
-  
+
   # Requires that the value is a gradient_type.
   # valid types are :linear and :path
   # @param [Any] v The value validated
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 2d406890..22407a84 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -4,8 +4,10 @@ module Axlsx
 require 'axlsx/workbook/worksheet/date_time_converter.rb'
 require 'axlsx/workbook/worksheet/cell.rb'
 require 'axlsx/workbook/worksheet/page_margins.rb'
+require 'axlsx/workbook/worksheet/cfvo.rb'
+require 'axlsx/workbook/worksheet/color_scale.rb'
 require 'axlsx/workbook/worksheet/conditional_formatting.rb'
-require 'axlsx/workbook/worksheet/conditional_formatting_rule.rb'    
+require 'axlsx/workbook/worksheet/conditional_formatting_rule.rb'
 require 'axlsx/workbook/worksheet/row.rb'
 require 'axlsx/workbook/worksheet/col.rb'
 require 'axlsx/workbook/worksheet/worksheet.rb'
diff --git a/lib/axlsx/workbook/worksheet/cfvo.rb b/lib/axlsx/workbook/worksheet/cfvo.rb
new file mode 100644
index 00000000..22cf95d9
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/cfvo.rb
@@ -0,0 +1,62 @@
+module Axlsx
+  # Conditional Format Value Object
+  # Describes the values of the interpolation points in a gradient scale. This object is used by ColorScale, DataBar and IconSet classes
+  #
+  # @note The recommended way to manage these rules is via Worksheet#add_conditional_formatting
+  # @see Worksheet#add_conditional_formatting
+  # @see ConditionalFormattingRule#initialize
+  #
+  class Cfvo
+
+    # Type (ST_CfvoType)
+    # The type of this conditional formatting value object. options are num, percent, max, min, formula and percentile
+    # @return [Symbol]
+    attr_reader :type
+
+
+    # Type (xsd:boolean)
+    # For icon sets, determines whether this threshold value uses the greater than or equal to operator. 0 indicates 'greater than' is used instead of 'greater than or equal to'.
+    # The default value is true
+    # @return [Boolean]
+    attr_reader :gte
+
+
+    # Type (ST_Xstring)
+    # The value of the conditional formatting object
+    # This library will accept any value so long as it supports to_s
+    attr_reader :val
+
+    # Creates a new Cfvo object
+    # @option options [Symbol] type The type of conditional formatting value object
+    # @option options [Boolean]  gte threshold value usage indicator
+    # @option options [String] val The value of the conditional formatting object
+    def initialize(options={})
+      @gte = true
+      options.each do |o|
+        self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
+      end
+    end
+
+    # @see type
+    def type=(v); Axlsx::validate_conditional_formatting_value_object_type(v); @type = v end
+
+    # @see gte
+    def gte=(v); Axlsx::validate_boolean(v); @gte = v end
+
+    # @see val
+    def val=(v)
+      raise ArgumentError, "#{v.inspect} must respond to to_s" unless v.respond_to?(:to_s)
+      @val = v.to_s
+    end
+
+    # serialize the Csvo object
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      str << '<cfvo '
+      str << instance_values.map { |key, value| '' << key << '="' << value.to_s << '"' }.join(' ')
+      str << ' />'
+    end
+
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/color_scale.rb b/lib/axlsx/workbook/worksheet/color_scale.rb
new file mode 100644
index 00000000..c2d3a5ac
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/color_scale.rb
@@ -0,0 +1,73 @@
+module Axlsx
+  # Conditional Format Rule color scale object
+  # Describes a gradated color scale in this conditional formatting rule.
+
+  # @note The recommended way to manage these rules is via Worksheet#add_conditional_formatting
+  # @see Worksheet#add_conditional_formatting
+  # @see ConditionalFormattingRule#initialize
+  class ColorScale
+
+    # A simple typed list of cfvos
+    # @return [SimpleTypedList]
+    # @see Cfvo
+    attr_reader :value_objects
+
+    # A simple types list of colors
+    # @return [SimpleTypedList]
+    # @see Color
+    attr_reader :colors
+
+    # creates a new ColorScale object.
+    # This method will yield it self so you can alter the properites of the defauls conditional formating value object (cfvo and colors
+    # Two value objects and two colors are created on initialization and cannot be deleted.
+    # @see Cfvo
+    # @see Color
+    def initialize
+      initialize_value_objects
+      initialize_colors
+      yield self if block_given?
+    end
+
+    # adds a new cfvo / color pair to the color scale and returns a hash containing
+    # a reference to the newly created cfvo and color objects so you can alter the default properties.
+    # @return [Hash] a hash with :cfvo and :color keys referencing the newly added objects.
+    def add(options={})
+      @value_objects << Cfvo.new(:type => options[:type] || :min, :val => options[:val] || 0)
+      @colors << Color.new(:rgb => options[:color] || "FF000000")
+      {:cfvo => @value_objects.last, :color => @colors.last}
+    end
+
+
+    # removes the cfvo and color pair at the index specified.
+    # @param [Integer] index The index of the cfvo and color object to delete
+    # @note you cannot remove the first two cfvo and color pairs
+    def delete_at(index=2)
+      @value_objects.delete_at index
+      @colors.delete_at index
+    end
+
+    def to_xml_string(str = '')
+      str << '<colorScale>'
+      @value_objects.each { |cfvo| cfvo.to_xml_string(str) }
+      @colors.each { |color| color.to_xml_string(str) }
+      str << '</colorScale>'
+    end
+
+    protected
+
+    # creates the initial cfvo objects
+    def initialize_value_objects
+      @value_objects = SimpleTypedList.new Cfvo
+      @value_objects.concat [Cfvo.new(:type => :min, :val => 0), Cfvo.new(:type => :max, :val => 0)]
+      @value_objects.lock
+    end
+
+    # creates the initial color objects
+    def initialize_colors
+      @colors = SimpleTypedList.new Color
+      @colors.concat [Color.new(:rgb => "FFFF0000"), Color.new(:rgb => "FF0000FF")]
+      @colors.lock
+    end
+
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
index e7cba96f..3ccb6174 100644
--- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
+++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
@@ -1,3 +1,4 @@
+# -*- coding: utf-8 -*-
 module Axlsx
   # Conditional formatting rules specify formulas whose evaluations
   # format cells
@@ -6,18 +7,18 @@ module Axlsx
   # @see Worksheet#add_conditional_formatting
   # @see ConditionalFormattingRule#initialize
   class ConditionalFormattingRule
-    CHILD_ELEMENTS = [:formula]
-    
+    CHILD_ELEMENTS = [:formula, :color_scale]
+
     # Formula
     # @return [String]
     attr_reader :formula
-    
+
     # Type (ST_CfType)
     # options are expression, cellIs, colorScale, dataBar, iconSet,
     # top10, uniqueValues, duplicateValues, containsText,
     # notContainsText, beginsWith, endsWith, containsBlanks,
     # notContainsBlanks, containsErrors, notContainsErrors,
-    # timePeriod, aboveAverage 
+    # timePeriod, aboveAverage
     # @return [Symbol]
     attr_reader :type
 
@@ -48,7 +49,7 @@ module Axlsx
     # rule. This attribute is ignored if type is not equal to cellIs
     #
     # Operator must be one of lessThan, lessThanOrEqual, equal,
-    # notEqual, greaterThanOrEqual, greaterThan, between, notBetween, 
+    # notEqual, greaterThanOrEqual, greaterThan, between, notBetween,
     # containsText, notContains, beginsWith, endsWith
     # @return [Symbol]
     attr_reader :operator
@@ -66,7 +67,7 @@ module Axlsx
     # rule.
     # @return [String]
     attr_reader :text
-    
+
     # percent (Top 10 Percent)
     # indicates whether a "top/bottom n" rule is a "top/bottom n
     # percent" rule. This attribute is ignored if type is not equal to
@@ -77,9 +78,9 @@ module Axlsx
     # rank (Rank)
     # The value of "n" in a "top/bottom n" conditional formatting
     # rule. This attribute is ignored if type is not equal to top10.
-    # @return [Integer] 
+    # @return [Integer]
     attr_reader :rank
-    
+
     # stdDev (StdDev)
     # The number of standard deviations to include above or below the
     # average in the conditional formatting rule. This attribute is
@@ -89,13 +90,13 @@ module Axlsx
     # rule.
     # @return [Integer]
     attr_reader :stdDev
-    
+
     # stopIfTrue (Stop If True)
     # If this flag is '1', no rules with lower priority shall be
     # applied over this rule, when this rule evaluates to true.
     # @return [Boolean]
     attr_reader :stopIfTrue
-    
+
     # timePeriod (Time Period)
     # The applicable time period in a "date occurring…" conditional
     # formatting rule. This attribute is ignored if type is not equal
@@ -103,13 +104,21 @@ module Axlsx
     # Valid types are today, yesterday, tomorrow, last7Days,
     # thisMonth, lastMonth, nextMonth, thisWeek, lastWeek, nextWeek
     attr_reader :timePeriod
-    
+
+
+    # colorScale (Color Scale)
+    # The color scale to apply to this conditional formatting
+    # @return [ColorScale]
+    def color_scale
+      @color_scale ||= ColorScale.new
+    end
+
     # Creates a new Conditional Formatting Rule object
     # @option options [Symbol] type The type of this formatting rule
     # @option options [Boolean] aboveAverage This is an aboveAverage rule
     # @option options [Boolean] bottom This is a bottom N rule.
     # @option options [Integer] dxfId The formatting id to apply to matches
-    # @option options [Boolean] equalAverage Is the aboveAverage or belowAverage rule inclusive 
+    # @option options [Boolean] equalAverage Is the aboveAverage or belowAverage rule inclusive
     # @option options [Integer] priority The priority of the rule, 1 is highest
     # @option options [Symbol] operator Which operator to apply
     # @option options [String] text The value to apply a text operator against
@@ -127,11 +136,11 @@ module Axlsx
 
     # @see type
     def type=(v); Axlsx::validate_conditional_formatting_type(v); @type = v end
-    # @see aboveAverage    
+    # @see aboveAverage
     def aboveAverage=(v); Axlsx::validate_boolean(v); @aboveAverage = v end
     # @see bottom
     def bottom=(v); Axlsx::validate_boolean(v); @bottom = v end
-    # @see dxfId    
+    # @see dxfId
     def dxfId=(v); Axlsx::validate_unsigned_numeric(v); @dxfId = v end
     # @see equalAverage
     def equalAverage=(v); Axlsx::validate_boolean(v); @equalAverage = v end
@@ -154,6 +163,11 @@ module Axlsx
     # @see formula
     def formula=(v); Axlsx::validate_string(v); @formula = v end
 
+    # @see color_scale
+    def color_scale=(v)
+      Axlsx::DataTypeValidator.validate 'conditional_formatting_rule.color scale', ColorScale, v
+      @color_scale = v
+    end
     # Serializes the conditional formatting rule
     # @param [String] str
     # @return [String]
@@ -161,9 +175,8 @@ module Axlsx
       str << '<cfRule '
       str << instance_values.map { |key, value| '' << key << '="' << value.to_s << '"' unless CHILD_ELEMENTS.include?(key.to_sym) }.join(' ')
       str << '>'
-      CHILD_ELEMENTS.each do |el|
-        str << "<#{el}>" << self.send(el) << "</#{el}>" if self.send(el)
-      end
+      str << '<formula>' << self.formula << '</formula>' if @formula
+      @color_scale.to_xml_string(str) if @color_scale
       str << '</cfRule>'
     end
   end
diff --git a/test/workbook/worksheet/tc_cfvo.rb b/test/workbook/worksheet/tc_cfvo.rb
new file mode 100644
index 00000000..d846f57d
--- /dev/null
+++ b/test/workbook/worksheet/tc_cfvo.rb
@@ -0,0 +1,31 @@
+require 'tc_helper.rb'
+
+class TestCfvo < Test::Unit::TestCase
+  def setup
+    @cfvo = Axlsx::Cfvo.new(:val => "0", :type => :min)
+  end
+
+  def test_val
+    assert_nothing_raised { @cfvo.val = "abc" }
+    assert_equal(@cfvo.val, "abc")
+  end
+
+  def test_type
+    assert_raise(ArgumentError) { @cfvo.type = :invalid_type }
+    assert_nothing_raised { @cfvo.type = :max }
+    assert_equal(@cfvo.type, :max)
+  end
+
+  def test_gte
+    assert_raise(ArgumentError) { @cfvo.gte = :bob }
+    assert_equal(@cfvo.gte, true)
+    assert_nothing_raised { @cfvo.gte = false }
+    assert_equal(@cfvo.gte, false)
+  end
+
+  def test_to_xml_string
+    doc = Nokogiri::XML.parse(@cfvo.to_xml_string)
+    assert doc.xpath(".//cfvo[@type='min'][@val=0][@gte=true]")
+  end
+
+end
diff --git a/test/workbook/worksheet/tc_color_scale.rb b/test/workbook/worksheet/tc_color_scale.rb
new file mode 100644
index 00000000..d7e66fcf
--- /dev/null
+++ b/test/workbook/worksheet/tc_color_scale.rb
@@ -0,0 +1,29 @@
+require 'tc_helper.rb'
+
+class TestColorScale < Test::Unit::TestCase
+  def setup
+    @color_scale = Axlsx::ColorScale.new
+  end
+
+  def test_add
+    @color_scale.add :type => :max, :val => 5, :color => "FFDEDEDE"
+    assert_equal(@color_scale.value_objects.size,3)
+    assert_equal(@color_scale.colors.size,3)
+  end
+
+  def test_delete_at
+    assert_raise(ArgumentError, "minimum two are protected") { @color_scale.delete_at 0 }
+    assert_raise(ArgumentError, "minimum two are protected") { @color_scale.delete_at 1 }
+    @color_scale.add :type => :max, :val => 5, :color => "FFDEDEDE"
+    assert_nothing_raised {@color_scale.delete_at 2}
+    assert_equal(@color_scale.value_objects.size,2)
+    assert_equal(@color_scale.colors.size,2)
+  end
+
+  def test_to_xml_string
+    doc = Nokogiri::XML.parse(@color_scale.to_xml_string)
+    assert_equal(doc.xpath(".//colorScale//cfvo").size, 2)
+    assert_equal(doc.xpath(".//colorScale//color").size, 2)
+  end
+
+end
diff --git a/test/workbook/worksheet/tc_conditional_formatting.rb b/test/workbook/worksheet/tc_conditional_formatting.rb
index 6feede10..2287c017 100644
--- a/test/workbook/worksheet/tc_conditional_formatting.rb
+++ b/test/workbook/worksheet/tc_conditional_formatting.rb
@@ -15,16 +15,27 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert_equal("AA1:AB100", optioned.sqref)
     assert_equal([1, 2], optioned.rules)
   end
-  
+
   def test_add_as_hash
+
+    color_scale = Axlsx::ColorScale.new do |cs|
+      cs.colors.first.rgb = "FFDFDFDF"
+      cs.colors.last.rgb = "FF00FF00"
+      cs.value_objects.first.type = :percentile
+      cs.value_objects.first.val = 5
+    end
+
     cfs = @ws.add_conditional_formatting( "B2:B2", [{ :type => :containsText, :text => "TRUE",
                                                       :dxfId => 0, :priority => 1,
-                                                      :formula => 'NOT(ISERROR(SEARCH("FALSE",AB1)))' }])
+                                                      :formula => 'NOT(ISERROR(SEARCH("FALSE",AB1)))',
+                                                      :color_scale => color_scale}])
     doc = Nokogiri::XML.parse(cfs.last.to_xml_string)
     assert_equal(1, doc.xpath(".//conditionalFormatting[@sqref='B2:B2']//cfRule[@type='containsText'][@dxfId=0][@priority=1]").size)
     assert doc.xpath(".//conditionalFormatting//cfRule[@type='containsText'][@dxfId=0][@priority=1]//formula='NOT(ISERROR(SEARCH(\"FALSE\",AB1)))'")
+    assert_equal(doc.xpath(".//conditionalFormatting//cfRule//colorScale//cfvo").size, 2)
+    assert_equal(doc.xpath(".//conditionalFormatting//cfRule//colorScale//color").size, 2)
   end
-  
+
   def test_single_rule
     doc = Nokogiri::XML.parse(@cf.to_xml_string)
     assert_equal(1, doc.xpath(".//conditionalFormatting//cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']").size)
@@ -41,32 +52,32 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert_equal(1, doc.xpath(".//conditionalFormatting//cfRule[@type='cellIs'][@aboveAverage='false'][@bottom='false'][@dxfId=0][@equalAverage='false'][@priority=2][@operator='lessThan'][@text=''][@percent='false'][@rank=0][@stdDev=1][@stopIfTrue='true'][@timePeriod='today']").size)
     assert doc.xpath(".//conditionalFormatting//cfRule[@type='cellIs'][@aboveAverage='false'][@bottom='false'][@dxfId=0][@equalAverage='false'][@priority=2][@operator='lessThan'][@text=''][@percent='false'][@rank=0][@stdDev=1][@stopIfTrue='true'][@timePeriod='today']//formula=0.0")
   end
-  
+
   def test_to_xml
     doc = Nokogiri::XML.parse(@ws.to_xml_string)
     assert_equal(1, doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']").size)
     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_formats
     @ws.add_conditional_formatting "B3:B3", { :type => :cellIs, :dxfId => 0, :priority => 1, :operator => :greaterThan, :formula => "1" }
     doc = Nokogiri::XML.parse(@ws.to_xml_string)
     assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']//xmlns:formula='1'")
-    assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']//xmlns:formula='0.5'")    
+    assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']//xmlns:formula='0.5'")
   end
-  
+
   def test_sqref
     assert_raise(ArgumentError) { @cf.sqref = 10 }
     assert_nothing_raised { @cf.sqref = "A1:A1" }
     assert_equal(@cf.sqref, "A1:A1")
   end
-  
+
   def test_type
     assert_raise(ArgumentError) { @cfr.type = "illegal" }
     assert_nothing_raised { @cfr.type = :containsBlanks }
     assert_equal(@cfr.type, :containsBlanks)
   end
-  
+
   def test_above_average
     assert_raise(ArgumentError) { @cfr.aboveAverage = "illegal" }
     assert_nothing_raised { @cfr.aboveAverage = true }
@@ -78,7 +89,7 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert_nothing_raised { @cfr.equalAverage = true }
     assert_equal(@cfr.equalAverage, true)
   end
-  
+
   def test_bottom
     assert_raise(ArgumentError) { @cfr.bottom = "illegal" }
     assert_nothing_raised { @cfr.bottom = true }
@@ -90,7 +101,7 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert_nothing_raised { @cfr.operator = :notBetween }
     assert_equal(@cfr.operator, :notBetween)
   end
-  
+
   def test_dxf_id
     assert_raise(ArgumentError) { @cfr.dxfId = "illegal" }
     assert_nothing_raised { @cfr.dxfId = 1 }
@@ -102,25 +113,25 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert_nothing_raised { @cfr.priority = 1 }
     assert_equal(@cfr.priority, 1)
   end
-  
+
   def test_text
     assert_raise(ArgumentError) { @cfr.text = 1.0 }
     assert_nothing_raised { @cfr.text = "testing" }
     assert_equal(@cfr.text, "testing")
   end
-  
+
   def test_percent
     assert_raise(ArgumentError) { @cfr.percent = "10%" } #WRONG!
     assert_nothing_raised { @cfr.percent = true }
     assert_equal(@cfr.percent, true)
   end
-  
+
   def test_rank
     assert_raise(ArgumentError) { @cfr.rank = -1 }
     assert_nothing_raised { @cfr.rank = 1 }
     assert_equal(@cfr.rank, 1)
   end
-  
+
   def test_std_dev
     assert_raise(ArgumentError) { @cfr.stdDev = -1 }
     assert_nothing_raised { @cfr.stdDev = 1 }
@@ -138,4 +149,5 @@ class TestConditionalFormatting < Test::Unit::TestCase
     assert_nothing_raised { @cfr.timePeriod = :today }
     assert_equal(@cfr.timePeriod, :today)
   end
+
 end