Class: Spreadsheet::Excel::Writer::Worksheet

Inherits:
Object
  • Object
show all
Includes:
Internals, Internals::Biff8, Biff8
Defined in:
lib/spreadsheet/excel/writer/worksheet.rb,
lib/spreadsheet/excel/writer/n_worksheet.rb

Overview

If you think you do, look at #write_worksheet

Constant Summary

Constants included from Internals::Biff8

Internals::Biff8::BINARY_FORMATS

Constants included from Internals

Internals::BINARY_FORMATS, Internals::BUILTIN_FORMATS, Internals::BUILTIN_STYLES, Internals::CODEPAGES, Internals::COLOR_CODES, Internals::EIGHT_BYTE_DOUBLE, Internals::ESCAPEMENT_TYPES, Internals::FONT_ENCODINGS, Internals::FONT_FAMILIES, Internals::FONT_WEIGHTS, Internals::LEAP_ERROR, Internals::NGILA_H_FX, Internals::NGILA_V_FX, Internals::NOITCERID_TXET_FX, Internals::OPCODES, Internals::OPCODE_SIZE, Internals::ROW_HEIGHT, Internals::SEDOCPO, Internals::SEDOC_ROLOC, Internals::SEGAPEDOC, Internals::SEILIMAF_TNOF, Internals::SEITILIBISIV_TEEHSKROW, Internals::SELYTS_ENIL_REDROB_FX, Internals::SEPYT_ENILREDNU, Internals::SEPYT_TNEMEPACSE, Internals::SGNIDOCNE_TNOF, Internals::SST_CHUNKSIZE, Internals::TWIPS, Internals::UNDERLINE_TYPES, Internals::WORKSHEET_VISIBILITIES, Internals::XF_BORDER_LINE_STYLES, Internals::XF_H_ALIGN, Internals::XF_TEXT_DIRECTION, Internals::XF_V_ALIGN

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Internals::Biff8

#binfmt

Methods included from Internals

#binfmt, #opcode

Methods included from Biff8

#_unicode_string, #compress_unicode_string, #unicode_string

Constructor Details

#initialize(workbook, worksheet) ⇒ Worksheet

Returns a new instance of Worksheet.



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 20

def initialize workbook, worksheet
  @workbook = workbook
  @worksheet = worksheet
  @io = StringIO.new ''.dup
  @biff_version = 0x0600
  @bof = 0x0809
  @build_id = 3515
  @build_year = 1996
  @bof_types = {
    :globals      => 0x0005,
    :visual_basic => 0x0006,
    :worksheet    => 0x0010,
    :chart        => 0x0020,
    :macro_sheet  => 0x0040,
    :workspace    => 0x0100,
  }
end

Instance Attribute Details

#worksheet ⇒ Object (readonly)

Returns the value of attribute worksheet.



19
20
21
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 19

def worksheet
  @worksheet
end

Instance Method Details

#boundsheet_size ⇒ Object

The number of bytes needed to write a Boundsheet record for this Worksheet Used by Writer::Worksheet to calculate various offsets.



40
41
42
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 40

def boundsheet_size
  name.size + 10
end

#data ⇒ Object 



43
44
45
46
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 43

def data
  @io.rewind
  @io.read
end

#encode_date(date) ⇒ Object 



47
48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 47

def encode_date date
  return date if date.is_a? Numeric
  if date.is_a? Time
    date = DateTime.new date.year, date.month, date.day,
                        date.hour, date.min, date.sec
  end
  base = @workbook.date_base
  value = date - base
  if LEAP_ERROR > base
    value += 1
  end
  value
end

#encode_rk(value) ⇒ Object 



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
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 60

def encode_rk value
  #  Bit  Mask        Contents
  #    0  0x00000001  0 = Value not changed 1 = Value is multiplied by 100
  #    1  0x00000002  0 = Floating-point value 1 = Signed integer value
  # 31-2  0xFFFFFFFC  Encoded value
  cent = 0
  int = 2
  higher = value * 100
  if (higher.is_a?(Rational) or higher.is_a?(BigDecimal) or higher.is_a?(Float)) && higher < 0xfffffffc
    cent = 1
    if higher == higher.to_i
      value = higher.to_i
    else
      value = higher
    end
  end
  if value.is_a?(Integer)
    ## although not documented as signed, 'V' appears to correctly pack
    #  negative numbers.
    value <<= 2
  else
    # FIXME: precision of small numbers
    int = 0
    value, = [value].pack(EIGHT_BYTE_DOUBLE).unpack('x4V')
    value &= 0xfffffffc
  end
  value | cent | int
end

#name ⇒ Object 



88
89
90
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 88

