Class: Format

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

Overview

Format - A class for defining Excel formatting.

See CELL FORMATTING, FORMAT METHODS, COLOURS IN EXCEL in WriteExcel’s rdoc.

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 =

:nodoc:

/[^!"#\$%&'\(\)\*\+,\-\.\/\:\;<=>\?@0-9A-Za-z_\[\\\]^` ~\0\n]/

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



53
54
55
56
57
58
59
60
61
62
63
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
 
# File 'lib/writeexcel/format.rb', line 53

def initialize(xf_index = 0, properties = {})   # :nodoc:

  @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.



1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
 
# File 'lib/writeexcel/format.rb', line 1668

def method_missing(name, *args)  # :nodoc:

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

Class Method Details

._get_color(colour) ⇒ Object

class method Format._get_color(colour)

used from Worksheet.rb

this is cut & copy of get_color().



688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
 
# File 'lib/writeexcel/format.rb', line 688

def self._get_color(colour)  # :nodoc:

  # 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

#bg_color ⇒ Object

:nodoc:



570
571
572
 
# File 'lib/writeexcel/format.rb', line 570

def bg_color  # :nodoc:

  @bg_color
end

#bold ⇒ Object

:nodoc:



482
483
484
 
# File 'lib/writeexcel/format.rb', line 482

def bold  # :nodoc:

  @bold
end

#bottom ⇒ Object

:nodoc:



578
579
580
 
# File 'lib/writeexcel/format.rb', line 578

def bottom  # :nodoc:

  @bottom
end

#bottom_color ⇒ Object

:nodoc:



594
595
596
 
# File 'lib/writeexcel/format.rb', line 594

def bottom_color  # :nodoc:

  @bottom_color
end

#color ⇒ Object

:nodoc:



490
491
492
 
# File 'lib/writeexcel/format.rb', line 490

def color  # :nodoc:

  @color
end

#copy(other) ⇒ Object

:call-seq:

copy(format)

Copy the attributes of another Format object.

This method is used to copy all of the properties from one Format object to another:

lorry1 = workbook.add_format
lorry1.set_bold
lorry1.set_italic
lorry1.set_color('red')     # lorry1 is bold, italic and red

lorry2 = workbook.add_format
lorry2.copy(lorry1)
lorry2.set_color('yellow')  # lorry2 is bold, italic and yellow

The copy() method is only useful if you are using the method interface to Format properties. It generally isn’t required if you are setting Format properties directly using hashes.

Note: this is not a copy constructor, both objects must exist prior to copying.



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
192
193
194
195
196
197
198
199
 
# File 'lib/writeexcel/format.rb', line 142

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

#diag_border ⇒ Object

:nodoc:



630
631
632
 
# File 'lib/writeexcel/format.rb', line 630

def diag_border  # :nodoc:

  @diag_border
end

#diag_color ⇒ Object

:nodoc:



626
627
628
 
# File 'lib/writeexcel/format.rb', line 626

def diag_color  # :nodoc:

  @diag_color
end

#diag_type ⇒ Object

:nodoc:



622
623
624
 
# File 'lib/writeexcel/format.rb', line 622

def diag_type  # :nodoc:

  @diag_type
end

#fg_color ⇒ Object

:nodoc:



566
567
568
 
# File 'lib/writeexcel/format.rb', line 566

def fg_color  # :nodoc:

  @fg_color
end

#font ⇒ Object

:nodoc:



474
475
476
 
# File 'lib/writeexcel/format.rb', line 474

def font  # :nodoc:

  @font
end

#font_charset ⇒ Object

:nodoc:



518
519
520
 
# File 'lib/writeexcel/format.rb', line 518

def font_charset  # :nodoc:

  @font_charset
end

#font_encoding ⇒ Object

:nodoc:



522
523
524
 
# File 'lib/writeexcel/format.rb', line 522

def font_encoding  # :nodoc:

  @font_encoding
end

#font_family ⇒ Object

:nodoc:



514
515
516
 
# File 'lib/writeexcel/format.rb', line 514

def font_family  # :nodoc:

  @font_family
end

#font_index ⇒ Object

:nodoc:



466
467
468
 
# File 'lib/writeexcel/format.rb', line 466

def font_index  # :nodoc:

  @font_index
end

#font_index=(val) ⇒ Object

:nodoc:



470
471
472
 
# File 'lib/writeexcel/format.rb', line 470

def font_index=(val)  # :nodoc:

  @font_index = val
end

#font_only ⇒ Object

:nodoc:



634
635
636
 
# File 'lib/writeexcel/format.rb', line 634

def font_only  # :nodoc:

  @font_only
end

#font_outline ⇒ Object

:nodoc:



502
503
504
 
# File 'lib/writeexcel/format.rb', line 502

def font_outline  # :nodoc:

  @font_outline
end

#font_script ⇒ Object

:nodoc:



510
511
512
 
# File 'lib/writeexcel/format.rb', line 510

def font_script  # :nodoc:

  @font_script
end

#font_shadow ⇒ Object

:nodoc:



506
507
508
 
# File 'lib/writeexcel/format.rb', line 506

def font_shadow  # :nodoc:

  @font_shadow
end

#font_strikeout ⇒ Object

:nodoc:



498
499
500
 
# File 'lib/writeexcel/format.rb', line 498

def font_strikeout  # :nodoc:

  @font_strikeout
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.



646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
 
# File 'lib/writeexcel/format.rb', line 646

def get_color(colour = nil)  # :nodoc:

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



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
418
419
420
421
422
423
424
 
# File 'lib/writeexcel/format.rb', line 363

def get_font  # :nodoc:


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



432
433
434
435
436
437
438
439
440
441
442
 
# File 'lib/writeexcel/format.rb', line 432

def get_font_key  # :nodoc:

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



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
349
350
351
352
353
354
355
 
# File 'lib/writeexcel/format.rb', line 207

def get_xf  # :nodoc:


  # 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        = @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

#hidden ⇒ Object

:nodoc:



538
539
540
 
# File 'lib/writeexcel/format.rb', line 538

def hidden  # :nodoc:

  @hidden
end

#indent ⇒ Object

:nodoc:



610
611
612
 
# File 'lib/writeexcel/format.rb', line 610

def indent  # :nodoc:

  @indent
end

#italic ⇒ Object

:nodoc:



486
487
488
 
# File 'lib/writeexcel/format.rb', line 486

def italic  # :nodoc:

  @italic
end

#left ⇒ Object

:nodoc:



586
587
588
 
# File 'lib/writeexcel/format.rb', line 586

def left  # :nodoc:

  @left
end

#left_color ⇒ Object

:nodoc:



602
603
604
 
# File 'lib/writeexcel/format.rb', line 602

def left_color  # :nodoc:

  @left_color
end

#locked ⇒ Object

:nodoc:



542
543
544
 
# File 'lib/writeexcel/format.rb', line 542

def locked  # :nodoc:

  @locked
end

#num_format ⇒ Object

:nodoc:



526
527
528
 
# File 'lib/writeexcel/format.rb', line 526

def num_format  # :nodoc:

  @num_format
end

#num_format=(val) ⇒ Object

:nodoc:



530
531
532
 
# File 'lib/writeexcel/format.rb', line 530

def num_format=(val)  # :nodoc:

  @num_format = val
end

#num_format_enc ⇒ Object

:nodoc:



534
535
536
 
# File 'lib/writeexcel/format.rb', line 534

def num_format_enc  # :nodoc:

  @num_format_enc
end

#pattern ⇒ Object

:nodoc:



574
575
576
 
# File 'lib/writeexcel/format.rb', line 574

def pattern  # :nodoc:

  @pattern
end

#reading_order ⇒ Object

:nodoc:



618
619
620
 
# File 'lib/writeexcel/format.rb', line 618

def reading_order  # :nodoc:

  @reading_order
end

#right ⇒ Object

:nodoc:



590
591
592
 
# File 'lib/writeexcel/format.rb', line 590

def right  # :nodoc:

  @right
end

#right_color ⇒ Object

:nodoc:



606
607
608
 
# File 'lib/writeexcel/format.rb', line 606

def right_color  # :nodoc:

  @right_color
end

#rotation ⇒ Object

:nodoc:



562
563
564
 
# File 'lib/writeexcel/format.rb', line 562

def rotation  # :nodoc:

  @rotation
end

#set_align(align = 'left') ⇒ Object

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:

   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.

For further examples see the ‘Alignment’ worksheet created by formats.rb.



1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
 
# File 'lib/writeexcel/format.rb', line 1058

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_bg_color(color = 0x41) ⇒ Object

The set_bg_color() method can be used to set the background colour of a pattern. Patterns are defined via the set_pattern() method. If a pattern hasn’t been defined then a solid fill pattern is used as the default.

Default state:      Color is off
Default action:     Solid fill.
Valid args:         See set_color()

Here is an example of how to set up a solid fill in a cell:

format = workbook.add_format

format.set_pattern()  # This is optional when using a solid fill

format.set_bg_color('green')
worksheet.write('A1', 'Ray', format)

For further examples see the ‘Patterns’ worksheet created by formats.rb.



1649
1650
1651
 
# File 'lib/writeexcel/format.rb', line 1649

def set_bg_color(color = 0x41)
  @bg_color = get_color(color)
end

#set_bold(weight = nil) ⇒ Object

Set the bold property of the font:

Default state:      bold is off
Default action:     Turn bold on
Valid args:         0, 1 [1]

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.



812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
 
# File 'lib/writeexcel/format.rb', line 812

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 cells borders to the same style

Also applies to:    set_bottom()
                    set_top()
                    set_left()
                    set_right()

Default state:      Border is off
Default action:     Set border type 1
Valid args:         0-13, See below.

A cell border is comprised of a border on the bottom, top, left and right. These can be set to the same value using set_border() or individually using the relevant method calls shown above.

The following shows the border styles sorted by WriteExcel index number:

Index   Name            Weight   Style
=====   =============   ======   ===========
0       None            0
1       Continuous      1        -----------
2       Continuous      2        -----------
3       Dash            1        - - - - - -
4       Dot             1        . . . . . .
5       Continuous      3        -----------
6       Double          3        ===========
7       Continuous      0        -----------
8       Dash            2        - - - - - -
9       Dash Dot        1        - . - . - .
10      Dash Dot        2        - . - . - .
11      Dash Dot Dot    1        - . . - . .
12      Dash Dot Dot    2        - . . - . .
13      SlantDash Dot   2        / - . / - .

The following shows the borders sorted by style:

Name            Weight   Style         Index
=============   ======   ===========   =====
Continuous      0        -----------   7
Continuous      1        -----------   1
Continuous      2        -----------   2
Continuous      3        -----------   5
Dash            1        - - - - - -   3
Dash            2        - - - - - -   8
Dash Dot        1        - . - . - .   9
Dash Dot        2        - . - . - .   10
Dash Dot Dot    1        - . . - . .   11
Dash Dot Dot    2        - . . - . .   12
Dot             1        . . . . . .   4
Double          3        ===========   6
None            0                      0
SlantDash Dot   2        / - . / - .   13

The following shows the borders in the order shown in the Excel Dialog.

Index   Style             Index   Style
=====   =====             =====   =====
0       None              12      - . . - . .
7       -----------       13      / - . / - .
4       . . . . . .       10      - . - . - .
11      - . . - . .       8       - - - - - -
9       - . - . - .       2       -----------
3       - - - - - -       5       -----------
1       -----------       6       ===========

Examples of the available border styles are shown in the ‘Borders’ worksheet created by formats.rb.



1228
1229
1230
1231
1232
1233
 
# File 'lib/writeexcel/format.rb', line 1228

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

Also applies to:    set_bottom_color()
                    set_top_color()
                    set_left_color()
                    set_right_color()

Default state:      Color is off
Default action:     Undefined
Valid args:         See set_color()

Set the colour of the cell borders. A cell border is comprised of a border on the bottom, top, left and right. These can be set to the same colour using set_border_color() or individually using the relevant method calls shown above. Examples of the border styles and colours are shown in the ‘Borders’ worksheet created by formats.rb.



1285
1286
1287
1288
1289
1290
 
# File 'lib/writeexcel/format.rb', line 1285

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

#set_bottom(style) ⇒ Object

set bottom border of the cell. see set_border() about style.



1239
1240
1241
 
# File 'lib/writeexcel/format.rb', line 1239

def set_bottom(style)
  @bottom = style
end

#set_bottom_color(color) ⇒ Object

set bottom border color of the cell. see set_border_color() about color.



1296
1297
1298
 
# File 'lib/writeexcel/format.rb', line 1296

def set_bottom_color(color)
  @bottom_color = get_color(color)
end

#set_center_across(arg = 1) ⇒ Object

Implements the Excel5 style “merge”.

Default state:      Center across selection is off
Default action:     Turn center across on
Valid args:         1

Text can be aligned across two or more adjacent cells using the set_center_across() method. This is an alias for the set_align(‘center_across’) method call.

Only one cell should contain the text, the other cells should be blank:

format = workbook.add_format
format.set_center_across

worksheet.write(1, 1, 'Center across selection', format)
worksheet.write_blank(1, 2, format)

See also the merge1.pl to merge6.rb programs in the examples directory and the merge_range() method.



1118
1119
1120
 
# File 'lib/writeexcel/format.rb', line 1118

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

#set_color(color = 0x7FFF) ⇒ Object

Set the font colour.

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'

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.



775
776
777
 
# File 'lib/writeexcel/format.rb', line 775

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

#set_fg_color(color = 0x40) ⇒ Object

The set_fg_color() method can be used to set the foreground colour of a pattern.

Default state:      Color is off
Default action:     Solid fill.
Valid args:         See set_color()

For further examples see the ‘Patterns’ worksheet created by formats.rb.



1663
1664
1665
 
# File 'lib/writeexcel/format.rb', line 1663

def set_fg_color(color = 0x40)
  @fg_color = get_color(color)
end

#set_font(fontname) ⇒ Object

Default state: Font is Arial

Default action:     None
Valid args:         Any valid font name

Specify the font used:

format.set_font('Times New Roman');

Excel can only display fonts that are installed on the system that it is running on. Therefore it is best to use the fonts that come as standard such as ‘Arial’, ‘Times New Roman’ and ‘Courier New’. See also the Fonts worksheet created by formats.rb



1422
1423
1424
 
# File 'lib/writeexcel/format.rb', line 1422

def set_font(fontname)
  @font = fontname
end

#set_font_outline(arg = 1) ⇒ Object

Macintosh only.

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



910
911
912
913
914
915
916
917
918
919
920
 
# File 'lib/writeexcel/format.rb', line 910

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 the superscript/subscript property of the font. This format is currently not very useful.

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



890
891
892
893
894
895
896
897
898
899
900
901
 
# File 'lib/writeexcel/format.rb', line 890

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

Macintosh only.

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



929
930
931
932
933
934
935
936
937
938
939
 
# File 'lib/writeexcel/format.rb', line 929

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 the strikeout property of the font.

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



868
869
870
871
872
873
874
875
876
877
878
 
# File 'lib/writeexcel/format.rb', line 868

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

: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');



1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
 
# File 'lib/writeexcel/format.rb', line 1390

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

hide a formula while still displaying its result.

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.

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  .



1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
 
# File 'lib/writeexcel/format.rb', line 1008

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_indent(indent = 1) ⇒ Object

This method can be used to indent text. The argument, which should be an integer, is taken as the level of indentation:

Default state:      Text indentation is off
Default action:     Indent text 1 level
Valid args:         Positive integers

format = workbook.add_format
format.set_indent(2)
worksheet.write(0, 0, 'This text is indented', format)

Indentation is a horizontal alignment property. It will override any other horizontal properties but it can be used in conjunction with vertical properties.



1584
1585
1586
 
# File 'lib/writeexcel/format.rb', line 1584

def set_indent(indent = 1)
  @indent = indent
end

#set_italic(arg = 1) ⇒ Object

Set the italic property of the font:

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

format.set_italic    # Turn italic on



788
789
790
791
792
793
794
795
796
797
 
# File 'lib/writeexcel/format.rb', line 788

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_left(style) ⇒ Object

set left border of the cell. see set_border() about style.



1255
1256
1257
 
# File 'lib/writeexcel/format.rb', line 1255

def set_left(style)
  @left = style
end

#set_left_color(color) ⇒ Object

set left border color of the cell. see set_border_color() about color.



1312
1313
1314
 
# File 'lib/writeexcel/format.rb', line 1312

def set_left_color(color)
  @left_color = get_color(color)
end

#set_locked(arg = 1) ⇒ Object

prevent modification of a cells contents.

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.



971
972
973
974
975
976
977
978
979
980
981
 
# File 'lib/writeexcel/format.rb', line 971

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



1131
1132
1133
 
# File 'lib/writeexcel/format.rb', line 1131

def set_merge(val=true)  # :nodoc:

  set_text_h_align(6)
end

#set_num_format(num_format) ⇒ Object

This method is used to define the numerical format of a number in Excel.

Default state:      General format
Default action:     Format index 1
Valid args:         See the following table

It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format.

The numerical format of a cell can be specified by using a format string or an index to one of Excel’s built-in formats:

format1 = workbook.add_format
format2 = workbook.add_format
format1.set_num_format('d mmm yyyy')  # Format string
format2.set_num_format(0x0f)          # Format index

worksheet.write(0, 0, 36892.521, format1)       # 1 Jan 2001
worksheet.write(0, 0, 36892.521, format2)       # 1-Jan-01

Using format strings you can define very sophisticated formatting of numbers.

format01.set_num_format('0.000')
worksheet.write(0,  0, 3.1415926, format01)     # 3.142

format02.set_num_format('#,##0')
worksheet.write(1,  0, 1234.56,   format02)     # 1,235

format03.set_num_format('#,##0.00')
worksheet.write(2,  0, 1234.56,   format03)     # 1,234.56

format04.set_num_format('0.00')
worksheet.write(3,  0, 49.99,     format04)     # 49.99

# Note you can use other currency symbols such as the pound or yen as well.
# Other currencies may require the use of Unicode.

format07.set_num_format('mm/dd/yy')
worksheet.write(6,  0, 36892.521, format07)     # 01/01/01

format08.set_num_format('mmm d yyyy')
worksheet.write(7,  0, 36892.521, format08)     # Jan 1 2001

format09.set_num_format('d mmmm yyyy')
worksheet.write(8,  0, 36892.521, format09)     # 1 January 2001

format10.set_num_format('dd/mm/yyyy hh:mm AM/PM')
worksheet.write(9,  0, 36892.521, format10)     # 01/01/2001 12:30 AM

format11.set_num_format('0 "dollar and" .00 "cents"')
worksheet.write(10, 0, 1.87,      format11)     # 1 dollar and .87 cents

# Conditional formatting
format12.set_num_format('[Green]General;[Red]-General;General')
worksheet.write(11, 0, 123,       format12)     # > 0 Green
worksheet.write(12, 0, -45,       format12)     # < 0 Red
worksheet.write(13, 0, 0,         format12)     # = 0 Default colour

# Zip code
format13.set_num_format('00000')
worksheet.write(14, 0, '01209',   format13)

The number system used for dates is described in “DATES AND TIME IN EXCEL”.

The colour format should have one of the following values:

[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]

Alternatively you can specify the colour based on a colour index as follows: [Color n], where n is a standard Excel colour index - 7. See the ‘Standard colors’ worksheet created by formats.rb.

For more information refer to the documentation on formatting in the doc directory of the WriteExcel distro, the Excel on-line help or office.microsoft.com/en-gb/assistance/HP051995001033.aspx

You should ensure that the format string is valid in Excel prior to using it in WriteExcel.

Excel’s built-in formats are shown in the following table:

Index   Index   Format String
0       0x00    General
1       0x01    0
2       0x02    0.00
3       0x03    #,##0
4       0x04    #,##0.00
5       0x05    ($#,##0_);($#,##0)
6       0x06    ($#,##0_);[Red]($#,##0)
7       0x07    ($#,##0.00_);($#,##0.00)
8       0x08    ($#,##0.00_);[Red]($#,##0.00)
9       0x09    0%
10      0x0a    0.00%
11      0x0b    0.00E+00
12      0x0c    # ?/?
13      0x0d    # ??/??
14      0x0e    m/d/yy
15      0x0f    d-mmm-yy
16      0x10    d-mmm
17      0x11    mmm-yy
18      0x12    h:mm AM/PM
19      0x13    h:mm:ss AM/PM
20      0x14    h:mm
21      0x15    h:mm:ss
22      0x16    m/d/yy h:mm
..      ....    ...........
37      0x25    (#,##0_);(#,##0)
38      0x26    (#,##0_);[Red](#,##0)
39      0x27    (#,##0.00_);(#,##0.00)
40      0x28    (#,##0.00_);[Red](#,##0.00)
41      0x29    _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
42      0x2a    _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
43      0x2b    _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
44      0x2c    _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
45      0x2d    mm:ss
46      0x2e    [h]:mm:ss
47      0x2f    mm:ss.0
48      0x30    ##0.0E+0
49      0x31    @

For examples of these formatting codes see the ‘Numerical formats’ worksheet created by formats.rb. – See also the number_formats1.html and the number_formats2.html documents in the doc directory of the distro. ++

Note 1. Numeric formats 23 to 36 are not documented by Microsoft and may differ in international versions.

Note 2. In Excel 5 the dollar sign appears as a dollar sign. In Excel 97-2000 it appears as the defined local currency symbol.

Note 3. The red negative numeric formats display slightly differently in Excel 5 and Excel 97-2000.



1564
1565
1566
 
# File 'lib/writeexcel/format.rb', line 1564

def set_num_format(num_format)
  @num_format = num_format
end

#set_pattern(pattern = 1) ⇒ Object

Default state: Pattern is off

Default action:     Solid fill is on
Valid args:         0 .. 18

Set the background pattern of a cell.

Examples of the available patterns are shown in the ‘Patterns’ worksheet created by formats.rb. However, it is unlikely that you will ever need anything other than Pattern 1 which is a solid fill of the background color.



1625
1626
1627
 
# File 'lib/writeexcel/format.rb', line 1625

def set_pattern(pattern = 1)
  @pattern = pattern
end

#set_right(style) ⇒ Object

set right border of the cell. see set_border() about style.



1263
1264
1265
 
# File 'lib/writeexcel/format.rb', line 1263

def set_right(style)
  @right = style
end

#set_right_color(color) ⇒ Object

set right border color of the cell. see set_border_color() about color.



1320
1321
1322
 
# File 'lib/writeexcel/format.rb', line 1320

def set_right_color(color)
  @right_color = get_color(color)
end

#set_rotation(rotation) ⇒ Object

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

Default state:      Text rotation is off
Default action:     None
Valid args:         Integers in the range -90 to 90 and 270

Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees.

format = workbook.add_format
format.set_rotation(30)
worksheet.write(0, 0, 'This text is rotated', format)

The angle 270 is also supported. This indicates text where the letters run from top to bottom.



1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
 
# File 'lib/writeexcel/format.rb', line 1341

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_shrink(arg = 1) ⇒ Object

This method can be used to shrink text so that it fits in a cell.

Default state:      Text shrinking is off
Default action:     Turn "shrink to fit" on
Valid args:         1

format = workbook.add_format
format.set_shrink
worksheet.write(0, 0, 'Honey, I shrunk the text!', format)



1599
1600
1601
 
# File 'lib/writeexcel/format.rb', line 1599

def set_shrink(arg = 1)
  @shrink = 1
end

#set_size(size = 1) ⇒ Object

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.

format = workbook.add_format
format.set_size(30)



749
750
751
752
753
 
# File 'lib/writeexcel/format.rb', line 749

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

#set_text_justlast(arg = 1) ⇒ Object

Default state: Justify last is off

Default action:     Turn justify last on
Valid args:         0, 1

Only applies to Far Eastern versions of Excel.



1610
1611
1612
 
# File 'lib/writeexcel/format.rb', line 1610

def set_text_justlast(arg = 1)
  @text_justlast = 1
end

#set_text_wrap(arg = 1) ⇒ Object

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)



1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
 
# File 'lib/writeexcel/format.rb', line 1147

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_top(style) ⇒ Object

set top border of the cell. see set_border() about style.



1247
1248
1249
 
# File 'lib/writeexcel/format.rb', line 1247

def set_top(style)
  @top = style
end

#set_top_color(color) ⇒ Object

set top border color of the cell. see set_border_color() about color.



1304
1305
1306
 
# File 'lib/writeexcel/format.rb', line 1304

def set_top_color(color)
  @top_color = get_color(color)
end

#set_type(type = nil) ⇒ Object

set_type()

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



728
729
730
731
732
733
734
735
 
# File 'lib/writeexcel/format.rb', line 728

def set_type(type = nil)  # :nodoc:


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

#set_underline(arg = 1) ⇒ Object

Set the underline property of the font.

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

format.set_underline();   # Single underline



845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
 
# File 'lib/writeexcel/format.rb', line 845

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.



1092
1093
1094
 
# File 'lib/writeexcel/format.rb', line 1092

def set_valign(alignment)  # :nodoc:

  set_align(alignment);
end

#shrink ⇒ Object

:nodoc:



614
615
616
 
# File 'lib/writeexcel/format.rb', line 614

def shrink  # :nodoc:

  @shrink
end

#size ⇒ Object

:nodoc:



478
479
480
 
# File 'lib/writeexcel/format.rb', line 478

def size  # :nodoc:

  @size
end

#text_h_align ⇒ Object

:nodoc:



546
547
548
 
# File 'lib/writeexcel/format.rb', line 546

def text_h_align  # :nodoc:

  @text_h_align
end

#text_justlast ⇒ Object

:nodoc:



558
559
560
 
# File 'lib/writeexcel/format.rb', line 558

def text_justlast  # :nodoc:

  @text_justlast
end

#text_v_align ⇒ Object

:nodoc:



554
555
556
 
# File 'lib/writeexcel/format.rb', line 554

def text_v_align  # :nodoc:

  @text_v_align
end

#text_wrap ⇒ Object

:nodoc:



550
551
552
 
# File 'lib/writeexcel/format.rb', line 550

def text_wrap  # :nodoc:

  @text_wrap
end

#top ⇒ Object

:nodoc:



582
583
584
 
# File 'lib/writeexcel/format.rb', line 582

def top  # :nodoc:

  @top
end

#top_color ⇒ Object

:nodoc:



598
599
600
 
# File 'lib/writeexcel/format.rb', line 598

def top_color  # :nodoc:

  @top_color
end

#type ⇒ Object

:nodoc:



462
463
464
 
# File 'lib/writeexcel/format.rb', line 462

def type  # :nodoc:

  @type
end

#underline ⇒ Object

:nodoc:



494
495
496
 
# File 'lib/writeexcel/format.rb', line 494

def underline  # :nodoc:

  @underline
end

#used_merge ⇒ Object

:nodoc:



454
455
456
 
# File 'lib/writeexcel/format.rb', line 454

def used_merge  # :nodoc:

  @used_merge
end

#used_merge=(val) ⇒ Object

:nodoc:



458
459
460
 
# File 'lib/writeexcel/format.rb', line 458

def used_merge=(val)  # :nodoc:

  @used_merge = val
end

#xf_index ⇒ Object

xf_index()

Returns the used by Worksheet->_XF()



450
451
452
 
# File 'lib/writeexcel/format.rb', line 450

def xf_index  # :nodoc:

  return @xf_index
end