Class: Writexlsx::Format

Inherits:
Object
  • Object
show all
Includes:
Utility
Defined in:
lib/write_xlsx/format.rb

Overview

CELL FORMATTING

This section describes the methods and properties that are available for formatting cells in Excel. The properties of a cell that can be formatted include: fonts, colours, patterns, borders, alignment and number formatting.

Creating and using a Format object

Cell formatting is defined through a Format object. Format objects are created by calling the workbook add_format() method as follows:

format1 = workbook.add_format             # Set properties later
format2 = workbook.add_format(props_hash) # Set at creation

The format object holds all the formatting properties that can be applied to a cell, a row or a column. The process of setting these properties is discussed in the next section.

Once a Format object has been constructed and its properties have been set it can be passed as an argument to the worksheet write methods as follows:

worksheet.write( 0, 0, 'One', format )
worksheet.write_string( 1, 0, 'Two', format )
worksheet.write_number( 2, 0, 3, format )
worksheet.write_blank( 3, 0, format )

Formats can also be passed to the worksheet set_row() and set_column() methods to define the default property for a row or column.

worksheet.set_row( 0, 15, format )
worksheet.set_column( 0, 0, 15, format )

Format methods and Format properties

The following table shows the Excel format categories, the formatting properties that can be applied and the equivalent object method:

Category   Description       Property        Method Name
--------   -----------       --------        -----------
Font       Font type         font            set_font()
           Font size         size            set_size()
           Font color        color           set_color()
           Bold              bold            set_bold()
           Italic            italic          set_italic()
           Underline         underline       set_underline()
           Strikeout         font_strikeout  set_font_strikeout()
           Super/Subscript   font_script     set_font_script()
           Outline           font_outline    set_font_outline()
           Shadow            font_shadow     set_font_shadow()

Number     Numeric format    num_format      set_num_format()

Protection Lock cells        locked          set_locked()
           Hide formulas     hidden          set_hidden()

Alignment  Horizontal align  align           set_align()
           Vertical align    valign          set_align()
           Rotation          rotation        set_rotation()
           Text wrap         text_wrap       set_text_wrap()
           Justify last      text_justlast   set_text_justlast()
           Center across     center_across   set_center_across()
           Indentation       indent          set_indent()
           Shrink to fit     shrink          set_shrink()

Pattern    Cell pattern      pattern         set_pattern()
           Background color  bg_color        set_bg_color()
           Foreground color  fg_color        set_fg_color()

Border     Cell border       border          set_border()
           Bottom border     bottom          set_bottom()
           Top border        top             set_top()
           Left border       left            set_left()
           Right border      right           set_right()
           Border color      border_color    set_border_color()
           Bottom color      bottom_color    set_bottom_color()
           Top color         top_color       set_top_color()
           Left color        left_color      set_left_color()
           Right color       right_color     set_right_color()

There are two ways of setting Format properties: by using the object method interface or by setting the property directly. For example, a typical use of the method interface would be as follows:

format = workbook.add_format
format.set_bold
format.set_color( 'red' )

By comparison the properties can be set directly by passing a hash of properties to the Format constructor:

format = workbook.add_format( :bold => 1, :color => 'red' )

or after the Format has been constructed by means of the set_format_properties() method as follows:

format = workbook.add_format
format.set_format_properties( :bold => 1, :color => 'red' )

You can also store the properties in one or more named hashes and pass them to the required method:

font = {
    :font  => 'Arial',
    :size  => 12,
    :color => 'blue',
    :bold  => 1
}

shading = {
    :bg_color => 'green',
    :pattern  => 1
}

format1 = workbook.add_format( font )           # Font only
format2 = workbook.add_format( font, shading )  # Font and shading

The provision of two ways of setting properties might lead you to wonder which is the best way. The method mechanism may be better is you prefer setting properties via method calls (which the author did when the code was first written) otherwise passing properties to the constructor has proved to be a little more flexible and self documenting in practice. An additional advantage of working with property hashes is that it allows you to share formatting between workbook objects as shown in the example above.

Working with formats

The default format is Arial 10 with all other properties off.

Each unique format in WriteXLSX must have a corresponding Format object. It isn't possible to use a Format with a write() method and then redefine the Format for use at a later stage. This is because a Format is applied to a cell not in its current state but in its final state. Consider the following example:

