Class: Format

Inherits:
Object
  • Object
show all
Defined in:
lib/WriteExcel/format.rb

Overview

Format - A class for defining Excel formatting.

Used in conjunction with Spreadsheet::WriteExcel

Copyright 2000-2008, John McNamara, [email protected]

original written in Perl by John McNamara converted to Ruby by Hideo Nakamura, [email protected]

require ‘nkf’

Constant Summary collapse

COLORS = 
{
  'aqua'    => 0x0F,
  'cyan'    => 0x0F,
  'black'   => 0x08,
  'blue'    => 0x0C,
  'brown'   => 0x10,
  'magenta' => 0x0E,
  'fuchsia' => 0x0E,
  'gray'    => 0x17,
  'grey'    => 0x17,
  'green'   => 0x11,
  'lime'    => 0x0B,
  'navy'    => 0x12,
  'orange'  => 0x35,
  'pink'    => 0x21,
  'purple'  => 0x14,
  'red'     => 0x0A,
  'silver'  => 0x16,
  'white'   => 0x09,
  'yellow'  => 0x0D,
}
NonAscii = 
/[^!"#\$%&'\(\)\*\+,\-\.\/\:\;<=>\?@0-9A-Za-z_\[\\\]^` ~\0\n]/

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(xf_index = 0, properties = {}) ⇒ Format

initialize(xf_index=0, properties = {})

xf_index   :
properties : Hash of property => value

Constructor



64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/WriteExcel/format.rb', line 64

def initialize(xf_index = 0, properties = {})
  @xf_index       = xf_index

  @type           = 0
  @font_index     = 0
  @font           = 'Arial'
  @size           = 10
  @bold           = 0x0190
  @italic         = 0
  @color          = 0x7FFF
  @underline      = 0
  @font_strikeout = 0
  @font_outline   = 0
  @font_shadow    = 0
  @font_script    = 0
  @font_family    = 0
  @font_charset   = 0
  @font_encoding  = 0

  @num_format     = 0
  @num_format_enc = 0

  @hidden         = 0
  @locked         = 1

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

  @fg_color       = 0x40
  @bg_color       = 0x41

  @pattern        = 0

  @bottom         = 0
  @top            = 0
  @left           = 0
  @right          = 0

  @bottom_color   = 0x40
  @top_color      = 0x40
  @left_color     = 0x40
  @right_color    = 0x40

  @indent         = 0
  @shrink         = 0
  @merge_range    = 0
  @reading_order  = 0

  @diag_type      = 0
  @diag_color     = 0x40
  @diag_border    = 0

  @font_only      = 0

  # Temp code to prevent merged formats in non-merged cells.
  @used_merge     = 0

  set_format_properties(properties) unless properties.empty?
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object

Dynamically create set methods that aren’t already defined.



1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
 
# File 'lib/WriteExcel/format.rb', line 1078

def method_missing(name, *args)
  # -- original perl comment --
  # There are two types of set methods: set_property() and
  # set_property_color(). When a method is AUTOLOADED we store a new anonymous
  # sub in the appropriate slot in the symbol table. The speeds up subsequent
  # calls to the same method.

  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 = get_color(args[0])
  else                            # for "set_xxx" methods
    value = args[0].nil? ? 1 : args[0]
  end
  if value.kind_of?(String)
    s = "#{attribute} = \"#{value.to_s}\""
  else
    s = "#{attribute} =   #{value.to_s}"
  end
  eval s
end

Instance Attribute Details

#align ⇒ Object

Returns the value of attribute align.



43
44
45
 
# File 'lib/WriteExcel/format.rb', line 43

def align
  @align
end

#bg_color ⇒ Object

Returns the value of attribute bg_color.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def bg_color
  @bg_color
end

#bold ⇒ Object

Returns the value of attribute bold.



41
42
43
 
# File 'lib/WriteExcel/format.rb', line 41

def bold
  @bold
end

#border ⇒ Object

Returns the value of attribute border.



43
44
45
 
# File 'lib/WriteExcel/format.rb', line 43

def border
  @border
end

#bottom ⇒ Object (readonly)

Returns the value of attribute bottom.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def bottom
  @bottom
end

#bottom_color ⇒ Object (readonly)

Returns the value of attribute bottom_color.



52
53
54
 
# File 'lib/WriteExcel/format.rb', line 52

def bottom_color
  @bottom_color
end

#color ⇒ Object

Returns the value of attribute color.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def color
  @color
end

#diag_border ⇒ Object (readonly)

Returns the value of attribute diag_border.



49
50
51
 
# File 'lib/WriteExcel/format.rb', line 49

def diag_border
  @diag_border
end

#diag_color ⇒ Object (readonly)

Returns the value of attribute diag_color.



49
50
51
 
# File 'lib/WriteExcel/format.rb', line 49

def diag_color
  @diag_color
end

#diag_type ⇒ Object (readonly)

Returns the value of attribute diag_type.



49
50
51
 
# File 'lib/WriteExcel/format.rb', line 49

def diag_type
  @diag_type
end

#fg_color ⇒ Object

Returns the value of attribute fg_color.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def fg_color
  @fg_color
end

#font ⇒ Object

Returns the value of attribute font.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def font
  @font
end

#font_charset ⇒ Object (readonly)

Returns the value of attribute font_charset.



47
48
49
 
# File 'lib/WriteExcel/format.rb', line 47

def font_charset
  @font_charset
end

#font_encoding ⇒ Object (readonly)

Returns the value of attribute font_encoding.



48
49
50
 
# File 'lib/WriteExcel/format.rb', line 48

def font_encoding
  @font_encoding
end

#font_family ⇒ Object (readonly)

Returns the value of attribute font_family.



47
48
49
 
# File 'lib/WriteExcel/format.rb', line 47

def font_family
  @font_family
end

#font_index ⇒ Object

Returns the value of attribute font_index.



44
45
46
 
# File 'lib/WriteExcel/format.rb', line 44

def font_index
  @font_index
end

#font_only ⇒ Object (readonly)

Returns the value of attribute font_only.



54
55
56
 
# File 'lib/WriteExcel/format.rb', line 54

def font_only
  @font_only
end

#font_outline ⇒ Object

Returns the value of attribute font_outline.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def font_outline
  @font_outline
end

#font_script ⇒ Object (readonly)

Returns the value of attribute font_script.



47
48
49
 
# File 'lib/WriteExcel/format.rb', line 47

def font_script
  @font_script
end

#font_shadow ⇒ Object

Returns the value of attribute font_shadow.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def font_shadow
  @font_shadow
end

#font_strikeout ⇒ Object (readonly)

Returns the value of attribute font_strikeout.



47
48
49
 
# File 'lib/WriteExcel/format.rb', line 47

def font_strikeout
  @font_strikeout
end

#hidden ⇒ Object (readonly)

Returns the value of attribute hidden.



50
51
52
 
# File 'lib/WriteExcel/format.rb', line 50

def hidden
  @hidden
end

#indent ⇒ Object (readonly)

Returns the value of attribute indent.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def indent
  @indent
end

#italic ⇒ Object (readonly)

Returns the value of attribute italic.



53
54
55
 
# File 'lib/WriteExcel/format.rb', line 53

def italic
  @italic
end

#left ⇒ Object (readonly)

Returns the value of attribute left.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def left
  @left
end

#left_color ⇒ Object (readonly)

Returns the value of attribute left_color.



52
53
54
 
# File 'lib/WriteExcel/format.rb', line 52

def left_color
  @left_color
end

#locked ⇒ Object (readonly)

Returns the value of attribute locked.



50
51
52
 
# File 'lib/WriteExcel/format.rb', line 50

def locked
  @locked
end

#merge_range ⇒ Object (readonly)

Returns the value of attribute merge_range.



48
49
50
 
# File 'lib/WriteExcel/format.rb', line 48

def merge_range
  @merge_range
end

#num_format ⇒ Object

Returns the value of attribute num_format.



45
46
47
 
# File 'lib/WriteExcel/format.rb', line 45

def num_format
  @num_format
end

#num_format_enc ⇒ Object (readonly)

Returns the value of attribute num_format_enc.



50
51
52
 
# File 'lib/WriteExcel/format.rb', line 50

def num_format_enc
  @num_format_enc
end

#pattern ⇒ Object (readonly)

Returns the value of attribute pattern.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def pattern
  @pattern
end

#reading_order ⇒ Object (readonly)

Returns the value of attribute reading_order.



48
49
50
 
# File 'lib/WriteExcel/format.rb', line 48

def reading_order
  @reading_order
end

#right ⇒ Object (readonly)

Returns the value of attribute right.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def right
  @right
end

#right_color ⇒ Object (readonly)

Returns the value of attribute right_color.



52
53
54
 
# File 'lib/WriteExcel/format.rb', line 52

def right_color
  @right_color
end

#rotation ⇒ Object (readonly)

Returns the value of attribute rotation.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def rotation
  @rotation
end

#shrink ⇒ Object (readonly)

Returns the value of attribute shrink.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def shrink
  @shrink
end

#size ⇒ Object

Returns the value of attribute size.



42
43
44
 
# File 'lib/WriteExcel/format.rb', line 42

def size
  @size
end

#text_h_align ⇒ Object (readonly)

Returns the value of attribute text_h_align.



54
55
56
 
# File 'lib/WriteExcel/format.rb', line 54

def text_h_align
  @text_h_align
end

#text_justlast ⇒ Object

Returns the value of attribute text_justlast.



41
42
43
 
# File 'lib/WriteExcel/format.rb', line 41

def text_justlast
  @text_justlast
end

#text_v_align ⇒ Object (readonly)

Returns the value of attribute text_v_align.



54
55
56
 
# File 'lib/WriteExcel/format.rb', line 54

def text_v_align
  @text_v_align
end

#text_wrap ⇒ Object

Returns the value of attribute text_wrap.



41
42
43
 
# File 'lib/WriteExcel/format.rb', line 41

def text_wrap
  @text_wrap
end

#top ⇒ Object (readonly)

Returns the value of attribute top.



51
52
53
 
# File 'lib/WriteExcel/format.rb', line 51

def top
  @top
end

#top_color ⇒ Object (readonly)

Returns the value of attribute top_color.



52
53
54
 
# File 'lib/WriteExcel/format.rb', line 52

def top_color
  @top_color
end

#type ⇒ Object (readonly)

Returns the value of attribute type.



46
47
48
 
# File 'lib/WriteExcel/format.rb', line 46

def type
  @type
end

#underline ⇒ Object (readonly)

Returns the value of attribute underline.



53
54
55
 
# File 'lib/WriteExcel/format.rb', line 53

def underline
  @underline
end

#used_merge ⇒ Object

Returns the value of attribute used_merge.



40
41
42
 
# File 'lib/WriteExcel/format.rb', line 40

def used_merge
  @used_merge
end

#xf_index ⇒ Object

Returns the value of attribute xf_index.



40
41
42
 
# File 'lib/WriteExcel/format.rb', line 40

def xf_index
  @xf_index
end

Class Method Details

._get_color(colour) ⇒ Object

class method Format._get_color(colour)

used from Worksheet.rb

this is cut & copy of get_color().



498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
 
# File 'lib/WriteExcel/format.rb', line 498

def self._get_color(colour)
  # Return the default color, 0x7FFF, if undef,
  return 0x7FFF if colour.nil?

  if colour.kind_of?(Numeric)
    if colour < 0
      return 0x7FFF

      # or an index < 8 mapped into the correct range,
    elsif colour < 8
      return (colour + 8).to_i

      # or the default color if arg is outside range,
    elsif 63 < colour
      return 0x7FFF

      # or an integer in the valid range
    else
      return colour.to_i
    end
  elsif colour.kind_of?(String)
    # or the color string converted to an integer,
    if COLORS.has_key?(colour)
      return COLORS[colour]

      # or the default color if string is unrecognised,
    else
      return 0x7FFF
    end
  else
    return 0x7FFF
  end
end

Instance Method Details

#copy(other) ⇒ Object

copy($format)

Copy the attributes of another Spreadsheet::WriteExcel::Format object.



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'lib/WriteExcel/format.rb', line 134

def copy(other)
    return unless other.kind_of?(Format)
  
    # copy properties except xf, merge_range, used_merge
    # Copy properties
    @type           = other.type
    @font_index     = other.font_index
    @font           = other.font
    @size           = other.size
    @bold           = other.bold
    @italic         = other.italic
    @color          = other.color
    @underline      = other.underline
    @font_strikeout = other.font_strikeout
    @font_outline   = other.font_outline
    @font_shadow    = other.font_shadow
    @font_script    = other.font_script
    @font_family    = other.font_family
    @font_charset   = other.font_charset
    @font_encoding  = other.font_encoding
    
    @num_format     = other.num_format
    @num_format_enc = other.num_format_enc
    
    @hidden         = other.hidden
    @locked         = other.locked
    
    @text_h_align   = other.text_h_align
    @text_wrap      = other.text_wrap
    @text_v_align   = other.text_v_align
    @text_justlast  = other.text_justlast
    @rotation       = other.rotation
    
    @fg_color       = other.fg_color
    @bg_color       = other.bg_color
    
    @pattern        = other.pattern
    
    @bottom         = other.bottom
    @top            = other.top
    @left           = other.left
    @right          = other.right
    
    @bottom_color   = other.bottom_color
    @top_color      = other.top_color
    @left_color     = other.left_color
    @right_color    = other.right_color
    
    @indent         = other.indent
    @shrink         = other.shrink
    @reading_order  = other.reading_order
    
    @diag_type      = other.diag_type
    @diag_color     = other.diag_color
    @diag_border    = other.diag_border
    
    @font_only      = other.font_only
end

#get_color(colour = nil) ⇒ Object

get_color(colour)

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.



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
 
# File 'lib/WriteExcel/format.rb', line 456

def get_color(colour = nil)
  # Return the default color, 0x7FFF, if undef,
  return 0x7FFF if colour.nil?

  if colour.kind_of?(Numeric)
    if colour < 0
      return 0x7FFF
      
    # or an index < 8 mapped into the correct range,
    elsif colour < 8
      return (colour + 8).to_i

    # or the default color if arg is outside range,
    elsif colour > 63
      return 0x7FFF

    # or an integer in the valid range
    else
      return colour.to_i
    end
  elsif colour.kind_of?(String)
    # or the color string converted to an integer,
    if COLORS.has_key?(colour)
      return COLORS[colour]

    # or the default color if string is unrecognised,
    else
      return 0x7FFF
    end
  else
    return 0x7FFF
  end
end

#get_font ⇒ Object

get_font()

Generate an Excel BIFF FONT record.



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
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
 
# File 'lib/WriteExcel/format.rb', line 356

def get_font

  #   my $record;     # Record identifier
  #   my $length;     # Record length

  #   my $dyHeight;   # Height of font (1/20 of a point)
  #   my $grbit;      # Font attributes
  #   my $icv;        # Index to color palette
  #   my $bls;        # Bold style
  #   my $sss;        # Superscript/subscript
  #   my $uls;        # Underline
  #   my $bFamily;    # Font family
  #   my $bCharSet;   # Character set
  #   my $reserved;   # Reserved
  #   my $cch;        # Length of font name
  #   my $rgch;       # Font name
  #   my $encoding;   # Font name character encoding


  dyHeight   = @size * 20
  icv        = @color
  bls        = @bold
  sss        = @font_script
  uls        = @underline
  bFamily    = @font_family
  bCharSet   = @font_charset
  rgch       = @font
  encoding   = @font_encoding

  # Handle utf8 strings
  if rgch =~ NonAscii
    rgch = NKF.nkf('-w16B0 -m0 -W', rgch)
    encoding = 1
  end

  cch = rgch.length
  #
  # Handle Unicode font names.
  if (encoding == 1)
    raise "Uneven number of bytes in Unicode font name" if cch % 2 != 0
    cch  /= 2 if encoding !=0
    rgch  = rgch.unpack('n*').pack('v*')
  end

  record     = 0x31
  length     = 0x10 + rgch.length
  reserved   = 0x00

  grbit      = 0x00
  grbit     |= 0x02 if @italic != 0
  grbit     |= 0x08 if @font_strikeout != 0
  grbit     |= 0x10 if @font_outline != 0
  grbit     |= 0x20 if @font_shadow != 0


  header = [record, length].pack("vv")
  data   = [dyHeight, grbit, icv, bls,
            sss, uls, bFamily,
            bCharSet, reserved, cch, encoding].pack('vvvvvCCCCCC')

  return header + data + rgch
end

#get_font_key ⇒ Object

get_font_key()

Returns a unique hash key for a font. Used by Workbook->_store_all_fonts()



425
426
427
428
429
430
431
432
433
434
435
 
# File 'lib/WriteExcel/format.rb', line 425

def get_font_key
  # The following elements are arranged to increase the probability of
  # generating a unique key. Elements that hold a large range of numbers
  # e.g. _color are placed between two binary elements such as _italic

  key  = "#{@font}#{@size}#{@font_script}#{@underline}#{@font_strikeout}#{@bold}#{@font_outline}"
  key += "#{@font_family}#{@font_charset}#{@font_shadow}#{@color}#{@italic}#{@font_encoding}"
  result =  key.gsub(' ', '_') # Convert the key to a single word

  return result
end

#get_xf ⇒ Object

get_xf($style)

Generate an Excel BIFF XF record.



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
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
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
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
 
# File 'lib/WriteExcel/format.rb', line 199

def get_xf

  # Local Variable
  #    record;     # Record identifier
  #    length;     # Number of bytes to follow
  #
  #    ifnt;       # Index to FONT record
  #    ifmt;       # Index to FORMAT record
  #    style;      # Style and other options
  #    align;      # Alignment
  #    indent;     #
  #    icv;        # fg and bg pattern colors
  #    border1;    # Border line options
  #    border2;    # Border line options
  #    border3;    # Border line options

  # Set the type of the XF record and some of the attributes.
  if @type == 0xFFF5 then
    style = 0xFFF5
  else
    style  = @locked
    style |= @hidden << 1
  end

  # Flags to indicate if attributes have been set.
  atr_num  = (@num_format   != 0) ? 1 : 0
  atr_fnt  = (@font_index   != 0) ? 1 : 0
  atr_alc  = (@text_h_align != 0 ||
              @text_v_align != 2 ||
              @shrink       != 0 ||
              @merge_range  != 0 ||
              @text_wrap    != 0 ||
              @indent       != 0) ? 1 : 0
  atr_bdr  = (@bottom       != 0 ||
              @top          != 0 ||
              @left         != 0 ||
              @right        != 0 ||
              @diag_type    != 0) ? 1 : 0
  atr_pat  = (@fg_color     != 0x40 ||
              @bg_color     != 0x41 ||
              @pattern      != 0x00) ? 1 : 0
  atr_prot = (@hidden       != 0 ||
              @locked       != 1) ? 1 : 0

  # Set attribute changed flags for the style formats.
  if @xf_index != 0 and @type == 0xFFF5
    if @xf_index >= 16
      atr_num    = 0
      atr_fnt    = 1
    else
      atr_num    = 1
      atr_fnt    = 0
    end
    atr_alc    = 1
    atr_bdr    = 1
    atr_pat    = 1
    atr_prot   = 1
  end

  # Set a default diagonal border style if none was specified.
  @diag_border = 1 if (@diag_border ==0 and @diag_type != 0)

  # Reset the default colours for the non-font properties
  @fg_color     = 0x40 if @fg_color     == 0x7FFF
  @bg_color     = 0x41 if @bg_color     == 0x7FFF
  @bottom_color = 0x40 if @bottom_color == 0x7FFF
  @top_color    = 0x40 if @top_color    == 0x7FFF
  @left_color   = 0x40 if @left_color   == 0x7FFF
  @right_color  = 0x40 if @right_color  == 0x7FFF
  @diag_color   = 0x40 if @diag_color   == 0x7FFF

  # Zero the default border colour if the border has not been set.
  @bottom_color = 0 if @bottom    == 0
  @top_color    = 0 if @top       == 0
  @right_color  = 0 if @right     == 0
  @left_color   = 0 if @left      == 0
  @diag_color   = 0 if @diag_type == 0

  # The following 2 logical statements take care of special cases in relation
  # to cell colours and patterns:
  # 1. For a solid fill (_pattern == 1) Excel reverses the role of foreground
  #    and background colours.
  # 2. If the user specifies a foreground or background colour without a
  #    pattern they probably wanted a solid fill, so we fill in the defaults.
  #
  if (@pattern  <= 0x01 && @bg_color != 0x41 && @fg_color == 0x40)
    @fg_color = @bg_color
    @bg_color = 0x40
    @pattern  = 1
  end

  if (@pattern <= 0x01 && @bg_color == 0x41 && @fg_color != 0x40)
    @bg_color = 0x40
    @pattern  = 1
  end

  # Set default alignment if indent is set.
  @text_h_align = 1 if @indent != 0 and @text_h_align == 0


  record         = 0x00E0
  length         = 0x0014

  ifnt           = @font_index
  ifmt           = @num_format


  align          = @text_h_align
  align         |= @text_wrap     << 3
  align         |= @text_v_align  << 4
  align         |= @text_justlast << 7
  align         |= @rotation      << 8

  indent         = @indent
  indent        |= @shrink        << 4
  indent        |= @merge_range   << 5
  indent        |= @reading_order << 6
  indent        |= atr_num        << 10
  indent        |= atr_fnt        << 11
  indent        |= atr_alc        << 12
  indent        |= atr_bdr        << 13
  indent        |= atr_pat        << 14
  indent        |= atr_prot       << 15


  border1        = @left
  border1       |= @right         << 4
  border1       |= @top           << 8
  border1       |= @bottom        << 12

  border2        = @left_color
  border2       |= @right_color   << 7
  border2       |= @diag_type     << 14

  border3        = 0
  border3       |= @top_color
  border3       |= @bottom_color  << 7
  border3       |= @diag_color    << 14
  border3       |= @diag_border   << 21
  border3       |= @pattern       << 26

  icv            = @fg_color
  icv           |= @bg_color      << 7

  header = [record, length].pack("vv")
  data   = [ifnt, ifmt, style, align, indent,
            border1, border2, border3, icv].pack("vvvvvvvVv")

  return header + data
end

#get_xf_index ⇒ Object

get_xf_index()

Returns the used by Worksheet->_XF()



443
444
445
 
# File 'lib/WriteExcel/format.rb', line 443

def get_xf_index
  return @xf_index
end

#set_align(align = 'left') ⇒ Object

set_align()

Set cell alignment.

Default state:      Alignment is off
Default action:     Left alignment
Valid args:         'left'              Horizontal
                    'center'
                    'right'
                    'fill'
                    'justify'
                    'center_across'

                    'top'               Vertical
                    'vcenter'
                    'bottom'
                    'vjustify'

This method is used to set the horizontal and vertical text alignment within a cell. Vertical and horizontal alignments can be combined.

The method is used as follows:

   my $format = $workbook->add_format();
   $format->set_align('center');
   $format->set_align('vcenter');
   $worksheet->set_row(0, 30);
   $worksheet->write(0, 0, 'X', $format);

Text can be aligned across two or more adjacent cells using the center_across property. However, for genuine merged cells it is better to use the merge_range() worksheet method.

The vjustify (vertical justify) option can be used to provide automatic text wrapping in a cell. The height of the cell will be adjusted to accommodate the wrapped text. To specify where the text wraps use the set_text_wrap() method.



856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
 
# File 'lib/WriteExcel/format.rb', line 856

def set_align(align = 'left')

  return unless align.kind_of?(String)

  location = align.downcase

  case location
  when 'left'             then set_text_h_align(1)
  when 'centre', 'center' then set_text_h_align(2)
  when 'right'            then set_text_h_align(3)
  when 'fill'             then set_text_h_align(4)
  when 'justify'          then set_text_h_align(5)
  when 'center_across', 'centre_across' then set_text_h_align(6)
  when 'merge'            then set_text_h_align(6) # S:WE name
  when 'distributed'      then set_text_h_align(7)
  when 'equal_space'      then set_text_h_align(7) # ParseExcel

  when 'top'              then set_text_v_align(0)
  when 'vcentre'          then set_text_v_align(1)
  when 'vcenter'          then set_text_v_align(1)
  when 'bottom'           then set_text_v_align(2)
  when 'vjustify'         then set_text_v_align(3)
  when 'vdistributed'     then set_text_v_align(4)
  when 'vequal_space'     then set_text_v_align(4) # ParseExcel
  end
end

#set_bold(weight = nil) ⇒ Object

set_bold()

Default state:      bold is off  (internal value = 400)
Default action:     Turn bold on
Valid args:         0, 1 [1]

Set the bold property of the font:

format.set_bold()   # Turn bold on
1

Actually, values in the range 100..1000 are also valid.

400 is normal, 700 is bold and 1000 is very bold indeed.
It is probably best to set the value to 1 and use normal bold.



960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
 
# File 'lib/WriteExcel/format.rb', line 960

def set_bold(weight = nil)
  if weight.nil?
    weight = 0x2BC
  elsif !weight.kind_of?(Numeric)
    weight = 0x190
  elsif weight == 1                    # Bold text
    weight = 0x2BC
  elsif weight == 0                    # Normal text
    weight = 0x190
  elsif weight <  0x064                # Lower bound
    weight = 0x190
  elsif weight >  0x3E8                # Upper bound
    weight = 0x190
  else
    weight = weight.to_i
  end

  @bold = weight
end

#set_border(style) ⇒ Object

set_border($style)

Set cells borders to the same style



987
988
989
990
991
992
 
# File 'lib/WriteExcel/format.rb', line 987

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

#set_border_color(color) ⇒ Object

set_border_color($color)

Set cells border to the same color



1001
1002
1003
1004
1005
1006
 
# File 'lib/WriteExcel/format.rb', line 1001

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

#set_center_across(val = true) ⇒ Object

set_center_across()

Implements the Excel5 style “merge”.



900
901
902
 
# File 'lib/WriteExcel/format.rb', line 900

def set_center_across(val=true)
  set_text_h_align(6)
end

#set_color(color = 0x7FFF) ⇒ Object

set_color(color)

Default state:      Excels default color, usually black
Default action:     Set the default color
Valid args:         Integers from 8..63 or the following strings:
                    'black', 'blue', 'brown', 'cyan', 'gray'
                    'green', 'lime', 'magenta', 'navy', 'orange'
                    'pink', 'purple', 'red', 'silver', 'white', 'yellow'

Set the font colour. The set_color() method is used as follows:

format = workbook.add_format()
format.set_color('red')
worksheet.write(0, 0, 'wheelbarrow', format)

Note: The set_color() method is used to set the colour of the font in a cell.

To set the colour of a cell use the set_bg_color()
and set_pattern() methods.



587
588
589
 
# File 'lib/WriteExcel/format.rb', line 587

def set_color(color = 0x7FFF)
  @color = get_color(color)
end

#set_font_outline(arg = 1) ⇒ Object

set_font_outline()

Default state:      Outline is off
Default action:     Turn outline on
Valid args:         0, 1

Macintosh only.



702
703
704
705
706
707
708
709
710
711
712
 
# File 'lib/WriteExcel/format.rb', line 702

def set_font_outline(arg = 1)
  begin
    if    arg == 0 then @font_outline = 0
    elsif arg == 1 then @font_outline = 1
    else
      raise ArgumentError,
      "\n\n  set_font_outline(#{arg.inspect})\n    arg must be 0, 1, or none.\n"
      " ( 0:OFF, 1 and none:outline on )\n"
    end
  end
end

#set_font_script(arg = 1) ⇒ Object

set_font_script()

Default state:      Super/Subscript is off
Default action:     Turn Superscript on
Valid args:         0  = Normal
                    1  = Superscript
                    2  = Subscript

Set the superscript/subscript property of the font. This format is currently not very useful.



679
680
681
682
683
684
685
686
687
688
689
690
 
# File 'lib/WriteExcel/format.rb', line 679

def set_font_script(arg = 1)
  begin
    if    arg == 0 then @font_script = 0
    elsif arg == 1 then @font_script = 1
    elsif arg == 2 then @font_script = 2
    else
      raise ArgumentError,
      "\n\n  set_font_script(#{arg.inspect})\n    arg must be 0, 1, or none. or 2\n"
      " ( 0:OFF, 1 and none:Superscript, 2:Subscript )\n"
    end
  end
end

#set_font_shadow(arg = 1) ⇒ Object

set_font_shadow()

Default state:      Shadow is off
Default action:     Turn shadow on
Valid args:         0, 1

Macintosh only.



724
725
726
727
728
729
730
731
732
733
734
 
# File 'lib/WriteExcel/format.rb', line 724

def set_font_shadow(arg = 1)
  begin
    if    arg == 0 then @font_shadow = 0
    elsif arg == 1 then @font_shadow = 1
    else
      raise ArgumentError,
      "\n\n  set_font_shadow(#{arg.inspect})\n    arg must be 0, 1, or none.\n"
      " ( 0:OFF, 1 and none:shadow on )\n"
    end
  end
end

#set_font_strikeout(arg = 1) ⇒ Object

set_font_strikeout()

Default state:      Strikeout is off
Default action:     Turn strikeout on
Valid args:         0, 1

Set the strikeout property of the font.



654
655
656
657
658
659
660
661
662
663
664
 
# File 'lib/WriteExcel/format.rb', line 654

def set_font_strikeout(arg = 1)
  begin
    if    arg == 0 then @font_strikeout = 0
    elsif arg == 1 then @font_strikeout = 1
    else
      raise ArgumentError,
      "\n\n  set_font_strikeout(#{arg.inspect})\n    arg must be 0, 1, or none.\n"
      " ( 0:OFF, 1 and none:Strikeout )\n"
    end
  end
end

#set_format_properties(*properties) ⇒ Object

set_format_properties(*properties)

  properties : Hash of properies
ex)   font  = { :color => 'red', :bold => 1 }
      shade = { :bg_color => 'green', :pattern => 1 }
   1) set_format_properties( :bold => 1 [, :color => 'red'..] )
   2) set_format_properties( font [, shade, ..])
   3) set_format_properties( :bold => 1, font, ...)

