Axlxs: Writing Excel With Ruby - Conditional Formatting
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' |
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' |
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)', '72230000', '=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' |
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' |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment