# adapted from http://axlsx.blogspot.com/, https://gist.github.com/2484520
require 'axlsx'
p = Axlsx::Package.new

# Required for use with numbers
p.use_shared_strings = true

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
  pascal_colors = { :bg_color => '567DCC', :fg_color => 'FFFF00' }
  pascal = styles.add_style pascal_colors.merge({ :border => Axlsx::STYLE_THIN_BORDER, :b => true })
  header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true
  money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER
  money_pascal = styles.add_style pascal_colors.merge({ :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER })
  percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER
  percent_pascal = styles.add_style pascal_colors.merge({ :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER })

  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
    # Passing one style applies the style to all columns
    ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => pascal

    # Otherwise you can specify a style for each column.
    ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [pascal, money_pascal, percent_pascal]
    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]

    # You can merge cells!
    ws.merge_cells 'A1:C1'

  end
end
p.serialize 'getting_barred.xlsx'