Class: Spreadsheet::Excel::Writer::Workbook

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

Overview

its methods directly. If you think you do, look at #write_workbook

Constant Summary

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

#binfmt, #opcode

Methods included from Biff8

#_unicode_string, #compress_unicode_string, #unicode_string

Methods inherited from Writer

#write

Constructor Details

#initialize(*args) ⇒ Workbook

Returns a new instance of Workbook.



21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 21

def initialize *args
  super
  @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,
  }
  @worksheets = {}
  @sst = {}
  @recordsize_limit = 8224
  @fonts = {}
  @formats = {}
  @number_formats = {}
end

Instance Attribute Details

#date_base ⇒ Object (readonly)

Returns the value of attribute date_base.



20
21
22
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 20

def date_base
  @date_base
end

#fonts ⇒ Object (readonly)

Returns the value of attribute fonts.



20
21
22
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 20

def fonts
  @fonts
end

Instance Method Details

#_write_sst(workbook, writer, offset, total, strings) ⇒ Object 



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

def _write_sst workbook, writer, offset, total, strings
  sst = {}
  worksheets(workbook).each do |worksheet|
    offset += worksheet.boundsheet_size
    @sst[worksheet] = sst
  end
  sst_size = strings.size
  data = [total, sst_size].pack 'V2'
  op = 0x00fc
  wide = 0
  offsets = []
  strings.each_with_index do |string, idx|
    sst.store string, idx
    op_offset = data.size + 4
    if idx % SST_CHUNKSIZE == 0
      offsets.push [offset + writer.pos + op_offset, op_offset]
    end
    header, packed, next_wide = _unicode_string string, 2
    # the first few bytes (header + first character) must not be split
    must_fit = header.size + wide + 1
    while data.size + must_fit > @recordsize_limit
      op, data, wide = write_string_part writer, op, data, wide
    end
    wide = next_wide
    data << header << packed
  end
  until data.empty?
    op, data, wide = write_string_part writer, op, data, wide
  end
  write_extsst workbook, offsets, writer
end

#cleanup(workbook) ⇒ Object 



42
43
44
45
46
47
48
49
50
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 42

def cleanup workbook
  worksheets(workbook).each do |worksheet|
    @sst.delete worksheet
  end
  @fonts.delete workbook
  @formats.delete workbook
  @number_formats.delete workbook
  @worksheets.delete workbook
end

#collect_formats(workbook, opts = {}) ⇒ Object 



51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 51

def collect_formats workbook, opts={}
  # The default cell format is always present in an Excel file, described by
  # the XF record with the fixed index 15 (0-based). By default, it uses the
  # worksheet/workbook default cell style, described by the very first XF
  # record (index 0).
  formats = []
  unless opts[:existing_document]
    15.times do
      formats.push Format.new(self, workbook, workbook.default_format,
                              :type => :style)
    end
    formats.push Format.new(self, workbook)
  end
  workbook.formats.each do |fmt|
    formats.push Format.new(self, workbook, fmt)
  end
  @formats[workbook] = {
    :writers => [],
    :xf_indexes => {}
  }
  formats.each_with_index do |fmt, idx|
    @formats[workbook][:writers] << fmt
    @formats[workbook][:xf_indexes][fmt.format] ||= idx
  end
end

#complete_sst_update?(workbook) ⇒ Boolean

Returns:

  • (Boolean) 


76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 76

def complete_sst_update? workbook
  stored = workbook.sst.collect do |entry| entry.content end
  num_total = 0
  current = worksheets(workbook).inject(Hash.new(0)) do |memo, worksheet|
    worksheet.strings.each do |k,v|
      memo[k] += v
      num_total += v
    end
    memo
  end
  current.delete ''
  if !stored.empty? && stored.all?{|x| current.include?(x) }
    ## if all previously stored strings are still needed, we don't have to
    #  rewrite all cells because the sst-index of such string does not change.
    additions = current.keys - stored
    [:partial_update, num_total, stored + additions]
  else
    [:complete_update, num_total, current.keys]
  end
end

#default_palette ⇒ Object

Returns Excel 97+ default colour palette.



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
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 672

