Class: Axlsx::Styles

Inherits:
Object
  • Object
show all
Defined in:
lib/axlsx/stylesheet/styles.rb

Overview

Note:

The recommended way to manage styles is with add_style

The Styles class manages worksheet styles In addition to creating the require style objects for a valid xlsx package, this class provides the key mechanism for adding styles to your workbook, and safely applying them to the cells of your worksheet. All portions of the stylesheet are implemented here exception colors, which specify legacy and modified pallete colors, and exLst, whic is used as a future feature data storage area.

See Also:

  • Open XML Part 1 18.8.11 for gory details on how this stuff gets put together
  • #add_style

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ Styles

Creates a new Styles object and prepopulates it with the requires objects to generate a valid package style part.



120
121
122
 
# File 'lib/axlsx/stylesheet/styles.rb', line 120

def initialize
  load_default_styles
end

Instance Attribute Details

#borders ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of borders used in this workbook Axlsx predefines THIN_BORDER which can be used to put a border around all of your cells.

Returns:

  • (SimpleTypedList)

See Also:



87
88
89
 
# File 'lib/axlsx/stylesheet/styles.rb', line 87

def borders
  @borders
end

#cellStyles ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of named styles, referencing cellStyleXfs items in the workbook.

Returns:

  • (SimpleTypedList)

See Also:



99
100
101
 
# File 'lib/axlsx/stylesheet/styles.rb', line 99

def cellStyles
  @cellStyles
end

#cellStyleXfs ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of master formatting records for named cell styles, which means records defined in cellStyles, in the workbook

Returns:

  • (SimpleTypedList)

See Also:



93
94
95
 
# File 'lib/axlsx/stylesheet/styles.rb', line 93

def cellStyleXfs
  @cellStyleXfs
end

#cellXfs ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of master formatting records. This is the list that you will actually use in styling a workbook.

Returns:

  • (SimpleTypedList)

See Also:



105
106
107
 
# File 'lib/axlsx/stylesheet/styles.rb', line 105

def cellXfs
  @cellXfs
end

#dxfs ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of non-cell formatting records used in the worksheet.

Returns:

  • (SimpleTypedList)

See Also:



111
112
113
 
# File 'lib/axlsx/stylesheet/styles.rb', line 111

def dxfs
  @dxfs
end

#fills ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of fills used in this workbook

Returns:

  • (SimpleTypedList)

See Also:



80
81
82
 
# File 'lib/axlsx/stylesheet/styles.rb', line 80

def fills
  @fills
end

#fonts ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of fonts used in this workbook

Returns:

  • (SimpleTypedList)

See Also:



74
75
76
 
# File 'lib/axlsx/stylesheet/styles.rb', line 74

def fonts
  @fonts
end

#numFmts ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

numFmts for your styles. The default styles, which change based on the system local, are as follows. id formatCode 0 General 1 0 2 0.00 3 #,##0 4 #,##0.00 9 0% 10 0.00% 11 0.00E+00 12 # ?/? 13 # ??/?? 14 mm-dd-yy 15 d-mmm-yy 16 d-mmm 17 mmm-yy 18 h:mm AM/PM 19 h:mm:ss AM/PM 20 h:mm 21 h:mm:ss 22 m/d/yy h:mm 37 #,##0 ;(#,##0) 38 #,##0 ;Red 39 #,##0.00;(#,##0.00) 40 #,##0.00;Red 45 mm:ss 46 [h]:mm:ss 47 mmss.0 48 ##0.0E+0 49 @ Axlsx also defines the following constants which you can use in add_style. NUM_FMT_PERCENT formats to "0%" NUM_FMT_YYYYMMDD formats to "yyyy/mm/dd" NUM_FMT_YYYYMMDDHHMMSS formats to "yyyy/mm/dd hh:mm:ss"

Returns:

  • (SimpleTypedList)

See Also:

  • Open XML Part 1 - 18.8.31 for more information on creating number formats
  • #add_style


68
69
70
 
# File 'lib/axlsx/stylesheet/styles.rb', line 68

def numFmts
  @numFmts
end

#tableStyles ⇒ SimpleTypedList (readonly)

Note:

The recommended way to manage styles is with add_style

The collection of table styles that will be available to the user in the Excel UI

Returns:

  • (SimpleTypedList)

See Also:



117
118
119
 
# File 'lib/axlsx/stylesheet/styles.rb', line 117

def tableStyles
  @tableStyles
end

Instance Method Details

#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:



229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/axlsx/stylesheet/styles.rb', line 229

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

  style = case options[:type]
          when :dxf
            Dxf.new fill: fill, font: font, numFmt: numFmt, border: border, alignment: alignment, protection: protection
          else
            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

    xf_index
  else
    dxfs << style
  end
end

#parse_alignment_options(options = {}) ⇒ CellAlignment

parses add_style options for alignment noop if options hash does not include :alignment key

Parameters:

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

    a customizable set of options

