Class Spreadsheet::Excel::Writer::Worksheet
In: lib/spreadsheet/excel/writer/worksheet.rb
Parent: Object
Column Format Row Font Enumerable Worksheet\n[lib/spreadsheet/excel.rb\nlib/spreadsheet/worksheet.rb] Link Workbook Workbook\n[lib/spreadsheet/excel.rb\nlib/spreadsheet/excel/workbook.rb] SstEntry String Row Array Worksheet ExcelCompatibleWorkbook Format Worksheet Workbook DelegateClassSpreadsheet::Format Writer Compatibility Formula Reader\n[lib/parseexcel/parseexcel.rb\nlib/spreadsheet/excel/reader.rb\nlib/spreadsheet/excel/reader/biff5.rb\nlib/spreadsheet/excel/reader/biff8.rb] Error lib/spreadsheet/excel.rb lib/spreadsheet/link.rb lib/spreadsheet/formula.rb lib/spreadsheet/workbook.rb lib/spreadsheet/font.rb lib/spreadsheet/column.rb lib/spreadsheet/format.rb lib/spreadsheet/writer.rb lib/spreadsheet/row.rb Encodings lib/spreadsheet/excel.rb lib/spreadsheet/excel/row.rb lib/spreadsheet/excel/reader/biff5.rb lib/spreadsheet/excel/worksheet.rb lib/spreadsheet/excel/sst_entry.rb lib/spreadsheet/excel/error.rb lib/spreadsheet/excel/workbook.rb lib/spreadsheet/excel/writer/format.rb lib/spreadsheet/excel/writer/workbook.rb lib/spreadsheet/excel/writer/worksheet.rb Biff8 Writer Biff5 Biff8 Internals Offset Excel Datatypes ParseExcel Compatibility Spreadsheet dot/m_34_0.png

Writer class for Excel Worksheets. Most write_* method correspond to an Excel-Record/Opcode. You should not need to call any of its methods directly. If you think you do, look at write_worksheet

Methods

Included Modules

Spreadsheet::Excel::Writer::Biff8 Spreadsheet::Excel::Internals Spreadsheet::Excel::Internals::Biff8

Attributes

worksheet  [R] 

Public Class methods

[Source]

    # File lib/spreadsheet/excel/writer/worksheet.rb, line 18
18:   def initialize workbook, worksheet
19:     @workbook = workbook
20:     @worksheet = worksheet
21:     @io = StringIO.new ''
22:     @biff_version = 0x0600
23:     @bof = 0x0809
24:     @build_id = 3515
25:     @build_year = 1996
26:     @bof_types = {
27:       :globals      => 0x0005,
28:       :visual_basic => 0x0006,
29:       :worksheet    => 0x0010,
30:       :chart        => 0x0020,
31:       :macro_sheet  => 0x0040,
32:       :workspace    => 0x0100,
33:     }
34:   end

Public Instance methods

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

[Source]

    # File lib/spreadsheet/excel/writer/worksheet.rb, line 38
38:   def boundsheet_size
39:     name.size + 10
40:   end

[Source]

    # File lib/spreadsheet/excel/writer/worksheet.rb, line 41
41:   def data
42:     @io.rewind
43:     @io.read
44:   end

[Source]

    # File lib/spreadsheet/excel/writer/worksheet.rb, line 45
45:   def encode_date date
46:     return date if date.is_a? Numeric
47:     if date.is_a? Time
48:       date = DateTime.new date.year, date.month, date.day,
49:                           date.hour, date.min, date.sec
50:     end
51:     base = @workbook.date_base
52:     value = date - base
53:     if LEAP_ERROR > base
54:       value += 1
55:     end
56:     value
57:   end

[Source]

    # File lib/spreadsheet/excel/writer/worksheet.rb, line 58
58:   def encode_rk value
59:     #  Bit  Mask        Contents
60:     #    0  0x00000001  0 = Value not changed 1 = Value is multiplied by 100
61:     #    1  0x00000002  0 = Floating-point value 1 = Signed integer value
62:     # 31-2  0xFFFFFFFC  Encoded value
63:     cent = 0
64:     int = 2
65:     higher = value * 100
66:     if higher.is_a?(Float) && higher < 0xfffffffc
67:       cent = 1
68:       if higher == higher.to_i
69:         value = higher.to_i
70:       else
71:         value = higher
72:       end
73:     end
74:     if value.is_a?(Integer)
75:       ## although not documented as signed, 'V' appears to correctly pack
76:       #  negative numbers.
77:       value <<= 2
78:     else
79:       # FIXME: precision of small numbers
80:       int = 0
81:       value, = [value].pack(EIGHT_BYTE_DOUBLE).unpack('x4V')
82:       value &= 0xfffffffc
83:     end
84:     value | cent | int
85:   end

