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.



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

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:



85
86
87
 
# File 'lib/axlsx/stylesheet/styles.rb', line 85

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:



97
98
99
 
# File 'lib/axlsx/stylesheet/styles.rb', line 97

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:



91
92
93
 
# File 'lib/axlsx/stylesheet/styles.rb', line 91

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:



103
104
105
 
# File 'lib/axlsx/stylesheet/styles.rb', line 103

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:



109
110
111
 
# File 'lib/axlsx/stylesheet/styles.rb', line 109

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:



78
79
80
 
# File 'lib/axlsx/stylesheet/styles.rb', line 78

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:



72
73
74
 
# File 'lib/axlsx/stylesheet/styles.rb', line 72

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](#,##0)
 39 #,##0.00;(#,##0.00)
 40 #,##0.00;[Red](#,##0.00)
 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


66
67
68
 
# File 'lib/axlsx/stylesheet/styles.rb', line 66

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:



115
116
117
 
# File 'lib/axlsx/stylesheet/styles.rb', line 115

def tableStyles
  @tableStyles
end

Instance Method Details

#add_style(options = {}) ⇒ Integer

Drastically simplifies style creation and management.

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.style.add_style(:bg_color => "FFFF0000", :fg_color=>"#FF000000", :sz=>14,  :border=>Axlsx::STYLE_THIN_BORDER

ws.add_row :values => ["Least Popular Pets"]
ws.add_row :values => ["", "Dry Skinned Reptiles", "Bald Cats", "Violent Parrots"], :style=>title
ws.add_row :values => ["Votes", 6, 4, 1], :style=>Axlsx::STYLE_THIN_BORDER
f = File.open('example_you_got_style.xlsx', 'w')
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.style.add_style(:bg_color => "FFFF0000", 
                           :fg_color=>"#FF000000",
                           :border=>Axlsx::STYLE_THIN_BORDER, 
                           :alignment=>{:horizontal => :center})

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

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

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

# build your rows
ws.add_row :values => ["Genreated At:", Time.now], :styles=>[nil, date_time]
ws.add_row :values => ["Previous Year Quarterly Profits (JPY)"], :style=>title
ws.add_row :values => ["Quarter", "Profit", "% of Total"], :style=>title
ws.add_row :values => ["Q1", 4000, 40], :style=>[title, currency, percent]
ws.add_row :values => ["Q2", 3000, 30], :style=>[title, currency, percent]
ws.add_row :values => ["Q3", 1000, 10], :style=>[title, currency, percent]
ws.add_row :values => ["Q4", 2000, 20], :style=>[title, currency, percent]
f = File.open('example_you_got_style.xlsx', 'w')
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

  • strike (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. If this is specified, num_fmt is ignored.

  • border (Integer) —

    The border style to use.

  • 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

  • alignment (Hash) —

    A hash defining any of the attributes used in CellAlignment

Returns:

  • (Integer)

Raises:

  • (ArgumentError)

See Also:



192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'lib/axlsx/stylesheet/styles.rb', line 192

def add_style(options={})
  
  numFmtId = if options[:format_code]
               n = @numFmts.map{ |f| f.numFmtId }.max + 1
               numFmts << NumFmt.new(:numFmtId => n, :formatCode=> options[:format_code])
               n
             else
               options[:num_fmt] || 0
             end
  
  borderId = options[:border] || 0

  raise ArgumentError, "Invalid borderId" unless borderId < borders.size
  
  fill = if options[:bg_color]
           color = Color.new(:rgb=>options[:bg_color])
           pattern = PatternFill.new(:patternType =>:solid, :fgColor=>color)
           fills << Fill.new(pattern)   
         else
           0
         end
  
  fontId = if (options.values_at(:fg_color, :sz, :b, :i, :u, :strike, :outline, :shadow, :charset, :family, :font_name).length)
             font = Font.new()
             [:b, :i, :u, :strike, :outline, :shadow, :charset, :family, :sz].each { |k| font.send("#{k}=", options[k]) unless options[k].nil? }
             font.color = Color.new(:rgb => options[:fg_color]) unless options[:fg_color].nil?
             font.name = options[:font_name] unless options[:font_name].nil?
             fonts << font
           else
             0 
           end
  
  applyProtection = (options[:hidden] || options[:locked]) ? 1 : 0
  
  xf = Xf.new(:fillId => fill, :fontId=>fontId, :applyFill=>1, :applyFont=>1, :numFmtId=>numFmtId, :borderId=>borderId, :applyProtection=>applyProtection)

  xf.applyNumberFormat = true if xf.numFmtId > 0
  xf.applyBorder = true if borderId > 0
  
  if options[:alignment]
    xf.alignment = CellAlignment.new(options[:alignment])
  end
  
  if applyProtection
    xf.protection = CellProtection.new(options)
  end
  
  cellXfs << xf
end

#to_xml ⇒ String

Serializes the styles document

Returns:

  • (String) 


244
245
246
247
248
249
250
251
252
253
 
# File 'lib/axlsx/stylesheet/styles.rb', line 244

def to_xml()
  builder = Nokogiri::XML::Builder.new(:encoding => ENCODING) do |xml|
    xml.styleSheet(:xmlns => XML_NS) {
      [:numFmts, :fonts, :fills, :borders, :cellStyleXfs, :cellXfs, :cellStyles, :dxfs, :tableStyles].each do |key|
        self.instance_values[key.to_s].to_xml(xml) unless self.instance_values[key.to_s].nil?
      end
    }
  end
  builder.to_xml(:save_with => 0)
end