Method: Axlsx::Styles#add_style

Defined in:

lib/axlsx/stylesheet/styles.rb

#add_style(options = {}) ⇒ Integer

Drastically simplifies style creation and management. An index for cell styles where keys are styles codes as per Axlsx::Style and values are Cell#raw_style The reason for the backward key/value ordering is that style lookup must be most efficient, while add_style can be less efficient

Examples:

You Got Style

require "rubygems" # if that is your preferred way to manage gems!
require "axlsx"

p = Axlsx::Package.new
ws = p.workbook.add_worksheet

# black text on a white background at 14pt with thin borders!
title = ws.styles.add_style(:bg_color => "FFFF0000", :fg_color=>"#FF000000", :sz=>14,  :border=> {:style => :thin, :color => "FFFF0000"}

ws.add_row ["Least Popular Pets"]
ws.add_row ["", "Dry Skinned Reptiles", "Bald Cats", "Violent Parrots"], :style=>title
ws.add_row ["Votes", 6, 4, 1], :style=>Axlsx::STYLE_THIN_BORDER
f = File.open('example_you_got_style.xlsx', 'wb')
p.serialize(f)

Styling specifically

# an example of applying specific styles to specific cells
require "rubygems" # if that is your preferred way to manage gems!
require "axlsx"

p = Axlsx::Package.new
ws = p.workbook.add_worksheet

# define your styles
title = ws.styles.add_style(:bg_color => "FFFF0000",
                           :fg_color=>"#FF000000",
                           :border=>Axlsx::STYLE_THIN_BORDER,
                           :alignment=>{:horizontal => :center})

date_time = ws.styles.add_style(:num_fmt => Axlsx::NUM_FMT_YYYYMMDDHHMMSS,
                               :border=>Axlsx::STYLE_THIN_BORDER)

percent = ws.styles.add_style(:num_fmt => Axlsx::NUM_FMT_PERCENT,
                             :border=>Axlsx::STYLE_THIN_BORDER)

currency = ws.styles.add_style(:format_code=>"¥#,##0;[Red]¥-#,##0",
                              :border=>Axlsx::STYLE_THIN_BORDER)

# build your rows
ws.add_row ["Generated At:", Time.now], :styles=>[nil, date_time]
ws.add_row ["Previous Year Quarterly Profits (JPY)"], :style=>title
ws.add_row ["Quarter", "Profit", "% of Total"], :style=>title
ws.add_row ["Q1", 4000, 40], :style=>[title, currency, percent]
ws.add_row ["Q2", 3000, 30], :style=>[title, currency, percent]
ws.add_row ["Q3", 1000, 10], :style=>[title, currency, percent]
ws.add_row ["Q4", 2000, 20], :style=>[title, currency, percent]
f = File.open('example_you_got_style.xlsx', 'wb')
p.serialize(f)

Differential styling

# Differential styles apply on top of cell styles. Used in Conditional Formatting. Must specify :type => :dxf, and you can't use :num_fmt.
require "rubygems" # if that is your preferred way to manage gems!
require "axlsx"

p = Axlsx::Package.new
wb = p.workbook
ws = wb.add_worksheet

# define your styles
profitable = wb.styles.add_style(:bg_color => "FFFF0000",
                           :fg_color=>"#FF000000",
                           :type => :dxf)

ws.add_row ["Genreated At:", Time.now], :styles=>[nil, date_time]
ws.add_row ["Previous Year Quarterly Profits (JPY)"], :style=>title
ws.add_row ["Quarter", "Profit", "% of Total"], :style=>title
ws.add_row ["Q1", 4000, 40], :style=>[title, currency, percent]
ws.add_row ["Q2", 3000, 30], :style=>[title, currency, percent]
ws.add_row ["Q3", 1000, 10], :style=>[title, currency, percent]
ws.add_row ["Q4", 2000, 20], :style=>[title, currency, percent]

ws.add_conditional_formatting("A1:A7", { :type => :cellIs, :operator => :greaterThan, :formula => "2000", :dxfId => profitable, :priority => 1 })
f = File.open('example_differential_styling', 'wb')
p.serialize(f)

Parameters:

• options (Hash) (defaults to: {}) —

  a customizable set of options

Options Hash (options):

• fg_color (String) —

  The text color

• sz (Integer) —

  The text size

• b (Boolean) —

  Indicates if the text should be bold

• i (Boolean) —

  Indicates if the text should be italicised

• u (Boolean) —

  Indicates if the text should be underlined

• strike (Boolean) —

  Indicates if the text should be rendered with a strikethrough

• shadow (Boolean) —

  Indicates if the text should be rendered with a shadow

• charset (Integer) —

  The character set to use.

• family (Integer) —

  The font family to use.

• font_name (String) —

  The name of the font to use

• num_fmt (Integer) —

  The number format to apply

• format_code (String) —

  The formatting to apply.

• border (Integer|Hash) —

  The border style to use. borders support style, color and edges options @see parse_border_options

• bg_color (String) —

  The background color to apply to the cell

• hidden (Boolean) —

  Indicates if the cell should be hidden

• locked (Boolean) —

  Indicates if the cell should be locked

• type (Symbol) —

  What type of style is this. Options are [:dxf, :xf]. :xf is default

• alignment (Hash) —

  A hash defining any of the attributes used in CellAlignment

Returns:

• (Integer)

Raises:

• (ArgumentError)

See Also:

• CellAlignment

# File 'lib/axlsx/stylesheet/styles.rb', line 227

def add_style(options = {})
  # Default to :xf
  options[:type] ||= :xf

  raise ArgumentError, "Type must be one of [:xf, :dxf]" unless [:xf, :dxf].include?(options[:type])

  if options[:border].is_a?(Hash)
    if options[:border][:edges] == :all
      options[:border][:edges] = Axlsx::Border::EDGES
    elsif options[:border][:edges]
      options[:border][:edges] = options[:border][:edges].map(&:to_sym) ### normalize for style caching
    end
  end

  if options[:type] == :xf
    # Check to see if style in cache already

    font_defaults = { name: @fonts.first.name, sz: @fonts.first.sz, family: @fonts.first.family }

    raw_style = { type: :xf }.merge(font_defaults).merge(options)

    if raw_style[:format_code]
      raw_style.delete(:num_fmt)
    end

    xf_index = style_index.key(raw_style)

    if xf_index
      return xf_index
    end
  end

  fill = parse_fill_options options
  font = parse_font_options options
  numFmt = parse_num_fmt_options options
  border = parse_border_options options
  alignment = parse_alignment_options options
  protection = parse_protection_options options

  case options[:type]
  when :dxf
    style = Dxf.new :fill => fill, :font => font, :numFmt => numFmt, :border => border, :alignment => alignment, :protection => protection
  else
    style = Xf.new :fillId => fill || 0, :fontId => font || 0, :numFmtId => numFmt || 0, :borderId => border || 0, :alignment => alignment, :protection => protection, :applyFill => !fill.nil?, :applyFont => !font.nil?, :applyNumberFormat => !numFmt.nil?, :applyBorder => !border.nil?, :applyAlignment => !alignment.nil?, :applyProtection => !protection.nil?
  end

  if options[:type] == :xf
    xf_index = (cellXfs << style)

    # Add styles to style_index cache for re-use
    style_index[xf_index] = raw_style

    return xf_index
  else
    dxf_index = (dxfs << style)

    return dxf_index
  end
end