Merge pull request #83 from scpike/master

Support for some of the conditional formatting spec

7 files changed, 463 insertions, 36 deletions

diff --git a/lib/axlsx/stylesheet/dxf.rb b/lib/axlsx/stylesheet/dxf.rb
new file mode 100644
index 00000000..ccd4cd95
--- /dev/null
+++ b/lib/axlsx/stylesheet/dxf.rb
@@ -0,0 +1,77 @@
+# encoding: UTF-8
+module Axlsx
+  # The Dxf class defines an incremental formatting record for use in Styles. The recommended way to manage styles for your workbook is with Styles#add_style
+  # @see Styles#add_style
+  class Dxf
+    # The order in which the child elements is put in the XML seems to
+    # be important for Excel
+    CHILD_ELEMENTS = [:font, :numFmt, :fill, :alignment, :border, :protection]
+    #does not support extList (ExtensionList)
+
+    # The cell alignment for this style
+    # @return [CellAlignment]
+    # @see CellAlignment
+    attr_reader :alignment
+
+    # The cell protection for this style
+    # @return [CellProtection]
+    # @see CellProtection
+    attr_reader :protection
+
+    # the child NumFmt to be used to this style
+    # @return [NumFmt]
+    attr_reader :numFmt
+
+    # the child font to be used for this style
+    # @return [Font]
+    attr_reader :font
+
+    # the child fill to be used in this style
+    # @return [Fill]
+    attr_reader :fill
+
+    # the border to be used in this style
+    # @return [Border]
+    attr_reader :border
+
+    # Creates a new Xf object
+    # @option options [Border] border
+    # @option options [NumFmt] numFmt
+    # @option options [Fill] fill
+    # @option options [Font] font
+    # @option options [CellAlignment] alignment
+    # @option options [CellProtection] protection
+    def initialize(options={})
+      options.each do |o|
+        self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
+      end
+    end
+
+    # @see Dxf#alignment
+    def alignment=(v) DataTypeValidator.validate "Dxf.alignment", CellAlignment, v; @alignment = v end
+    # @see protection
+    def protection=(v) DataTypeValidator.validate "Dxf.protection", CellProtection, v; @protection = v end
+    # @see numFmt
+    def numFmt=(v) DataTypeValidator.validate "Dxf.numFmt", NumFmt, v; @numFmt = v end
+    # @see font
+    def font=(v) DataTypeValidator.validate "Dxf.font", Font, v; @font = v end
+    # @see border
+    def border=(v) DataTypeValidator.validate "Dxf.border", Border, v; @border = v end
+    # @see fill
+    def fill=(v) DataTypeValidator.validate "Dxf.fill", Fill, v; @fill = v end
+
+    # Serializes the object
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      str << '<dxf>'
+      # Dxf elements have no attributes. All of the instance variables
+      # are child elements.
+      CHILD_ELEMENTS.each do |element|
+        self.send(element).to_xml_string(str) if self.send(element)
+      end
+      str << '</dxf>'
+    end
+
+  end
+end
diff --git a/lib/axlsx/stylesheet/styles.rb b/lib/axlsx/stylesheet/styles.rb
index 5a890033..499657ca 100644
--- a/lib/axlsx/stylesheet/styles.rb
+++ b/lib/axlsx/stylesheet/styles.rb
@@ -14,6 +14,7 @@ module Axlsx
   require 'axlsx/stylesheet/table_style.rb'
   require 'axlsx/stylesheet/table_styles.rb'
   require 'axlsx/stylesheet/table_style_element.rb'
+  require 'axlsx/stylesheet/dxf.rb'  
   require 'axlsx/stylesheet/xf.rb'
   require 'axlsx/stylesheet/cell_protection.rb'
 
@@ -137,6 +138,7 @@ module Axlsx
     # @option options [String] bg_color The background color to apply to the cell
     # @option options [Boolean] hidden Indicates if the cell should be hidden
     # @option options [Boolean] locked Indicates if the cell should be locked
+    # @option options [Symbol] type What type of style is this. Options are [:dxf, :xf]. :xf is default
     # @option options [Hash] alignment A hash defining any of the attributes used in CellAlignment
     # @see CellAlignment
     #
@@ -189,67 +191,112 @@ module Axlsx
     #   ws.add_row :values => ["Q4", 2000, 20], :style=>[title, currency, percent]
     #   f = File.open('example_you_got_style.xlsx', 'w')
     #   p.serialize(f)
