diff options
Diffstat (limited to 'examples/conditional_formatting')
5 files changed, 0 insertions, 239 deletions
diff --git a/examples/conditional_formatting/example_conditional_formatting.rb b/examples/conditional_formatting/example_conditional_formatting.rb deleted file mode 100644 index 37df52f0..00000000 --- a/examples/conditional_formatting/example_conditional_formatting.rb +++ /dev/null @@ -1,89 +0,0 @@ -#!/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) -status = book.styles.add_style(:border => Axlsx::STYLE_THIN_BORDER) - -# define the style for conditional formatting -profitable = book.styles.add_style( :fg_color => "428751", :type => :dxf ) -unprofitable = book.styles.add_style( :fg_color => "FF0000", :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 }) -# Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween - ws.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :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 - -book.add_worksheet(:name => "Contains Text") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total", "Status"] - 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})", (10000*((rows/2-i) * (rows/2-i))) > 100000 ? "PROFIT" : "LOSS"], :style=>[nil, money, percent, status] - end - -# Apply conditional formatting to range D3:D100 in the worksheet - ws.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "PROFIT", :dxfId => profitable, :priority => 1 }) - ws.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "LOSS", :dxfId => unprofitable, :priority => 1 }) -end - -p.serialize('example_conditional_formatting.xlsx') diff --git a/examples/conditional_formatting/getting_barred.rb b/examples/conditional_formatting/getting_barred.rb deleted file mode 100644 index 6e69b6ab..00000000 --- a/examples/conditional_formatting/getting_barred.rb +++ /dev/null @@ -1,37 +0,0 @@ -#!/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 deleted file mode 100644 index 6ea7ea0f..00000000 --- a/examples/conditional_formatting/hitting_the_high_notes.rb +++ /dev/null @@ -1,37 +0,0 @@ -#!/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 deleted file mode 100644 index dcbaf11d..00000000 --- a/examples/conditional_formatting/scaled_colors.rb +++ /dev/null @@ -1,39 +0,0 @@ -#!/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 deleted file mode 100644 index 301bf3fa..00000000 --- a/examples/conditional_formatting/stop_and_go.rb +++ /dev/null @@ -1,37 +0,0 @@ -#!/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' |