[Source]

    # File lib/spreadsheet/excel/writer/worksheet.rb, line 86
86:   def name
87:     unicode_string @worksheet.name
88:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 89
 89:   def need_number? cell
 90:     if cell.is_a?(Numeric) && cell.abs > 0x1fffffff
 91:       true
 92:     elsif cell.is_a?(Float)
 93:       higher = cell * 100
 94:       if higher == higher.to_i
 95:         need_number? higher.to_i
 96:       else
 97:         test1, test2 = [cell * 100].pack(EIGHT_BYTE_DOUBLE).unpack('V2')
 98:         test1 > 0 || need_number?(test2)
 99:       end
100:     else
101:       false
102:     end
103:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 104
104:   def row_blocks
105:     # All cells in an Excel document are divided into blocks of 32 consecutive
106:     # rows, called Row Blocks. The first Row Block starts with the first used
107:     # row in that sheet. Inside each Row Block there will occur ROW records
108:     # describing the properties of the rows, and cell records with all the cell
109:     # contents in this Row Block.
110:     blocks = []
111:     @worksheet.reject do |row| row.empty? end.each_with_index do |row, idx|
112:       blocks << [] if idx % 32 == 0
113:       blocks.last << row
114:     end
115:     blocks
116:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 117
117:   def size
118:     @io.size
119:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 120
120:   def strings
121:     @worksheet.inject [] do |memo, row|
122:       strings = row.select do |cell| cell.is_a?(String) && !cell.empty? end
123:       memo.concat strings
124:     end
125:   end

Write a blank cell

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 128
128:   def write_blank row, idx
129:     write_cell :blank, row, idx
130:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 131
131:   def write_bof
132:     data = [
133:       @biff_version, # BIFF version (always 0x0600 for BIFF8)
134:       0x0010,        # Type of the following data:
135:                      # 0x0005 = Workbook globals
136:                      # 0x0006 = Visual Basic module
137:                      # 0x0010 = Worksheet
138:                      # 0x0020 = Chart
139:                      # 0x0040 = Macro sheet
140:                      # 0x0100 = Workspace file
141:       @build_id,     # Build identifier
142:       @build_year,   # Build year
143:       0x000,         # File history flags
144:       0x006,         # Lowest Excel version that can read
145:                      # all records in this file
146:     ]
147:     write_op @bof, data.pack("v4V2")
148:   end