+    #
+    # @example Differential styling
+    #   # Differential styles apply on top of cell styles. Used in Conditional Formatting. Must specify :type => :dxf, and you can't use :num_fmt.
+    #   require "rubygems" # if that is your preferred way to manage gems!
+    #   require "axlsx"
+    #
+    #   p = Axlsx::Package.new
+    #   wb = p.workbook
+    #   ws = wb.add_worksheet
+    #
+    #   # define your styles
+    #   profitable = wb.styles.add_style(:bg_color => "FFFF0000",
+    #                              :fg_color=>"#FF000000",
+    #                              :type => :dxf)
+    # 
+    #   ws.add_row :values => ["Genreated At:", Time.now], :styles=>[nil, date_time]
+    #   ws.add_row :values => ["Previous Year Quarterly Profits (JPY)"], :style=>title
+    #   ws.add_row :values => ["Quarter", "Profit", "% of Total"], :style=>title
+    #   ws.add_row :values => ["Q1", 4000, 40], :style=>[title, currency, percent]
+    #   ws.add_row :values => ["Q2", 3000, 30], :style=>[title, currency, percent]
+    #   ws.add_row :values => ["Q3", 1000, 10], :style=>[title, currency, percent]
+    #   ws.add_row :values => ["Q4", 2000, 20], :style=>[title, currency, percent]
+    # 
+    #   ws.add_conditional_formatting("A1:A7", { :type => :cellIs, :operator => :greaterThan, :formula => "2000", :dxfId => profitable, :priority => 1 })
+    #   f = File.open('example_differential_styling', 'w')
+    #   p.serialize(f)
+    #
     def add_style(options={})
+      # Default to :xf
+      options[:type] ||= :xf
+      raise ArgumentError, "Type must be one of [:xf, :dxf]" unless [:xf, :dxf].include?(options[:type] )
+      
+      numFmt = if options[:format_code]
+                 n = @numFmts.map{ |f| f.numFmtId }.max + 1
+                 NumFmt.new(:numFmtId => n, :formatCode=> options[:format_code])                   
+               elsif options[:type] == :xf
+                 options[:num_fmt] || 0
+               elsif options[:type] == :dxf and options[:num_fmt]
+                 raise ArgumentError, "Can't use :num_fmt with :dxf"
+               end
+      
+      border = options[:border]
 
-      numFmtId = if options[:format_code]
-                   n = @numFmts.map{ |f| f.numFmtId }.max + 1
-                   numFmts << NumFmt.new(:numFmtId => n, :formatCode=> options[:format_code])
-                   n
-                 else
-                   options[:num_fmt] || 0
-                 end
-
-      borderId = options[:border] || 0
-
-      if borderId.is_a?(Hash)
-        raise ArgumentError, "border hash definitions must include both style and color" unless borderId.keys.include?(:style) && borderId.keys.include?(:color)
+      if border.is_a?(Hash)
+        raise ArgumentError, "border hash definitions must include both style and color" unless border.keys.include?(:style) && border.keys.include?(:color)
 
-        s = borderId[:style]
-        c = borderId[:color]
-        edges = borderId[:edges] || [:left, :right, :top, :bottom]
+        s = border[:style]
+        c = border[:color]
+        edges = border[:edges] || [:left, :right, :top, :bottom]
 
         border = Border.new
         edges.each {|pr| border.prs << BorderPr.new(:name => pr, :style=>s, :color => Color.new(:rgb => c))}
-        borderId = self.borders << border
+      elsif border.is_a? Integer
+        raise ArgumentError, "Must pass border options directly if specifying dxf" if options[:type] == :dxf
+        raise ArgumentError, "Invalid borderId" unless border < borders.size
       end
 
-      raise ArgumentError, "Invalid borderId" unless borderId < borders.size
-
       fill = if options[:bg_color]
                color = Color.new(:rgb=>options[:bg_color])
                pattern = PatternFill.new(:patternType =>:solid, :fgColor=>color)
-               fills << Fill.new(pattern)
-             else
-               0
+               Fill.new(pattern)
              end
 
