Class: Worksheet

Inherits:
BIFFWriter show all
Defined in:
lib/spreadsheet/worksheet.rb

Constant Summary collapse

RowMax = 
65536
ColMax = 
256
StrMax = 
255
Buffer = 
4096
ContMax = 
2080

Constants inherited from BIFFWriter

BIFFWriter::BIFF_Version, BIFFWriter::BigEndian

Instance Attribute Summary collapse

Attributes inherited from BIFFWriter

#byte_order, #datasize

Instance Method Summary collapse

Methods inherited from BIFFWriter

#append, #prepend, #store_bof, #store_eof

Constructor Details

#initialize(name, index = 0, active_sheet = 0, first_sheet = 0, url_format = nil) ⇒ Worksheet

Returns a new instance of Worksheet.



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/spreadsheet/worksheet.rb', line 14

def initialize(name, index=0, active_sheet=0, first_sheet=0, url_format=nil)
   super(name,index)

   @name         = name
   @index        = index
   @active_sheet = active_sheet
   @first_sheet  = first_sheet
   @url_format   = url_format

   @offset = 0

   @dim_rowmin = RowMax + 1
   @dim_rowmax = 0
   @dim_colmin = ColMax + 1
   @dim_colmax = 0

   @colinfo = []
   @selection = [0,0]
   @row_formats = {}
   @column_formats = {}
   @outline_row_level = 0
end

Instance Attribute Details

#colinfo ⇒ Object

Returns the value of attribute colinfo.



12
13
14
 
# File 'lib/spreadsheet/worksheet.rb', line 12

def colinfo
  @colinfo
end

#index ⇒ Object

Returns the value of attribute index.



12
13
14
 
# File 'lib/spreadsheet/worksheet.rb', line 12

def index
  @index
end

#name ⇒ Object (readonly)

Returns the value of attribute name.



11
12
13
 
# File 'lib/spreadsheet/worksheet.rb', line 11

def name
  @name
end

#offset ⇒ Object

Returns the value of attribute offset.



12
13
14
 
# File 'lib/spreadsheet/worksheet.rb', line 12

def offset
  @offset
end

#selection ⇒ Object

Returns the value of attribute selection.



12
13
14
 
# File 'lib/spreadsheet/worksheet.rb', line 12

def selection
  @selection
end

#xf_index ⇒ Object (readonly)

Returns the value of attribute xf_index.



11
12
13
 
# File 'lib/spreadsheet/worksheet.rb', line 11

def xf_index
  @xf_index
end

Instance Method Details

#activate ⇒ Object 



63
64
65
 
# File 'lib/spreadsheet/worksheet.rb', line 63

def activate
   @active_sheet = @index
end

#close ⇒ Object 



37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/spreadsheet/worksheet.rb', line 37

def close
   store_dimensions

   unless @colinfo.empty?
      while @colinfo.length > 0
         store_colinfo(*@colinfo.pop)
      end
      store_defcol
   end

   store_guts
   store_bof(0x0010)

   store_window2
   store_selection(*@selection)
   store_eof
end

#column(*range) ⇒ Object 



71
72
73
 
# File 'lib/spreadsheet/worksheet.rb', line 71

def column(*range)
   @colinfo = *range
end

#data ⇒ Object 



55
56
57
58
59
60
61
 
# File 'lib/spreadsheet/worksheet.rb', line 55

def data
   if @data && @data.length > 0
      tmp = @data
      @data = ""
      return tmp
   end
end

#format_column(column, width = nil, format = nil) ⇒ Object 



429
430
431
432
433
434
435
436
437
438
439
440
441
 
# File 'lib/spreadsheet/worksheet.rb', line 429

def format_column(column, width=nil, format=nil)
   unless column.kind_of?(Range) || column.kind_of?(Fixnum)
      raise TypeError
   end

   width = 8.43 if width.nil?
   column = column..column if column.kind_of?(Fixnum)

   column.each{ |e|
      @column_formats[e] = format unless format.nil?
   }
   @colinfo.push([column.begin, column.end, width, format])
end

#format_rectangle(x1, y1, x2, y2, format) ⇒ Object

Format a rectangular section of cells. Note that you should call this method only after you have written data to it, or the formatting will be lost.

Raises:

  • (TypeError) 


328
329
330
331
332
333
334
335
336
 
# File 'lib/spreadsheet/worksheet.rb', line 328

def format_rectangle(x1, y1, x2, y2, format)
   raise TypeError, "invalid format" unless format.kind_of?(Format)

   x1.upto(x2){ |row|
      y1.upto(y2){ |col|
         write_blank(row, col, format)
      }
   }
end

#format_row(row, height = nil, format = nil, hidden = false, level = 0) ⇒ Object 



364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
 
# File 'lib/spreadsheet/worksheet.rb', line 364