Write a cell with a Boolean or Error value

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 151
151:   def write_boolerr row, idx
152:     value = row[idx]
153:     type = 0
154:     numval = 0
155:     if value.is_a? Error
156:       type = 1
157:       numval = value.code
158:     elsif value
159:       numval = 1
160:     end
161:     data = [
162:       numval, # Boolean or error value (type depends on the following byte)
163:       type    # 0 = Boolean value; 1 = Error code
164:     ]
165:     write_cell :boolerr, row, idx, *data
166:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 167
167:   def write_calccount
168:     count = 100 # Maximum number of iterations allowed in circular references
169:     write_op 0x000c, [count].pack('v')
170:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 171
171:   def write_cell type, row, idx, *args
172:     xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx)
173:     data = [
174:       row.idx, # Index to row
175:       idx,     # Index to column
176:       xf_idx,  # Index to XF record (➜ 6.115)
177:     ].concat args
178:     write_op opcode(type), data.pack(binfmt(type))
179:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 180
180:   def write_cellblocks row
181:     # BLANK ➜ 6.7
182:     # BOOLERR ➜ 6.10
183:     # INTEGER ➜ 6.56 (BIFF2 only)
184:     # LABEL ➜ 6.59 (BIFF2-BIFF7)
185:     # LABELSST ➜ 6.61 (BIFF8 only)
186:     # MULBLANK ➜ 6.64 (BIFF5-BIFF8)
187:     # MULRK ➜ 6.65 (BIFF5-BIFF8)
188:     # NUMBER ➜ 6.68
189:     # RK ➜ 6.82 (BIFF3-BIFF8)
190:     # RSTRING ➜ 6.84 (BIFF5/BIFF7)
191:     multiples, first_idx = nil
192:     row = row.formatted
193:     row.each_with_index do |cell, idx|
194:       cell = nil if cell == ''
195:       ## it appears that there are limitations to RK precision, both for
196:       #  Integers and Floats, that lie well below 2^30 significant bits, or
197:       #  Ruby's Bignum threshold. In that case we'll just write a Number
198:       #  record
199:       need_number = need_number? cell
200:       if multiples && (!multiples.last.is_a?(cell.class) || need_number)
201:         write_multiples row, first_idx, multiples
202:         multiples, first_idx = nil
203:       end
204:       nxt = idx + 1
205:       case cell
206:       when NilClass
207:         if multiples
208:           multiples.push cell
209:         elsif nxt < row.size && row[nxt].nil?
210:           multiples = [cell]
211:           first_idx = idx
212:         else
213:           write_blank row, idx
214:         end
215:       when TrueClass, FalseClass, Error
216:         write_boolerr row, idx
217:       when String
218:         write_labelsst row, idx
219:       when Numeric
220:         ## RK encodes Floats with 30 significant bits, which is a bit more than
221:         #  10^9. Not sure what is a good rule of thumb here, but it seems that
222:         #  Decimal Numbers with more than 4 significant digits are not represented
223:         #  with sufficient precision by RK
224:         if need_number
225:           write_number row, idx
226:         elsif multiples
227:           multiples.push cell
228:         elsif nxt < row.size && row[nxt].is_a?(Numeric)
229:           multiples = [cell]
230:           first_idx = idx
231:         else
232:           write_rk row, idx
233:         end
234:       when Formula
235:         write_formula row, idx
236:       when Date, Time
237:         write_number row, idx
238:       end
239:     end
240:     write_multiples row, first_idx, multiples if multiples
241:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 242
242:   def write_changes reader, endpos, sst_status
243:     reader.seek @worksheet.offset
244:     blocks = row_blocks
245:     lastpos = reader.pos
246:     offsets = {}
247:     row_offsets = []
248:     changes = @worksheet.changes
249:     @worksheet.offsets.each do |key, pair|
250:       if changes.include?(key) \
251:         || (sst_status == :complete_update && key.is_a?(Integer))
252:         offsets.store pair, key
253:       end
254:     end
255:     ## FIXME it may be smarter to simply write all rowblocks, instead of doing a
256:     #        song-and-dance routine for every row...
257:     work = offsets.invert
258:     work.each do |key, (pos, len)|
259:       case key
260:       when Integer
261:         row_offsets.push [key, [pos, len]]
262:       when :dimensions
263:         row_offsets.push [-1, [pos, len]]
264:       end
265:     end
266:     row_offsets.sort!
267:     row_offsets.reverse!
268:     control = changes.size
269:     @worksheet.each do |row|
270:       key = row.idx
271:       if changes.include?(key) && !work.include?(key)
272:         row, pair = row_offsets.find do |idx, _| idx <= key end
273:         work.store key, pair
274:       end
275:     end
276:     if changes.size > control
277:       warn "Your Worksheet was modified while it was being written. This should not happen.\nPlease contact the author (hannes dot wyss at gmail dot com) with a sample file\nand minimal code that generates this warning. Thanks!\n"
278:     end
279:     work = work.sort_by do |key, (pos, len)|
280:       [pos, key.is_a?(Integer) ? key : -1]
281:     end
282:     work.each do |key, (pos, len)|
283:       @io.write reader.read(pos - lastpos) if pos > lastpos
284:       if key.is_a?(Integer)
285:         if block = blocks.find do |rows| rows.any? do |row| row.idx == key end end
286:           write_rowblock block
287:           blocks.delete block
288:         end
289:       else
290:         send "write_#{key}"
291:       end
292:       lastpos = pos + len
293:       reader.seek lastpos
294:     end
295:     @io.write reader.read(endpos - lastpos)
296:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 302
302:   def write_colinfo bunch
303:     col = bunch.first
304:     width = col.width.to_f * 256
305:     xf_idx = @workbook.xf_index @worksheet.workbook, col.default_format
306:     opts =  0
307:     opts |= 0x0001 if col.hidden?
308:     opts |= col.outline_level.to_i << 8
309:     opts |= 0x1000 if col.collapsed?
310:     data = [
311:       col.idx,        # Index to first column in the range
312:       bunch.last.idx, # Index to last column in the range
313:       width.to_i,     # Width of the columns in 1/256 of the width of the zero
314:                       # character, using default font (first FONT record in the
315:                       # file)
316:       xf_idx.to_i,    # Index to XF record (➜ 6.115) for default column formatting
317:       opts,           # Option flags:
318:                       # Bits  Mask    Contents
319:                       #    0  0x0001  1 = Columns are hidden
320:                       # 10-8  0x0700  Outline level of the columns
321:                       #               (0 = no outline)
322:                       #   12  0x1000  1 = Columns are collapsed
323:     ]
324:     write_op opcode(:colinfo), data.pack(binfmt(:colinfo))
325:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 326
326:   def write_colinfos
327:     cols = @worksheet.columns
328:     bunch = []
329:     cols.each_with_index do |column, idx|
330:       if column
331:         bunch << column
332:         if cols[idx.next] != column
333:           write_colinfo bunch
334:           bunch.clear
335:         end
336:       end
337:     end
338:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 339
339:   def write_defaultrowheight
340:     data = [
341:       0x00, # Option flags:
342:             # Bit  Mask  Contents
343:             #   0  0x01  1 = Row height and default font height do not match
344:             #   1  0x02  1 = Row is hidden
345:             #   2  0x04  1 = Additional space above the row
346:             #   3  0x08  1 = Additional space below the row
347:       0xf2, #   Default height for unused rows, in twips = 1/20 of a point
348:     ]
349:     write_op 0x0225, data.pack('v2')
350:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 351
351:   def write_defcolwidth
352:     # Offset  Size  Contents
353:     #      0     2  Column width in characters, using the width of the zero
354:     #               character from default font (first FONT record in the
355:     #               file). Excel adds some extra space to the default width,
356:     #               depending on the default font and default font size. The
357:     #               algorithm how to exactly calculate the resulting column
358:     #               width is not known.
359:     #
360:     #               Example: The default width of 8 set in this record results
361:     #               in a column width of 8.43 using Arial font with a size of
362:     #               10 points.
363:     write_op 0x0055, [8].pack('v')
364:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 365
365:   def write_dimensions
366:     # Offset  Size  Contents
367:     #      0     4  Index to first used row
368:     #      4     4  Index to last used row, increased by 1
369:     #      8     2  Index to first used column
370:     #     10     2  Index to last used column, increased by 1
371:     #     12     2  Not used
372:     write_op 0x0200, @worksheet.dimensions.pack(binfmt(:dimensions))
373:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 374
374:   def write_eof
375:     write_op 0x000a
376:   end

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

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 380
380:   def write_formula row, idx
381:     xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx)
382:     cell = row[idx]
383:     data1 = [
384:       row.idx,      # Index to row
385:       idx,          # Index to column
386:       xf_idx,       # Index to XF record (➜ 6.115)
387:     ].pack 'v3'
388:     data2 = nil
389:     case value = cell.value
390:     when Numeric    # IEEE 754 floating-point value (64-bit double precision)
391:       data2 = [value].pack EIGHT_BYTE_DOUBLE
392:     when String
393:       data2 = [
394:         0x00,       # (identifier for a string value)
395:         0xffff,     #
396:       ].pack 'Cx5v'
397:     when true, false
398:       value = value ? 1 : 0
399:       data2 = [
400:         0x01,     # (identifier for a Boolean value)
401:         value,    # 0 = FALSE, 1 = TRUE
402:         0xffff,   #
403:       ].pack 'CxCx3v'
404:     when Error
405:       data2 = [
406:         0x02,       # (identifier for an error value)
407:         value.code, # Error code
408:         0xffff,     #
409:       ].pack 'CxCx3v'
410:     when nil
411:       data2 = [
412:         0x03,       # (identifier for an empty cell)
413:         0xffff,     #
414:       ].pack 'Cx5v'
415:     else
416:       data2 = [
417:         0x02,       # (identifier for an error value)
418:         0x2a,       # Error code: #N/A! Argument or function not available
419:         0xffff,     #
420:       ].pack 'CxCx3v'
421:     end
422:     opts = 0x03
423:     opts |= 0x08 if cell.shared
424:     data3 = [
425:       opts        # Option flags:
426:                   # Bit  Mask    Contents
427:                   #   0  0x0001  1 = Recalculate always
428:                   #   1  0x0002  1 = Calculate on open
429:                   #   3  0x0008  1 = Part of a shared formula
430:     ].pack 'vx4'
431:     write_op opcode(:formula), data1, data2, data3, cell.data
432:     if cell.value.is_a?(String)
433:       write_op opcode(:string), unicode_string(cell.value, 2)
434:     end
435:   end