def default_palette
  [
    [0x00, 0x00, 0x00],
    [0xff, 0xff, 0xff],
    [0xff, 0x00, 0x00],
    [0x00, 0xff, 0x00],
    [0x00, 0x00, 0xff],
    [0xff, 0xff, 0x00],
    [0xff, 0x00, 0xff],
    [0x00, 0xff, 0xff],
    [0x80, 0x00, 0x00],
    [0x00, 0x80, 0x00],
    [0x00, 0x00, 0x80],
    [0x80, 0x80, 0x00],
    [0x80, 0x00, 0x80],
    [0x00, 0x80, 0x80],
    [0xc0, 0xc0, 0xc0],
    [0x80, 0x80, 0x80],
    [0x99, 0x99, 0xff],
    [0x99, 0x33, 0x66],
    [0xff, 0xff, 0xcc],
    [0xcc, 0xff, 0xff],
    [0x66, 0x00, 0x66],
    [0xff, 0x80, 0x80],
    [0x00, 0x66, 0xcc],
    [0xcc, 0xcc, 0xff],
    [0x00, 0x00, 0x80],
    [0xff, 0x00, 0xff],
    [0xff, 0xff, 0x00],
    [0x00, 0xff, 0xff],
    [0x80, 0x00, 0x80],
    [0x80, 0x00, 0x00],
    [0x00, 0x80, 0x80],
    [0x00, 0x00, 0xff],
    [0x00, 0xcc, 0xff],
    [0xcc, 0xff, 0xff],
    [0xcc, 0xff, 0xcc],
    [0xff, 0xff, 0x99],
    [0x99, 0xcc, 0xff],
    [0xff, 0x99, 0xcc],
    [0xcc, 0x99, 0xff],
    [0xff, 0xcc, 0x99],
    [0x33, 0x66, 0xff],
    [0x33, 0xcc, 0xcc],
    [0x99, 0xcc, 0x00],
    [0xff, 0xcc, 0x00],
    [0xff, 0x99, 0x00],
    [0xff, 0x66, 0x00],
    [0x66, 0x66, 0x99],
    [0x96, 0x96, 0x96],
    [0x00, 0x33, 0x66],
    [0x33, 0x99, 0x66],
    [0x00, 0x33, 0x00],
    [0x33, 0x33, 0x00],
    [0x99, 0x33, 0x00],
    [0x99, 0x33, 0x66],
    [0x33, 0x33, 0x99],
    [0x33, 0x33, 0x33]
  ]
end

#font_index(workbook, font_key) ⇒ Object 



96
97
98
99
100
101
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 96

def font_index workbook, font_key
  idx = @fonts[workbook][font_key] || 0
  ## this appears to be undocumented: the first 4 fonts seem to be accessed
  #  with a 0-based index, but all subsequent font indices are 1-based.
  idx > 3 ? idx.next : idx
end

#number_format_index(workbook, format) ⇒ Object 



102
103
104
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 102

def number_format_index workbook, format
  @number_formats[workbook][format] || 0
end

#sanitize_worksheets(sheets) ⇒ Object 



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

def sanitize_worksheets sheets
  return sheets if sheets.empty?
  found_selected = false
  sheets.each do |sheet|
    found_selected ||= sheet.selected
    sheet.format_dates!
  end
  unless found_selected
    sheets.first.selected = true
  end
  sheets
end

#sst_index(worksheet, str) ⇒ Object 



664
665
666
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 664

def sst_index worksheet, str
  @sst[worksheet][str]
end

#worksheets(workbook) ⇒ Object 



117
118
119
120
121
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 117

def worksheets workbook
  @worksheets[workbook] ||= workbook.worksheets.collect do |worksheet|
    Excel::Writer::Worksheet.new self, worksheet
  end
end

#write_bof(workbook, writer, type) ⇒ Object 



122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 122