def name
  unicode_string @worksheet.name
end

#need_number?(cell) ⇒ Boolean

Returns:

  • (Boolean) 


91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 91

def need_number? cell
  if cell.is_a?(Numeric) && cell.abs > 0x1fffffff
    true
  elsif cell.is_a?(Rational) or ((cell.is_a?(BigDecimal) or cell.is_a?(Float)) and not cell.nan?)
    higher = cell * 100
    if higher == higher.to_i
      need_number? higher.to_i
    else
      test1, test2 = [cell * 100].pack(EIGHT_BYTE_DOUBLE).unpack('V2')
      test1 > 0 || need_number?(test2)
    end
  else
    false
  end
end

#row_blocks ⇒ Object 



106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 106

def row_blocks
  # All cells in an Excel document are divided into blocks of 32 consecutive
  # rows, called Row Blocks. The first Row Block starts with the first used
  # row in that sheet. Inside each Row Block there will occur ROW records
  # describing the properties of the rows, and cell records with all the cell
  # contents in this Row Block.
  blocks = []
  @worksheet.reject do |row| row.empty? end.each_with_index do |row, idx|
    blocks << [] if idx % 32 == 0
    blocks.last << row
  end
  blocks
end

#size ⇒ Object 



119
120
121
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 119

def size
  @io.size
end

#strings ⇒ Object 



122
123
124
125
126
127
128
129
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 122

def strings
  @worksheet.inject(Hash.new(0)) do |memo, row|
    row.each do |cell|
      memo[cell] += 1 if (cell.is_a?(String) && !cell.empty?)
    end
    memo
  end
end

#write_blank(row, idx) ⇒ Object

Write a blank cell



132
133
134
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 132

def write_blank row, idx
  write_cell :blank, row, idx
end

#write_bof ⇒ Object 



135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 135

def write_bof
  data = [
    @biff_version, # BIFF version (always 0x0600 for BIFF8)
    0x0010,        # Type of the following data:
                   # 0x0005 = Workbook globals
                   # 0x0006 = Visual Basic module
                   # 0x0010 = Worksheet
                   # 0x0020 = Chart
                   # 0x0040 = Macro sheet
                   # 0x0100 = Workspace file
    @build_id,     # Build identifier
    @build_year,   # Build year
    0x000,         # File history flags
    0x006,         # Lowest Excel version that can read
                   # all records in this file
  ]
  write_op @bof, data.pack("v4V2")
end

#write_boolerr(row, idx) ⇒ Object

Write a cell with a Boolean or Error value



155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 155

def write_boolerr row, idx
  value = row[idx]
  type = 0
  numval = 0
  if value.is_a? Error
    type = 1
    numval = value.code
  elsif value
    numval = 1
  end
  data = [
    numval, # Boolean or error value (type depends on the following byte)
    type    # 0 = Boolean value; 1 = Error code
  ]
  write_cell :boolerr, row, idx, *data
end

#write_calccount ⇒ Object 



171
172
173
174
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 171

def write_calccount
  count = 100 # Maximum number of iterations allowed in circular references
  write_op 0x000c, [count].pack('v')
end

#write_cell(type, row, idx, *args) ⇒ Object 



175
176
177
178
179
180
181
182
183
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 175

def write_cell type, row, idx, *args
  xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx)
  data = [
    row.idx, # Index to row
    idx,     # Index to column
    xf_idx,  # Index to XF record (➜ 6.115)
  ].concat args
  write_op opcode(type), data.pack(binfmt(type))
end

#write_cellblocks(row) ⇒ Object 



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 184

def write_cellblocks row
  # BLANK ➜ 6.7
  # BOOLERR ➜ 6.10
  # INTEGER ➜ 6.56 (BIFF2 only)
  # LABEL ➜ 6.59 (BIFF2-BIFF7)
  # LABELSST ➜ 6.61 (BIFF8 only)
  # MULBLANK ➜ 6.64 (BIFF5-BIFF8)
  # MULRK ➜ 6.65 (BIFF5-BIFF8)
  # NUMBER ➜ 6.68
  # RK ➜ 6.82 (BIFF3-BIFF8)
  # RSTRING ➜ 6.84 (BIFF5/BIFF7)
  multiples, first_idx = nil
  row = row.formatted
  row.each_with_index do |cell, idx|
    cell = nil if cell == ''
    ## it appears that there are limitations to RK precision, both for
    #  Integers and Floats, that lie well below 2^30 significant bits, or
    #  Ruby's Bignum threshold. In that case we'll just write a Number
    #  record
    need_number = need_number? cell
    if multiples && (!multiples.last.is_a?(cell.class) || need_number)
      write_multiples row, first_idx, multiples
      multiples, first_idx = nil
    end
    nxt = idx + 1
    case cell
    when NilClass
      if multiples
        multiples.push cell
      elsif nxt < row.size && row[nxt].nil?
        multiples = [cell]
        first_idx = idx
      else
        write_blank row, idx
      end
    when TrueClass, FalseClass, Error
      write_boolerr row, idx
    when String
      write_labelsst row, idx
    when Numeric
      ## RK encodes Floats with 30 significant bits, which is a bit more than
      #  10^9. Not sure what is a good rule of thumb here, but it seems that
      #  Decimal Numbers with more than 4 significant digits are not represented
      #  with sufficient precision by RK
      if need_number
        write_number row, idx
      elsif multiples
        multiples.push cell
      elsif nxt < row.size && row[nxt].is_a?(Numeric)
        multiples = [cell]
        first_idx = idx
      else
        write_rk row, idx
      end
    when Formula
      write_formula row, idx
    when Date, Time
      write_number row, idx
    end
  end
  write_multiples row, first_idx, multiples if multiples
