label rotation and conditional formatting examples

8 files changed, 180 insertions, 2 deletions

diff --git a/examples/getting_barred.rb b/examples/getting_barred.rb
new file mode 100644
index 00000000..6e69b6ab
--- /dev/null
+++ b/examples/getting_barred.rb
@@ -0,0 +1,37 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+p = Axlsx::Package.new
+p.workbook do |wb|
+  # define your regular styles
+  styles = wb.styles
+  title = styles.add_style :sz => 15, :b => true, :u => true
+  default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER
+  header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true
+  money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER
+  percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER
+
+  # define the style for conditional formatting - its the :dxf bit that counts!
+  profitable =  styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true
+
+  wb.add_worksheet(:name => 'Data Bar Conditional Formatting') do  |ws|
+    ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title
+    ws.add_row
+    ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header
+    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent]
+
+    ws.merge_cells 'A1:C1'
+
+    # Apply conditional formatting to range B4:B7 in the worksheet
+    data_bar = Axlsx::DataBar.new
+    ws.add_conditional_formatting 'B4:B7', { :type => :dataBar,
+                                             :dxfId => profitable,
+                                             :priority => 1,
+                                             :data_bar => data_bar }
+  end
+end
+p.serialize 'getting_barred.xlsx'
diff --git a/examples/hitting_the_high_notes.rb b/examples/hitting_the_high_notes.rb
new file mode 100644
index 00000000..6ea7ea0f
--- /dev/null
+++ b/examples/hitting_the_high_notes.rb
@@ -0,0 +1,37 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+p = Axlsx::Package.new
+p.workbook do |wb|
+  # define your regular styles
+  styles = wb.styles
+  title = styles.add_style :sz => 15, :b => true, :u => true
+  default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER
+  header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true
+  money = styles.add_style :format_code => '###,###,###,##0', :border => Axlsx::STYLE_THIN_BORDER
+  percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER
+
+  # define the style for conditional formatting - its the :dxf bit that counts!
+  profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true
+
+  wb.add_worksheet(:name => 'The High Notes') do  |ws|
+    ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title
+    ws.add_row
+    ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header
+    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent]
+
+    ws.merge_cells 'A1:C1'
+
+    # Apply conditional formatting to range B4:B7 in the worksheet
+    ws.add_conditional_formatting 'B4:B7', { :type => :cellIs,
+                                             :operator => :greaterThan,
+                                             :formula => '27000000000',
+                                             :dxfId => profitable,
+                                             :priority => 1 }
+  end
+end
+p.serialize 'the_high_notes.xlsx'
diff --git a/examples/scaled_colors.rb b/examples/scaled_colors.rb
new file mode 100644
index 00000000..dcbaf11d
--- /dev/null
+++ b/examples/scaled_colors.rb
@@ -0,0 +1,39 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+p = Axlsx::Package.new
+p.workbook do |wb|
+  # define your regular styles
+  styles = wb.styles
+  title = styles.add_style :sz => 15, :b => true, :u => true
+  default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER
+  header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true
+  money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER
+  percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER
+
+  # define the style for conditional formatting - its the :dxf bit that counts!
+  profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true
+
+  wb.add_worksheet(:name => 'Scaled Colors') do  |ws|
+    ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title
+    ws.add_row
+    ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header
+    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent]
+
+    ws.merge_cells 'A1:C1'
+
+    # Apply conditional formatting to range B4:B7 in the worksheet
+    color_scale = Axlsx::ColorScale.new
+    ws.add_conditional_formatting 'B4:B7', { :type => :colorScale,
+                                             :operator => :greaterThan,
+                                             :formula => '27000000000',
+                                             :dxfId => profitable,
+                                             :priority => 1,
+                                             :color_scale => color_scale }
+  end
+end
+p.serialize 'scaled_colors.xlsx'
diff --git a/examples/stop_and_go.rb b/examples/stop_and_go.rb
new file mode 100644
index 00000000..301bf3fa
--- /dev/null
+++ b/examples/stop_and_go.rb
@@ -0,0 +1,37 @@
+#!/usr/bin/env ruby -w -s
+# -*- coding: utf-8 -*-
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"
+require 'axlsx'
+p = Axlsx::Package.new
+p.workbook do |wb|
+  # define your regular styles
+  styles = wb.styles
+  title = styles.add_style :sz => 15, :b => true, :u => true
+  default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER
+  header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true
+  money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER
+  percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER
+
+  # define the style for conditional formatting - its the :dxf bit that counts!
+  profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true
+
+  wb.add_worksheet(:name => 'Downtown traffic') do  |ws|
+    ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title
+    ws.add_row
+    ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header
+    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent]
+    ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent]
+
+    ws.merge_cells 'A1:C1'
+
+    # Apply conditional formatting to range B3:B7 in the worksheet
+    icon_set = Axlsx::IconSet.new
+    ws.add_conditional_formatting 'B3:B7', { :type => :iconSet,
+                                             :dxfId => profitable,
+                                             :priority => 1,
+                                             :icon_set => icon_set }
+  end
+end
+p.serialize 'stop_and_go.xlsx'
diff --git a/lib/axlsx/drawing/axis.rb b/lib/axlsx/drawing/axis.rb
index 9206e848..ea093391 100644
--- a/lib/axlsx/drawing/axis.rb
+++ b/lib/axlsx/drawing/axis.rb
@@ -36,6 +36,10 @@ module Axlsx
     # @return [Symbol]
     attr_reader :crosses
 