def write_bof workbook, writer, type
  data = [
    @biff_version,    # BIFF version (always 0x0600 for BIFF8)
    @bof_types[type], # 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 writer, @bof, data.pack("v4V2")
end

#write_bookbool(workbook, writer) ⇒ Object 



140
141
142
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 140

def write_bookbool workbook, writer
  write_placeholder writer, 0x00da
end

#write_boundsheets(workbook, writer, offset) ⇒ Object 



143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 143

def write_boundsheets workbook, writer, offset
  worksheets = worksheets(workbook)
  worksheets.each do |worksheet|
    # account for boundsheet-entry
    offset += worksheet.boundsheet_size
  end
  worksheets.each do |worksheet|
    visibility = SEITILIBISIV_TEEHSKROW[worksheet.worksheet.visibility]
    data = [
      offset,   # Absolute stream position of the BOF record of the sheet
                # represented by this record. This field is never encrypted
                # in protected files.
      visibility,     # Visibility: 0x00 = Visible
                #             0x01 = Hidden
                #             0x02 = Strong hidden (see below)
      0x00,     # Sheet type: 0x00 = Worksheet
                #             0x02 = Chart
                #             0x06 = Visual Basic module
    ]
    write_op writer, 0x0085, data.pack("VC2"), worksheet.name
    offset += worksheet.size
  end
end

#write_changes(workbook, io) ⇒ Object

Copy unchanged data verbatim, adjust offsets and write new records for changed data.



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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 169

def write_changes workbook, io
  sanitize_worksheets workbook.worksheets
  collect_formats workbook, :existing_document => true
  reader = workbook.ole
  sheet_data = {}
  sst_status, sst_total, sst_strings = complete_sst_update? workbook
  sst = {}
  sst_strings.each_with_index do |str, idx| sst.store str, idx end
  sheets = worksheets(workbook)
  positions = []
  newsheets = []
  sheets.each do |sheet|
    @sst[sheet] = sst
    pos, len = workbook.offsets[sheet.worksheet]
    if pos
      positions.push pos
      sheet.write_changes reader, pos + len, sst_status
    else
      newsheets.push sheet
      sheet.write_from_scratch
    end
    sheet_data[sheet.worksheet] = sheet.data
  end
  Ole::Storage.open io do |ole|
    ole.file.open 'Workbook', 'w' do |writer|
      reader.seek lastpos = 0
      workbook.offsets.select do |key, pair|
        workbook.changes.include? key
      end.sort_by do |key, (pos, _)|
        pos
      end.each do |key, (pos, len)|
        data = reader.read(pos - lastpos)
        writer.write data
        case key
        when Spreadsheet::Worksheet
          writer.write sheet_data[key]
        when :boundsheets
          ## boundsheets are hard to calculate. The offset below is only
          #  correct if there are no more changes in the workbook globals
          #  string after this.
          oldoffset = positions.min - len
          lastpos = pos + len
          bytechange = 0
          buffer = StringIO.new ''
          if tuple = workbook.offsets[:sst]
            write_sst_changes workbook, buffer, writer.pos,
                              sst_total, sst_strings
            pos, len = tuple
            if offset = workbook.offsets[:extsst]
              len += offset[1].to_i
            end
            bytechange = buffer.size - len
            write_boundsheets workbook, writer, oldoffset + bytechange
            reader.seek lastpos
            writer.write reader.read(pos - lastpos)
            buffer.rewind
            writer.write buffer.read
          elsif sst.empty? || workbook.biff_version < 8
            write_boundsheets workbook, writer, oldoffset + bytechange
          else
            write_sst workbook, buffer, writer.pos
            write_boundsheets workbook, writer, oldoffset + buffer.size
            pos = lastpos
            len = positions.min - lastpos
            if len > OPCODE_SIZE
              reader.seek pos
              writer.write reader.read(len - OPCODE_SIZE)
            end
            buffer.rewind
            writer.write buffer.read
            write_eof workbook, writer
          end
        else
          send "write_#{key}", workbook, writer
        end
        lastpos = [pos + len, reader.size - 1].min
        reader.seek lastpos
      end
      writer.write reader.read
      newsheets.each do |sheet|
        writer.write sheet.data
      end
    end
  end
end

#write_datemode(workbook, writer) ⇒ Object 



254
255
256
257
258
259
260
261
262
263
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 254

def write_datemode workbook, writer
  mode = @date_base.year == 1899 ? 0x00 : 0x01
  data = [
    mode, # 0 = Base date is 1899-Dec-31
          #     (the cell value 1 represents 1900-Jan-01)
          # 1 = Base date is 1904-Jan-01
          #     (the cell value 1 represents 1904-Jan-02)
  ]
  write_op writer, 0x0022, data.pack('v')
end

#write_dsf(workbook, writer) ⇒ Object 



264
265
266
267
268
269
270
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 264

def write_dsf workbook, writer
  data = [
    0x00, # 0 = Only the BIFF8 “Workbook” stream is present
          # 1 = Additional BIFF5/BIFF7 “Book” stream is in the file
  ]
  write_op writer, 0x0161, data.pack('v')
end

#write_encoding(workbook, writer) ⇒ Object 



271
272
273
274
275
276
277
278
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 271

def write_encoding workbook, writer
  enc = workbook.encoding || 'UTF-16LE'
  if RUBY_VERSION >= '1.9' && enc.is_a?(Encoding)
    enc = enc.name.upcase
  end
  cp = SEGAPEDOC[enc] or raise "Invalid or Unknown Codepage '#{enc}'"
  write_op writer, 0x0042, [cp].pack('v')
end

#write_eof(workbook, writer) ⇒ Object 



279
280
281
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 279

def write_eof workbook, writer
  write_op writer, 0x000a
end

#write_extsst(workbook, offsets, writer) ⇒ Object 



282
283
284
285
286
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 282

def write_extsst workbook, offsets, writer
  header = [SST_CHUNKSIZE].pack('v')
  data = offsets.collect do |pair| pair.push(0).pack('Vv2') end
  write_op writer, 0x00ff, header, data
end

#write_font(workbook, writer, font) ⇒ Object 



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
356
357
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 287

def write_font workbook, writer, font
  # TODO: Colors/Palette index
  size      = font.size * TWIPS
  color     = SEDOC_ROLOC[font.color] || SEDOC_ROLOC[:text]
  weight    = FONT_WEIGHTS.fetch(font.weight, font.weight)
  weight    = [[weight, 1000].min, 100].max
  esc       = SEPYT_TNEMEPACSE.fetch(font.escapement, 0)
  underline = SEPYT_ENILREDNU.fetch(font.underline, 0)
  family    = SEILIMAF_TNOF.fetch(font.family, 0)
  encoding  = SGNIDOCNE_TNOF.fetch(font.encoding, 0)
  options   = 0
  options  |= 0x0001 if weight > 600
  options  |= 0x0002 if font.italic?
  options  |= 0x0004 if underline > 0
  options  |= 0x0008 if font.strikeout?
  options  |= 0x0010 if font.outline?
  options  |= 0x0020 if font.shadow?
  data = [
    size,     # Height of the font (in twips = 1/20 of a point)
    options,  # Option flags:
              # Bit  Mask    Contents
              #   0  0x0001  1 = Characters are bold (redundant, see below)
              #   1  0x0002  1 = Characters are italic
              #   2  0x0004  1 = Characters are underlined (redundant)
              #   3  0x0008  1 = Characters are struck out
              #   4  0x0010  1 = Characters are outlined (djberger)
              #   5  0x0020  1 = Characters are shadowed (djberger)
    color,    # Palette index (➜ 6.70)
    weight,   # Font weight (100-1000). Standard values are
              #      0x0190 (400) for normal text and
              #      0x02bc (700) for bold text.
    esc,      # Escapement type: 0x0000 = None
              #                  0x0001 = Superscript
              #                  0x0002 = Subscript
    underline,# Underline type:  0x00 = None
              #                  0x01 = Single
              #                  0x02 = Double
              #                  0x21 = Single accounting
              #                  0x22 = Double accounting
    family,   # Font family:     0x00 = None (unknown or don't care)
              #                  0x01 = Roman (variable width, serifed)
              #                  0x02 = Swiss (variable width, sans-serifed)
              #                  0x03 = Modern (fixed width,
              #                                 serifed or sans-serifed)
              #                  0x04 = Script (cursive)
              #                  0x05 = Decorative (specialised,
              #                                       e.g. Old English, Fraktur)
    encoding, # Character set: 0x00 =   0 = ANSI Latin
              #                0x01 =   1 = System default
              #                0x02 =   2 = Symbol
              #                0x4d =  77 = Apple Roman
              #                0x80 = 128 = ANSI Japanese Shift-JIS
              #                0x81 = 129 = ANSI Korean (Hangul)
              #                0x82 = 130 = ANSI Korean (Johab)
              #                0x86 = 134 = ANSI Chinese Simplified GBK
              #                0x88 = 136 = ANSI Chinese Traditional BIG5
              #                0xa1 = 161 = ANSI Greek
              #                0xa2 = 162 = ANSI Turkish
              #                0xa3 = 163 = ANSI Vietnamese
              #                0xb1 = 177 = ANSI Hebrew
              #                0xb2 = 178 = ANSI Arabic
              #                0xba = 186 = ANSI Baltic
              #                0xcc = 204 = ANSI Cyrillic
              #                0xde = 222 = ANSI Thai
              #                0xee = 238 = ANSI Latin II (Central European)
              #                0xff = 255 = OEM Latin I
  ]
  name = unicode_string font.name # Font name: Unicode string,
                                  # 8-bit string length (➜ 3.4)
  write_op writer, opcode(:font), data.pack(binfmt(:font)), name
end

#write_fonts(workbook, writer) ⇒ Object 



358
359
360
361
362
363
364
365
366
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 358

def write_fonts workbook, writer
  fonts = @fonts[workbook] = {}
  @formats[workbook][:writers].map{|format| format.font }.compact.uniq.each do |font|
    unless fonts.include?(font.key)
      fonts.store font.key, fonts.size
      write_font workbook, writer, font
    end
  end
end

#write_formats(workbook, writer) ⇒ Object 



367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 367

def write_formats workbook, writer
  # From BIFF5 on, the built-in number formats will be omitted. The built-in
  # formats are dependent on the current regional settings of the operating
  # system. BUILTIN_FORMATS shows which number formats are used by
  # default in a US-English environment. All indexes from 0 to 163 are
  # reserved for built-in formats.
  # The first user-defined format starts at 164 (0xa4).
  formats = @number_formats[workbook] = {}
  BUILTIN_FORMATS.each do |idx, str|
    formats.store client(str, 'UTF-8'), idx
  end
  ## Ensure at least a 'GENERAL' format is written
  formats.delete client('GENERAL', 'UTF-8')
  idx = 0xa4
  workbook.formats.each do |fmt|
    str = fmt.number_format
    unless formats[str]
      formats.store str, idx
      # Number format string (Unicode string, 16-bit string length, ➜ 3.4)
      write_op writer, opcode(:format), [idx].pack('v'), unicode_string(str, 2)
      idx += 1
    end
  end
end

#write_from_scratch(workbook, io) ⇒ Object

Write a new Excel file.



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
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 393

def write_from_scratch workbook, io
  sanitize_worksheets workbook.worksheets
  collect_formats workbook
  sheets = worksheets workbook
  buffer1 = StringIO.new ''
  # ●  BOF Type = workbook globals (➜ 6.8)
  write_bof workbook, buffer1, :globals
  # ○  File Protection Block ➜ 4.19
  # ○  WRITEACCESS User name (BIFF3-BIFF8, ➜ 5.112)
  # ○  FILESHARING File sharing options (BIFF3-BIFF8, ➜ 5.44)
  # ○  CODEPAGE ➜ 6.17
  write_encoding workbook, buffer1
  # ○  DSF ➜ 6.32
  write_dsf workbook, buffer1
  # ○  TABID
  write_tabid workbook, buffer1
  # ○  FNGROUPCOUNT
  # ○  Workbook Protection Block ➜ 4.18
  # ○  WINDOWPROTECT Window settings: 1 = protected (➜ 5.111)
  # ○  PROTECT Cell contents: 1 = protected (➜ 5.82)
  write_protect workbook, buffer1
  # ○  OBJECTPROTECT Embedded objects: 1 = protected (➜ 5.72)
  # ○  PASSWORD Hash value of the password; 0 = No password (➜ 5.76)
  write_password workbook, buffer1
  # ○  BACKUP ➜ 5.5
  # ○  HIDEOBJ ➜ 5.56
  # ●  WINDOW1 ➜ 5.109
  write_window1 workbook, buffer1
  # ○  DATEMODE ➜ 5.28
  write_datemode workbook, buffer1
  # ○  PRECISION ➜ 5.79
  write_precision workbook, buffer1
  # ○  REFRESHALL
  write_refreshall workbook, buffer1
  # ○  BOOKBOOL ➜ 5.9
  write_bookbool workbook, buffer1
  # ●● FONT ➜ 5.45
  write_fonts workbook, buffer1
  # ○○ FORMAT ➜ 5.49
  write_formats workbook, buffer1
  # ●● XF ➜ 5.115
  write_xfs workbook, buffer1
  # ●● STYLE ➜ 5.103
  write_styles workbook, buffer1
  # ○  PALETTE ➜ 5.74
  write_palette workbook, buffer1
  # ○  USESELFS ➜ 5.106
  buffer1.rewind
  # ●● BOUNDSHEET ➜ 5.95
  buffer2 = StringIO.new ''
  # ○  COUNTRY ➜ 5.22
  # ○  Link Table ➜ 4.10.3
  # ○○ NAME ➜ 6.66
  # ○  Shared String Table ➜ 4.11
  # ●  SST ➜ 5.100
  # ●  EXTSST ➜ 5.42
  write_sst workbook, buffer2, buffer1.size
  # ●  EOF ➜ 5.37
  write_eof workbook, buffer2
  buffer2.rewind
  # worksheet data can only be assembled after write_sst
  sheets.each do |worksheet| worksheet.write_from_scratch end
  Ole::Storage.open io do |ole|
    ole.file.open 'Workbook', 'w' do |writer|
      writer.write buffer1.read
      write_boundsheets workbook, writer, buffer1.size + buffer2.size
      writer.write buffer2.read
      sheets.each do |worksheet|
        writer.write worksheet.data
      end
    end
  end
end

#write_op(writer, op, *args) ⇒ Object 



466
467
468
469
470
471
472
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 466

def write_op writer, op, *args
  data = args.join
  limited = data.slice!(0...@recordsize_limit)
  writer.write [op,limited.size].pack("v2")
  writer.write limited
  data
end

#write_palette(workbook, writer) ⇒ Object 



593
594
595
596
597
598
599
600
601
602
603
604
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 593

def write_palette workbook, writer
  data = default_palette

  workbook.palette.each do |idx, color|
    idx = SEDOC_ROLOC[idx] - 8 if idx.kind_of? Symbol
    raise "Undefined color index: #{idx}" unless data[idx]
    data[idx] = color
  end

  writer.write [opcode(:palette), 2 + 4 * data.size, data.size].pack('v3')
  writer.write data.collect { |c| c.push(0).pack('C4') }.join
end

#write_password(workbook, writer) ⇒ Object 



473
474
475
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 473

def write_password workbook, writer
  write_placeholder writer, 0x0013
end

#write_placeholder(writer, op, value = 0x0000, fmt = 'v') ⇒ Object 



476
477
478
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 476

def write_placeholder writer, op, value=0x0000, fmt='v'
  write_op writer, op, [value].pack(fmt)
end

#write_precision(workbook, writer) ⇒ Object 



479
480
481
482
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 479

def write_precision workbook, writer
  # 0 = Use displayed values; 1 = Use real cell values
  write_placeholder writer, 0x000e, 0x0001
end

#write_protect(workbook, writer) ⇒ Object 



483
484
485
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 483

def write_protect workbook, writer
  write_placeholder writer, 0x0012
end

#write_refreshall(workbook, writer) ⇒ Object 



486
487
488
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 486

def write_refreshall workbook, writer
  write_placeholder writer, 0x01b7
end

#write_sst(workbook, writer, offset) ⇒ Object 



489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 489

def write_sst workbook, writer, offset
  # Offset  Size  Contents
  #      0     4  Total number of strings in the workbook (see below)
  #      4     4  Number of following strings (nm)
  #      8  var.  List of nm Unicode strings, 16-bit string length (➜ 3.4)
  num_total = 0
  strings = worksheets(workbook).inject(Hash.new(0)) do |memo, worksheet|
    worksheet.strings.each do |k,v|
      memo[k] += v
      num_total += v
    end
    memo
  end
  _write_sst workbook, writer, offset, num_total, strings.keys
end

#write_sst_changes(workbook, writer, offset, total, strings) ⇒ Object 



535
536
537
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 535

def write_sst_changes workbook, writer, offset, total, strings
  _write_sst workbook, writer, offset, total, strings
end

#write_string_part(writer, op, data, wide) ⇒ Object 



538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 538

def write_string_part writer, op, data, wide
  bef = data.size
  ## if we're writing wide characters, we need to make sure we don't cut
  #  characters in half
  if wide > 0 && data.size > @recordsize_limit
    remove = @recordsize_limit - bef
    remove -= remove % 2
    rest = data.slice!(remove..-1)
    write_op writer, op, data
    data = rest
  else
    data = write_op writer, op, data
  end
  op = 0x003c
  # Unicode strings are split in a special way. At the beginning of each
  # CONTINUE record the option flags byte is repeated. Only the
  # character size flag will be set in this flags byte, the Rich-Text
  # flag and the Far-East flag are set to zero.
  unless data.empty?
    if wide == 1
      # check if we can compress the rest of the string
      data, wide = compress_unicode_string data
    end
    data = [wide].pack('C') << data
  end
  [op, data, wide]
end

#write_styles(workbook, writer) ⇒ Object 



565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 565

def write_styles workbook, writer
  # TODO: Style implementation. The following is simply a standard builtin
  #       style.
  # TODO: User defined styles
  data = [
    0x8000, #   Bit  Mask    Contents
            # 11- 0  0x0fff  Index to style XF record (➜ 6.115)
            #    15  0x8000  Always 1 for built-in styles
    0x00,   # Identifier of the built-in cell style:
            # 0x00 = Normal
            # 0x01 = RowLevel_lv (see next field)
            # 0x02 = ColLevel_lv (see next field)
            # 0x03 = Comma
            # 0x04 = Currency
            # 0x05 = Percent
            # 0x06 = Comma [0] (BIFF4-BIFF8)
            # 0x07 = Currency [0] (BIFF4-BIFF8)
            # 0x08 = Hyperlink (BIFF8)
            # 0x09 = Followed Hyperlink (BIFF8)
    0xff,   # Level for RowLevel or ColLevel style (zero-based, lv),
            # 0xff otherwise
            # The RowLevel and ColLevel styles specify the formatting of
            # subtotal cells in a specific outline level. The level is
            # specified by the last field in the STYLE record. Valid values
            # are 0…6 for the outline levels 1…7.
  ]
  write_op writer, 0x0293, data.pack('vC2')
end

#write_tabid(workbook, writer) ⇒ Object 



605
606
607
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 605

def write_tabid workbook, writer
  write_op writer, 0x013d, [1].pack('v')
end

#write_window1(workbook, writer) ⇒ Object 



608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 608

def write_window1 workbook, writer
  selected = workbook.worksheets.find do |sheet| sheet.selected end
  actidx = workbook.worksheets.index selected
  data = [
    0x0000, # Horizontal position of the document window
            # (in twips = 1/20 of a point)
    0x0000, # Vertical position of the document window
            # (in twips = 1/20 of a point)
    0x4000, # Width of the document window (in twips = 1/20 of a point)
    0x2000, # Height of the document window (in twips = 1/20 of a point)
    0x0038, # Option flags:
            # Bit  Mask    Contents
            #   0  0x0001  0 = Window is visible
            #              1 = Window is hidden
            #   1  0x0002  0 = Window is open
            #              1 = Window is minimised
            #   3  0x0008  0 = Horizontal scroll bar hidden
            #              1 = Horizontal scroll bar visible
            #   4  0x0010  0 = Vertical scroll bar hidden
            #              1 = Vertical scroll bar visible
            #   5  0x0020  0 = Worksheet tab bar hidden
            #              1 = Worksheet tab bar visible
    actidx, # Index to active (displayed) worksheet
    0x0000, # Index of first visible tab in the worksheet tab bar
    0x0001, # Number of selected worksheets
            # (highlighted in the worksheet tab bar)
    0x00e5, # Width of worksheet tab bar (in 1/1000 of window width).
            # The remaining space is used by the horizontal scrollbar.
  ]
  write_op writer, 0x003d, data.pack('v*')
end

#write_workbook(workbook, io) ⇒ Object

The main writer method. Calls #write_from_scratch or #write_changes depending on the class and state of workbook.



642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 642

def write_workbook workbook, io
  unless workbook.is_a?(Excel::Workbook) && workbook.io
    @date_base = Date.new 1899, 12, 31
    write_from_scratch workbook, io
  else
    @date_base = workbook.date_base
    if workbook.changes.empty?
      super
    else
      write_changes workbook, io
    end
  end
ensure
  cleanup workbook
end

#write_xfs(workbook, writer) ⇒ Object 



657
658
659
660
661
662
663
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 657

def write_xfs workbook, writer
  # The default cell format is always present in an Excel file, described by
  # the XF record with the fixed index 15 (0-based). By default, it uses the
  # worksheet/workbook default cell style, described by the very first XF
  # record (index 0).
  @formats[workbook][:writers].each do |fmt| fmt.write_xf writer end
end

#xf_index(workbook, format) ⇒ Object 



667
668
669
 
# File 'lib/spreadsheet/excel/writer/workbook.rb', line 667

def xf_index workbook, format
  @formats[workbook][:xf_indexes][format] || 0
end