format = workbook.add_format
format.set_bold
format.set_color( 'red' )
worksheet.write( 'A1', 'Cell A1', format )
format.set_color( 'green' )
worksheet.write( 'B1', 'Cell B1', format )

Cell A1 is assigned the Format format which is initially set to the colour red. However, the colour is subsequently set to green. When Excel displays Cell A1 it will display the final state of the Format which in this case will be the colour green.

In general a method call without an argument will turn a property on, for example:

format1 = workbook.add_format
format1.set_bold         # Turns bold on
format1.set_bold( 1 )    # Also turns bold on
format1.set_bold( 0 )    # Turns bold off

Constant Summary

Constants included from Utility

Utility::COL_MAX, Utility::ROW_MAX, Utility::SHEETNAME_MAX, Utility::STR_MAX

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Utility

#absolute_char, #check_dimensions, #check_dimensions_and_update_max_min_values, #check_parameter, #convert_date_time, #dash_types, delete_files, #fill_properties, #float_to_str, #layout_properties, #line_fill_properties, #line_properties, #palette_color, #pixels_to_points, #process_workbook_options, #ptrue?, #put_deprecate_message, #quote_sheetname, #r_id_attributes, #row_col_notation, #shape_style_base, #store_col_max_min_values, #store_row_max_min_values, #substitute_cellref, #underline_attributes, #v_shape_attributes_base, #v_shape_style_base, #value_or_raise, #write_anchor, #write_auto_fill, #write_color, #write_comment_path, #write_div, #write_fill, #write_stroke, #write_xml_declaration, #xl_cell_to_rowcol, #xl_col_to_name, #xl_range, #xl_range_formula, #xl_rowcol_to_cell, #xml_str

Constructor Details

#initialize(formats, params = {}) ⇒ Format

:nodoc:


177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
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
241
242
243
 
# File 'lib/write_xlsx/format.rb', line 177

def initialize(formats, params = {})   # :nodoc:
  @formats = formats

  @xf_index       = nil
  @dxf_index      = nil

  @num_format     = 0
  @num_format_index = 0
  @font_index     = 0
  @font           = 'Calibri'
  @size           = 11
  @bold           = 0
  @italic         = 0
  @color          = 0x0
  @underline      = 0
  @font_strikeout = 0
  @font_outline   = 0
  @font_shadow    = 0
  @font_script    = 0
  @font_family    = 2
  @font_charset   = 0
  @font_scheme    = 'minor'
  @font_condense  = 0
  @font_extend    = 0
  @theme          = 0
  @hyperlink      = 0

  @hidden         = 0
  @locked         = 1

  @text_h_align   = 0
  @text_wrap      = 0
  @text_v_align   = 0
  @text_justlast  = 0
  @rotation       = 0

  @fg_color       = 0x00
  @bg_color       = 0x00
  @pattern        = 0
  @fill_index     = 0
  @fill_count     = 0

  @border_index   = 0
  @border_count   = 0

  @bottom         = 0
  @bottom_color   = 0x0
  @diag_border    = 0
  @diag_color     = 0x0
  @diag_type      = 0
  @left           = 0
  @left_color     = 0x0
  @right          = 0
  @right_color    = 0x0
  @top            = 0
  @top_color      = 0x0

  @indent         = 0
  @shrink         = 0
  @merge_range    = 0
  @reading_order  = 0
  @just_distrib   = 0
  @color_indexed  = 0
  @font_only      = 0

  set_format_properties(params) unless params.empty?
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object

:nodoc:


638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
 
# File 'lib/write_xlsx/format.rb', line 638

def method_missing(name, *args)  # :nodoc:
  method = "#{name}"

  # Check for a valid method names, i.e. "set_xxx_yyy".
  method =~ /set_(\w+)/ or raise "Unknown method: #{method}\n"

  # Match the attribute, i.e. "@xxx_yyy".
  attribute = "@#{$1}"

  # Check that the attribute exists
  # ........
  if method =~ /set\w+color$/    # for "set_property_color" methods
    value = color(args[0])
  else                            # for "set_xxx" methods
    value = args[0] || 1
  end

  instance_variable_set(attribute, value)
end

Instance Attribute Details

#bg_color ⇒ Object

:nodoc:


172
173
174
 
# File 'lib/write_xlsx/format.rb', line 172

def bg_color
  @bg_color
end

#bold ⇒ Object (readonly)

Returns the value of attribute bold


175
176
177
 
# File 'lib/write_xlsx/format.rb', line 175

def bold
  @bold
end

#border_index ⇒ Object

:nodoc:


170
171
172
 
# File 'lib/write_xlsx/format.rb', line 170

def border_index
  @border_index
end

#bottom ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def bottom
  @bottom
end

#bottom_color ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def bottom_color
  @bottom_color
end

#color(color_code) ⇒ Object (readonly)

:nodoc:


167
168
169
 
# File 'lib/write_xlsx/format.rb', line 167

def color
  @color
end

#color_indexed ⇒ Object (readonly)

:nodoc:


167
168
169
 
# File 'lib/write_xlsx/format.rb', line 167

def color_indexed
  @color_indexed
end

#diag_border ⇒ Object

:nodoc:


171
172
173
 
# File 'lib/write_xlsx/format.rb', line 171

def diag_border
  @diag_border
end

#diag_color ⇒ Object (readonly)

:nodoc:


167
168
169
 
# File 'lib/write_xlsx/format.rb', line 167

def diag_color
  @diag_color
end

#diag_type ⇒ Object (readonly)

:nodoc:


167
168
169
 
# File 'lib/write_xlsx/format.rb', line 167

def diag_type
  @diag_type
end

#dxf_bg_color ⇒ Object

:nodoc:


174
175
176
 
# File 'lib/write_xlsx/format.rb', line 174

def dxf_bg_color
  @dxf_bg_color
end

#dxf_fg_color ⇒ Object

:nodoc:


174
175
176
 
# File 'lib/write_xlsx/format.rb', line 174

def dxf_fg_color
  @dxf_fg_color
end

#dxf_index ⇒ Object (readonly)

:nodoc:


165
166
167
 
# File 'lib/write_xlsx/format.rb', line 165

def dxf_index
  @dxf_index
end

#fg_color ⇒ Object

:nodoc:


172
173
174
 
# File 'lib/write_xlsx/format.rb', line 172

def fg_color
  @fg_color
end

#fill_index ⇒ Object

:nodoc:


171
172
173
 
# File 'lib/write_xlsx/format.rb', line 171

def fill_index
  @fill_index
end

#font ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def font
  @font
end

#font_condense ⇒ Object

:nodoc:


171
172
173
 
# File 'lib/write_xlsx/format.rb', line 171

def font_condense
  @font_condense
end

#font_extend ⇒ Object

:nodoc:


171
172
173
 
# File 'lib/write_xlsx/format.rb', line 171

def font_extend
  @font_extend
end

#font_family ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def font_family
  @font_family
end

#font_index ⇒ Object

:nodoc:


170
171
172
 
# File 'lib/write_xlsx/format.rb', line 170

def font_index
  @font_index
end

#font_only ⇒ Object (readonly)

:nodoc:


167
168
169
 
# File 'lib/write_xlsx/format.rb', line 167

def font_only
  @font_only
end

#font_scheme ⇒ Object (readonly)

:nodoc:


169
170
171
 
# File 'lib/write_xlsx/format.rb', line 169

def font_scheme
  @font_scheme
end

#font_script ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def font_script
  @font_script
end

#font_strikeout ⇒ Object (readonly)

Returns the value of attribute font_strikeout


175
176
177
 
# File 'lib/write_xlsx/format.rb', line 175

def font_strikeout
  @font_strikeout
end

#hyperlink ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def hyperlink
  @hyperlink
end

#italic ⇒ Object (readonly)

Returns the value of attribute italic


175
176
177
 
# File 'lib/write_xlsx/format.rb', line 175

def italic
  @italic
end

#left ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def left
  @left
end

#left_color ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def left_color
  @left_color
end

#num_format ⇒ Object (readonly)

:nodoc:


165
166
167
 
# File 'lib/write_xlsx/format.rb', line 165

def num_format
  @num_format
end

#num_format_index ⇒ Object

:nodoc:


170
171
172
 
# File 'lib/write_xlsx/format.rb', line 170

def num_format_index
  @num_format_index
end

#pattern ⇒ Object

:nodoc:


172
173
174
 
# File 'lib/write_xlsx/format.rb', line 172

def pattern
  @pattern