Write a new Worksheet.

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 438
438:   def write_from_scratch
439:     # ●  BOF Type = worksheet (➜ 5.8)
440:     write_bof
441:     # ○  UNCALCED ➜ 5.105
442:     # ○  INDEX ➜ 4.7 (Row Blocks), ➜ 5.59
443:     # ○  Calculation Settings Block ➜ 4.3
444:     write_calccount
445:     write_refmode
446:     write_iteration
447:     write_saverecalc
448:     # ○  PRINTHEADERS ➜ 5.81
449:     # ○  PRINTGRIDLINES ➜ 5.80
450:     # ○  GRIDSET ➜ 5.52
451:     # ○  GUTS ➜ 5.53
452:     # ○  DEFAULTROWHEIGHT ➜ 5.31
453:     write_defaultrowheight
454:     # ○  WSBOOL ➜ 5.113
455:     write_wsbool
456:     # ○  Page Settings Block ➜ 4.4
457:     # ○  Worksheet Protection Block ➜ 4.18
458:     # ○  DEFCOLWIDTH ➜ 5.32
459:     write_defcolwidth
460:     # ○○ COLINFO ➜ 5.18
461:     write_colinfos
462:     # ○  SORT ➜ 5.99
463:     # ●  DIMENSIONS ➜ 5.35
464:     write_dimensions
465:     # ○○ Row Blocks ➜ 4.7
466:     write_rows
467:     # ●  Worksheet View Settings Block ➜ 4.5
468:     # ●  WINDOW2 ➜ 5.110
469:     write_window2
470:     # ○  SCL ➜ 5.92 (BIFF4-BIFF8 only)
471:     # ○  PANE ➜ 5.75
472:     # ○○ SELECTION ➜ 5.93
473:     # ○  STANDARDWIDTH ➜ 5.101
474:     # ○○ MERGEDCELLS ➜ 5.67
475:     # ○  LABELRANGES ➜ 5.64
476:     # ○  PHONETIC ➜ 5.77
477:     # ○  Conditional Formatting Table ➜ 4.12
478:     # ○  Hyperlink Table ➜ 4.13
479:     write_hyperlink_table
480:     # ○  Data Validity Table ➜ 4.14
481:     # ○  SHEETLAYOUT ➜ 5.96 (BIFF8X only)
482:     # ○  SHEETPROTECTION Additional protection, ➜ 5.98 (BIFF8X only)
483:     # ○  RANGEPROTECTION Additional protection, ➜ 5.84 (BIFF8X only)
484:     # ●  EOF ➜ 5.36
485:     write_eof
486:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 487
487:   def write_hlink row, col, link
488:     # FIXME: only Hyperlinks are supported at present.
489:     cell_range = [
490:       row, row, # Cell range address of all cells containing this hyperlink
491:       col, col, # (➜ 3.13.1)
492:     ].pack 'v4'
493:     guid = [
494:       # GUID of StdLink:
495:       # D0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B
496:       # (79EAC9D0-BAF9-11CE-8C82-00AA004BA90B)
497:       "d0c9ea79f9bace118c8200aa004ba90b",
498:     ].pack 'H32'
499:     opts  = 0x01
500:     opts |= 0x02
501:     opts |= 0x14 unless link == link.url
502:     opts |= 0x08 if link.fragment
503:     opts |= 0x80 if link.target_frame
504:     # TODO: UNC support
505:     options = [
506:       2,        # Unknown value: 0x00000002
507:       opts,     # Option flags
508:                 #     Bit  Mask        Contents
509:                 #       0  0x00000001  0 = No link extant
510:                 #                      1 = File link or URL
511:                 #       1  0x00000002  0 = Relative file path
512:                 #                      1 = Absolute path or URL
513:                 # 2 and 4  0x00000014  0 = No description
514:                 #                      1 (both bits) = Description
515:                 #       3  0x00000008  0 = No text mark
516:                 #                      1 = Text mark
517:                 #       7  0x00000080  0 = No target frame
518:                 #                      1 = Target frame
519:                 #       8  0x00000100  0 = File link or URL
520:                 #                      1 = UNC path (incl. server name)
521: 
522:     ].pack('V2')
523:     tail = []
524:     ## call internal to get the correct internal encoding in Ruby 1.9
525:     nullstr = internal "\000"
526:     unless link == link.url
527:       desc = internal(link).dup << nullstr
528:       tail.push [desc.size / 2].pack('V'), desc
529:     end
530:     if link.target_frame
531:       frme = internal(link.target_frame).dup << nullstr
532:       tail.push [frme.size / 2].pack('V'), frme
533:     end
534:     url = internal(link.url).dup << nullstr
535:     tail.push [
536:       # 6.53.2 Hyperlink containing a URL (Uniform Resource Locator)
537:       # These data fields occur for links which are not local files or files
538:       # in the local network (for instance HTTP and FTP links and e-mail
539:       # addresses). The lower 9 bits of the option flags field must be
540:       # 0.x00x.xx112 (x means optional, depending on hyperlink content). The
541:       # GUID could be used to distinguish a URL from a file link.
542:       # GUID of URL Moniker:
543:       # E0 C9 EA 79 F9 BA CE 11 8C 82 00 AA 00 4B A9 0B
544:       # (79EAC9E0-BAF9-11CE-8C82-00AA004BA90B)
545:       'e0c9ea79f9bace118c8200aa004ba90b',
546:       url.size  # Size of character array of the URL, including trailing zero
547:                 # word (us). There are us/2-1 characters in the following
548:                 # string.
549:     ].pack('H32V'), url
550:     if link.fragment
551:       frag = internal(link.fragment).dup << nullstr
552:       tail.push [frag.size / 2].pack('V'), frag
553:     end
554:     write_op opcode(:hlink), cell_range, guid, options, *tail
555:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 556
556:   def write_hyperlink_table
557:     # TODO: theoretically it's possible to write fewer records by combining
558:     #       identical neighboring links in cell-ranges
559:     links = []
560:     @worksheet.each do |row|
561:       row.each_with_index do |cell, idx|
562:         if cell.is_a? Link
563:           write_hlink row.idx, idx, cell
564:         end
565:       end
566:     end
567:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 568
568:   def write_iteration
569:     its = 0 # 0 = Iterations off; 1 = Iterations on
570:     write_op 0x0011, [its].pack('v')
571:   end

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

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 575
575:   def write_labelsst row, idx
576:     write_cell :labelsst, row, idx, @workbook.sst_index(self, row[idx])
577:   end

