Class: Spreadsheet::Worksheet

Inherits:
Object
  • Object
show all
Includes:
Enumerable, 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)

Constructor Details

- (Worksheet) initialize(opts = {})

A new instance of Worksheet



32
33
34
35
36
37
38
39
40
41
42
43
44
# File 'lib/spreadsheet/worksheet.rb', line 32

def initialize opts={}
  @default_format = nil
  @selected = opts[:selected]
  @dimensions = [0,0,0,0]
  @name = opts[:name] || 'Worksheet'
  @workbook = opts[:workbook]
  @rows = []
  @columns = []
  @links = {}
  @merged_cells = []
  @protected = false
  @password_hash = 0
end

Instance Attribute Details

- (Object) columns (readonly)

Returns the value of attribute columns



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

def columns
  @columns
end

- (Object) merged_cells (readonly)

Returns the value of attribute merged_cells



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

def merged_cells
  @merged_cells
end

- (Object) name

Returns the value of attribute name



30
31
32
# File 'lib/spreadsheet/worksheet.rb', line 30

def name
  @name
end

- (Object) password_hash

Returns the value of attribute password_hash



30
31
32
# File 'lib/spreadsheet/worksheet.rb', line 30

def password_hash
  @password_hash
end

- (Object) rows (readonly)

Returns the value of attribute rows



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

def rows
  @rows
end

- (Object) selected

Returns the value of attribute selected



30
31
32
# File 'lib/spreadsheet/worksheet.rb', line 30

def selected
  @selected
end

- (Object) workbook

Returns the value of attribute workbook



30
31
32
# File 'lib/spreadsheet/worksheet.rb', line 30

def workbook
  @workbook
end

Instance Method Details

- (Object) [](row, column)

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



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

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

- (Object) []=(row, column, value)

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



280
281
282
# File 'lib/spreadsheet/worksheet.rb', line 280

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

- (Object) active

:nodoc:



45
46
47
48
# File 'lib/spreadsheet/worksheet.rb', line 45

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

- (Object) active=(selected)

:nodoc:



49
50
51
52
# File 'lib/spreadsheet/worksheet.rb', line 49

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

- (Object) add_format(fmt)

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



56
57
58
# File 'lib/spreadsheet/worksheet.rb', line 56

def add_format fmt
  @workbook.add_format fmt if fmt
end

- (Object) cell(row, column)

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



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

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

- (Object) column(idx)

Returns the Column at idx.



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

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

- (Object) column_count

The number of columns in this Worksheet which contain data.



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

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

- (Object) column_updated(idx, column)



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

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

- (Object) default_format

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



88
89
90
# File 'lib/spreadsheet/worksheet.rb', line 88

def default_format
  @default_format || @workbook.default_format
end

- (Object) default_format=(format)

Set the default Format of this Worksheet.



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

def default_format= format
  @default_format = format
  add_format format
  format
end

- (Object) delete_row(idx)

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



80
81
82
83
84
# File 'lib/spreadsheet/worksheet.rb', line 80

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

- (Object) dimensions

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. )



121
122
123
# File 'lib/spreadsheet/worksheet.rb', line 121

def dimensions
  @dimensions || recalculate_dimensions
end

- (Object) each(skip = dimensions[0])

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.



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

def each skip=dimensions[0]
  skip.upto(dimensions[1] - 1) do |idx|
    yield row(idx)
  end
end

- (Object) encoding

:nodoc:



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

def encoding # :nodoc:
  @workbook.encoding
end

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

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.



149
150
151
152
153
154
155
# File 'lib/spreadsheet/worksheet.rb', line 149

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

- (Object) format_dates!(format = nil)

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



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

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

- (Object) insert_row(idx, cells = [])

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



201
202
203
204
205
# File 'lib/spreadsheet/worksheet.rb', line 201

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

- (Object) inspect



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

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

- (Object) last_row

The last Row containing any data



216
217
218
# File 'lib/spreadsheet/worksheet.rb', line 216

def last_row
  row(last_row_index)
end

- (Object) last_row_index

The index of the last Row containing any data



220
221
222
# File 'lib/spreadsheet/worksheet.rb', line 220

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

- (Object) merge_cells(start_row, start_col, end_row, end_col)

Merges multiple cells into one.



285
286
287
288
# File 'lib/spreadsheet/worksheet.rb', line 285

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

- (Object) new_format_column



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

alias :new_format_column :format_column

- (Object) protect!(password = '')

Set worklist protection



105
106
107
108
109
110
111
112
113
# File 'lib/spreadsheet/worksheet.rb', line 105

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

- (Boolean) protected?

Is the worksheet protected?

Returns:

  • (Boolean)


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

def protected?
  @protected
end

- (Object) replace_row(idx, *cells)

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.



226
227
228
229
230
231
# File 'lib/spreadsheet/worksheet.rb', line 226

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

- (Object) row(idx)

The Row at idx or a new Row.



234
235
236
# File 'lib/spreadsheet/worksheet.rb', line 234

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

- (Object) row_count

The number of Rows in this Worksheet which contain data.



239
240
241
# File 'lib/spreadsheet/worksheet.rb', line 239

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

- (Object) row_updated(idx, row)

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.



245
246
247
248
# File 'lib/spreadsheet/worksheet.rb', line 245

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

- (Object) update_row(idx, *cells)

Updates the Row at idx with the following arguments.



251
252
253
254
255
256
257
258
259
260
# File 'lib/spreadsheet/worksheet.rb', line 251

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

- (Object) updated_from(index)

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



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

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

- (Object) write(row, col, data = nil, format = nil)



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

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

- (Object) write_column(row, col, data = nil, format = nil)



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

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

- (Object) write_row(row, col, data = nil, format = nil)



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

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



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

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