Class: Spreadsheet::Worksheet

Inherits:
Object
  • Object
show all
Includes:
Enumerable, Datatypes, Encodings
Defined in:
lib/spreadsheet/worksheet.rb,
lib/spreadsheet/excel.rb

Overview

The Worksheet class. Contains most of the Spreadsheet data in Rows.

Interesting Attributes

#name

The Name of this Worksheet.

#default_format

The default format used for all cells in this Workhseet that have no format set explicitly or in Row#default_format.

#rows

The Rows in this Worksheet. It is not recommended to Manipulate this Array directly. If you do, call #updated_from with the smallest modified index.

#columns

The Column formatting in this Worksheet. Column instances may appear at more than one position in #columns. If you modify a Column directly, your changes will be reflected in all those positions.

#selected

When a user chooses to print a Workbook, Excel will include all selected Worksheets. If no Worksheet is selected at Workbook#write, then the first Worksheet is selected by default.

Direct Known Subclasses

Excel::Worksheet

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Datatypes

append_features

Methods included from Compatibility

#ivar_name, #method_name

Constructor Details

#initialize(opts = {}) ⇒ Worksheet

Returns a new instance of Worksheet.



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/spreadsheet/worksheet.rb', line 36

def initialize opts = {}
  @froze_top = 0
  @froze_left = 0
  @default_format = nil
  @selected = opts[:selected]
  @dimensions = [0, 0, 0, 0]
  @pagesetup = {
    orig_data: [9, 100, 1, 1, 1, 0, 300, 300, 0.5, 0.5, 1],
    orientation: :portrait,
    adjust_to: 100
  }
  @margins = {
    top: 1,
    left: 0.75,
    right: 0.75,
    bottom: 1
  }
  @name = sanitize_invalid_characters(opts[:name] || "Worksheet")
  @workbook = opts[:workbook]
  @rows = []
  @columns = []
  @links = {}
  @merged_cells = []
  @protected = false
  @password_hash = 0
  @visibility = opts[:visibility]
end

Instance Attribute Details

#columns ⇒ Object (readonly)

Returns the value of attribute columns.



33
34
35
 
# File 'lib/spreadsheet/worksheet.rb', line 33

def columns
  @columns
end

#froze_left ⇒ Object (readonly)

Returns the value of attribute froze_left.



34
35
36
 
# File 'lib/spreadsheet/worksheet.rb', line 34

def froze_left
  @froze_left
end

#froze_top ⇒ Object (readonly)

Returns the value of attribute froze_top.



34
35
36
 
# File 'lib/spreadsheet/worksheet.rb', line 34

def froze_top
  @froze_top
end

#margins ⇒ Object (readonly)

Returns the value of attribute margins.



33
34
35
 
# File 'lib/spreadsheet/worksheet.rb', line 33

def margins
  @margins
end

#merged_cells ⇒ Object (readonly)

Returns the value of attribute merged_cells.



33
34
35
 
# File 'lib/spreadsheet/worksheet.rb', line 33

def merged_cells
  @merged_cells
end

#name ⇒ Object

Returns the value of attribute name.



32
33
34
 
# File 'lib/spreadsheet/worksheet.rb', line 32

def name
  @name
end

#pagesetup ⇒ Object (readonly)

Returns the value of attribute pagesetup.



33
34
35
 
# File 'lib/spreadsheet/worksheet.rb', line 33

def pagesetup
  @pagesetup
end

#password_hash ⇒ Object

Returns the value of attribute password_hash.



32
33
34
 
# File 'lib/spreadsheet/worksheet.rb', line 32

def password_hash
  @password_hash
end

#rows ⇒ Object (readonly)

Returns the value of attribute rows.



33
34
35
 
# File 'lib/spreadsheet/worksheet.rb', line 33

def rows
  @rows
end

#selected ⇒ Object

Returns the value of attribute selected.



32
33
34
 
# File 'lib/spreadsheet/worksheet.rb', line 32

def selected
  @selected
end

#workbook ⇒ Object

Returns the value of attribute workbook.



32
33
34
 
# File 'lib/spreadsheet/worksheet.rb', line 32

def workbook
  @workbook
end

Instance Method Details

#<<(cells = []) ⇒ Object 



254
255
256
 
# File 'lib/spreadsheet/worksheet.rb', line 254

def << cells = []
  insert_row @rows.size, cells
end

#[](row, column) ⇒ Object

Get the enriched value of the Cell at row, column. See also Worksheet#cell, Row#[].



335
336
337
 
# File 'lib/spreadsheet/worksheet.rb', line 335

