documentation updates and example dir restructuring.

5 files changed, 222 insertions, 0 deletions

diff --git a/examples/conditional_formatting/example_conditional_formatting.rb b/examples/conditional_formatting/example_conditional_formatting.rb
new file mode 100644
index 00000000..ab49d238
--- /dev/null
+++ b/examples/conditional_formatting/example_conditional_formatting.rb
@@ -0,0 +1,72 @@
+#!/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
+
+# 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)
+
+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 B3:B100 in the worksheet
+  ws.add_conditional_formatting("B3: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 B3:B100 in the worksheet
+  color_scale = Axlsx::ColorScale.new
+  ws.add_conditional_formatting("B3: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 B3:B100 in the worksheet
+  data_bar = Axlsx::DataBar.new
+  ws.add_conditional_formatting("B3: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 B3:B100 in the worksheet
+  icon_set = Axlsx::IconSet.new
+  ws.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set })
+end
+
+p.serialize('example_conditional_formatting.xlsx')
diff --git a/examples/conditional_formatting/getting_barred.rb b/examples/conditional_formatting/getting_barred.rb
new file mode 100644
index 00000000..6e69b6ab
--- /dev/null
+++ b/examples/conditional_formatting/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/conditional_formatting/hitting_the_high_notes.rb b/examples/conditional_formatting/hitting_the_high_notes.rb
new file mode 100644
index 00000000..6ea7ea0f
--- /dev/null
+++ b/examples/conditional_formatting/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/conditional_formatting/scaled_colors.rb b/examples/conditional_formatting/scaled_colors.rb
new file mode 100644
index 00000000..dcbaf11d
--- /dev/null
+++ b/examples/conditional_formatting/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/conditional_formatting/stop_and_go.rb b/examples/conditional_formatting/stop_and_go.rb
new file mode 100644
index 00000000..301bf3fa
--- /dev/null
+++ b/examples/conditional_formatting/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'