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

#initialize(opts = {}) ⇒ Worksheet

Returns a new instance of Worksheet.



32
33
34
35
36
37
38
39
40
41
 
# 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 = {}
end

Instance Attribute Details

#autofilter_enabled ⇒ Object (readonly)

Returns the value of attribute autofilter_enabled.



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

def autofilter_enabled
  @autofilter_enabled
end

#autofilter_left_column_index ⇒ Object (readonly)

Returns the value of attribute autofilter_left_column_index.



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

def autofilter_left_column_index
  @autofilter_left_column_index
end

#autofilter_right_column_index ⇒ Object (readonly)

Returns the value of attribute autofilter_right_column_index.



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

def autofilter_right_column_index
  @autofilter_right_column_index
end

#columns ⇒ Object (readonly)

Returns the value of attribute columns.



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

def columns
  @columns
end

#name ⇒ Object

Returns the value of attribute name.



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

def name
  @name
end

#rows ⇒ Object (readonly)

Returns the value of attribute rows.



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

def rows
  @rows
end

#selected ⇒ Object

Returns the value of attribute selected.



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

def selected
  @selected
end

#workbook ⇒ Object

Returns the value of attribute workbook.



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

def workbook
  @workbook
end

Instance Method Details

#[](row, column) ⇒ Object

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



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

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#[]=.



252
253
254
 
# File 'lib/spreadsheet/worksheet.rb', line 252

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

#active ⇒ Object

:nodoc:



42
43
44
45
 
# File 'lib/spreadsheet/worksheet.rb', line 42

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

#active=(selected) ⇒ Object

:nodoc:



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

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.



53
54
55
 
# File 'lib/spreadsheet/worksheet.rb', line 53

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#[].



59
60
61
 
# File 'lib/spreadsheet/worksheet.rb', line 59

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

#column(idx) ⇒ Object

Returns the Column at idx.



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

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.



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

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

#column_updated(idx, column) ⇒ Object 



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

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

#default_format ⇒ Object

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



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

def default_format
  @default_format || @workbook.default_format
end

#default_format=(format) ⇒ Object

Set the default Format of this Worksheet.



90
91
92
93
94
 
# File 'lib/spreadsheet/worksheet.rb', line 90

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

#delete_row(idx) ⇒ Object

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



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

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



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

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.



111
112
113
114
115
 
# File 'lib/spreadsheet/worksheet.rb', line 111

def each skip=dimensions[0], &block
  skip.upto(dimensions[1] - 1) do |idx|
    block.call row(idx)
  end
end

#enable_autofilter(number_of_autofilter_columns) ⇒ Object

enable autofiltering on this sheet, NOTE autofiltering can (for now) only be done on 1 sheet and on the first row starting from column on left_column_index (0 based) up to right column_index (0 based)



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

def enable_autofilter(number_of_autofilter_columns)
  @autofilter_enabled = true
  @autofilter_left_column_index = 0
  @autofilter_right_column_index = number_of_autofilter_columns - 1
end

#encoding ⇒ Object

:nodoc:



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

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.



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

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



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/spreadsheet/worksheet.rb', line 149

def format_dates! format=nil
  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 || client('DD.MM.YYYY hh:mm:ss', 'UTF-8')
                 when Date
                   format || client('DD.MM.YYYY', 'UTF-8')
                 end
        case numfmt
        when Format
          row.set_format idx, numfmt
        when String
          fmt = row.format(idx).dup
          fmt.number_format = numfmt
          row.set_format idx, fmt
        end
      end
    end
  end
end

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

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



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

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

#inspect ⇒ Object 



178
179
180
181
182
183
184
185
186
 
# File 'lib/spreadsheet/worksheet.rb', line 178

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



188
189
190
 
# File 'lib/spreadsheet/worksheet.rb', line 188

def last_row
  row(last_row_index)
end

#last_row_index ⇒ Object

The index of the last Row containing any data



192
193
194
 
# File 'lib/spreadsheet/worksheet.rb', line 192

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

#new_format_column ⇒ Object 



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

alias :new_format_column :format_column

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



198
199
200
201
202
203
 
# File 'lib/spreadsheet/worksheet.rb', line 198

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.



206
207
208
 
# File 'lib/spreadsheet/worksheet.rb', line 206

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

#row_count ⇒ Object

The number of Rows in this Worksheet which contain data.



211
212
213
 
# File 'lib/spreadsheet/worksheet.rb', line 211

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.



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

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.



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

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.



236
237
238
239
240
241
242
 
# File 'lib/spreadsheet/worksheet.rb', line 236

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 



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

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



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

#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 



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