def [] row, column
  row(row)[column]
end

#[]=(row, column, value) ⇒ Object

Set the value of the Cell at row, column to value. See also Row#[]=.



342
343
344
 
# File 'lib/spreadsheet/worksheet.rb', line 342

def []= row, column, value
  row(row)[column] = value
end

#active ⇒ Object

:nodoc:



73
74
75
76
 
# File 'lib/spreadsheet/worksheet.rb', line 73

def active # :nodoc:
  warn "Worksheet#active is deprecated. Please use Worksheet#selected instead."
  selected
end

#active=(selected) ⇒ Object

:nodoc:



78
79
80
81
 
# File 'lib/spreadsheet/worksheet.rb', line 78

def active= selected # :nodoc:
  warn "Worksheet#active= is deprecated. Please use Worksheet#selected= instead."
  self.selected = selected
end

#add_format(fmt) ⇒ Object

Add a Format to the Workbook. If you use Row#set_format, you should not need to use this Method.



86
87
88
 
# File 'lib/spreadsheet/worksheet.rb', line 86

def add_format fmt
  @workbook.add_format fmt if fmt
end

#cell(row, column) ⇒ Object

Get the enriched value of the Cell at row, column. See also Worksheet#[], Row#[].



93
94
95
 
# File 'lib/spreadsheet/worksheet.rb', line 93

def cell row, column
  row(row)[column]
end

#column(idx) ⇒ Object

Returns the Column at idx.



99
100
101
 
# File 'lib/spreadsheet/worksheet.rb', line 99

def column idx
  @columns[idx] || Column.new(idx, default_format, worksheet: self)
end

#column_count ⇒ Object

The number of columns in this Worksheet which contain data.



105
106
107
 
# File 'lib/spreadsheet/worksheet.rb', line 105

def column_count
  dimensions[3] - dimensions[2]
end

#column_updated(idx, column) ⇒ Object 



109
110
111
 
# File 'lib/spreadsheet/worksheet.rb', line 109

def column_updated idx, column
  @columns[idx] = column
end

#compact! ⇒ Object 



353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
 
# File 'lib/spreadsheet/worksheet.rb', line 353

def compact!
  recalculate_dimensions

  # detect first non-nil non-empty row if given first row is empty or nil
  if row(@dimensions[0]).empty? || row(@dimensions[0]).compact.join("").empty?
    (@dimensions[0]...@dimensions[1]).each do |i|
      break unless row(i).empty? || row(i).compact.join("").empty?
      @dimensions[0] = i
    end
  end

  # detect last non-nil non-empty row if given last row is empty or nil
  if row(@dimensions[1] - 1).empty? || row(@dimensions[1] - 1).compact.join("").empty?
    row_size = @dimensions[1] - 1
    @dimensions[1] = @dimensions[0]
    # divide and conquer
    while row_size - @dimensions[1] > 1
      if row(row_size).empty? || row(row_size).compact.join("").empty?
        row_size = @dimensions[1] + (((row_size - @dimensions[1]) + 1) / 2).to_i
      else
        idx = ((row_size - @dimensions[1]) / 2).to_i + 1
        @dimensions[1] = row_size
        row_size += idx
      end
    end
    @dimensions[1] = row_size + 1
  end

  # detect first non-empty non-nil column if first column is empty or nil
  if (@dimensions[0]..@dimensions[1]).inject(true) { |t, j| t && row(j)[@dimensions[2]].nil? }
    (@dimensions[2]..@dimensions[3]).each do |i|
      break unless (@dimensions[0]..@dimensions[1]).inject(true) { |t, j| t && row(j)[i].to_s.empty? }
      @dimensions[2] = i
    end
  end

  # detect last non-empty non-nil column if last column is empty or nil
  if (@dimensions[0]..@dimensions[1]).inject(true) { |t, j| t && row(j)[@dimensions[3]].nil? }
    (@dimensions[2]..@dimensions[3]).reverse_each do |i|
      break unless (@dimensions[0]..@dimensions[1]).inject(true) { |t, j| t && row(j)[i].to_s.empty? }
      @dimensions[3] = i
    end
    @dimensions[3]
  end
end

#default_format ⇒ Object

The default Format of this Worksheet, if you have set one. Returns the Workbook’s default Format otherwise.



124
125
126
 
# File 'lib/spreadsheet/worksheet.rb', line 124

def default_format
  @default_format || @workbook.default_format
end

#default_format=(format) ⇒ Object

Set the default Format of this Worksheet.



130
131
132
133
 