Write multiple consecutive blank cells.

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 580
580:   def write_mulblank row, idx, multiples
581:     data = [
582:       row.idx, # Index to row
583:       idx, # Index to first column (fc)
584:     ]
585:     # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115)
586:     multiples.each_with_index do |blank, cell_idx|
587:       xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx)
588:       data.push xf_idx
589:     end
590:     # Index to last column (lc)
591:     data.push idx + multiples.size - 1
592:     write_op opcode(:mulblank), data.pack('v*')
593:   end

Write multiple consecutive cells with RK values (see write_rk)

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 596
596:   def write_mulrk row, idx, multiples
597:     fmt = 'v2'
598:     data = [
599:       row.idx, # Index to row
600:       idx, # Index to first column (fc)
601:     ]
602:     # List of nc=lc-fc+1 16-bit indexes to XF records (➜ 6.115)
603:     multiples.each_with_index do |cell, cell_idx|
604:       xf_idx = @workbook.xf_index @worksheet.workbook, row.format(idx + cell_idx)
605:       data.push xf_idx, encode_rk(cell)
606:       fmt << 'vV'
607:     end
608:     # Index to last column (lc)
609:     data.push idx + multiples.size - 1
610:     write_op opcode(:mulrk), data.pack(fmt << 'v')
611:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 612
612:   def write_multiples row, idx, multiples
613:     case multiples.last
614:     when NilClass
615:       write_mulblank row, idx, multiples
616:     when Numeric
617:       if multiples.size > 1
618:         write_mulrk row, idx, multiples
619:       else
620:         write_rk row, idx
621:       end
622:     end
623:   end

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

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 626
626:   def write_number row, idx
627:     # Offset Size Contents
628:     # 0 2 Index to row
629:     # 2 2 Index to column
630:     # 4 2 Index to XF record (➜ 6.115)
631:     # 6 8 IEEE 754 floating-point value (64-bit double precision)
632:     value = row[idx]
633:     case value
634:     when Date, Time
635:       value = encode_date(value)
636:     end
637:     write_cell :number, row, idx, value
638:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 639
639:   def write_op op, *args
640:     data = args.join
641:     @io.write [op,data.size].pack("v2")
642:     @io.write data
643:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 644
644:   def write_refmode
645:     # • The “RC” mode uses numeric indexes for rows and columns, for example
646:     #   “R(1)C(-1)”, or “R1C1:R2C2”.
647:     # • The “A1” mode uses characters for columns and numbers for rows, for
648:     #   example “B1”, or “$A$1:$B$2”.
649:     mode = 1 # 0 = RC mode; 1 = A1 mode
650:     write_op 0x000f, [mode].pack('v')
651:   end

