From bb2811296df0de79bf3e3543f847981b86f74112 Mon Sep 17 00:00:00 2001 From: Stephen Pike Date: Thu, 19 Apr 2012 21:23:13 -0400 Subject: # Support for conditional formatting Adds support for conditional formatting via two new classes, ConditionalFormatting and ConditionalFormattingRule. Conditional Formats apply to ranges of cells, and can include multiple rules, ranked by priority. A single worksheet has many @conditional_formattings applied to different ranges. There are still pieces of the spec missing from the implementation. The biggest glaring ommission are the child elements colorScale, dataBar, and iconSet (I only implemented formula). http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule.aspx --- lib/axlsx/util/validators.rb | 29 ++++ lib/axlsx/workbook/workbook.rb | 2 + .../workbook/worksheet/conditional_formatting.rb | 69 +++++++++ .../worksheet/conditional_formatting_rule.rb | 169 +++++++++++++++++++++ lib/axlsx/workbook/worksheet/worksheet.rb | 21 ++- 5 files changed, 288 insertions(+), 2 deletions(-) create mode 100644 lib/axlsx/workbook/worksheet/conditional_formatting.rb create mode 100644 lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb (limited to 'lib') 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 << '' + str << rules.collect{ |rule| rule.to_xml_string }.join(' ') + str << '' + 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 << '' + @@child_elements.each do |el| + str << "<#{el}>" << self.send(el) << "" if self.send(el) + end + str << '' + 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 "%s" % [@tables.size, @tables.reduce('') { |memo, obj| memo += "" % obj.rId }] end + @conditional_formattings.each do |cf| + str.concat cf.to_xml_string + end str + '' end -- cgit v1.2.3 From 16782169d50ebf1ee9f8ab0d6e28f6dd21ca7613 Mon Sep 17 00:00:00 2001 From: Stephen Pike Date: Thu, 19 Apr 2012 22:07:02 -0400 Subject: Put encoding back... whoops --- lib/axlsx/workbook/worksheet/worksheet.rb | 1 + 1 file changed, 1 insertion(+) (limited to 'lib') diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 05b13195..44975736 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -1,3 +1,4 @@ +# encoding: UTF-8 module Axlsx # The Worksheet class represents a worksheet in the workbook. -- cgit v1.2.3 From 6b0c1e3c59e581525161491d85a153a537711370 Mon Sep 17 00:00:00 2001 From: Stephen Pike Date: Fri, 20 Apr 2012 11:19:32 -0400 Subject: Formatting and documentation cleanup --- .../workbook/worksheet/conditional_formatting.rb | 35 +++++++++++++++------- .../worksheet/conditional_formatting_rule.rb | 11 +++---- lib/axlsx/workbook/worksheet/worksheet.rb | 12 ++++---- 3 files changed, 36 insertions(+), 22 deletions(-) (limited to 'lib') diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting.rb b/lib/axlsx/workbook/worksheet/conditional_formatting.rb index 01a1d719..13f6183d 100644 --- a/lib/axlsx/workbook/worksheet/conditional_formatting.rb +++ b/lib/axlsx/workbook/worksheet/conditional_formatting.rb @@ -3,22 +3,22 @@ module Axlsx # # @note The recommended way to manage conditional formatting is via Worksheet#add_conditional_formatting # @see Worksheet#add_conditional_formatting + # @see ConditionalFormattingRule class ConditionalFormatting - # Sqref - # Range over which the formatting is applied + # Range over which the formatting is applied, in "A1:B2" format # @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 + # 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 + # 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={}) @@ -28,10 +28,17 @@ module Axlsx 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 + # 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 @@ -41,8 +48,8 @@ module Axlsx 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 + # 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 @@ -58,6 +65,12 @@ module Axlsx def sqref=(v); Axlsx::validate_string(v); @sqref = v end # Serializes the conditional formatting element + # @example Conditional Formatting XML looks like: + # + # + # 0.5 + # + # # @param [String] str # @return [String] def to_xml_string(str = '') diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb index bddea5a5..e7cba96f 100644 --- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb +++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb @@ -4,8 +4,9 @@ module Axlsx # # @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] + CHILD_ELEMENTS = [:formula] # Formula # @return [String] @@ -61,13 +62,13 @@ module Axlsx attr_reader :priority # Text - # The text value in a "text contains" conditional formatting + # 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 + # 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] @@ -158,9 +159,9 @@ module Axlsx # @return [String] def to_xml_string(str = '') str << '' - @@child_elements.each do |el| + CHILD_ELEMENTS.each do |el| str << "<#{el}>" << self.send(el) << "" if self.send(el) end str << '' diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 44975736..59e57ac2 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -124,14 +124,14 @@ module Axlsx rows.flatten end - # Add conditinoal formatting to this worksheet. + # Add conditional 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 } + # @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 + # worksheet.add_conditional_formatting( "A1:A1048576", { :type => :cellIs, :operator => :equal, :formula => "FALSE", :dxfId => 1, :priority => 1 } # - # @see ConditionalFormattingRule.#initialize + # @see ConditionalFormattingRule#initialize def add_conditional_formatting(cells, rules) cf = ConditionalFormatting.new( :sqref => cells ) cf.add_rules rules -- cgit v1.2.3 From f96205df2b10c68d10a2a6882fe77928358ed362 Mon Sep 17 00:00:00 2001 From: Stephen Pike Date: Fri, 20 Apr 2012 15:05:01 -0400 Subject: [#33] Add support for Dxf elements to enable conditional formatting New Dxf class implements 18.8.14. Conditional formatting now "works". Add :type option to add_styles, defaults to :xf when add_styles is called with :dxf type, new styles are not added to the global @styles set. Dxf child elements only exist inside the `` chunk. Added tests and an example. --- examples/example_conditional_formatting.rb | 31 ++++++++ lib/axlsx/stylesheet/dxf.rb | 77 +++++++++++++++++++ lib/axlsx/stylesheet/styles.rb | 117 ++++++++++++++++++++--------- test/stylesheet/tc_dxf.rb | 81 ++++++++++++++++++++ test/stylesheet/tc_styles.rb | 44 +++++++++++ 5 files changed, 315 insertions(+), 35 deletions(-) create mode 100644 examples/example_conditional_formatting.rb create mode 100644 lib/axlsx/stylesheet/dxf.rb create mode 100644 test/stylesheet/tc_dxf.rb (limited to 'lib') 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 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 << '' + 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 -- cgit v1.2.3 From 83f633051eb24440424fe8700bbc49caa74c0589 Mon Sep 17 00:00:00 2001 From: Stephen Pike Date: Fri, 20 Apr 2012 15:15:11 -0400 Subject: Add some more documentation for example of conditional formatting --- lib/axlsx/workbook/worksheet/worksheet.rb | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'lib') diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 59e57ac2..407a25e5 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -128,10 +128,12 @@ module Axlsx # # @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 + # @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 -- cgit v1.2.3