diff options
| -rw-r--r-- | lib/axlsx/util/validators.rb | 29 | ||||
| -rw-r--r-- | lib/axlsx/workbook/workbook.rb | 2 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/conditional_formatting.rb | 69 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb | 169 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 21 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_conditional_formatting.rb | 141 |
6 files changed, 429 insertions, 2 deletions
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..01a1d719 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/conditional_formatting.rb @@ -0,0 +1,69 @@ +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 + class ConditionalFormatting + + # Sqref + # Range over which the formatting is applied + # @return [String] + attr_reader :sqref + + # Rules + # Rules to apply the formatting to. Can be either a hash of + # options for one ConditionalFormattingRule, an array of hashes + # for multiple ConditionalFormattingRules, or an array of already + # created ConditionalFormattingRules. + # @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 ConditionalFormattingRules to this object. Rules can either + # be already created objects or hashes of options for automatic + # creation. + # @option rules [Array, Hash] the rules 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 + # @option rule [ConditionalFormattingRule, Hash] A rule to create + # @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 + # @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..bddea5a5 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb @@ -0,0 +1,169 @@ +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 + 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 + # The text value 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..05b13195 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -1,4 +1,3 @@ -# encoding: UTF-8 module Axlsx # The Worksheet class represents a worksheet in the workbook. @@ -101,7 +100,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 +123,20 @@ module Axlsx rows.flatten end + # Add conditinoal formatting to this worksheet. + # + # @option cells [String] The range to apply the formatting to + # @option rules [Array, Hash] An array of hashes (or just one) to create Conditional formatting rules from + # @example This would color column A whenever it is FALSE + # worksheet.add_conditional_formatting( "A1:A1048576", { :type => :cellIs, :operator => :equal, :formula => "FALSE", :dxfId => 0, priority => 1 } + # + # @see ConditionalFormattingRule.#initialize + 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 +425,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 diff --git a/test/workbook/worksheet/tc_conditional_formatting.rb b/test/workbook/worksheet/tc_conditional_formatting.rb new file mode 100644 index 00000000..6feede10 --- /dev/null +++ b/test/workbook/worksheet/tc_conditional_formatting.rb @@ -0,0 +1,141 @@ +require 'tc_helper.rb' + +class TestConditionalFormatting < Test::Unit::TestCase + + def setup + p = Axlsx::Package.new + @ws = p.workbook.add_worksheet :name=>"hmmm" + @cfs = @ws.add_conditional_formatting( "A1:A1", [{ :type => :cellIs, :dxfId => 0, :priority => 1, :operator => :greaterThan, :formula => "0.5" }]) + @cf = @cfs.first + @cfr = @cf.rules.first + end + + def test_initialize_with_options + optioned = Axlsx::ConditionalFormatting.new( :sqref => "AA1:AB100", :rules => [1, 2] ) + assert_equal("AA1:AB100", optioned.sqref) + assert_equal([1, 2], optioned.rules) + end + + def test_add_as_hash + cfs = @ws.add_conditional_formatting( "B2:B2", [{ :type => :containsText, :text => "TRUE", + :dxfId => 0, :priority => 1, + :formula => 'NOT(ISERROR(SEARCH("FALSE",AB1)))' }]) + 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)))'") + 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) + assert doc.xpath(".//conditionalFormatting//cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']//formula='0.5'") + end + + def test_many_options + cf = Axlsx::ConditionalFormatting.new( :sqref => "B3:B4" ) + cf.add_rule({: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"}) + doc = Nokogiri::XML.parse(cf.to_xml_string) + 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'") + 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 } + assert_equal(@cfr.aboveAverage, true) + end + + def test_equal_average + assert_raise(ArgumentError) { @cfr.equalAverage = "illegal" } + 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 } + assert_equal(@cfr.bottom, true) + end + + def test_operator + assert_raise(ArgumentError) { @cfr.operator = "cellIs" } + 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 } + assert_equal(@cfr.dxfId, 1) + end + + def test_priority + assert_raise(ArgumentError) { @cfr.priority = -1.0 } + 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 } + assert_equal(@cfr.stdDev, 1) + end + + def test_stop_if_true + assert_raise(ArgumentError) { @cfr.stopIfTrue = "illegal" } + assert_nothing_raised { @cfr.stopIfTrue = false } + assert_equal(@cfr.stopIfTrue, false) + end + + def test_time_period + assert_raise(ArgumentError) { @cfr.timePeriod = "illegal" } + assert_nothing_raised { @cfr.timePeriod = :today } + assert_equal(@cfr.timePeriod, :today) + end +end |