Convert hashes of properties to method calls.



1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
 
# File 'lib/WriteExcel/format.rb', line 1054

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

      # Create a sub to set the property.
      if value.kind_of?(String)
        s = "set_#{key}('#{value}')"
      else
        s = "set_#{key}(#{value})"
      end
      eval s
    end
  end
end

#set_hidden(arg = 1) ⇒ Object

set_hidden()

Default state:      Formula hiding is off
Default action:     Turn hiding on
Valid args:         0, 1

This property is used to hide a formula while still displaying its result. This is generally used to hide complex calculations from end users who are only interested in the result. It only has an effect if the worksheet has been protected, see the worksheet protect() method.

my hidden = workbook.add_format()
hidden.set_hidden()

# Enable worksheet protection
worksheet.protect()

# The formula in this cell isn't visible
worksheet.write('A1', '=1+2', hidden)

Note: This offers weak protection even with a password,

see the note in relation to the protect() method  .



805
806
807
808
809
810
811
812
813
814
815
 
# File 'lib/WriteExcel/format.rb', line 805

def set_hidden(arg = 1)
  begin
    if    arg == 0 then @hidden = 0
    elsif arg == 1 then @hidden = 1
    else
      raise ArgumentError,
      "\n\n  set_hidden(#{arg.inspect})\n    arg must be 0, 1, or none.\n"
      " ( 0:OFF, 1 and none:hiding On )\n"
    end
  end
end

#set_italic(arg = 1) ⇒ Object

set_italic()

  Default state:      Italic is off
  Default action:     Turn italic on
  Valid args:         0, 1

Set the italic property of the font:

  format.set_italic()  # Turn italic on



603
604
605
606
607
608
609
610
611
612
 
# File 'lib/WriteExcel/format.rb', line 603

def set_italic(arg = 1)
  begin
    if    arg == 1  then @italic = 1   # italic on
    elsif arg == 0  then @italic = 0   # italic off
    else
      raise ArgumentError,
      "\n\n  set_italic(#{arg.inspect})\n    arg must be 0, 1, or none. ( 0:OFF , 1 and none:ON )\n"
    end
  end
end

#set_locked(arg = 1) ⇒ Object

set_locked()

Default state:      Cell locking is on
Default action:     Turn locking on
Valid args:         0, 1

This property can be used to prevent modification of a cells contents. Following Excel’s convention, cell locking is turned on by default. However, it only has an effect if the worksheet has been protected, see the worksheet protect() method.

locked  = workbook.add_format()
locked.set_locked(1) # A non-op

unlocked = workbook.add_format()
locked.set_locked(0)

# Enable worksheet protection
worksheet.protect()

# This cell cannot be edited.
worksheet.write('A1', '=1+2', locked)

# This cell can be edited.
worksheet.write('A2', '=1+2', unlocked)

Note: This offers weak protection even with a password, see the note in relation to the protect() method.



767
768
769
770
771
772
773
774
775
776
777
 
# File 'lib/WriteExcel/format.rb', line 767

def set_locked(arg = 1)
  begin
    if    arg == 0 then @locked = 0
    elsif arg == 1 then @locked = 1
    else
      raise ArgumentError,
      "\n\n  set_locked(#{arg.inspect})\n    arg must be 0, 1, or none.\n"
      " ( 0:OFF, 1 and none:Lock On )\n"
    end
  end
end

#set_merge(val = true) ⇒ Object

set_merge()

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().



913
914
915
 
# File 'lib/WriteExcel/format.rb', line 913

def set_merge(val=true)
  set_text_h_align(6)
end

#set_rotation(rotation) ⇒ Object

set_rotation($angle)

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



1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
 
# File 'lib/WriteExcel/format.rb', line 1014

def set_rotation(rotation)
  # Argument should be a number
  return unless rotation.kind_of?(Numeric)

  # The arg type can be a double but the Excel dialog only allows integers.
  rotation = rotation.to_i

  #      if (rotation == 270)
  #         rotation = 255
  #      elsif (rotation >= -90 or rotation <= 90)
  #         rotation = -rotation +90 if rotation < 0;
  #      else
  #         # carp "Rotation $rotation outside range: -90 <= angle <= 90";
  #         rotation = 0;
  #      end
  #
  if rotation == 270
    rotation = 255
  elsif rotation >= -90 && rotation <= 90
    rotation = -rotation + 90 if rotation < 0
  else
    rotation = 0
  end

  @rotation = rotation;
end

#set_size(size = 1) ⇒ Object

set_size(size)

Default state:      Font size is 10
Default action:     Set font size to 1
Valid args:         Integer values from 1 to as big as your screen.
                    Set the font size. Excel adjusts the height of a row
                    to accommodate the

largest font size in the row. You can also explicitly specify the height of a row using the set_row() worksheet method.Set cell alignment.



560
561
562
563
564
 
# File 'lib/WriteExcel/format.rb', line 560

def set_size(size = 1)
  if size.kind_of?(Numeric) && size >= 1
    @size = size.to_i
  end
end

#set_text_wrap(arg = 1) ⇒ Object

set_text_wrap()

Default state:      Text wrap is off
Default action:     Turn text wrap on
Valid args:         0, 1

Here is an example using the text wrap property, the escape character n is used to indicate the end of line:

format = workbook.add_format()
format.set_text_wrap()
worksheet.write(0, 0, "It's\na bum\nwrap", format)



932
933
934
935
936
937
938
939
940
941
942
 
# File 'lib/WriteExcel/format.rb', line 932

def set_text_wrap(arg = 1)
  begin
    if    arg == 0 then @text_wrap = 0
    elsif arg == 1 then @text_wrap = 1
    else
      raise ArgumentError,
      "\n\n  set_text_wrap(#{arg.inspect})\n    arg must be 0, 1, or none.\n"
      " ( 0:OFF, 1 and none:text wrap On )\n"
    end
  end