end

#right ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def right
  @right
end

#right_color ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def right_color
  @right_color
end

#rotation ⇒ Object (readonly)

Returns the value of attribute rotation


175
176
177
 
# File 'lib/write_xlsx/format.rb', line 175

def rotation
  @rotation
end

#size ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def size
  @size
end

#theme ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def theme
  @theme
end

#top ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def top
  @top
end

#top_color ⇒ Object (readonly)

:nodoc:


168
169
170
 
# File 'lib/write_xlsx/format.rb', line 168

def top_color
  @top_color
end

#underline ⇒ Object (readonly)

:nodoc:


166
167
168
 
# File 'lib/write_xlsx/format.rb', line 166

def underline
  @underline
end

#xf_index ⇒ Object (readonly)

:nodoc:


165
166
167
 
# File 'lib/write_xlsx/format.rb', line 165

def xf_index
  @xf_index
end

Class Method Details

.color(color_code) ⇒ Object

Used in conjunction with the set_xxx_color methods to convert a color string into a number. Color range is 0..63 but we will restrict it to 8..63 to comply with Gnumeric. Colors 0..7 are repeated in 8..15.


476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
 
# File 'lib/write_xlsx/format.rb', line 476

def self.color(color_code)

  colors = Colors::COLORS

  if color_code.respond_to?(:to_str)
    # Return RGB style colors for processing later.
    return color_code if color_code =~ /^#[0-9A-F]{6}$/i

    # Return the default color if undef,
    return 0x00 unless color_code

    # or the color string converted to an integer,
    return colors[color_code.downcase.to_sym] if colors[color_code.downcase.to_sym]

    # or the default color if string is unrecognised,
    return 0x00 if color_code =~ /\D/
  else
    # or an index < 8 mapped into the correct range,
    return color_code + 8 if color_code < 8

    # or the default color if arg is outside range,
    return 0x00 if color_code > 63

    # or an integer in the valid range
    return color_code
  end
end

Instance Method Details

#[](attr) ⇒ Object 


734
735
736
 
# File 'lib/write_xlsx/format.rb', line 734

def [](attr)
  self.instance_variable_get("@#{attr}")
end

#bold? ⇒ Boolean

Returns:

  • (Boolean) 

662
663
664
 
# File 'lib/write_xlsx/format.rb', line 662

def bold?
  ptrue?(@bold)
end

#border_attributes ⇒ Object 


787
788
789
790
791
792
793
794
795
796
797
798
799
800
 
# File 'lib/write_xlsx/format.rb', line 787

def border_attributes
  attributes = []

  # Diagonal borders add attributes to the <border> element.
  if diag_type == 1
    attributes << ['diagonalUp',   1]
  elsif diag_type == 2
    attributes << ['diagonalDown', 1]
  elsif diag_type == 3
    attributes << ['diagonalUp',   1]
    attributes << ['diagonalDown', 1]
  end
  attributes
end

#color? ⇒ Boolean

Returns:

  • (Boolean) 

658
659
660
 
# File 'lib/write_xlsx/format.rb', line 658

def color?
  ptrue?(@color)
end

#copy(other) ⇒ Object

Copy the attributes of another Format object.


248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/write_xlsx/format.rb', line 248

def copy(other)
  reserve = [
             :xf_index,
             :dxf_index,
             :xdf_format_indices,
             :palette
            ]
  (instance_variables - reserve).each do |v|
    instance_variable_set(v, other.instance_variable_get(v))
  end
end

#get_align_properties ⇒ Object

Return properties for an Style xf <alignment> sub-element.


302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/write_xlsx/format.rb', line 302