-      fontId = if (options.values_at(:fg_color, :sz, :b, :i, :u, :strike, :outline, :shadow, :charset, :family, :font_name).length)
+      font = if (options.values_at(:fg_color, :sz, :b, :i, :u, :strike, :outline, :shadow, :charset, :family, :font_name).length)
                  font = Font.new()
                  [:b, :i, :u, :strike, :outline, :shadow, :charset, :family, :sz].each { |k| font.send("#{k}=", options[k]) unless options[k].nil? }
                  font.color = Color.new(:rgb => options[:fg_color]) unless options[:fg_color].nil?
                  font.name = options[:font_name] unless options[:font_name].nil?
-                 fonts << font
-               else
-                 0
+                 font
                end
 
       applyProtection = (options[:hidden] || options[:locked]) ? 1 : 0
 
-      xf = Xf.new(:fillId => fill, :fontId=>fontId, :applyFill=>1, :applyFont=>1, :numFmtId=>numFmtId, :borderId=>borderId, :applyProtection=>applyProtection)
-
-      xf.applyNumberFormat = true if xf.numFmtId > 0
-      xf.applyBorder = true if borderId > 0
-
+      if options[:type] == :dxf
+        style = Dxf.new
+        style.fill = fill if fill
+        style.font = font if font
+        style.numFmt = numFmt if numFmt
+        style.border = border if border
+      else
+        # Only add styles if we're adding to Xf. They're embedded inside the Dxf pieces directly
+        fontId = font ? fonts << font : 0
+        fillId = fill ? fills << fill : 0
+        # Default to borderId = 0 rather than no border
+        border ||= 0
+        borderId = border.is_a?(Border) ? borders << border : border
+        numFmtId = numFmt.is_a?(NumFmt) ? numFmts << numFmt : numFmt 
+        style = Xf.new(:fillId=>fillId, :fontId=>fontId, :applyFill=>1, :applyFont=>1, :numFmtId=>numFmtId, :borderId=>borderId, :applyProtection=>applyProtection)
+        style.applyNumberFormat = true if style.numFmtId > 0
+        style.applyBorder = true if borderId > 0
+      end
+        
       if options[:alignment]
-        xf.alignment = CellAlignment.new(options[:alignment])
-        xf.applyAlignment = true
+        style.alignment = CellAlignment.new(options[:alignment])
+        style.applyAlignment = true if style.is_a? Xf
       end
 
       if applyProtection
-        xf.protection = CellProtection.new(options)
+        style.protection = CellProtection.new(options)
       end
 
-      cellXfs << xf
+      if style.is_a? Dxf
+        dxfs << style
+      else
+        cellXfs << style if style.is_a? Xf
+      end
     end
 
     # Serializes the object
@@ -306,7 +353,7 @@ module Axlsx
       @cellXfs << Xf.new(:borderId=>0, :xfId=>0, :numFmtId=>14, :fontId=>0, :fillId=>0, :applyNumberFormat=>1)
       @cellXfs.lock
 
-      @dxfs = SimpleTypedList.new(Xf, "dxfs"); @dxfs.lock
+      @dxfs = SimpleTypedList.new(Dxf, "dxfs"); @dxfs.lock
       @tableStyles = TableStyles.new(:defaultTableStyle => "TableStyleMedium9", :defaultPivotStyle => "PivotStyleLight16"); @tableStyles.lock
     end
   end
diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index 54a0aeb4..14d34d36 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -96,6 +96,35 @@ module Axlsx
        :darkTrellis, :lightHorizontal, :lightVertical, :lightDown, :lightUp, :lightGrid, :lightTrellis, :gray125, :gray0625], v
   end
 
