Class: Worksheet

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

Constant Summary collapse

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

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.



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

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.



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

def colinfo
  @colinfo
end

#index ⇒ Object

Returns the value of attribute index.



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

def index
  @index
end

#name ⇒ Object (readonly)

Returns the value of attribute name.



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

def name
  @name
end

#offset ⇒ Object

Returns the value of attribute offset.



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

def offset
  @offset
end

#selection ⇒ Object

Returns the value of attribute selection.



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

def selection
  @selection
end

#xf_index ⇒ Object (readonly)

Returns the value of attribute xf_index.



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

def xf_index
  @xf_index
end

Instance Method Details

#activate ⇒ Object 



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

def activate
   @active_sheet = @index
end

#close ⇒ Object 



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

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 



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

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

#data ⇒ Object 



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

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

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



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

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) 


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

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 



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

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 



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

def set_first_sheet
   @first_sheet = @index
end

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



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

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 



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

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 



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

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 



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

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



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

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 



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

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 



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

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:



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

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 



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

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:



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

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 



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

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:



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

def write_string(row, col, str, format)
   record = 0x0204
   length = 0x0008 + str.length

   xf_index = XF(row, col, format)

   strlen = str.length

   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

   # Truncate strings over 255 characters
   if strlen > StrMax
      str    = str[0..StrMax-1]
      length = 0x0008 + StrMax
      strlen = StrMax
   end

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

   append(header, data, str)
end

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



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

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



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

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