def get_align_properties
  align = []    # Attributes to return

  # Check if any alignment options in the format have been changed.
  if @text_h_align != 0 || @text_v_align != 0 || @indent != 0 ||
     @rotation != 0 || @text_wrap != 0 || @shrink != 0 || @reading_order != 0
    changed = 1
  else
    return
  end

  # Indent is only allowed for horizontal left, right and distributed. If it
  # is defined for any other alignment or no alignment has been set then
  # default to left alignment.
  @text_h_align = 1 if @indent != 0 && ![1, 3, 7].include?(@text_h_align)

  # Check for properties that are mutually exclusive.
  @shrink       = 0 if @text_wrap != 0
  @shrink       = 0 if @text_h_align == 4    # Fill
  @shrink       = 0 if @text_h_align == 5    # Justify
  @shrink       = 0 if @text_h_align == 7    # Distributed
  @just_distrib = 0 if @text_h_align != 7    # Distributed
  @just_distrib = 0 if @indent != 0

  continuous = 'centerContinuous'

  align << ['horizontal', 'left']        if @text_h_align == 1
  align << ['horizontal', 'center']      if @text_h_align == 2
  align << ['horizontal', 'right']       if @text_h_align == 3
  align << ['horizontal', 'fill']        if @text_h_align == 4
  align << ['horizontal', 'justify']     if @text_h_align == 5
  align << ['horizontal', continuous]    if @text_h_align == 6
  align << ['horizontal', 'distributed'] if @text_h_align == 7

  align << ['justifyLastLine', 1] if @just_distrib != 0

  # Property 'vertical' => 'bottom' is a default. It sets applyAlignment
  # without an alignment sub-element.
  align << ['vertical', 'top']         if @text_v_align == 1
  align << ['vertical', 'center']      if @text_v_align == 2
  align << ['vertical', 'justify']     if @text_v_align == 4
  align << ['vertical', 'distributed'] if @text_v_align == 5

  align << ['indent',       @indent]   if @indent   != 0
  align << ['textRotation', @rotation] if @rotation != 0

  align << ['wrapText',     1] if @text_wrap != 0
  align << ['shrinkToFit',  1] if @shrink    != 0

  align << ['readingOrder', 1] if @reading_order == 1
  align << ['readingOrder', 2] if @reading_order == 2

  return changed, align
end

#get_alignment_key ⇒ Object

Returns a unique hash key for alignment formats.


437
438
439
 
# File 'lib/write_xlsx/format.rb', line 437

def get_alignment_key
  [@text_h_align, @text_v_align, @indent, @rotation, @text_wrap, @shrink, @reading_order].join(':')
end

#get_border_key ⇒ Object

Returns a unique hash key for a border style. Used by Workbook.


407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
 
# File 'lib/write_xlsx/format.rb', line 407

def get_border_key
  [
    @bottom,
    @bottom_color,
    @diag_border,
    @diag_color,
    @diag_type,
    @left,
    @left_color,
    @right,
    @right_color,
    @top,
    @top_color
  ].join(':')
end

#get_dxf_index ⇒ Object

Returns the index used by Worksheet->_XF()


457
458
459
460
461
462
463
464
465
 
# File 'lib/write_xlsx/format.rb', line 457

def get_dxf_index
  if @dxf_index
    @dxf_index
  elsif @formats.dxf_index_by_key(get_format_key)
    @formats.dxf_index_by_key(get_format_key)
  else
    @dxf_index = @formats.set_dxf_index_by_key(get_format_key)
  end
end

#get_fill_key ⇒ Object

Returns a unique hash key for a fill style. Used by Workbook.


426
427
428
429
430
431
432
 
# File 'lib/write_xlsx/format.rb', line 426

def get_fill_key
  [
    @pattern,
    @bg_color,
    @fg_color
  ].join(':')
end

#get_font_key ⇒ Object

Returns a unique hash key for a font. Used by Workbook.


387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
 
# File 'lib/write_xlsx/format.rb', line 387

def get_font_key
  [
    @bold,
    @color,
    @font_charset,
    @font_family,
    @font_outline,
    @font_script,
    @font_shadow,
    @font_strikeout,
    @font,
    @italic,
    @size,
    @underline
  ].join(':')
end

#get_format_key ⇒ Object

Returns a unique hash key for the Format object.


380
381
382
 
# File 'lib/write_xlsx/format.rb', line 380

def get_format_key
  [get_font_key, get_border_key, get_fill_key, get_alignment_key, @num_format, @locked, @hidden].join(':')
end

#get_protection_properties ⇒ Object

Return properties for an Excel XML <Protection> element.


360
361
362
363
364
365
366
367
 
# File 'lib/write_xlsx/format.rb', line 360

def get_protection_properties
  attributes = []

  attributes << ['locked', 0] unless ptrue?(@locked)
  attributes << ['hidden', 1] if     ptrue?(@hidden)

  attributes.empty? ? nil : attributes
end

#get_xf_index ⇒ Object

Returns the index used by Worksheet->_XF()