+  # Requires that the value is one of the ST_TimePeriod types
+  # valid time period types are today, yesterday, tomorrow, last7Days,
+  # 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   
+  # @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 that the value is valid conditional formatting operator.
+  # valid operators must be one of lessThan, lessThanOrEqual, equal,
+  # notEqual, greaterThanOrEqual, greaterThan, between, notBetween,
+  # containsText, notContains, beginsWith, endsWith
+  # @param [Any] v The value validated
+  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 ce4e161b..2d406890 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -4,6 +4,8 @@ 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/conditional_formatting.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/conditional_formatting.rb b/lib/axlsx/workbook/worksheet/conditional_formatting.rb
new file mode 100644
index 00000000..13f6183d
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/conditional_formatting.rb
@@ -0,0 +1,82 @@
+module Axlsx
+  # Conditional formatting allows styling of ranges based on functions
+  #
+  # @note The recommended way to manage conditional formatting is via Worksheet#add_conditional_formatting
+  # @see Worksheet#add_conditional_formatting
+  # @see ConditionalFormattingRule  
+  class ConditionalFormatting
+
+    # Range over which the formatting is applied, in "A1:B2" format
+    # @return [String]
+    attr_reader :sqref
+
+    # Rules to apply the formatting to. Can be either a hash of
+    # options to create a {ConditionalFormattingRule}, an array of hashes
+    # for multiple ConditionalFormattingRules, or an array of already
+    # created ConditionalFormattingRules.
+    # @see ConditionalFormattingRule#initialize
+    # @return [Array]
+    attr_reader :rules
+    
+    # Creates a new {ConditionalFormatting} object
+    # @option options [Array] rules The rules to apply
+    # @option options [String] sqref The range to apply the rules to
+    def initialize(options={})
+      @rules = []
+      options.each do |o|
+        self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}="
+      end
+    end
+
+    # Add Conditional Formatting Rules to this object. Rules can either
+    # be already created {ConditionalFormattingRule} elements or
+    # hashes of options for automatic creation.  If rules is a hash
+    # instead of an array, assume only one rule being added.
+    #
+    # @example This would apply formatting "1" to cells > 20, and formatting "2" to cells < 1
+    #        conditional_formatting.add_rules [
+    #            { :type => :cellIs, :operator => :greaterThan, :formula => "20", :dxfId => 1, :priority=> 1 },
+    #            { :type => :cellIs, :operator => :lessThan, :formula => "10", :dxfId => 2, :priority=> 2 } ]    
+    #
+    # @param [Array|Hash] rules the rules to apply, can be just one in hash form
+    # @see ConditionalFormattingRule#initialize    
+    def add_rules(rules)
+      rules = [rules] if rules.is_a? Hash
+      conditional_rules = rules.each do |rule|
+        add_rule rule
+      end
+    end
+
+    # Add a ConditionalFormattingRule. If a hash of options is passed
+    # in create a rule on the fly.
+    # @param [ConditionalFormattingRule|Hash] rule A rule to use, or the options necessary to create one.
+    # @see ConditionalFormattingRule#initialize
+    def add_rule(rule)
+      if rule.is_a? Axlsx::ConditionalFormattingRule
+        @rules << rule
+      elsif rule.is_a? Hash
+        @rules << ConditionalFormattingRule.new(rule)
+      end
+    end
+    
+    # @see rules
+    def rules=(v); @rules = v end
+    # @see sqref
+    def sqref=(v); Axlsx::validate_string(v); @sqref = v end
+
+    # Serializes the conditional formatting element
+    # @example Conditional Formatting XML looks like:
+    #    <conditionalFormatting sqref="E3:E9">
+    #        <cfRule type="cellIs" dxfId="0" priority="1" operator="greaterThan">
+    #             <formula>0.5</formula> 
+    #        </cfRule>
+    #    </conditionalFormatting>
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      str << '<conditionalFormatting sqref="' << sqref << '">'
+      str << rules.collect{ |rule| rule.to_xml_string }.join(' ')
+      str << '</conditionalFormatting>'
+    end
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
new file mode 100644
index 00000000..e7cba96f
--- /dev/null
+++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb
@@ -0,0 +1,170 @@
+module Axlsx
+  # Conditional formatting rules specify formulas whose evaluations
+  # format cells
+  #
+  # @note The recommended way to manage these rules is via Worksheet#add_conditional_formatting
+  # @see Worksheet#add_conditional_formatting
+  # @see ConditionalFormattingRule#initialize
+  class ConditionalFormattingRule
+    CHILD_ELEMENTS = [:formula]
+    
+    # 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 
+    # @return [Symbol]
+    attr_reader :type
+
+    # Above average rule
+    # Indicates whether the rule is an "above average" rule. True
+    # indicates 'above average'. This attribute is ignored if type is
+    # not equal to aboveAverage.
+    # @return [Boolean]
+    attr_reader :aboveAverage
+
+    # Bottom N rule
+    # @return [Boolean]
+    attr_reader :bottom
+
+    # Differential Formatting Id
+    # @return [Integer]
+    attr_reader :dxfId
+
+    # Equal Average
+    # Flag indicating whether the 'aboveAverage' and 'belowAverage'
+    # criteria is inclusive of the average itself, or exclusive of
+    # that value.
+    # @return [Boolean]
+    attr_reader :equalAverage
+
+    # Operator
+    # The operator in a "cell value is" conditional formatting
+    # 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, 
+    # containsText, notContains, beginsWith, endsWith
+    # @return [Symbol]
+    attr_reader :operator
+
+    # Priority
+    # The priority of this conditional formatting rule. This value is
+    # used to determine which format should be evaluated and
+    # rendered. Lower numeric values are higher priority than higher
+    # numeric values, where '1' is the highest priority.
+    # @return [Integer]
+    attr_reader :priority
+
+    # Text
+    # used in a "text contains" conditional formatting
+    # 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
+    # top10.
+    # @return [Boolean]
+    attr_reader :percent
+
+    # 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] 
+    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
+    # ignored if type is not equal to aboveAverage. If a value is
+    # present for stdDev and the rule type = aboveAverage, then this
+    # rule is automatically an "above or below N standard deviations"
+    # 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
+    # to timePeriod.
+    # Valid types are today, yesterday, tomorrow, last7Days,
+    # thisMonth, lastMonth, nextMonth, thisWeek, lastWeek, nextWeek
+    attr_reader :timePeriod
+    
+    # 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 [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
+    # @option options [Boolean] percent If a top/bottom N rule, evaluate as N% rather than N
+    # @option options [Integer] rank If a top/bottom N rule, the value of N
+    # @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
+    def initialize(options={})
+      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_type(v); @type = v end
+    # @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    
+    def dxfId=(v); Axlsx::validate_unsigned_numeric(v); @dxfId = v end
+    # @see equalAverage
+    def equalAverage=(v); Axlsx::validate_boolean(v); @equalAverage = v end
+    # @see priority
+    def priority=(v); Axlsx::validate_unsigned_numeric(v); @priority = v end
+    # @see operator
+    def operator=(v); Axlsx::validate_conditional_formatting_operator(v); @operator = v end
+    # @see text
+    def text=(v); Axlsx::validate_string(v); @text = v end
+    # @see percent
+    def percent=(v); Axlsx::validate_boolean(v); @percent = v end
+    # @see rank
+    def rank=(v); Axlsx::validate_unsigned_numeric(v); @rank = v end
+    # @see stdDev
+    def stdDev=(v); Axlsx::validate_unsigned_numeric(v); @stdDev = v end
+    # @see stopIfTrue
+    def stopIfTrue=(v); Axlsx::validate_boolean(v); @stopIfTrue = v end
+    # @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
+
+    # Serializes the conditional formatting rule
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      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 << '</cfRule>'
+    end
+  end
+end
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 6433b9bc..407a25e5 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -101,7 +101,8 @@ module Axlsx
       @drawing = @page_margins = @auto_filter = nil
       @merged_cells = []
       @auto_fit_data = []