+    # specifies how the degree of label rotation
+    # @return [Integer]
+    attr_reader :label_rotation
+
     # specifies if gridlines should be shown in the chart
     # @return [Boolean]
     attr_reader :gridlines
@@ -53,6 +57,7 @@ module Axlsx
       @axId = axId
       @crossAx = crossAx
       @format_code = "General"
+      @label_rotation = 0
       @scaling = Scaling.new(:orientation=>:minMax)
       self.axPos = :b
       self.tickLblPos = :nextTo
@@ -83,6 +88,15 @@ module Axlsx
     # must be one of [:autoZero, :min, :max]
     def crosses=(v) RestrictionValidator.validate "#{self.class}.crosses", [:autoZero, :min, :max], v; @crosses = v; end
 
+    # Specify the degree of label rotation to apply to labels
+    # default true
+    def label_rotation=(v)
+      Axlsx::validate_int(v)
+      adjusted = v.to_i * 60000
+      Axlsx::validate_angle(adjusted)
+      @label_rotation = adjusted
+    end
+
 
     # Serializes the object
     # @param [String] str
@@ -105,6 +119,8 @@ module Axlsx
       str << '<c:majorTickMark val="none"/>'
       str << '<c:minorTickMark val="none"/>'
       str << '<c:tickLblPos val="' << @tickLblPos.to_s << '"/>'
+      # some potential value in implementing this in full. Very detailed!
+      str << "<c:txPr><a:bodyPr rot='#{@label_rotation}'/><a:lstStyle/><a:p><a:pPr><a:defRPr/></a:pPr><a:endParaRPr/></a:p></c:txPr>"
       str << '<c:crossAx val="' << @crossAx.to_s << '"/>'
       str << '<c:crosses val="' << @crosses.to_s << '"/>'
     end
diff --git a/lib/axlsx/util/constants.rb b/lib/axlsx/util/constants.rb
index 4ec6f4d6..0699a482 100644
--- a/lib/axlsx/util/constants.rb
+++ b/lib/axlsx/util/constants.rb
@@ -235,8 +235,9 @@ module Axlsx
   # error message when user does not provide color and or style options for border in Style#add_sytle
   ERR_INVALID_BORDER_OPTIONS = "border hash must include both style and color. e.g. :border => { :color => 'FF000000', :style => :thin }. You provided: %s"
 
-  ERR_INVALID_DXF_BORDER_OPTIONS = "border options required for dxf style stypes. e.g. :border => { :color => 'FF000000', :style => :thin }. You provided: %s"
-
+  # error message for invalid border id reference
   ERR_INVALID_BORDER_ID = "The border id you specified (%s) does not exist. Please add a border with Style#add_style before referencing its index."
 
+  # error message for invalid angles
+  ERR_ANGLE = "Angles must be a value between -90 and 90. You provided: %s"
 end
diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index 6826803b..5dc1f66e 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -45,6 +45,9 @@ module Axlsx
     true
   end
 
+  def self.validate_angle(v)
+    raise ArgumentError, (ERR_ANGLE % v.inspect) unless (v >= -5400000 && v <= 5400000)
+  end
   # Requires that the value is a Fixnum or Integer and is greater or equal to 0
   # @param [Any] v The value validated
   # @raise [ArgumentError] raised if the value is not a Fixnum or Integer value greater or equal to 0
diff --git a/test/drawing/tc_axis.rb b/test/drawing/tc_axis.rb
index c627b4d1..7fe01395 100644
--- a/test/drawing/tc_axis.rb
+++ b/test/drawing/tc_axis.rb
@@ -21,6 +21,14 @@ class TestAxis < Test::Unit::TestCase
     assert_nothing_raised("accepts valid axis position") { @axis.axPos = :r }
   end
 
+  def test_label_rotation
+    assert_raise(ArgumentError, "requires valid angle") { @axis.label_rotation = :nowhere }
+    assert_raise(ArgumentError, "requires valid angle") { @axis.label_rotation = 91 }
+    assert_raise(ArgumentError, "requires valid angle") { @axis.label_rotation = -91 }
+    assert_nothing_raised("accepts valid angle") { @axis.label_rotation = 45 }
+    assert_equal(@axis.label_rotation, 45 * 60000)
+  end
+
   def test_tick_label_position
     assert_raise(ArgumentError, "requires valid tick label position") { @axis.tickLblPos = :nowhere }
     assert_nothing_raised("accepts valid tick label position") { @axis.tickLblPos = :high }