444
445
446
447
448
449
450
451
452
 
# File 'lib/write_xlsx/format.rb', line 444

def get_xf_index
  if @xf_index
    @xf_index
  elsif @formats.xf_index_by_key(get_format_key)
    @formats.xf_index_by_key(get_format_key)
  else
    @xf_index = @formats.set_xf_index_by_key(get_format_key)
  end
end

#has_border(flag) ⇒ Object 


686
687
688
 
# File 'lib/write_xlsx/format.rb', line 686

def has_border(flag)
  @has_border = flag
end

#has_border? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 

690
691
692
 
# File 'lib/write_xlsx/format.rb', line 690

def has_border? # :nodoc:
  @has_border
end

#has_dxf_border(flag) ⇒ Object 


694
695
696
 
# File 'lib/write_xlsx/format.rb', line 694

def has_dxf_border(flag)
  @has_dxf_border = flag
end

#has_dxf_border? ⇒ Boolean

Returns:

  • (Boolean) 

698
699
700
 
# File 'lib/write_xlsx/format.rb', line 698

def has_dxf_border?
  @has_dxf_border
end

#has_dxf_fill(flag) ⇒ Object 


726
727
728
 
# File 'lib/write_xlsx/format.rb', line 726

def has_dxf_fill(flag)
  @has_dxf_fill = flag
end

#has_dxf_fill? ⇒ Boolean

Returns:

  • (Boolean) 

730
731
732
 
# File 'lib/write_xlsx/format.rb', line 730

def has_dxf_fill?
  @has_dxf_fill
end

#has_dxf_font(flag) ⇒ Object 


710
711
712
 
# File 'lib/write_xlsx/format.rb', line 710

def has_dxf_font(flag)
  @has_dxf_font = flag
end

#has_dxf_font? ⇒ Boolean

Returns:

  • (Boolean) 

714
715
716
 
# File 'lib/write_xlsx/format.rb', line 714

def has_dxf_font?
  @has_dxf_font
end

#has_fill(flag) ⇒ Object 


718
719
720
 
# File 'lib/write_xlsx/format.rb', line 718

def has_fill(flag)
  @has_fill = flag
end

#has_fill? ⇒ Boolean

Returns:

  • (Boolean) 

722
723
724
 
# File 'lib/write_xlsx/format.rb', line 722

def has_fill?
  @has_fill
end

#has_font(flag) ⇒ Object 


702
703
704
 
# File 'lib/write_xlsx/format.rb', line 702

def has_font(flag)
  @has_font = flag
end

#has_font? ⇒ Boolean

Returns:

  • (Boolean) 

706
707
708
 
# File 'lib/write_xlsx/format.rb', line 706

def has_font?
  @has_font
end

#inspect ⇒ Object 


373
374
375
 
# File 'lib/write_xlsx/format.rb', line 373

def inspect
  to_s
end

#italic? ⇒ Boolean

Returns:

  • (Boolean) 

666
667
668
 
# File 'lib/write_xlsx/format.rb', line 666

def italic?
  ptrue?(@italic)
end

#outline? ⇒ Boolean

Returns:

  • (Boolean) 

674
675
676
 
# File 'lib/write_xlsx/format.rb', line 674

def outline?
  ptrue?(@font_outline)
end

#set_align(location) ⇒ Object

Set cell alignment.


507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
 
# File 'lib/write_xlsx/format.rb', line 507

def set_align(location)
  return unless location             # No default

  location.downcase!

  set_text_h_align(1) if location == 'left'
  set_text_h_align(2) if location == 'centre'
  set_text_h_align(2) if location == 'center'
  set_text_h_align(3) if location == 'right'
  set_text_h_align(4) if location == 'fill'
  set_text_h_align(5) if location == 'justify'
  set_text_h_align(6) if location == 'center_across'
  set_text_h_align(6) if location == 'centre_across'
  set_text_h_align(6) if location == 'merge'              # Legacy.
  set_text_h_align(7) if location == 'distributed'
  set_text_h_align(7) if location == 'equal_space'        # S::PE.
  set_text_h_align(7) if location == 'justify_distributed'

  @just_distrib =   1 if location == 'justify_distributed'

  set_text_v_align(1) if location == 'top'
  set_text_v_align(2) if location == 'vcentre'
  set_text_v_align(2) if location == 'vcenter'
  set_text_v_align(3) if location == 'bottom'
  set_text_v_align(4) if location == 'vjustify'
  set_text_v_align(5) if location == 'vdistributed'
  set_text_v_align(5) if location == 'vequal_space'    # S::PE.