-
+      @conditional_formattings = []
+      
       @selected = false
       @show_gridlines = true
       self.name = "Sheet" + (index+1).to_s
@@ -123,6 +124,22 @@ module Axlsx
       rows.flatten
     end
 
+    # Add conditional formatting to this worksheet.
+    # 
+    # @param [String] cells The range to apply the formatting to
+    # @param [Array|Hash] rules An array of hashes (or just one) to create Conditional formatting rules from.
+    # @example This would format column A whenever it is FALSE.
+    #        # for a longer example, see examples/example_conditional_formatting.rb (link below)
+    #        worksheet.add_conditional_formatting( "A1:A1048576", { :type => :cellIs, :operator => :equal, :formula => "FALSE", :dxfId => 1, :priority => 1 }
+    #
+    # @see ConditionalFormattingRule#initialize
+    # @see file:examples/example_conditional_formatting.rb
+    def add_conditional_formatting(cells, rules)
+      cf = ConditionalFormatting.new( :sqref => cells )
+      cf.add_rules rules
+      @conditional_formattings << cf
+    end
+    
     # Creates merge information for this worksheet.
     # Cells can be merged by calling the merge_cells method on a worksheet.
     # @example This would merge the three cells C1..E1    #
@@ -411,6 +428,9 @@ module Axlsx
       unless @tables.empty?
         str.concat "<tableParts count='%s'>%s</tableParts>" % [@tables.size, @tables.reduce('') { |memo, obj| memo += "<tablePart r:id='%s'/>" % obj.rId }]
       end
+      @conditional_formattings.each do |cf|
+        str.concat cf.to_xml_string
+      end
       str + '</worksheet>'
     end