end

#write_colinfo(bunch) ⇒ Object 



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 246

def write_colinfo bunch
  col = bunch.first
  width = col.width.to_f * 256
  xf_idx = @workbook.xf_index @worksheet.workbook, col.default_format
  opts =  0
  opts |= 0x0001 if col.hidden?
  opts |= col.outline_level.to_i << 8
  opts |= 0x1000 if col.collapsed?
  data = [
    col.idx,        # Index to first column in the range
    bunch.last.idx, # Index to last column in the range
    width.to_i,     # Width of the columns in 1/256 of the width of the zero
                    # character, using default font (first FONT record in the
                    # file)
    xf_idx.to_i,    # Index to XF record (➜ 6.115) for default column formatting
    opts,           # Option flags:
                    # Bits  Mask    Contents
                    #    0  0x0001  1 = Columns are hidden
                    # 10-8  0x0700  Outline level of the columns
                    #               (0 = no outline)
                    #   12  0x1000  1 = Columns are collapsed
  ]
  write_op opcode(:colinfo), data.pack(binfmt(:colinfo))
end

#write_colinfos ⇒ Object 



270
271
272
273
274
275
276
277
278
279
280
281
282
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 270

def write_colinfos
  cols = @worksheet.columns
  bunch = []
  cols.each_with_index do |column, idx|
    if column
      bunch << column
      if cols[idx.next] != column
        write_colinfo bunch
        bunch.clear
      end
    end
  end
end

#write_defaultrowheight ⇒ Object 



283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 283

def write_defaultrowheight
  data = [
    0x00, # Option flags:
          # Bit  Mask  Contents
          #   0  0x01  1 = Row height and default font height do not match
          #   1  0x02  1 = Row is hidden
          #   2  0x04  1 = Additional space above the row
          #   3  0x08  1 = Additional space below the row
    0xf2, #   Default height for unused rows, in twips = 1/20 of a point
  ]
  write_op 0x0225, data.pack('v2')
end

#write_defcolwidth ⇒ Object 



295
296
297
298
299
300
301
302
303
304
305
306
307
308
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 295

def write_defcolwidth
  # Offset  Size  Contents
  #      0     2  Column width in characters, using the width of the zero
  #               character from default font (first FONT record in the
  #               file). Excel adds some extra space to the default width,
  #               depending on the default font and default font size. The
  #               algorithm how to exactly calculate the resulting column
  #               width is not known.
  #
  #               Example: The default width of 8 set in this record results
  #               in a column width of 8.43 using Arial font with a size of
  #               10 points.
  write_op 0x0055, [8].pack('v')
end

#write_dimensions ⇒ Object 



309
310
311
312
313
314
315
316
317
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 309

def write_dimensions
  # Offset  Size  Contents
  #      0     4  Index to first used row
  #      4     4  Index to last used row, increased by 1
  #      8     2  Index to first used column
  #     10     2  Index to last used column, increased by 1
  #     12     2  Not used
  write_op 0x0200, @worksheet.dimensions.pack(binfmt(:dimensions))
end

#write_eof ⇒ Object 



318
319
320
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 318

def write_eof
  write_op 0x000a
end

#write_formula(row, idx) ⇒ Object

Write a cell with a Formula. May write an additional String record depending on the stored result of the Formula.



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
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 324

