Merge branch '33-conditional-formatting'

5 files changed, 315 insertions, 35 deletions

diff --git a/examples/example_conditional_formatting.rb b/examples/example_conditional_formatting.rb
new file mode 100644
index 00000000..cfff3c86
--- /dev/null
+++ b/examples/example_conditional_formatting.rb
@@ -0,0 +1,31 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+
+p = Axlsx::Package.new
+book = p.workbook
+ws = book.add_worksheet
+
+# define your regular styles
+percent = book.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE_THIN_BORDER)
+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)
+
+# Generate 20 rows of data
+ws.add_row ["Previous Year Quarterly Profits (JPY)"]
+ws.add_row ["Quarter", "Profit", "% of Total"]
+offset = 3
+rows = 20
+offset.upto(rows + offset) do |i|
+  ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent]
+end
+
+# Apply conditional formatting to range B4:B100 in the worksheet
+ws.add_conditional_formatting("B4:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 })
+
+f = File.open('example_differential_styling.xlsx', 'w')
+p.serialize(f)
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/test/stylesheet/tc_dxf.rb b/test/stylesheet/tc_dxf.rb
new file mode 100644
index 00000000..e94a1614
--- /dev/null
+++ b/test/stylesheet/tc_dxf.rb
@@ -0,0 +1,81 @@
+require 'tc_helper.rb'
+
+class TestDxf < Test::Unit::TestCase
+
+  def setup
+    @item = Axlsx::Dxf.new
+    @styles = Axlsx::Styles.new    
+  end
+
+  def teardown
+  end
+
+  def test_initialiation
+    assert_equal(@item.alignment, nil)
+    assert_equal(@item.protection, nil)
+    assert_equal(@item.numFmt, nil)
+    assert_equal(@item.font, nil)
+    assert_equal(@item.fill, nil)
+    assert_equal(@item.border, nil)
+  end
+
+  def test_alignment
+    assert_raise(ArgumentError) { @item.alignment = -1.1 }
+    assert_nothing_raised { @item.alignment = Axlsx::CellAlignment.new }
+    assert(@item.alignment.is_a?(Axlsx::CellAlignment))
+  end
+
+  def test_protection
+    assert_raise(ArgumentError) { @item.protection = -1.1 }
+    assert_nothing_raised { @item.protection = Axlsx::CellProtection.new }
+    assert(@item.protection.is_a?(Axlsx::CellProtection))
+  end
+
+  def test_numFmt
+    assert_raise(ArgumentError) { @item.numFmt = 1 }
+    assert_nothing_raised { @item.numFmt = Axlsx::NumFmt.new }
+    assert @item.numFmt.is_a? Axlsx::NumFmt
+  end
+
+  def test_fill
+    assert_raise(ArgumentError) { @item.fill =  1 }
+    assert_nothing_raised { @item.fill = Axlsx::Fill.new(Axlsx::PatternFill.new(:patternType =>:solid, :fgColor=> Axlsx::Color.new(:rgb => "FF000000"))) }
+    assert @item.fill.is_a? Axlsx::Fill
+  end
+
+  def test_font
+    assert_raise(ArgumentError) { @item.font = 1 }
+    assert_nothing_raised { @item.font = Axlsx::Font.new }
+    assert @item.font.is_a? Axlsx::Font 
+  end
+
+  def test_border
+    assert_raise(ArgumentError) { @item.border = 1 }
+    assert_nothing_raised { @item.border = Axlsx::Border.new }
+    assert @item.border.is_a? Axlsx::Border
+  end
+
+  def test_to_xml
+    @item.border = Axlsx::Border.new
+    doc = Nokogiri::XML.parse(@item.to_xml_string)
+    assert_equal(1, doc.xpath(".//dxf//border").size)
+    assert_equal(0, doc.xpath(".//dxf//font").size)    
+  end
+
+  def test_many_options_xml
+    @item.border = Axlsx::Border.new
+    @item.alignment = Axlsx::CellAlignment.new
+    @item.fill = Axlsx::Fill.new(Axlsx::PatternFill.new(:patternType =>:solid, :fgColor=> Axlsx::Color.new(:rgb => "FF000000")))
+    @item.font = Axlsx::Font.new
+    @item.protection = Axlsx::CellProtection.new
+    @item.numFmt = Axlsx::NumFmt.new
+    
+    doc = Nokogiri::XML.parse(@item.to_xml_string)
+    assert_equal(1, doc.xpath(".//dxf//fill//patternFill[@patternType='solid']//fgColor[@rgb='FF000000']").size)
+    assert_equal(1, doc.xpath(".//dxf//font").size)
+    assert_equal(1, doc.xpath(".//dxf//protection").size)
+    assert_equal(1, doc.xpath(".//dxf//numFmt[@numFmtId='0'][@formatCode='']").size)
+    assert_equal(1, doc.xpath(".//dxf//alignment").size)
+    assert_equal(1, doc.xpath(".//dxf//border").size)    
+  end
+end
diff --git a/test/stylesheet/tc_styles.rb b/test/stylesheet/tc_styles.rb
index 31461ae3..4f63df9d 100644
--- a/test/stylesheet/tc_styles.rb
+++ b/test/stylesheet/tc_styles.rb
@@ -73,5 +73,49 @@ class TestStyles < Test::Unit::TestCase
     assert_equal(xf.applyNumberFormat, true, "number format applied")
     assert_equal(xf.applyAlignment, true, "alignment applied")
   end
+  
+  def test_basic_add_style_dxf
+    border_count = @styles.borders.size
+    s = @styles.add_style :border => {:style => :thin, :color => "FFFF0000"}, :type => :dxf
+    assert_equal(@styles.borders.size, border_count, "styles borders not affected")
+    assert_equal(@styles.dxfs.last.border.prs.last.color.rgb, "FFFF0000")
+    assert_raise(ArgumentError) { @styles.add_style :border => {:color => "FFFF0000"}, :type => :dxf }
+    assert_equal @styles.borders.last.prs.size, 4
+  end
+
+  def test_add_style_dxf
+    fill_count = @styles.fills.size
+    font_count = @styles.fonts.size
+    dxf_count = @styles.dxfs.size
 
+    style = @styles.add_style :bg_color=>"FF000000", :fg_color=>"FFFFFFFF", :sz=>13, :alignment=>{:horizontal=>:left}, :border=>{:style => :thin, :color => "FFFF0000"}, :hidden=>true, :locked=>true, :type => :dxf
+    assert_equal(@styles.dxfs.size, dxf_count+1)
+    assert_equal(0, style, "returns the zero-based dxfId")
+    
+    dxf = @styles.dxfs.last
+    assert_equal(@styles.dxfs.last.fill.fill_type.fgColor.rgb, "FF000000", "fill created with color")
+
+    assert_equal(font_count, (@styles.fonts.size), "font not created under styles")
+    assert_equal(fill_count, (@styles.fills.size), "fill not created under styles")
+
+    assert(dxf.border.is_a?(Axlsx::Border), "border is set")
+    assert_equal(nil, dxf.numFmt, "number format is not set")
+
+    assert(dxf.alignment.is_a?(Axlsx::CellAlignment), "alignment was created")
+    assert_equal(dxf.alignment.horizontal, :left, "horizontal alignment applied")
+    assert_equal(dxf.protection.hidden, true, "hidden protection set")
+    assert_equal(dxf.protection.locked, true, "cell locking set")
+    assert_raise(ArgumentError, "should reject invalid borderId") { @styles.add_style :border => 3 }
+    assert_raise(ArgumentError, "should reject num_fmt option") {
+      @styles.add_style :type=>:dxf, :num_fmt=>Axlsx::NUM_FMT_PERCENT
+    }
+  end
+
+  def test_multiple_dxf
+    # add a second style
+    style = @styles.add_style :bg_color=>"00000000", :fg_color=>"FFFFFFFF", :sz=>13, :alignment=>{:horizontal=>:left}, :border=>{:style => :thin, :color => "FFFF0000"}, :hidden=>true, :locked=>true, :type => :dxf
+    assert_equal(0, style, "returns the first dxfId")
+    style = @styles.add_style :bg_color=>"FF000000", :fg_color=>"FFFFFFFF", :sz=>13, :alignment=>{:horizontal=>:left}, :border=>{:style => :thin, :color => "FFFF0000"}, :hidden=>true, :locked=>true, :type => :dxf
+    assert_equal(1, style, "returns the second dxfId")
+  end
 end