Options Hash (options):

  • alignment (Hash) —

    A hash of options to prive the CellAlignment intializer

Returns:

See Also:



304
305
306
307
308
 
# File 'lib/axlsx/stylesheet/styles.rb', line 304

def parse_alignment_options(options = {})
  return unless options[:alignment]

  CellAlignment.new options[:alignment]
end

#parse_border_options(options = {}) ⇒ Border|Integer

Note:

noop if :border is not specified in options

parses Style#add_style options for borders. Border style definition hashes must include :style and :color key-value entries and may include an :edges entry that references an array of symbols identifying which border edges you wish to apply the style or any other valid Border initializer options. If the :edges entity is not provided the style is applied to all edges of cells that reference this style. Also available :border_top, :border_right, :border_bottom and :border_left options with :style and/or :color key-value entries, which override :border values.

Examples:

#apply a thick red border to the top and bottom
{ :border => { :style => :thick, :color => "FFFF0000", :edges => [:top, :bottom] }

Parameters:

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

    a customizable set of options

Options Hash (options):

  • A (Hash|Integer) —

    border style definition hash or the index of an existing border.

Returns:



403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
 
# File 'lib/axlsx/stylesheet/styles.rb', line 403

def parse_border_options(options = {})
  if options[:border].nil? && Border::EDGES.all? { |x| options[:"border_#{x}"].nil? }
    return nil
  end

  if options[:border].is_a?(Integer)
    if options[:border] >= borders.size
      raise ArgumentError, format(ERR_INVALID_BORDER_ID, options[:border])
    end

    if options[:type] == :dxf
      return borders[options[:border]].clone
    else
      return options[:border]
    end
  end

  validate_border_hash = lambda { |val|
    unless val.key?(:style) && val.key?(:color)
      raise ArgumentError, format(ERR_INVALID_BORDER_OPTIONS, options[:border])
    end
  }

  borders_array = []

  if options[:border].nil?
    base_border_opts = {}
  elsif options[:border].is_a?(Array)
    borders_array += options[:border]

    base_border_opts = {}

    options[:border].each do |b_opts|
      if b_opts[:edges].nil?
        base_border_opts = base_border_opts.merge(b_opts)
      end
    end
  else
    borders_array << options[:border]

    base_border_opts = options[:border]

    validate_border_hash.call(base_border_opts)
  end

  Border::EDGES.each do |edge|
    val = options[:"border_#{edge}"]

    if val
      borders_array << val.merge(edges: [edge])
    end
  end

  border = Border.new(base_border_opts)

  Border::EDGES.each do |edge|
    edge_b_opts = base_border_opts

    skip_edge = true

    borders_array.each do |b_opts|
      if b_opts[:edges] && b_opts[:edges].include?(edge)
        edge_b_opts = edge_b_opts.merge(b_opts)
        skip_edge = false
      end
    end

    if options[:"border_#{edge}"]
      edge_b_opts = edge_b_opts.merge(options[:"border_#{edge}"])
      skip_edge = false
    end

    if skip_edge && base_border_opts[:edges]
      next
    end

    unless edge_b_opts.empty?
      if base_border_opts.empty?
        validate_border_hash.call(edge_b_opts)
      end

      border.prs << BorderPr.new({
        name: edge,
        style: edge_b_opts[:style],
        color: Color.new(rgb: edge_b_opts[:color])
      })
    end
  end

  if options[:type] == :dxf
    border
  else
    borders << border
  end
end

#parse_fill_options(options = {}) ⇒ Fill|Integer

Note:

noop unless at least one of the documented attributes is specified in options

parses add_style options for fills. If the options hash contains :type => :dxf we return a Fill object. If not, we return the index of the fill after being added to the fills collection.

Parameters:

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

    a customizable set of options

Options Hash (options):

  • bg_color (String) —

    The rgb color to apply to the fill. An alias for pattern_bg_color if you need only a solid background

  • pattern_type (String) —

    The fill pattern to apply to the fill

  • pattern_bg_color (String) —

    The rgb color to apply to the fill as the first color

  • pattern_fg_color (String) —

    The rgb color to apply to the fill as the second color

Returns:



345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
 
# File 'lib/axlsx/stylesheet/styles.rb', line 345

def parse_fill_options(options = {})
  return unless options[:bg_color] || options[:pattern_type] || options[:pattern_bg_color] || options[:pattern_fg_color]

  pattern_type = options[:pattern_type] || :solid
  dxf = options[:type] == :dxf

  pattern_options = {
    patternType: pattern_type
  }

  if options[:pattern_bg_color] && options[:bg_color]
    warn 'Both `bg_color` and `pattern_bg_color` got defined. To get a solid background without defining it in `patter_type`, use only `bg_color`, otherwise use only `pattern_bg_color` to avoid confusion.'
  end

  bg_color = options[:pattern_bg_color] || options[:bg_color]
  fg_color = options[:pattern_fg_color]

  # Both bgColor and fgColor happens to configure the background of the cell.
  # One of them sets the "background" of the cell, while the other one is
  # responsibe for the "pattern" of the cell. When you pick "solid" pattern for
  # a normal xf style, then it's a rectangle covering all bgColor with fgColor,
  # which means we need to to set the given background color to fgColor as well.
  # For some reason I wasn't able find, it works the opposite for dxf styles
  # (differential formatting records), so to get the expected color, we need
  # to put it into bgColor. We only need these cross-assignments when using
  # "solid" pattern and the user provided only one color to get the least
  # amount of surprise

  if bg_color
    pattern_options[:bgColor] = Color.new(rgb: bg_color)
  elsif pattern_type == :solid && fg_color
    pattern_options[:bgColor] = Color.new(rgb: fg_color)
  end

  if fg_color
    pattern_options[:fgColor] = Color.new(rgb: fg_color)
  elsif pattern_type == :solid && bg_color
    pattern_options[:fgColor] = Color.new(rgb: bg_color)
  end

  pattern = PatternFill.new(pattern_options)
  fill = Fill.new(pattern)
  dxf ? fill : fills << fill
end

#parse_font_options(options = {}) ⇒ Font|Integer

Note:

noop if none of the options described here are set on the options parameter.

parses add_style options for fonts. If the options hash contains :type => :dxf we return a new Font object. if not, we return the index of the newly created font object in the styles.fonts collection.

Parameters:

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

    a customizable set of options

Options Hash (options):

  • type (Symbol) —

    The type of style object we are working with (dxf or xf)

  • 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

  • outline (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

Returns:



325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/axlsx/stylesheet/styles.rb', line 325

def parse_font_options(options = {})
  return if (options.keys & [:fg_color, :sz, :b, :i, :u, :strike, :outline, :shadow, :charset, :family, :font_name]).empty?

  Axlsx.instance_values_for(fonts.first).each do |key, value|
    # Thanks for that 1.8.7 - cant do a simple merge...
    options[key.to_sym] = value unless options.key?(key.to_sym)
  end
  font = Font.new(options)
  font.color = Color.new(rgb: options[:fg_color]) if options[:fg_color]
  font.name = options[:font_name] if options[:font_name]
  options[:type] == :dxf ? font : fonts << font
end

#parse_num_fmt_options(options = {}) ⇒ NumFmt|Integer

Parses Style#add_style options for number formatting. noop if neither :format_code or :num_format options are set.

Parameters:

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

    a customizable set of options

Options Hash (options):

  • A (Hash) —

    hash describing the :format_code and/or :num_fmt integer for the style.

Returns:



503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
 
# File 'lib/axlsx/stylesheet/styles.rb', line 503

def parse_num_fmt_options(options = {})
  return if (options.keys & [:format_code, :num_fmt]).empty?

  # When the user provides format_code - we always need to create a new numFmt object
  # When the type is :dxf we always need to create a new numFmt object
  if options[:format_code] || options[:type] == :dxf
    # If this is a standard xf we pull from numFmts the highest current and increment for num_fmt
    options[:num_fmt] ||= (@numFmts.map(&:numFmtId).max + 1) if options[:type] != :dxf
    numFmt = NumFmt.new(numFmtId: options[:num_fmt] || 0, formatCode: options[:format_code].to_s)
    if options[:type] == :dxf
      numFmt
    else
      numFmts << numFmt
      numFmt.numFmtId
    end
  else
    options[:num_fmt]
  end
end

#parse_protection_options(options = {}) ⇒ CellProtection

Parameters:

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

    a customizable set of options

Options Hash (options):

  • hide (Boolean) —

    boolean value defining cell protection attribute for hiding.

  • locked (Boolean) —

    boolean value defining cell protection attribute for locking.

Returns:



293
294
295
296
297
 
# File 'lib/axlsx/stylesheet/styles.rb', line 293

def parse_protection_options(options = {})
  return if (options.keys & [:hidden, :locked]).empty?

  CellProtection.new(options)
end

#style_index ⇒ Object 



124
125
126
 
# File 'lib/axlsx/stylesheet/styles.rb', line 124

def style_index
  @style_index ||= {}
end

#to_xml_string(str = +'')) ⇒ String

Serializes the object

Parameters:

  • str (String) (defaults to: +''))

Returns:

  • (String) 


526
527
528
529
530
531
532
533
 
# File 'lib/axlsx/stylesheet/styles.rb', line 526

def to_xml_string(str = +'')
  str << '<styleSheet xmlns="' << XML_NS << '">'
  instance_vals = Axlsx.instance_values_for(self)
  [:numFmts, :fonts, :fills, :borders, :cellStyleXfs, :cellXfs, :cellStyles, :dxfs, :tableStyles].each do |key|
    instance_vals[key.to_s].to_xml_string(str) unless instance_vals[key.to_s].nil?
  end
  str << '</styleSheet>'
end