def write_formula row, idx
  xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx)
  cell = row[idx]
  data1 = [
    row.idx,      # Index to row
    idx,          # Index to column
    xf_idx,       # Index to XF record (➜ 6.115)
  ].pack 'v3'
  data2 = nil
  case value = cell.value
  when Numeric    # IEEE 754 floating-point value (64-bit double precision)
    data2 = [value].pack EIGHT_BYTE_DOUBLE
  when String
    data2 = [
      0x00,       # (identifier for a string value)
      0xffff,     #
    ].pack 'Cx5v'
  when true, false
    value = value ? 1 : 0
    data2 = [
      0x01,     # (identifier for a Boolean value)
      value,    # 0 = FALSE, 1 = TRUE
      0xffff,   #
    ].pack 'CxCx3v'
  when Error
    data2 = [
      0x02,       # (identifier for an error value)
      value.code, # Error code
      0xffff,     #
    ].pack 'CxCx3v'
  when nil
    data2 = [
      0x03,       # (identifier for an empty cell)
      0xffff,     #
    ].pack 'Cx5v'
  else
    data2 = [
      0x02,       # (identifier for an error value)
      0x2a,       # Error code: #N/A! Argument or function not available
      0xffff,     #
    ].pack 'CxCx3v'
  end
  opts = 0x03
  opts |= 0x08 if cell.shared
  data3 = [
    opts        # Option flags:
                # Bit  Mask    Contents
                #   0  0x0001  1 = Recalculate always
                #   1  0x0002  1 = Calculate on open
                #   3  0x0008  1 = Part of a shared formula
  ].pack 'vx4'
  write_op opcode(:formula), data1, data2, data3, cell.data
  if cell.value.is_a?(String)
    write_op opcode(:string), unicode_string(cell.value, 2)
  end
end

#write_from_scratch ⇒ Object

Write a new Worksheet.



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
425
426
427
428
429
430
431
432
433
434
435
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 382

def write_from_scratch
  # ●  BOF Type = worksheet (➜ 5.8)
  write_bof
  # ○  UNCALCED ➜ 5.105
  # ○  INDEX ➜ 4.7 (Row Blocks), ➜ 5.59
  # ○  Calculation Settings Block ➜ 4.3
  write_calccount
  write_refmode
  write_iteration
  write_saverecalc
  # ○  PRINTHEADERS ➜ 5.81
  # ○  PRINTGRIDLINES ➜ 5.80
  # ○  GRIDSET ➜ 5.52
  # ○  GUTS ➜ 5.53
  write_guts
  # ○  DEFAULTROWHEIGHT ➜ 5.31
  write_defaultrowheight
  # ○  WSBOOL ➜ 5.113
  write_wsbool
  # ○  Page Settings Block ➜ 4.4
  # ○  Worksheet Protection Block ➜ 4.18
  write_proctection
  # ○  DEFCOLWIDTH ➜ 5.32
  write_defcolwidth
  # ○○ COLINFO ➜ 5.18
  write_colinfos
  # ○  SORT ➜ 5.99
  # ●  DIMENSIONS ➜ 5.35
  write_dimensions
  # ○○ Row Blocks ➜ 4.7
  write_rows
  # ●  Worksheet View Settings Block ➜ 4.5
  # ●  WINDOW2 ➜ 5.110
  write_window2
  # ○  SCL ➜ 5.92 (BIFF4-BIFF8 only)
  # ○  PANE ➜ 5.75
  # ○○ SELECTION ➜ 5.93
  # ○  STANDARDWIDTH ➜ 5.101
  # ○○ MERGEDCELLS ➜ 5.67
  write_merged_cells
  # ○  LABELRANGES ➜ 5.64
  # ○  PHONETIC ➜ 5.77
  # ○  Conditional Formatting Table ➜ 4.12
  # ○  Hyperlink Table ➜ 4.13
  write_pagesetup
  write_margins
  write_hyperlink_table
  # ○  Data Validity Table ➜ 4.14
  # ○  SHEETLAYOUT ➜ 5.96 (BIFF8X only)
  # ○  SHEETPROTECTION Additional protection, ➜ 5.98 (BIFF8X only)
  # ○  RANGEPROTECTION Additional protection, ➜ 5.84 (BIFF8X only)
  # ●  EOF ➜ 5.36
  write_eof
end

#write_guts ⇒ Object

Write record that contains information about the layout of outline symbols.



438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 438

def write_guts
  # find the maximum outline_level in rows and columns
  row_outline_level = 0
  col_outline_level = 0
  if(row = @worksheet.rows.select{|x| x!=nil}.max{|a,b| a.outline_level <=> b.outline_level})
    row_outline_level = row.outline_level
  end
  if(col = @worksheet.columns.select{|x| x!=nil}.max{|a,b| a.outline_level <=> b.outline_level})
    col_outline_level = col.outline_level
  end
  # set data
  data = [
    0,  # Width of the area to display row outlines (left of the sheet), in pixel
    0,  # Height of the area to display column outlines (above the sheet), in pixel
    row_outline_level+1, # Number of visible row outline levels (used row levels+1; or 0,if not used)
    col_outline_level+1  # Number of visible column outline levels (used column levels+1; or 0,if not used)
  ]
  # write record
  write_op opcode(:guts), data.pack('v4')