end

#set_bold(bold = 1) ⇒ Object 


369
370
371
 
# File 'lib/write_xlsx/format.rb', line 369

def set_bold(bold = 1)
  @bold = ptrue?(bold) ? 1 : 0
end

#set_border(style) ⇒ Object

Set cells borders to the same style


563
564
565
566
567
568
 
# File 'lib/write_xlsx/format.rb', line 563

def set_border(style)
  set_bottom(style)
  set_top(style)
  set_left(style)
  set_right(style)
end

#set_border_color(color) ⇒ Object

Set cells border to the same color


573
574
575
576
577
578
 
# File 'lib/write_xlsx/format.rb', line 573

def set_border_color(color)
  set_bottom_color(color)
  set_top_color(color)
  set_left_color(color)
  set_right_color(color)
end

#set_border_info(borders) ⇒ Object 


623
624
625
626
627
628
629
630
631
632
633
634
635
636
 
# File 'lib/write_xlsx/format.rb', line 623

def set_border_info(borders)
  key = get_border_key

  if borders[key]
    # Border has already been used.
    @border_index = borders[key]
    @has_border   = false
  else
    # This is a new border.
    @border_index = borders.size
    borders[key]  = borders.size
    @has_border   = true
  end
end

#set_center_across(flag = 1) ⇒ Object

Implements the Excel5 style “merge”.


547
548
549
 
# File 'lib/write_xlsx/format.rb', line 547

def set_center_across(flag = 1)
  set_text_h_align(6)
end

#set_font_info(fonts) ⇒ Object 


608
609
610
611
612
613
614
615
616
617
618
619
620
621
 
# File 'lib/write_xlsx/format.rb', line 608

def set_font_info(fonts)
  key = get_font_key

  if fonts[key]
    # Font has already been used.
    @font_index = fonts[key]
    @has_font   = false
  else
    # This is a new font.
    @font_index = fonts.size
    fonts[key]  = fonts.size
    @has_font   = true
  end
end

#set_format_properties(*properties) ⇒ Object

:call-seq:

set_format_properties( :bold => 1 [, :color => 'red'..] )
set_format_properties( font [, shade, ..])
set_format_properties( :bold => 1, font, ...)
  *) font  = { :color => 'red', :bold => 1 }
     shade = { :bg_color => 'green', :pattern => 1 }

Convert hashes of properties to method calls.

The properties of an existing Format object can be also be set by means of set_format_properties():

format = workbook.add_format
format.set_format_properties(:bold => 1, :color => 'red');

However, this method is here mainly for legacy reasons. It is preferable to set the properties in the format constructor:

format = workbook.add_format(:bold => 1, :color => 'red');


281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
 
# File 'lib/write_xlsx/format.rb', line 281

def set_format_properties(*properties)   # :nodoc:
  return if properties.empty?
  properties.each do |property|
    property.each do |key, value|
      # Strip leading "-" from Tk style properties e.g. "-color" => 'red'.
      key = key.sub(/^-/, '') if key.respond_to?(:to_str)

      # Create a sub to set the property.
      if value.respond_to?(:to_str) || !value.respond_to?(:+)
        s = "set_#{key}('#{value}')"
      else
        s = "set_#{key}(#{value})"
      end
      eval s
    end
  end
end

#set_hyperlink ⇒ Object

Set the properties for the hyperlink style. TODO. This doesn't currently work. Fix it when styles are supported.


600
601
602
603
604
605
606
 
# File 'lib/write_xlsx/format.rb', line 600

def set_hyperlink
  @hyperlink = 1

  set_underline(1)
  set_theme(10)
  set_align('top')
end

#set_merge(merge = 1) ⇒ Object

This was the way to implement a merge in Excel5. However it should have been called “center_across” and not “merge”. This is now deprecated. Use set_center_across() or better merge_range().


556
557
558
 
# File 'lib/write_xlsx/format.rb', line 556

def set_merge(merge = 1)
  set_text_h_align(6)
end

#set_rotation(rotation) ⇒ Object