end

#set_type(type = nil) ⇒ Object

set_type()

Set the XF object type as 0 = cell XF or 0xFFF5 = style XF.



538
539
540
541
542
543
544
545
 
# File 'lib/WriteExcel/format.rb', line 538

def set_type(type = nil)

  if !type.nil? and type == 0
    @type = 0x0000
  else
    @type = 0xFFF5
  end
end

#set_underline(arg = 1) ⇒ Object

set_underline()

  Default state:      Underline is off
  Default action:     Turn on single underline
  Valid args:         0  = No underline
                      1  = Single underline
                      2  = Double underline
                      33 = Single accounting underline
                      34 = Double accounting underline

Set the underline property of the font.



628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
 
# File 'lib/WriteExcel/format.rb', line 628

def set_underline(arg = 1)
  begin
    case arg
    when  0  then @underline =  0    # off
    when  1  then @underline =  1    # Single
    when  2  then @underline =  2    # Double
    when 33  then @underline = 33    # Single accounting
    when 34  then @underline = 34    # Double accounting
    else
      raise ArgumentError,
      "\n\n  set_underline(#{arg.inspect})\n    arg must be 0, 1, or none, 2, 33, 34.\n"
      " ( 0:OFF, 1 and none:Single, 2:Double, 33:Single accounting, 34:Double accounting )\n"
    end
  end
end

#set_valign(alignment) ⇒ Object

set_valign()

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



890
891
892
 
# File 'lib/WriteExcel/format.rb', line 890

def set_valign(alignment)
  set_align(alignment);
end