end

#write_hlink(row, col, link) ⇒ Object 



458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
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
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 458

def write_hlink row, col, link
  # FIXME: only Hyperlinks are supported at present.
  cell_range = [
    row, row, # Cell range address of all cells containing this hyperlink
    col, col, # (➜ 3.13.1)
  ].pack 'v4'
  guid = [
    # GUID of StdLink:
    # D0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B
    # (79EAC9D0-BAF9-11CE-8C82-00AA004BA90B)
    "d0c9ea79f9bace118c8200aa004ba90b",
  ].pack 'H32'
  opts  = 0x01
  opts |= 0x02
  opts |= 0x14 unless link == link.url
  opts |= 0x08 if link.fragment
  opts |= 0x80 if link.target_frame
  # TODO: UNC support
  options = [
    2,        # Unknown value: 0x00000002
    opts,     # Option flags
              #     Bit  Mask        Contents
              #       0  0x00000001  0 = No link extant
              #                      1 = File link or URL
              #       1  0x00000002  0 = Relative file path
              #                      1 = Absolute path or URL
              # 2 and 4  0x00000014  0 = No description
              #                      1 (both bits) = Description
              #       3  0x00000008  0 = No text mark
              #                      1 = Text mark
              #       7  0x00000080  0 = No target frame
              #                      1 = Target frame
              #       8  0x00000100  0 = File link or URL
              #                      1 = UNC path (incl. server name)

  ].pack('V2')
  tail = []
  ## call internal to get the correct internal encoding in Ruby 1.9
  nullstr = internal "\000"
  unless link == link.url
    desc = internal(link).dup << nullstr
    tail.push [desc.size / 2].pack('V'), desc
  end
  if link.target_frame
    frme = internal(link.target_frame).dup << nullstr
    tail.push [frme.size / 2].pack('V'), frme
  end
  url = internal(link.url).dup << nullstr
  tail.push [
    # 6.53.2 Hyperlink containing a URL (Uniform Resource Locator)
    # These data fields occur for links which are not local files or files
    # in the local network (for instance HTTP and FTP links and e-mail
    # addresses). The lower 9 bits of the option flags field must be
    # 0.x00x.xx112 (x means optional, depending on hyperlink content). The
    # GUID could be used to distinguish a URL from a file link.
    # GUID of URL Moniker:
    # E0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B
    # (79EAC9E0-BAF9-11CE-8C82-00AA004BA90B)
    'e0c9ea79f9bace118c8200aa004ba90b',
    url.size  # Size of character array of the URL, including trailing zero
              # word (us). There are us/2-1 characters in the following
              # string.
  ].pack('H32V'), url
  if link.fragment
    frag = internal(link.fragment).dup << nullstr
    tail.push [frag.size / 2].pack('V'), frag
  end
  write_op opcode(:hlink), cell_range, guid, options, *tail
end

#write_hyperlink_table ⇒ Object 



527
528
529
530
531
532
533
534
535
536
537
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 527

def write_hyperlink_table
  # TODO: theoretically it's possible to write fewer records by combining
  #       identical neighboring links in cell-ranges
  @worksheet.each do |row|
    row.each_with_index do |cell, idx|
      if cell.is_a? Link
        write_hlink row.idx, idx, cell
      end
    end
  end
end

#write_iteration ⇒ Object 



538
539
540
541
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 538

def write_iteration
  its = 0 # 0 = Iterations off; 1 = Iterations on
  write_op 0x0011, [its].pack('v')
end

#write_labelsst(row, idx) ⇒ Object

Write a cell with a String value. The String must have been stored in the Shared String Table.



545
546
547
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 545

def write_labelsst row, idx
  write_cell :labelsst, row, idx, @workbook.sst_index(self, row[idx])
end

#write_margins ⇒ Object 



824
825
826
827
828
829
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 824

def write_margins
  @worksheet.margins.each do |key, value|
    next unless [:left, :top, :right, :bottom].include?(key)
    write_op opcode(:"#{key}margin"), [value].pack(binfmt(:margin))
  end
end

#write_merged_cells ⇒ Object 



797
798
799
800
801
802
803
804
805
806
807
808
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 797

def write_merged_cells
  return unless @worksheet.merged_cells.any?
  # FIXME standards say the record is limited by 1027 records at once
  # And no CONTINUE is supported

  merge_cells = @worksheet.merged_cells.dup
  while (window = merge_cells.slice!(0...1027)).any?
    count = window.size
    data = ([count] + window.flatten).pack('v2v*')
    write_op opcode(:mergedcells), data
  end