Set the rotation angle of the text. An alignment property.


583
584
585
586
587
588
589
590
591
592
593
594
 
# File 'lib/write_xlsx/format.rb', line 583

def set_rotation(rotation)
  if rotation == 270
    rotation = 255
  elsif rotation >= -90 || rotation <= 90
    rotation = -rotation + 90 if rotation < 0
  else
    raise "Rotation #{rotation} outside range: -90 <= angle <= 90"
    rotation = 0
  end

  @rotation = rotation
end

#set_valign(location) ⇒ Object

Set vertical cell alignment. This is required by the set_properties() method to differentiate between the vertical and horizontal properties.


540
541
542
 
# File 'lib/write_xlsx/format.rb', line 540

def set_valign(location)
  set_align(location)
end

#shadow? ⇒ Boolean

Returns:

  • (Boolean) 

678
679
680
 
# File 'lib/write_xlsx/format.rb', line 678

def shadow?
  ptrue?(@font_shadow)
end

#strikeout? ⇒ Boolean

Returns:

  • (Boolean) 

670
671
672
 
# File 'lib/write_xlsx/format.rb', line 670

def strikeout?
  ptrue?(@font_strikeout)
end

#underline? ⇒ Boolean

Returns:

  • (Boolean) 

682
683
684
 
# File 'lib/write_xlsx/format.rb', line 682

def underline?
  ptrue?(@underline)
end

#write_font(writer, worksheet, dxf_format = nil) ⇒ Object

:nodoc:


738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
 
# File 'lib/write_xlsx/format.rb', line 738

def write_font(writer, worksheet, dxf_format = nil) #:nodoc:
  writer.tag_elements('font') do
    # The condense and extend elements are mainly used in dxf formats.
    write_condense(writer) if ptrue?(@font_condense)
    write_extend(writer)   if ptrue?(@font_extend)

    write_font_shapes(writer)

    writer.empty_tag('sz', [ ['val', size] ]) unless dxf_format

    if theme == -1
      # Ignore for excel2003_style
    elsif ptrue?(theme)
      write_color(writer, 'theme', theme)
    elsif ptrue?(@color_indexed)
      write_color(writer, 'indexed', @color_indexed)
    elsif ptrue?(@color)
      color = worksheet.palette_color(@color)
      write_color(writer, 'rgb', color)
    elsif !ptrue?(dxf_format)
      write_color(writer, 'theme', 1)
    end

    unless ptrue?(dxf_format)
      writer.empty_tag('name', [ ['val', @font] ])
      write_font_family_scheme(writer)
    end
  end
end

#write_font_rpr(writer, worksheet) ⇒ Object

:nodoc:


768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
 
# File 'lib/write_xlsx/format.rb', line 768

def write_font_rpr(writer, worksheet) #:nodoc:
  writer.tag_elements('rPr') do
    write_font_shapes(writer)
    writer.empty_tag('sz', [ ['val', size] ])

    if ptrue?(theme)
      write_color(writer, 'theme', theme)
    elsif ptrue?(@color)
      color = worksheet.palette_color(@color)
      write_color(writer, 'rgb', color)
    else
      write_color(writer, 'theme', 1)
    end

    writer.empty_tag('rFont', [ ['val', @font] ])
    write_font_family_scheme(writer)
  end
end

#xf_attributes ⇒ Object 


802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
 
# File 'lib/write_xlsx/format.rb', line 802

def xf_attributes
  attributes = [
    ['numFmtId', num_format_index],
    ['fontId'  , font_index],
    ['fillId'  , fill_index],
    ['borderId', border_index],
    ['xfId'    , 0]
  ]
  attributes << ['applyNumberFormat', 1] if num_format_index > 0
  # Add applyFont attribute if XF format uses a font element.
  attributes << ['applyFont', 1] if font_index > 0
  # Add applyFill attribute if XF format uses a fill element.
  attributes << ['applyFill', 1] if fill_index > 0
  # Add applyBorder attribute if XF format uses a border element.
  attributes << ['applyBorder', 1] if border_index > 0

  # Check if XF format has alignment properties set.
  apply_align, align = get_align_properties
  # We can also have applyAlignment without a sub-element.
  attributes << ['applyAlignment', 1] if apply_align
  attributes << ['applyProtection', 1] if get_protection_properties

  attributes
end