def format_row(row, height=nil, format=nil, hidden=false, level=0)
   unless row.kind_of?(Range) || row.kind_of?(Integer)
      raise TypeError, 'row must be an Integer or Range'
   end
   
   if hidden.nil? || hidden == false
      hidden = 0
   end

   record = 0x0208 # record identifier
   length = 0x0010 # number of bytes to follow

   col_first = 0x0000 # first defined column
   col_last  = 0x0000 # last defined column
   irwmac    = 0x0000 # used by Excel to optimize loading
   reserved  = 0x0000 # reservered
   grbit     = 0x0000 # option flags
   xf_index  = nil

   grbit |= level
   if hidden > 0
      grbit |= 0x0020
   end
   grbit |= 0x0040 #unsynched
   grbit |= 0x0100

   @outline_row_level = level if level > @outline_row_level

   if format.nil?
      xf_index = 0x0F
   else
      xf_index = format.xf_index
      grbit |= 0x80
   end

   if height.nil?
      height = 0xff
   else
      height = height * 20
   end

   row = row..row if row.kind_of?(Fixnum) # Avoid an if..else clause :)
   row.each{ |r|
      header = [record, length].pack("vv")
      fields = [r,col_first,col_last,height,irwmac,reserved,grbit,xf_index]
      data = fields.pack("vvvvvvvv")

      @row_formats[r] = format if format
      append(header + data)
   }
end

#set_first_sheet ⇒ Object 



67
68
69
 
# File 'lib/spreadsheet/worksheet.rb', line 67

def set_first_sheet
   @first_sheet = @index
end

#store_colinfo(first = 0, last = 0, coldx = 8.43, ixfe = 0x0F, grbit = 0) ⇒ Object 



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'lib/spreadsheet/worksheet.rb', line 146

def store_colinfo(first=0, last=0, coldx=8.43, ixfe=0x0F, grbit=0)
   record   = 0x007D
   length   = 0x000B

   coldx += 0.72
   coldx *= 256
   reserved = 0x00

   if ixfe.kind_of?(Format)
      ixfe = ixfe.xf_index
   end

   header = [record, length].pack("vv")
   data   = [first, last, coldx, ixfe, grbit, reserved].pack("vvvvvC")

   prepend(header,data)
end

#store_defcol ⇒ Object 



134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/spreadsheet/worksheet.rb', line 134

def store_defcol
   record   = 0x0055
   length   = 0x0002

   colwidth = 0x0008

   header = [record, length].pack("vv")
   data   = [colwidth].pack("v")

   prepend(header, data)
end

#store_dimensions ⇒ Object 



75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/spreadsheet/worksheet.rb', line 75

def store_dimensions
   record   = 0x0000
   length   = 0x000A
   reserved = 0x0000

   header = [record, length].pack("vv")
   fields = [@dim_rowmin, @dim_rowmax, @dim_colmin, @dim_colmax, reserved]
   data   = fields.pack("vvvvv")

   prepend(header, data)
end

#store_guts ⇒ Object 



87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/spreadsheet/worksheet.rb', line 87

def store_guts
   record   = 0x0080 # record identifier
   length   = 0x0008 # bytes to follow
   
   dxRwGut  = 0x0000 # size of row gutter
   dxColGut = 0x0000 # size of col gutter
   
   row_level = @outline_row_level
   col_level = 0
 
   @colinfo.each do |colinfo|
      next if colinfo.length < 6
      col_level = colinfo[5] if colinfo[5] > col_level
   end
   
   col_level = 0 if col_level < 0
   col_level = 7 if col_level > 7
   
   row_level += 1 if row_level > 0
   col_level += 1 if col_level > 0
   
   header = [record, length].pack("vv")
   fields = [dxRwGut, dxColGut, row_level, col_level]
   data   = fields.pack("vvvv")
 
   prepend(header, data)
end

#store_selection(row = 0, col = 0, row_last = 0, col_last = 0) ⇒ Object

I think this may have problems



165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/spreadsheet/worksheet.rb', line 165

def store_selection(row=0, col=0, row_last=0, col_last=0)
   record = 0x001D
   length = 0x000F

   pnn     = 3
   rwAct   = row
   colAct  = col
   irefAct = 0
   cref    = 1

   rwFirst  = row
   colFirst = col

   if row_last != 0
      rwLast = row_last
   else
      rwLast = rwFirst
   end

   if col_last != 0
      colLast = col_last
   else
      colLast = colFirst
   end

   if rwFirst > rwLast
      rwFirst,rwLast = rwLast,rwFirst
   end

   if colFirst > colLast
      colFirst,colLast = colLast,colFirst
   end

   header = [record, length].pack("vv")
   fields = [pnn,rwAct,colAct,irefAct,cref,rwFirst,rwLast,colFirst,colLast]
   data   = fields.pack("CvvvvvvCC")

   append(header, data)
end

#store_window2 ⇒ Object 



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/spreadsheet/worksheet.rb', line 115

def store_window2
   record  = 0x023E
   length  = 0x000A

   grbit   = 0x00B6
   rwTop   = 0x0000
   colLeft = 0x0000
   rgbHdr  = 0x00000000

   if @active_sheet == @index
     grbit = 0x06B6
   end

   header = [record, length].pack("vv")
   data   = [grbit, rwTop, colLeft, rgbHdr].pack("vvvV")

   append(header, data)
end

#write(row, col, data = nil, format = nil) ⇒ Object 