end

#write_mulblank(row, idx, multiples) ⇒ Object

Write multiple consecutive blank cells.



550
551
552
553
554
555
556
557
558
559
560
561
562
563
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 550

def write_mulblank row, idx, multiples
  data = [
    row.idx, # Index to row
    idx, # Index to first column (fc)
  ]
  # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115)
  multiples.each_with_index do |blank, cell_idx|
    xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx)
    data.push xf_idx
  end
  # Index to last column (lc)
  data.push idx + multiples.size - 1
  write_op opcode(:mulblank), data.pack('v*')
end

#write_mulrk(row, idx, multiples) ⇒ Object

Write multiple consecutive cells with RK values (see #write_rk)



566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 566

def write_mulrk row, idx, multiples
  fmt = ['v2']
  data = [
    row.idx, # Index to row
    idx, # Index to first column (fc)
  ]
  # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115)
  multiples.each_with_index do |cell, cell_idx|
    xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx)
    data.push xf_idx, encode_rk(cell)
    fmt << 'vV'
  end
  # Index to last column (lc)
  data.push idx + multiples.size - 1
  write_op opcode(:mulrk), data.pack((fmt << 'v').join)
end

#write_multiples(row, idx, multiples) ⇒ Object 



582
583
584
585
586
587
588
589
590
591
592
593
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 582

def write_multiples row, idx, multiples
  case multiples.last
  when NilClass
    write_mulblank row, idx, multiples
  when Numeric
    if multiples.size > 1
      write_mulrk row, idx, multiples
    else
      write_rk row, idx
    end
  end
end

#write_number(row, idx) ⇒ Object

Write a cell with a 64-bit double precision Float value



596
597
598
599
600
601
602
603
604
605
606
607
608
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 596

def write_number row, idx
  # Offset Size Contents
  # 0 2 Index to row
  # 2 2 Index to column
  # 4 2 Index to XF record (➜ 6.115)
  # 6 8 IEEE 754 floating-point value (64-bit double precision)
  value = row[idx]
  case value
  when Date, Time
    value = encode_date(value)
  end
  write_cell :number, row, idx, value
end

#write_op(op, *args) ⇒ Object 



609
610
611
612
613
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 609

def write_op op, *args
  data = args.join
  @io.write [op,data.size].pack("v2")
  @io.write data
end

#write_pagesetup ⇒ Object 



810
811
812
813
814
815
816
817
818
819
820
821
822
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 810

def write_pagesetup
  return unless @worksheet.pagesetup
  data = @worksheet.pagesetup[:orig_data].dup
  if @worksheet.pagesetup[:orientation]
    data[5] = @worksheet.pagesetup[:orientation] == :landscape ? 0 : 2
  end

  if @worksheet.pagesetup[:adjust_to]
    data[1] = @worksheet.pagesetup[:adjust_to]
  end

  write_op opcode(:pagesetup), data.pack(binfmt(:pagesetup))
end

#write_proctection ⇒ Object 



831
832
833
834
835
836
837
838
839
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 831

def write_proctection
  return unless @worksheet.protected?
  # ○ PROTECT Worksheet contents: 1 = protected (➜ 5.82)
  write_op opcode(:protect), [1].pack('v')
  # ○ OBJECTPROTECT Embedded objects: 1 = protected (➜ 5.72)
  # ○ SCENPROTECT Scenarios: 1 = protected (➜ 5.91)
  # ○ PASSWORD Hash value of the password; 0 = no password (➜ 5.76)
  write_op opcode(:password), [@worksheet.password_hash].pack('v')
end

#write_refmode ⇒ Object 



614
615
616
617
618
619
620
621
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 614

def write_refmode
  # • The “RC” mode uses numeric indexes for rows and columns, for example
  #   “R(1)C(-1)”, or “R1C1:R2C2”.
  # • The “A1” mode uses characters for columns and numbers for rows, for
  #   example “B1”, or “$A$1:$B$2”.
  mode = 1 # 0 = RC mode; 1 = A1 mode
  write_op 0x000f, [mode].pack('v')
end

#write_rk(row, idx) ⇒ Object

Write a cell with a Numeric or Date value.



624
625
626
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 624

def write_rk row, idx
  write_cell :rk, row, idx, encode_rk(row[idx])
end

#write_row(row) ⇒ Object 



627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
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
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 627