# File 'lib/spreadsheet/worksheet.rb', line 130

def default_format= format
  @default_format = format
  add_format format
end

#delete_row(idx) ⇒ Object

Delete the Row at idx (0-based) from this Worksheet.



115
116
117
118
119
 
# File 'lib/spreadsheet/worksheet.rb', line 115

def delete_row idx
  res = @rows.delete_at idx
  updated_from idx
  res
end

#dimensions ⇒ Object

Dimensions

[ first used row, first unused row, first used column, first unused column ] ( First used means that all rows or columns before that are empty. First unused means that this and all following rows or columns are empty. )



159
160
161
 
# File 'lib/spreadsheet/worksheet.rb', line 159

def dimensions
  @dimensions || recalculate_dimensions
end

#each(skip = , &block) ⇒ Object

If no argument is given, #each iterates over all used Rows (from the first used Row until but omitting the first unused Row, see also #dimensions).

If the argument skip is given, #each iterates from that row until but omitting the first unused Row, effectively skipping the first skip Rows from the top of the Worksheet.



170
171
172
173
174
175
176
177
178
 
# File 'lib/spreadsheet/worksheet.rb', line 170

def each(skip = dimensions[0], &block)
  rows = skip.upto(dimensions[1] - 1).map { |index| row(index) }.to_enum

  if block_given?
    rows.each(&block)
  else
    rows
  end
end

#encoding ⇒ Object

:nodoc:



180
181
182
 
# File 'lib/spreadsheet/worksheet.rb', line 180

def encoding # :nodoc:
  @workbook.encoding
end

#format_column(idx, format = nil, opts = {}) ⇒ Object

Sets the default Format of the column at idx.

idx may be an Integer, or an Enumerable that iterates over a number of Integers.

format is a Format, or nil if you want to remove the Formatting at idx

Returns an instance of Column if idx is an Integer, an Array of Columns otherwise.



194
195
196
197
198
199
200
 
# File 'lib/spreadsheet/worksheet.rb', line 194

def format_column column, width = nil, format = nil
  if width.is_a? Format
    new_format_column column, width, format
  else
    new_format_column column, format, width: width
  end
end

#format_dates!(format = nil) ⇒ Object

Formats all Date, DateTime and Time cells with format or the default formats:

  • ‘DD.MM.YYYY’ for Date

  • ‘DD.MM.YYYY hh:mm:ss’ for DateTime and Time



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

def format_dates! format = nil
  new_formats = {}
  fmt_str_time = client("DD.MM.YYYY hh:mm:ss", "UTF-8")
  fmt_str_date = client("DD.MM.YYYY", "UTF-8")
  each do |row|
    row.each_with_index do |value, idx|
      unless row.formats[idx] || row.format(idx).date_or_time?
        numfmt = case value
        when DateTime, Time
          format || fmt_str_time
        when Date
          format || fmt_str_date
        end
        case numfmt
        when Format
          row.set_format idx, numfmt
        when String
          existing_format = row.format(idx)
          new_formats[existing_format] ||= {}
          new_format = new_formats[existing_format][numfmt]
          if !new_format
            new_format = new_formats[existing_format][numfmt] = existing_format.dup
            new_format.number_format = numfmt
          end
          row.set_format idx, new_format
        end
      end
    end
  end
end

#freeze!(top, left) ⇒ Object 



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

def freeze!(top, left)
  @froze_top = top.to_i
  @froze_left = left.to_i
end

#has_frozen_panel? ⇒ Boolean

Returns:

  • (Boolean) 


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

def has_frozen_panel?
  @froze_top > 0 or @froze_left > 0
end

#insert_row(idx, cells = []) ⇒ Object

Insert a Row at idx (0-based) containing cells



248
249
250
251
252
 
# File 'lib/spreadsheet/worksheet.rb', line 248

def insert_row idx, cells = []
  res = @rows.insert idx, Row.new(self, idx, cells)
  updated_from idx
  res
end

#inspect ⇒ Object 



258
259
260
261
262
263
264
265
266
 
# File 'lib/spreadsheet/worksheet.rb', line 258

def inspect
  names = instance_variables
  names.delete "@rows"
  variables = names.collect do |name|
    "%s=%s" % [name, instance_variable_get(name)]
  end.join(" ")
  sprintf "#<%s:0x%014x %s @rows[%i]>", self.class, object_id,
    variables, row_count
end

#last_row ⇒ Object

The last Row containing any data



269
270
271
 
# File 'lib/spreadsheet/worksheet.rb', line 269

def last_row
  row(last_row_index)