Write a cell with a Numeric or Date value.

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 654
654:   def write_rk row, idx
655:     write_cell :rk, row, idx, encode_rk(row[idx])
656:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 657
657:   def write_row row
658:     # Offset  Size  Contents
659:     #      0     2  Index of this row
660:     #      2     2  Index to column of the first cell which
661:     #               is described by a cell record
662:     #      4     2  Index to column of the last cell which is
663:     #               described by a cell record, increased by 1
664:     #      6     2  Bit   Mask    Contents
665:     #               14-0  0x7fff  Height of the row, in twips = 1/20 of a point
666:     #                 15  0x8000  0 = Row has custom height;
667:     #                             1 = Row has default height
668:     #      8     2  Not used
669:     #     10     2  In BIFF3-BIFF4 this field contains a relative offset to
670:     #               calculate stream position of the first cell record for this
671:     #               row (➜ 5.7.1). In BIFF5-BIFF8 this field is not used
672:     #               anymore, but the DBCELL record (➜ 6.26) instead.
673:     #     12     4  Option flags and default row formatting:
674:     #                  Bit  Mask        Contents
675:     #                  2-0  0x00000007  Outline level of the row
676:     #                    4  0x00000010  1 = Outline group starts or ends here
677:     #                                       (depending on where the outline
678:     #                                       buttons are located, see WSBOOL
679:     #                                       record, ➜ 6.113), and is collapsed
680:     #                    5  0x00000020  1 = Row is hidden (manually, or by a
681:     #                                       filter or outline group)
682:     #                    6  0x00000040  1 = Row height and default font height
683:     #                                       do not match
684:     #                    7  0x00000080  1 = Row has explicit default format (fl)
685:     #                    8  0x00000100  Always 1
686:     #                27-16  0x0fff0000  If fl = 1: Index to default XF record
687:     #                                              (➜ 6.115)
688:     #                   28  0x10000000  1 = Additional space above the row.
689:     #                                       This flag is set, if the upper
690:     #                                       border of at least one cell in this
691:     #                                       row or if the lower border of at
692:     #                                       least one cell in the row above is
693:     #                                       formatted with a thick line style.
694:     #                                       Thin and medium line styles are not
695:     #                                       taken into account.
696:     #                   29  0x20000000  1 = Additional space below the row.
697:     #                                       This flag is set, if the lower
698:     #                                       border of at least one cell in this
699:     #                                       row or if the upper border of at
700:     #                                       least one cell in the row below is
701:     #                                       formatted with a medium or thick
702:     #                                       line style. Thin line styles are
703:     #                                       not taken into account.
704:     height = row.height || ROW_HEIGHT
705:     opts = row.outline_level & 0x00000007
706:     opts |= 0x00000010 if row.collapsed?
707:     opts |= 0x00000020 if row.hidden?
708:     opts |= 0x00000040 if height != ROW_HEIGHT
709:     if fmt = row.default_format
710:       xf_idx = @workbook.xf_index @worksheet.workbook, fmt
711:       opts |= 0x00000080
712:       opts |= xf_idx << 16
713:     end
714:     opts |= 0x00000100
715:     height = if height == ROW_HEIGHT
716:                (height * TWIPS).to_i | 0x8000
717:              else
718:                height * TWIPS
719:              end
720:     # TODO: Row spacing
721:     data = [
722:       row.idx,
723:       row.first_used,
724:       row.first_unused,
725:       height,
726:       opts,
727:     ].pack binfmt(:row)
728:     write_op opcode(:row), data
729:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 730
730:   def write_rowblock block
731:     # ●● ROW Properties of the used rows
732:     # ○○ Cell Block(s) Cell records for all used cells
733:     # ○  DBCELL Stream offsets to the cell records of each row
734:     block.each do |row|
735:       write_row row
736:     end
737:     block.each do |row|
738:       write_cellblocks row
739:     end
740:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 741
741:   def write_rows
742:     row_blocks.each do |block|
743:       write_rowblock block
744:     end
745:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 746
746:   def write_saverecalc
747:     # 0 = Do not recalculate; 1 = Recalculate before saving the document
748:     write_op 0x005f, [1].pack('v')
749:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 750
750:   def write_window2
751:     # This record contains additional settings for the document window
752:     # (BIFF2-BIFF4) or for the window of a specific worksheet (BIFF5-BIFF8).
753:     # It is part of the Sheet View Settings Block (➜ 4.5).
754:     # Offset  Size  Contents
755:     #      0     2  Option flags:
756:     #               Bits  Mask    Contents
757:     #                  0  0x0001  0 = Show formula results
758:     #                             1 = Show formulas
759:     #                  1  0x0002  0 = Do not show grid lines
760:     #                             1 = Show grid lines
761:     #                  2  0x0004  0 = Do not show sheet headers
762:     #                             1 = Show sheet headers
763:     #                  3  0x0008  0 = Panes are not frozen
764:     #                             1 = Panes are frozen (freeze)
765:     #                  4  0x0010  0 = Show zero values as empty cells
766:     #                             1 = Show zero values
767:     #                  5  0x0020  0 = Manual grid line colour
768:     #                             1 = Automatic grid line colour
769:     #                  6  0x0040  0 = Columns from left to right
770:     #                             1 = Columns from right to left
771:     #                  7  0x0080  0 = Do not show outline symbols
772:     #                             1 = Show outline symbols
773:     #                  8  0x0100  0 = Keep splits if pane freeze is removed
774:     #                             1 = Remove splits if pane freeze is removed
775:     #                  9  0x0200  0 = Sheet not selected
776:     #                             1 = Sheet selected (BIFF5-BIFF8)
777:     #                 10  0x0400  0 = Sheet not active
778:     #                             1 = Sheet active (BIFF5-BIFF8)
779:     #                 11  0x0800  0 = Show in normal view
780:     #                             1 = Show in page break preview (BIFF8)
781:     #      2     2  Index to first visible row
782:     #      4     2  Index to first visible column
783:     #      6     2  Colour index of grid line colour (➜ 5.74).
784:     #               Note that in BIFF2-BIFF5 an RGB colour is written instead.
785:     #      8     2  Not used
786:     #     10     2  Cached magnification factor in page break preview (in percent)
787:     #               0 = Default (60%)
788:     #     12     2  Cached magnification factor in normal view (in percent)
789:     #               0 = Default (100%)
790:     #     14     4  Not used
791:     flags = 0x0536  # Show grid lines, sheet headers, zero values. Automatic
792:                     # grid line colour, Remove slits if pane freeze is removed,
793:                     # Sheet is active.
794:     if @worksheet.selected
795:       flags |= 0x0200
796:     end
797:     data = [ flags, 0, 0, 0, 0, 0 ].pack binfmt(:window2)
798:     write_op opcode(:window2), data
799:   end

