diff options
| -rw-r--r-- | examples/example_conditional_formatting.rb | 63 | ||||
| -rw-r--r-- | lib/axlsx/util/validators.rb | 6 | ||||
| -rw-r--r-- | lib/axlsx/workbook/workbook.rb | 1 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/conditional_formatting.rb | 14 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb | 37 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/icon_set.rb | 81 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_conditional_formatting.rb | 22 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_icon_set.rb | 45 |
8 files changed, 247 insertions, 22 deletions
diff --git a/examples/example_conditional_formatting.rb b/examples/example_conditional_formatting.rb index cfff3c86..429b6fac 100644 --- a/examples/example_conditional_formatting.rb +++ b/examples/example_conditional_formatting.rb @@ -5,7 +5,6 @@ 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) @@ -15,17 +14,59 @@ money = book.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_T 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] +book.add_worksheet(:name => "Cell Is") do |ws| + + # 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 }) end +book.add_worksheet(:name => "Color Scale") do |ws| + 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 }) + color_scale = Axlsx::ColorScale.new + ws.add_conditional_formatting("B4:B100", { :type => :colorScale, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1, :color_scale => color_scale }) +end + + +book.add_worksheet(:name => "Data Bar") do |ws| + 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 + data_bar = Axlsx::DataBar.new + ws.add_conditional_formatting("B4:B100", { :type => :dataBar, :dxfId => profitable, :priority => 1, :data_bar => data_bar }) +end + +book.add_worksheet(:name => "Icon Set") do |ws| + 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 + icon_set = Axlsx::IconSet.new + ws.add_conditional_formatting("B4:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) +end -f = File.open('example_differential_styling.xlsx', 'w') -p.serialize(f) +p.serialize('example_differential_styling.xlsx') diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb index 555bc038..6826803b 100644 --- a/lib/axlsx/util/validators.rb +++ b/lib/axlsx/util/validators.rb @@ -105,6 +105,12 @@ module Axlsx end + # Requires that the value is one of the valid ST_IconSet types + # Allowed values are: 3Arrows, 3ArrowsGray, 3Flags, 3TrafficLights1, 3TrafficLights2, 3Signs, 3Symbols, 3Symbols2, 4Arrows, 4ArrowsGray, 4RedToBlack, 4Rating, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Rating, 5Quarters + def self.validate_icon_set(v) + RestrictionValidator.validate :iconSet, ["3Arrows", "3ArrowsGray", "3Flags", "3TrafficLights1", "3TrafficLights2", "3Signs", "3Symbols", "3Symbols2", "4Arrows", "4ArrowsGray", "4RedToBlack", "4Rating", "4TrafficLights", "5Arrows", "5ArrowsGray", "5Rating", "5Quarters"], 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, diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index b71a345a..5f849b2b 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -7,6 +7,7 @@ require 'axlsx/workbook/worksheet/page_margins.rb' require 'axlsx/workbook/worksheet/cfvo.rb' require 'axlsx/workbook/worksheet/color_scale.rb' require 'axlsx/workbook/worksheet/data_bar.rb' +require 'axlsx/workbook/worksheet/icon_set.rb' require 'axlsx/workbook/worksheet/conditional_formatting.rb' require 'axlsx/workbook/worksheet/conditional_formatting_rule.rb' require 'axlsx/workbook/worksheet/row.rb' diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting.rb b/lib/axlsx/workbook/worksheet/conditional_formatting.rb index 13f6183d..7ca07be6 100644 --- a/lib/axlsx/workbook/worksheet/conditional_formatting.rb +++ b/lib/axlsx/workbook/worksheet/conditional_formatting.rb @@ -3,7 +3,7 @@ module Axlsx # # @note The recommended way to manage conditional formatting is via Worksheet#add_conditional_formatting # @see Worksheet#add_conditional_formatting - # @see ConditionalFormattingRule + # @see ConditionalFormattingRule class ConditionalFormatting # Range over which the formatting is applied, in "A1:B2" format @@ -17,7 +17,7 @@ module Axlsx # @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 @@ -36,13 +36,13 @@ module Axlsx # @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 } ] + # { :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 + # @see ConditionalFormattingRule#initialize def add_rules(rules) rules = [rules] if rules.is_a? Hash - conditional_rules = rules.each do |rule| + rules.each do |rule| add_rule rule end end @@ -58,7 +58,7 @@ module Axlsx @rules << ConditionalFormattingRule.new(rule) end end - + # @see rules def rules=(v); @rules = v end # @see sqref @@ -68,7 +68,7 @@ module Axlsx # @example Conditional Formatting XML looks like: # <conditionalFormatting sqref="E3:E9"> # <cfRule type="cellIs" dxfId="0" priority="1" operator="greaterThan"> - # <formula>0.5</formula> + # <formula>0.5</formula> # </cfRule> # </conditionalFormatting> # @param [String] str diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb index 3ccb6174..3e6192e0 100644 --- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb +++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb @@ -7,7 +7,7 @@ module Axlsx # @see Worksheet#add_conditional_formatting # @see ConditionalFormattingRule#initialize class ConditionalFormattingRule - CHILD_ELEMENTS = [:formula, :color_scale] + CHILD_ELEMENTS = [:formula, :color_scale, :data_bar, :icon_set] # Formula # @return [String] @@ -113,6 +113,20 @@ module Axlsx @color_scale ||= ColorScale.new end + # dataBar (Data Bar) + # The data bar to apply to this conditional formatting + # @return [DataBar] + def data_bar + @data_bar ||= DataBar.new + end + + # iconSet (Icon Set) + # The icon set to apply to this conditional formatting + # @return [IconSet] + def icon_set + @icon_set ||= IconSet.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 @@ -129,6 +143,7 @@ module Axlsx # @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={}) + @color_scale = @data_bar = @icon_set = @formula = nil options.each do |o| self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" end @@ -165,9 +180,23 @@ module Axlsx # @see color_scale def color_scale=(v) - Axlsx::DataTypeValidator.validate 'conditional_formatting_rule.color scale', ColorScale, v + Axlsx::DataTypeValidator.validate 'conditional_formatting_rule.color_scale', ColorScale, v @color_scale = v end + + # @see data_bar + def data_bar=(v) + Axlsx::DataTypeValidator.validate 'conditional_formatting_rule.data_bar', DataBar, v + @data_bar = v + end + + # @see icon_set + def icon_set=(v) + Axlsx::DataTypeValidator.validate 'conditional_formatting_rule.icon_set', IconSet, v + @icon_set = v + end + + # Serializes the conditional formatting rule # @param [String] str # @return [String] @@ -176,7 +205,9 @@ module Axlsx str << instance_values.map { |key, value| '' << key << '="' << value.to_s << '"' unless CHILD_ELEMENTS.include?(key.to_sym) }.join(' ') str << '>' str << '<formula>' << self.formula << '</formula>' if @formula - @color_scale.to_xml_string(str) if @color_scale + @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 str << '</cfRule>' end end diff --git a/lib/axlsx/workbook/worksheet/icon_set.rb b/lib/axlsx/workbook/worksheet/icon_set.rb new file mode 100644 index 00000000..7ec94088 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/icon_set.rb @@ -0,0 +1,81 @@ +module Axlsx + # Conditional Format Rule icon sets + # Describes an icon set 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 IconSet + CHILD_ELEMENTS = [:value_objects] + + # The icon set to display. + # Allowed values are: 3Arrows, 3ArrowsGray, 3Flags, 3TrafficLights1, 3TrafficLights2, 3Signs, 3Symbols, 3Symbols2, 4Arrows, 4ArrowsGray, 4RedToBlack, 4Rating, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Rating, 5Quarters + # The default value is 3TrafficLights1 + # @return [String] + attr_reader :iconSet + + # Indicates whether the thresholds indicate percentile values, instead of number values. + # The default falue is true + # @return [Boolean] + attr_reader :percent + + # If true, reverses the default order of the icons in this icon set.maxLength attribute + # The default value is false + # @return [Boolean] + attr_reader :reverse + + # Indicates whether to show the values of the cells on which this data bar is applied. + # The default value is true + # @return [Boolean] + attr_reader :showValue + + # Creates a new icon set object + # @option options [String] iconSet + # @option options [Boolean] reverse + # @option options [Boolean] percent + # @option options [Boolean] showValue + def initialize(options = {}) + @percent = @showValue = true + @reverse = false + @iconSet = "3TrafficLights1" + initialize_value_objects + options.each do |o| + self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" + end + yield self if block_given? + end + + # @see iconSet + def iconSet=(v); Axlsx::validate_icon_set(v); @iconSet = v end + + # @see showValue + def showValue=(v); Axlsx.validate_boolean(v); @showValue = v end + + # @see percent + def percent=(v); Axlsx.validate_boolean(v); @percent = v end + + # @see reverse + def reverse=(v); Axlsx.validate_boolean(v); @reverse = v end + + # Serialize this object to an xml string + # @param [String] str + # @return [String] + def to_xml_string(str="") + str << '<iconSet ' + str << instance_values.map { |key, value| '' << key << '="' << value.to_s << '"' unless CHILD_ELEMENTS.include?(key.to_sym) }.join(' ') + str << '>' + @value_objects.each { |cfvo| cfvo.to_xml_string(str) } + str << '</iconSet>' + end + + private + + # Initalize the simple typed list of value objects + # I am keeping this private for now as I am not sure what impact changes to the required two cfvo objects will do. + 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 + end +end diff --git a/test/workbook/worksheet/tc_conditional_formatting.rb b/test/workbook/worksheet/tc_conditional_formatting.rb index 2287c017..0dcb1a7c 100644 --- a/test/workbook/worksheet/tc_conditional_formatting.rb +++ b/test/workbook/worksheet/tc_conditional_formatting.rb @@ -25,15 +25,35 @@ class TestConditionalFormatting < Test::Unit::TestCase cs.value_objects.first.val = 5 end + data_bar = Axlsx::DataBar.new :color => "FFFF0000" + icon_set = Axlsx::IconSet.new :iconSet => "5Rating" + cfs = @ws.add_conditional_formatting( "B2:B2", [{ :type => :containsText, :text => "TRUE", :dxfId => 0, :priority => 1, :formula => 'NOT(ISERROR(SEARCH("FALSE",AB1)))', - :color_scale => color_scale}]) + :color_scale => color_scale, + :data_bar => data_bar, + :icon_set => icon_set}]) 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)))'") + + cfs.last.rules.last.type = :colorScale + doc = Nokogiri::XML.parse(cfs.last.to_xml_string) assert_equal(doc.xpath(".//conditionalFormatting//cfRule//colorScale//cfvo").size, 2) assert_equal(doc.xpath(".//conditionalFormatting//cfRule//colorScale//color").size, 2) + + cfs.last.rules.last.type = :dataBar + doc = Nokogiri::XML.parse(cfs.last.to_xml_string) + assert_equal(doc.xpath(".//conditionalFormatting//cfRule//dataBar").size, 1) + assert_equal(doc.xpath(".//conditionalFormatting//cfRule//dataBar//cfvo").size, 2) + assert_equal(doc.xpath(".//conditionalFormatting//cfRule//dataBar//color[@rgb='FFFF0000']").size, 1) + + cfs.last.rules.last.type = :iconSet + doc = Nokogiri::XML.parse(cfs.last.to_xml_string) + assert_equal(doc.xpath(".//conditionalFormatting//cfRule//iconSet//cfvo").size, 2) + assert_equal(doc.xpath(".//conditionalFormatting//cfRule//iconSet[@iconSet='5Rating']").size, 1) + end def test_single_rule diff --git a/test/workbook/worksheet/tc_icon_set.rb b/test/workbook/worksheet/tc_icon_set.rb new file mode 100644 index 00000000..bb90276c --- /dev/null +++ b/test/workbook/worksheet/tc_icon_set.rb @@ -0,0 +1,45 @@ +require 'tc_helper.rb' + +class TestIconSet < Test::Unit::TestCase + def setup + @icon_set = Axlsx::IconSet.new + end + + def test_defaults + assert_equal @icon_set.iconSet, "3TrafficLights1" + assert_equal @icon_set.percent, true + assert_equal @icon_set.reverse, false + assert_equal @icon_set.showValue, true + end + + def test_icon_set + assert_raise(ArgumentError) { @icon_set.iconSet = "invalid_value" } + assert_nothing_raised { @icon_set.iconSet = "5Rating"} + assert_equal(@icon_set.iconSet, "5Rating") + end + + def test_percent + assert_raise(ArgumentError) { @icon_set.percent = :invalid_type } + assert_nothing_raised { @icon_set.percent = false} + assert_equal(@icon_set.percent, false) + end + + def test_showValue + assert_raise(ArgumentError) { @icon_set.showValue = :invalid_type } + assert_nothing_raised { @icon_set.showValue = false} + assert_equal(@icon_set.showValue, false) + end + + def test_reverse + assert_raise(ArgumentError) { @icon_set.reverse = :invalid_type } + assert_nothing_raised { @icon_set.reverse = false} + assert_equal(@icon_set.reverse, false) + end + + def test_to_xml_string + doc = Nokogiri::XML.parse(@icon_set.to_xml_string) + assert_equal(doc.xpath(".//iconSet[@iconSet='3TrafficLights1'][@percent='true'][@reverse='false'][@showValue='true']").size, 1) + assert_equal(doc.xpath(".//iconSet//cfvo").size, 2) + end + +end |