From 91ea5ff009672adbad8587a1a4437d0b02519b0e Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Wed, 25 Apr 2012 20:57:49 +0900 Subject: documentation updates and example dir restructuring. --- README.md | 6 +- .../example_conditional_formatting.rb | 72 ++++++++++++++++++++++ examples/conditional_formatting/getting_barred.rb | 37 +++++++++++ .../hitting_the_high_notes.rb | 37 +++++++++++ examples/conditional_formatting/scaled_colors.rb | 39 ++++++++++++ examples/conditional_formatting/stop_and_go.rb | 37 +++++++++++ examples/example_conditional_formatting.rb | 72 ---------------------- examples/getting_barred.rb | 37 ----------- examples/hitting_the_high_notes.rb | 37 ----------- examples/real_example.rb | 63 ------------------- examples/scaled_colors.rb | 39 ------------ examples/skydrive/real_example.rb | 63 +++++++++++++++++++ examples/stop_and_go.rb | 37 ----------- .../worksheet/conditional_formatting_rule.rb | 2 + lib/axlsx/workbook/worksheet/data_bar.rb | 2 + lib/axlsx/workbook/worksheet/icon_set.rb | 2 + 16 files changed, 294 insertions(+), 288 deletions(-) create mode 100644 examples/conditional_formatting/example_conditional_formatting.rb create mode 100644 examples/conditional_formatting/getting_barred.rb create mode 100644 examples/conditional_formatting/hitting_the_high_notes.rb create mode 100644 examples/conditional_formatting/scaled_colors.rb create mode 100644 examples/conditional_formatting/stop_and_go.rb delete mode 100644 examples/example_conditional_formatting.rb delete mode 100644 examples/getting_barred.rb delete mode 100644 examples/hitting_the_high_notes.rb delete mode 100644 examples/real_example.rb delete mode 100644 examples/scaled_colors.rb create mode 100644 examples/skydrive/real_example.rb delete mode 100644 examples/stop_and_go.rb diff --git a/README.md b/README.md index 1591d13f..bc2b0454 100644 --- a/README.md +++ b/README.md @@ -24,7 +24,7 @@ Axlsx: Office Open XML Spreadsheet Generation **Rubinius Version**: rubinius 2.0.0dev * lower versions may run, this gem always tests against head. -**Release Date**: April 18th 2012 +**Release Date**: April 25th 2012 Synopsis -------- @@ -478,7 +478,7 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, - Conditional Formatting completely implemented. - refactoring / documentation for Style#add_style - added in label rotation for chart axis labels - - bugfix to porperly assign style and type info to cells when only partial information is provided in the types/style option + - bugfix to properly assign style and type info to cells when only partial information is provided in the types/style option - ** April.18.12**: 1.1.1 release - bugfix for autowidth calculations across multiple rows @@ -530,4 +530,4 @@ Please see the {file:CHANGELOG.md} document for past release information. ---------- Axlsx © 2011-2012 by [Randy Morgan](mailto:digial.ipseity@gmail.com). Axlsx is -licensed under the MIT license. Please see the {file:LICENSE} document for more information. +licensed under the MIT license. Please see the LICENSE document for more information. 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' diff --git a/examples/example_conditional_formatting.rb b/examples/example_conditional_formatting.rb deleted file mode 100644 index ab49d238..00000000 --- a/examples/example_conditional_formatting.rb +++ /dev/null @@ -1,72 +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) - -# 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/getting_barred.rb b/examples/getting_barred.rb deleted file mode 100644 index 6e69b6ab..00000000 --- a/examples/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/hitting_the_high_notes.rb b/examples/hitting_the_high_notes.rb deleted file mode 100644 index 6ea7ea0f..00000000 --- a/examples/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/real_example.rb b/examples/real_example.rb deleted file mode 100644 index 0274ebe9..00000000 --- a/examples/real_example.rb +++ /dev/null @@ -1,63 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" - -require 'axlsx' - -Axlsx::Package.new do |p| - p.workbook do |wb| - styles = wb.styles - header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center} - tbl_header = styles.add_style :b => true, :alignment => { :horizontal => :center } - ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1} - col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => { :horizontal => :center } - label = styles.add_style :alignment => { :indent => 1 } - money = styles.add_style :num_fmt => 5 - t_label = styles.add_style :b => true, :bg_color => "FFDFDEDF" - t_money = styles.add_style :b => true, :num_fmt => 5, :bg_color => "FFDFDEDF" - - wb.add_worksheet do |sheet| - sheet.add_row - sheet.add_row [nil, "College Budget"], :style => [nil, header] - sheet.add_row - sheet.add_row [nil, "What's coming in this month.", nil, nil, "How am I doing"], :style => tbl_header - sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] - sheet.add_row [nil, "Estimated monthly net income", 500, nil, "Monthly income", "=C9"], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Financial aid", 100, nil, "Monthly expenses", "=C27"], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Allowance from mom & dad", 20000, nil, "Semester expenses", "=F19"], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Total", "=SUM(C6:C8)", nil, "Difference", "=F6 - SUM(F7:F8)"], :style => [nil, t_label, t_money, nil, t_label, t_money] - sheet.add_row - sheet.add_row [nil, "What's going out this month.", nil, nil, "Semester Costs"], :style => tbl_header - sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] - sheet.add_row [nil, "Rent", 650, nil, "Tuition", 200], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Utilities", 120, nil, "Lab fees", 50], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Cell phone", 100, nil, "Other fees", 10], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Groceries", 75, nil, "Books", 150], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Auto expenses", 0, nil, "Deposits", 0], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Student loans", 0, nil, "Transportation", 30], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Other loans", 350, nil, "Total", "=SUM(F13:F18)"], :style => [nil, label, money, nil, t_label, t_money] - sheet.add_row [nil, "Credit cards", 450], :style => [nil, label, money] - sheet.add_row [nil, "Insurance", 0], :style => [nil, label, money] - sheet.add_row [nil, "Laundry", 10], :style => [nil, label, money] - sheet.add_row [nil, "Haircuts", 0], :style => [nil, label, money] - sheet.add_row [nil, "Medical expenses", 0], :style => [nil, label, money] - sheet.add_row [nil, "Entertainment", 500], :style => [nil, label, money] - sheet.add_row [nil, "Miscellaneous", 0], :style => [nil, label, money] - sheet.add_row [nil, "Total", "=SUM(C13:C26)"], :style => [nil, t_label, t_money] - sheet.add_chart(Axlsx::Pie3DChart) do |chart| - chart.title = sheet["B11"] - chart.add_series :data => sheet["C13:C26"], :labels => sheet["B13:B26"] - chart.start_at 7, 2 - chart.end_at 12, 15 - end - sheet.add_chart(Axlsx::Bar3DChart, :barDir => :col) do |chart| - chart.title = sheet["E11"] - chart.add_series :labels => sheet["E13:E18"], :data => sheet["F13:F18"] - chart.start_at 7, 16 - chart.end_at 12, 31 - end - sheet.merged_cells.concat ["B4:C4","E4:F4","B11:C11","E11:F11","B2:F2"] - sheet.column_widths 2, nil, nil, 2, nil, nil, 2 - end - end - p.use_shared_strings = true - p.serialize 'axlsx.xlsx' -end diff --git a/examples/scaled_colors.rb b/examples/scaled_colors.rb deleted file mode 100644 index dcbaf11d..00000000 --- a/examples/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/skydrive/real_example.rb b/examples/skydrive/real_example.rb new file mode 100644 index 00000000..0274ebe9 --- /dev/null +++ b/examples/skydrive/real_example.rb @@ -0,0 +1,63 @@ +$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" + +require 'axlsx' + +Axlsx::Package.new do |p| + p.workbook do |wb| + styles = wb.styles + header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center} + tbl_header = styles.add_style :b => true, :alignment => { :horizontal => :center } + ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1} + col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => { :horizontal => :center } + label = styles.add_style :alignment => { :indent => 1 } + money = styles.add_style :num_fmt => 5 + t_label = styles.add_style :b => true, :bg_color => "FFDFDEDF" + t_money = styles.add_style :b => true, :num_fmt => 5, :bg_color => "FFDFDEDF" + + wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row [nil, "College Budget"], :style => [nil, header] + sheet.add_row + sheet.add_row [nil, "What's coming in this month.", nil, nil, "How am I doing"], :style => tbl_header + sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] + sheet.add_row [nil, "Estimated monthly net income", 500, nil, "Monthly income", "=C9"], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Financial aid", 100, nil, "Monthly expenses", "=C27"], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Allowance from mom & dad", 20000, nil, "Semester expenses", "=F19"], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Total", "=SUM(C6:C8)", nil, "Difference", "=F6 - SUM(F7:F8)"], :style => [nil, t_label, t_money, nil, t_label, t_money] + sheet.add_row + sheet.add_row [nil, "What's going out this month.", nil, nil, "Semester Costs"], :style => tbl_header + sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] + sheet.add_row [nil, "Rent", 650, nil, "Tuition", 200], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Utilities", 120, nil, "Lab fees", 50], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Cell phone", 100, nil, "Other fees", 10], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Groceries", 75, nil, "Books", 150], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Auto expenses", 0, nil, "Deposits", 0], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Student loans", 0, nil, "Transportation", 30], :style => [nil, label, money, nil, label, money] + sheet.add_row [nil, "Other loans", 350, nil, "Total", "=SUM(F13:F18)"], :style => [nil, label, money, nil, t_label, t_money] + sheet.add_row [nil, "Credit cards", 450], :style => [nil, label, money] + sheet.add_row [nil, "Insurance", 0], :style => [nil, label, money] + sheet.add_row [nil, "Laundry", 10], :style => [nil, label, money] + sheet.add_row [nil, "Haircuts", 0], :style => [nil, label, money] + sheet.add_row [nil, "Medical expenses", 0], :style => [nil, label, money] + sheet.add_row [nil, "Entertainment", 500], :style => [nil, label, money] + sheet.add_row [nil, "Miscellaneous", 0], :style => [nil, label, money] + sheet.add_row [nil, "Total", "=SUM(C13:C26)"], :style => [nil, t_label, t_money] + sheet.add_chart(Axlsx::Pie3DChart) do |chart| + chart.title = sheet["B11"] + chart.add_series :data => sheet["C13:C26"], :labels => sheet["B13:B26"] + chart.start_at 7, 2 + chart.end_at 12, 15 + end + sheet.add_chart(Axlsx::Bar3DChart, :barDir => :col) do |chart| + chart.title = sheet["E11"] + chart.add_series :labels => sheet["E13:E18"], :data => sheet["F13:F18"] + chart.start_at 7, 16 + chart.end_at 12, 31 + end + sheet.merged_cells.concat ["B4:C4","E4:F4","B11:C11","E11:F11","B2:F2"] + sheet.column_widths 2, nil, nil, 2, nil, nil, 2 + end + end + p.use_shared_strings = true + p.serialize 'axlsx.xlsx' +end diff --git a/examples/stop_and_go.rb b/examples/stop_and_go.rb deleted file mode 100644 index 301bf3fa..00000000 --- a/examples/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' diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb index 3e6192e0..3e8c3e1b 100644 --- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb +++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb @@ -7,6 +7,8 @@ module Axlsx # @see Worksheet#add_conditional_formatting # @see ConditionalFormattingRule#initialize class ConditionalFormattingRule + + # instance values that must be serialized as their own elements - e.g. not attributes. CHILD_ELEMENTS = [:formula, :color_scale, :data_bar, :icon_set] # Formula diff --git a/lib/axlsx/workbook/worksheet/data_bar.rb b/lib/axlsx/workbook/worksheet/data_bar.rb index 701fba84..c5054876 100644 --- a/lib/axlsx/workbook/worksheet/data_bar.rb +++ b/lib/axlsx/workbook/worksheet/data_bar.rb @@ -6,6 +6,8 @@ module Axlsx # @see Worksheet#add_conditional_formatting # @see ConditionalFormattingRule#initialize class DataBar + + # instance values that must be serialized as their own elements - e.g. not attributes. CHILD_ELEMENTS = [:value_objects, :color] # minLength attribute diff --git a/lib/axlsx/workbook/worksheet/icon_set.rb b/lib/axlsx/workbook/worksheet/icon_set.rb index 68f217da..534c33c1 100644 --- a/lib/axlsx/workbook/worksheet/icon_set.rb +++ b/lib/axlsx/workbook/worksheet/icon_set.rb @@ -6,6 +6,8 @@ module Axlsx # @see Worksheet#add_conditional_formatting # @see ConditionalFormattingRule#initialize class IconSet + + # instance values that must be serialized as their own elements - e.g. not attributes. CHILD_ELEMENTS = [:value_objects] # The icon set to display. -- cgit v1.2.3