From cb5fb866eefeaacb99dafb22b703224a480350ff 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 --- .../worksheet/tc_conditional_formatting.rb | 141 +++++++++++++++++++++ 1 file changed, 141 insertions(+) create mode 100644 test/workbook/worksheet/tc_conditional_formatting.rb (limited to 'test/workbook') 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 -- cgit v1.2.3