205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/spreadsheet/worksheet.rb', line 205

def write(row, col, data=nil, format=nil)
   if data.nil?
      write_blank(row, col, format)
   elsif data.kind_of?(Array)
      write_row(row, col, data, format)
   elsif data.kind_of?(Numeric)
      write_number(row, col, data, format)
   else
      write_string(row, col, data, format)
   end
end

#write_blank(row, col, format) ⇒ Object

Write a blank cell to the specified row and column (zero indexed). A blank cell is used to specify formatting without adding data.

Raises:



305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
 
# File 'lib/spreadsheet/worksheet.rb', line 305

def write_blank(row, col, format)
   record = 0x0201
   length = 0x0006

   xf_index = XF(row, col, format)

   raise MaxSizeError if row >= RowMax
   raise MaxSizeError if col >= ColMax

   @dim_rowmin = row if row < @dim_rowmin
   @dim_rowmax = row if row > @dim_rowmax
   @dim_colmin = col if col < @dim_colmin
   @dim_colmax = col if col > @dim_colmax

   header = [record, length].pack("vv")
   data   = [row, col, xf_index].pack("vvv")

   append(header, data)
end

#write_column(row, col, tokens = nil, format = nil) ⇒ Object 



233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/spreadsheet/worksheet.rb', line 233

def write_column(row, col, tokens=nil, format=nil)
   if tokens.nil?
      write(row,col,tokens,format)
      return
   end

   tokens.each{ |token|
      if token.kind_of?(Array)
         write_row(row, col, token, format)
      else
         write(row, col, token, format)
      end
      row += 1
   }
end

#write_number(row, col, num, format = nil) ⇒ Object

Raises:



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

def write_number(row, col, num, format=nil)
   record  = 0x0203
   length  = 0x000E
   
   xf_index = XF(row,col,format)

   raise MaxSizeError if row >= RowMax
   raise MaxSizeError if col >= ColMax

   @dim_rowmin = row if row < @dim_rowmin
   @dim_rowmax = row if row > @dim_rowmax
   @dim_colmin = col if col < @dim_colmin
   @dim_colmax = col if col > @dim_colmax

   header    = [record,length].pack("vv")
   data      = [row,col,xf_index].pack("vvv")
   xl_double = [num].pack("d")

   if BigEndian
      xl_double.reverse!
   end

   append(header,data,xl_double)
end

#write_row(row, col, tokens = nil, format = nil) ⇒ Object 



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
 
# File 'lib/spreadsheet/worksheet.rb', line 217

def write_row(row, col, tokens=nil, format=nil)
   if tokens.nil?
      write(row,col,tokens,format)
      return
   end

   tokens.each{ |token|
      if token.kind_of?(Array)
         write_column(row,col,token,format)
      else
         write(row,col,token,format)
      end
      col += 1
   }
end

#write_string(row, col, str, format) ⇒ Object

Raises:



274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
 
# File 'lib/spreadsheet/worksheet.rb', line 274

def write_string(row, col, str, format)
   record = 0x0204

   xf_index = XF(row, col, format)

   raise MaxSizeError if row >= RowMax
   raise MaxSizeError if col >= ColMax

   @dim_rowmin = row if row < @dim_rowmin
   @dim_rowmax = row if row > @dim_rowmax
   @dim_colmin = col if col < @dim_colmin
   @dim_colmax = col if col > @dim_colmax

   strlen = str.size
   pstr = str.slice!(0,StrMax)
   length = 0x0008 + pstr.size
   header = [record, length].pack("vv")
   data   = [row, col, xf_index, strlen].pack("vvvv")
   append(header, data, pstr)

   record = 0x003C
   until(str.empty?)
     pstr = str.slice!(0,ContMax)
     header = [record, pstr.size].pack("vv")
     append(header, pstr)
   end

end

#write_url(row, col, url, string = url, format = nil) ⇒ Object 



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

def write_url(row, col, url, string=url, format=nil)
   record = 0x01B8
   length = 0x0034 + 2 * (1+url.length)
   
   write_string(row,col,string,format)

   header = [record, length].pack("vv")
   data   = [row, row, col, col].pack("vvvv")

   unknown = "D0C9EA79F9BACE118C8200AA004BA90B02000000"
   unknown += "03000000E0C9EA79F9BACE118C8200AA004BA90B"

   stream = [unknown].pack("H*")

   url = url.split('').join("\0")
   url += "\0\0\0"

   len = url.length
   url_len = [len].pack("V")

   append(header + data)
   append(stream)
   append(url_len)
   append(url)
end

#XF(row, col, xf = nil) ⇒ Object

private - adapted from .37 of Spreadsheet::WriteExcel



417
418
419
420
421
422
423
424
425
426
427
 
# File 'lib/spreadsheet/worksheet.rb', line 417

def XF(row, col, xf=nil)
   if xf.kind_of?(Format)
      return xf.xf_index
   elsif @row_formats.has_key?(row) 
      return @row_formats[row].xf_index
   elsif @column_formats.has_key?(col)
      return @column_formats[col].xf_index
   else
      return 0x0F
   end
end