def write_row row
  # Offset  Size  Contents
  #      0     2  Index of this row
  #      2     2  Index to column of the first cell which
  #               is described by a cell record
  #      4     2  Index to column of the last cell which is
  #               described by a cell record, increased by 1
  #      6     2  Bit   Mask    Contents
  #               14-0  0x7fff  Height of the row, in twips = 1/20 of a point
  #                 15  0x8000  0 = Row has custom height;
  #                             1 = Row has default height
  #      8     2  Not used
  #     10     2  In BIFF3-BIFF4 this field contains a relative offset to
  #               calculate stream position of the first cell record for this
  #               row (➜ 5.7.1). In BIFF5-BIFF8 this field is not used
  #               anymore, but the DBCELL record (➜ 6.26) instead.
  #     12     4  Option flags and default row formatting:
  #                  Bit  Mask        Contents
  #                  2-0  0x00000007  Outline level of the row
  #                    4  0x00000010  1 = Outline group starts or ends here
  #                                       (depending on where the outline
  #                                       buttons are located, see WSBOOL
  #                                       record, ➜ 6.113), and is collapsed
  #                    5  0x00000020  1 = Row is hidden (manually, or by a
  #                                       filter or outline group)
  #                    6  0x00000040  1 = Row height and default font height
  #                                       do not match
  #                    7  0x00000080  1 = Row has explicit default format (fl)
  #                    8  0x00000100  Always 1
  #                27-16  0x0fff0000  If fl = 1: Index to default XF record
  #                                              (➜ 6.115)
  #                   28  0x10000000  1 = Additional space above the row.
  #                                       This flag is set, if the upper
  #                                       border of at least one cell in this
  #                                       row or if the lower border of at
  #                                       least one cell in the row above is
  #                                       formatted with a thick line style.
  #                                       Thin and medium line styles are not
  #                                       taken into account.
  #                   29  0x20000000  1 = Additional space below the row.
  #                                       This flag is set, if the lower
  #                                       border of at least one cell in this
  #                                       row or if the upper border of at
  #                                       least one cell in the row below is
  #                                       formatted with a medium or thick
  #                                       line style. Thin line styles are
  #                                       not taken into account.
  height = row.height || ROW_HEIGHT
  opts = row.outline_level & 0x00000007
  opts |= 0x00000010 if row.collapsed?
  opts |= 0x00000020 if row.hidden?
  opts |= 0x00000040 if height != ROW_HEIGHT
  if fmt = row.default_format
    xf_idx = @workbook.xf_index @worksheet.workbook, fmt
    opts |= 0x00000080
    opts |= xf_idx << 16
  end
  opts |= 0x00000100
  height = if height == ROW_HEIGHT
             (height * TWIPS).to_i | 0x8000
           else
             height * TWIPS
           end

  attrs = [
    row.idx,
    row.first_used,
    row.first_unused,
    height,
    opts]

  return if attrs.any?(&:nil?)

  # TODO: Row spacing
  data = attrs.pack binfmt(:row)
  write_op opcode(:row), data
end

#write_rowblock(block) ⇒ Object 



704
705
706
707
708
709
710
711
712
713
714
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 704

def write_rowblock block
  # ●● ROW Properties of the used rows
  # ○○ Cell Block(s) Cell records for all used cells
  # ○  DBCELL Stream offsets to the cell records of each row
  block.each do |row|
    write_row row
  end
  block.each do |row|
    write_cellblocks row
  end
end

#write_rows ⇒ Object 



715
716
717
718
719
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 715

def write_rows
  row_blocks.each do |block|
    write_rowblock block
  end
end

#write_saverecalc ⇒ Object 



720
721
722
723
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 720

def write_saverecalc
  # 0 = Do not recalculate; 1 = Recalculate before saving the document
  write_op 0x005f, [1].pack('v')
end

#write_window2 ⇒ Object 



724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 724