end

#last_row_index ⇒ Object

The index of the last Row containing any data



274
275
276
 
# File 'lib/spreadsheet/worksheet.rb', line 274

def last_row_index
  [dimensions[1] - 1, 0].max
end

#merge_cells(start_row, start_col, end_row, end_col) ⇒ Object

Merges multiple cells into one.



348
349
350
351
 
# File 'lib/spreadsheet/worksheet.rb', line 348

def merge_cells start_row, start_col, end_row, end_col
  # FIXME enlarge or dup check
  @merged_cells.push [start_row, end_row, start_col, end_col]
end

#new_format_column ⇒ Object 



36
 
# File 'lib/spreadsheet/excel.rb', line 36

alias_method :new_format_column, :format_column

#protect!(password = "") ⇒ Object

Set worklist protection



143
144
145
146
147
148
149
150
151
 
# File 'lib/spreadsheet/worksheet.rb', line 143

def protect! password = ""
  @protected = true
  password = password.to_s
  @password_hash = if password.size == 0
    0
  else
    Excel::Password.password_hash password
  end
end

#protected? ⇒ Boolean

Is the worksheet protected?

Returns:

  • (Boolean) 


137
138
139
 
# File 'lib/spreadsheet/worksheet.rb', line 137

def protected?
  @protected
end

#replace_row(idx, *cells) ⇒ Object

Replace the Row at idx with the following arguments. Like #update_row, but truncates the Row if there are fewer arguments than Cells in the Row.



281
282
283
284
285
286
 
# File 'lib/spreadsheet/worksheet.rb', line 281

def replace_row idx, *cells
  if (row = @rows[idx]) && cells.size < row.size
    cells.concat Array.new(row.size - cells.size)
  end
  update_row idx, *cells
end

#row(idx) ⇒ Object

The Row at idx or a new Row.



290
291
292
 
# File 'lib/spreadsheet/worksheet.rb', line 290

def row idx
  @rows[idx] || Row.new(self, idx)
end

#row_count ⇒ Object

The number of Rows in this Worksheet which contain data.



296
297
298
 
# File 'lib/spreadsheet/worksheet.rb', line 296

def row_count
  dimensions[1] - dimensions[0]
end

#row_updated(idx, row) ⇒ Object

Tell Worksheet that the Row at idx has been updated and the #dimensions need to be recalculated. You should not need to call this directly.



303
304
305
306
 
# File 'lib/spreadsheet/worksheet.rb', line 303

def row_updated idx, row
  @dimensions = nil
  @rows[idx] = row
end

#update_row(idx, *cells) ⇒ Object

Updates the Row at idx with the following arguments.



310
311
312
313
314
315
316
317
318
319
 
# File 'lib/spreadsheet/worksheet.rb', line 310

def update_row idx, *cells
  res = if (row = @rows[idx])
    row[0, cells.size] = cells
    row
  else
    Row.new self, idx, cells
  end
  row_updated idx, res
  res
end

#updated_from(index) ⇒ Object

Renumbers all Rows starting at idx and calls #row_updated for each of them.



324
325
326
327
328
329
330
 
# File 'lib/spreadsheet/worksheet.rb', line 324

def updated_from index
  index.upto(@rows.size - 1) do |idx|
    row = row(idx)
    row.idx = idx
    row_updated idx, row
  end
end

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



45
46
47
48
49
50
51
52
53
 
# File 'lib/spreadsheet/excel.rb', line 45

def write row, col, data = nil, format = nil
  if data.is_a? Array
    write_row row, col, data, format
  else
    row = row(row)
    row[col] = data
    row.set_format col, format
  end
end

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



55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
# File 'lib/spreadsheet/excel.rb', line 55

def write_column row, col, data = nil, format = nil
  if data.is_a? Array
    data.each do |token|
      if token.is_a? Array
        write_row row, col, token, format
      else
        write row, col, token, format
      end
      row += 1
    end
  else
    write row, col, data, format
  end
end

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



70
71
72
73
74
75
76
77
78
79
80
81
82
83
 
# File 'lib/spreadsheet/excel.rb', line 70

def write_row row, col, data = nil, format = nil
  if data.is_a? Array
    data.each do |token|
      if token.is_a? Array
        write_column row, col, token, format
      else
        write row, col, token, format
      end
      col += 1
    end
  else
    write row, col, data, format
  end
end

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



85
86
87
 
# File 'lib/spreadsheet/excel.rb', line 85

def write_url row, col, url, string = url, format = nil
  row(row)[col] = Link.new url, string
end