[Source]

     # File lib/spreadsheet/excel/writer/worksheet.rb, line 800
800:   def write_wsbool
801:     bits = [
802:          #   Bit  Mask    Contents
803:       1, #     0  0x0001  0 = Do not show automatic page breaks
804:          #                1 = Show automatic page breaks
805:       0, #     4  0x0010  0 = Standard sheet
806:          #                1 = Dialogue sheet (BIFF5-BIFF8)
807:       0, #     5  0x0020  0 = No automatic styles in outlines
808:          #                1 = Apply automatic styles to outlines
809:       1, #     6  0x0040  0 = Outline buttons above outline group
810:          #                1 = Outline buttons below outline group
811:       1, #     7  0x0080  0 = Outline buttons left of outline group
812:          #                1 = Outline buttons right of outline group
813:       0, #     8  0x0100  0 = Scale printout in percent (➜ 6.89)
814:          #                1 = Fit printout to number of pages (➜ 6.89)
815:       0, #     9  0x0200  0 = Save external linked values
816:          #                    (BIFF3-BIFF4 only, ➜ 5.10)
817:          #                1 = Do not save external linked values
818:          #                    (BIFF3-BIFF4 only, ➜ 5.10)
819:       1, #    10  0x0400  0 = Do not show row outline symbols
820:          #                1 = Show row outline symbols
821:       0, #    11  0x0800  0 = Do not show column outline symbols
822:          #                1 = Show column outline symbols
823:       0, # 13-12  0x3000  These flags specify the arrangement of windows.
824:          #                They are stored in BIFF4 only.
825:          #                00 = Arrange windows tiled
826:          #                01 = Arrange windows horizontal
827:       0, #                10 = Arrange windows vertical
828:          #                11 = Arrange windows cascaded
829:          # The following flags are valid for BIFF4-BIFF8 only:
830:       0, #    14  0x4000  0 = Standard expression evaluation
831:          #                1 = Alternative expression evaluation
832:       0, #    15  0x8000  0 = Standard formula entries
833:          #                1 = Alternative formula entries
834:     ]
835:     weights = [4,5,6,7,8,9,10,11,12,13,14,15]
836:     value = bits.inject do |a, b| a | (b << weights.shift) end
837:     write_op 0x0081, [value].pack('v')
838:   end

[Validate]