def write_window2
  # This record contains additional settings for the document window
  # (BIFF2-BIFF4) or for the window of a specific worksheet (BIFF5-BIFF8).
  # It is part of the Sheet View Settings Block (➜ 4.5).
  # Offset  Size  Contents
  #      0     2  Option flags:
  #               Bits  Mask    Contents
  #                  0  0x0001  0 = Show formula results
  #                             1 = Show formulas
  #                  1  0x0002  0 = Do not show grid lines
  #                             1 = Show grid lines
  #                  2  0x0004  0 = Do not show sheet headers
  #                             1 = Show sheet headers
  #                  3  0x0008  0 = Panes are not frozen
  #                             1 = Panes are frozen (freeze)
  #                  4  0x0010  0 = Show zero values as empty cells
  #                             1 = Show zero values
  #                  5  0x0020  0 = Manual grid line colour
  #                             1 = Automatic grid line colour
  #                  6  0x0040  0 = Columns from left to right
  #                             1 = Columns from right to left
  #                  7  0x0080  0 = Do not show outline symbols
  #                             1 = Show outline symbols
  #                  8  0x0100  0 = Keep splits if pane freeze is removed
  #                             1 = Remove splits if pane freeze is removed
  #                  9  0x0200  0 = Sheet not selected
  #                             1 = Sheet selected (BIFF5-BIFF8)
  #                 10  0x0400  0 = Sheet not active
  #                             1 = Sheet active (BIFF5-BIFF8)
  #                 11  0x0800  0 = Show in normal view
  #                             1 = Show in page break preview (BIFF8)
  #      2     2  Index to first visible row
  #      4     2  Index to first visible column
  #      6     2  Colour index of grid line colour (➜ 5.74).
  #               Note that in BIFF2-BIFF5 an RGB colour is written instead.
  #      8     2  Not used
  #     10     2  Cached magnification factor in page break preview (in percent)
  #               0 = Default (60%)
  #     12     2  Cached magnification factor in normal view (in percent)
  #               0 = Default (100%)
  #     14     4  Not used
  flags = 0x0536  # Show grid lines, sheet headers, zero values. Automatic
                  # grid line colour, Remove slits if pane freeze is removed,
                  # Sheet is active.
  if @worksheet.selected
    flags |= 0x0200
  end
  flags |= 0x0080 # Show outline symbols,
                  # but if [Row|Column]#outline_level = 0 the symbols are not shown.
  
  if @worksheet.has_frozen_panel?
    # See:
    # OpenOffice.org's Documentation of the Microsoft Excel File FormatExcel 
    # Versions 2, 3, 4, 5, 95, 97, 2000, XP, 2003
    # https://www.openoffice.org/sc/excelfileformat.pdf
    # section 5.110.2
    flags |= 0x0008 #Panes are frozen (freeze)
  end
  data = [ flags, 0, 0, 0, 0, 0 ].pack binfmt(:window2)
      
  write_op opcode(:window2), data
  if @worksheet.has_frozen_panel?
    # See:
    # OpenOffice.org's Documentation of the Microsoft Excel File FormatExcel 
    # Versions 2, 3, 4, 5, 95, 97, 2000, XP, 2003
    # https://www.openoffice.org/sc/excelfileformat.pdf
    # section 5.75
    pane_data = [@worksheet.froze_left, @worksheet.froze_top, @worksheet.froze_top, @worksheet.froze_left, 3].pack binfmt(:colinfo)
    write_op opcode(:pane), pane_data
  end
  
end

#write_wsbool ⇒ Object 



841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
 
# File 'lib/spreadsheet/excel/writer/worksheet.rb', line 841

def write_wsbool
  bits = [
       #   Bit  Mask    Contents
    1, #     0  0x0001  0 = Do not show automatic page breaks
       #                1 = Show automatic page breaks
    0, #     4  0x0010  0 = Standard sheet
       #                1 = Dialogue sheet (BIFF5-BIFF8)
    0, #     5  0x0020  0 = No automatic styles in outlines
       #                1 = Apply automatic styles to outlines
    1, #     6  0x0040  0 = Outline buttons above outline group
       #                1 = Outline buttons below outline group
    1, #     7  0x0080  0 = Outline buttons left of outline group
       #                1 = Outline buttons right of outline group
    0, #     8  0x0100  0 = Scale printout in percent (➜ 6.89)
       #                1 = Fit printout to number of pages (➜ 6.89)
    0, #     9  0x0200  0 = Save external linked values
       #                    (BIFF3-BIFF4 only, ➜ 5.10)
       #                1 = Do not save external linked values
       #                    (BIFF3-BIFF4 only, ➜ 5.10)
    1, #    10  0x0400  0 = Do not show row outline symbols
       #                1 = Show row outline symbols
    0, #    11  0x0800  0 = Do not show column outline symbols
       #                1 = Show column outline symbols
    0, # 13-12  0x3000  These flags specify the arrangement of windows.
       #                They are stored in BIFF4 only.
       #                00 = Arrange windows tiled
       #                01 = Arrange windows horizontal
    0, #                10 = Arrange windows vertical
       #                11 = Arrange windows cascaded
       # The following flags are valid for BIFF4-BIFF8 only:
    0, #    14  0x4000  0 = Standard expression evaluation
       #                1 = Alternative expression evaluation
    0, #    15  0x8000  0 = Standard formula entries
       #                1 = Alternative formula entries
  ]
  weights = [4,5,6,7,8,9,10,11,12,13,14,15]
  value = bits.inject do |a, b| a | (b << weights.shift) end
  write_op 0x0081, [value].pack('v')
end