Class: Spreadsheet::Worksheet
- Inherits:
-
Object
- Object
- Spreadsheet::Worksheet
- 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
Instance Attribute Summary collapse
-
#columns ⇒ Object
readonly
Returns the value of attribute columns.
-
#margins ⇒ Object
readonly
Returns the value of attribute margins.
-
#merged_cells ⇒ Object
readonly
Returns the value of attribute merged_cells.
-
#name ⇒ Object
Returns the value of attribute name.
-
#pagesetup ⇒ Object
readonly
Returns the value of attribute pagesetup.
-
#password_hash ⇒ Object
Returns the value of attribute password_hash.
-
#rows ⇒ Object
readonly
Returns the value of attribute rows.
-
#selected ⇒ Object
Returns the value of attribute selected.
-
#workbook ⇒ Object
Returns the value of attribute workbook.
Instance Method Summary collapse
-
#[](row, column) ⇒ Object
Get the enriched value of the Cell at row, column.
-
#[]=(row, column, value) ⇒ Object
Set the value of the Cell at row, column to value.
-
#active ⇒ Object
:nodoc:.
-
#active=(selected) ⇒ Object
:nodoc:.
-
#add_format(fmt) ⇒ Object
Add a Format to the Workbook.
-
#cell(row, column) ⇒ Object
Get the enriched value of the Cell at row, column.
-
#column(idx) ⇒ Object
Returns the Column at idx.
-
#column_count ⇒ Object
The number of columns in this Worksheet which contain data.
- #column_updated(idx, column) ⇒ Object
-
#default_format ⇒ Object
The default Format of this Worksheet, if you have set one.
-
#default_format=(format) ⇒ Object
Set the default Format of this Worksheet.
-
#delete_row(idx) ⇒ Object
Delete the Row at idx (0-based) from this Worksheet.
-
#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. ).
-
#each(skip = ) ⇒ 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).
-
#encoding ⇒ Object
:nodoc:.
-
#format_column(idx, format = nil, opts = {}) ⇒ Object
Sets the default Format of the column at idx.
-
#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.
-
#initialize(opts = {}) ⇒ Worksheet
constructor
A new instance of Worksheet.
-
#insert_row(idx, cells = []) ⇒ Object
Insert a Row at idx (0-based) containing cells.
- #inspect ⇒ Object
-
#last_row ⇒ Object
The last Row containing any data.
-
#last_row_index ⇒ Object
The index of the last Row containing any data.
-
#merge_cells(start_row, start_col, end_row, end_col) ⇒ Object
Merges multiple cells into one.
- #new_format_column ⇒ Object
-
#protect!(password = '') ⇒ Object
Set worklist protection.
-
#protected? ⇒ Boolean
Is the worksheet protected?.
-
#replace_row(idx, *cells) ⇒ Object
Replace the Row at idx with the following arguments.
-
#row(idx) ⇒ Object
The Row at idx or a new Row.
-
#row_count ⇒ Object
The number of Rows in this Worksheet which contain data.
-
#row_updated(idx, row) ⇒ Object
Tell Worksheet that the Row at idx has been updated and the #dimensions need to be recalculated.
-
#update_row(idx, *cells) ⇒ Object
Updates the Row at idx with the following arguments.
-
#updated_from(index) ⇒ Object
Renumbers all Rows starting at idx and calls #row_updated for each of them.
- #write(row, col, data = nil, format = nil) ⇒ Object
- #write_column(row, col, data = nil, format = nil) ⇒ Object
- #write_row(row, col, data = nil, format = nil) ⇒ Object
- #write_url(row, col, url, string = url, format = nil) ⇒ Object
Methods included from Datatypes
Methods included from Compatibility
Constructor Details
#initialize(opts = {}) ⇒ Worksheet
Returns a new instance of Worksheet.
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
# File 'lib/spreadsheet/worksheet.rb', line 34 def initialize opts={} @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 = 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.
32 33 34 |
# File 'lib/spreadsheet/worksheet.rb', line 32 def columns @columns end |
#margins ⇒ Object (readonly)
Returns the value of attribute margins.
32 33 34 |
# File 'lib/spreadsheet/worksheet.rb', line 32 def margins @margins end |
#merged_cells ⇒ Object (readonly)
Returns the value of attribute merged_cells.
32 33 34 |
# File 'lib/spreadsheet/worksheet.rb', line 32 def merged_cells @merged_cells end |
#name ⇒ Object
Returns the value of attribute name.
31 32 33 |
# File 'lib/spreadsheet/worksheet.rb', line 31 def name @name end |
#pagesetup ⇒ Object (readonly)
Returns the value of attribute pagesetup.
32 33 34 |
# File 'lib/spreadsheet/worksheet.rb', line 32 def pagesetup @pagesetup end |
#password_hash ⇒ Object
Returns the value of attribute password_hash.
31 32 33 |
# File 'lib/spreadsheet/worksheet.rb', line 31 def password_hash @password_hash end |
#rows ⇒ Object (readonly)
Returns the value of attribute rows.
32 33 34 |
# File 'lib/spreadsheet/worksheet.rb', line 32 def rows @rows end |
#selected ⇒ Object
Returns the value of attribute selected.
31 32 33 |
# File 'lib/spreadsheet/worksheet.rb', line 31 def selected @selected end |
#workbook ⇒ Object
Returns the value of attribute workbook.
31 32 33 |
# File 'lib/spreadsheet/worksheet.rb', line 31 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#[].
288 289 290 |
# File 'lib/spreadsheet/worksheet.rb', line 288 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#[]=.
294 295 296 |
# File 'lib/spreadsheet/worksheet.rb', line 294 def []= row, column, value row(row)[column] = value end |
#active ⇒ Object
:nodoc:
59 60 61 62 |
# File 'lib/spreadsheet/worksheet.rb', line 59 def active # :nodoc: warn "Worksheet#active is deprecated. Please use Worksheet#selected instead." selected end |
#active=(selected) ⇒ Object
:nodoc:
63 64 65 66 |
# File 'lib/spreadsheet/worksheet.rb', line 63 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.
70 71 72 |
# File 'lib/spreadsheet/worksheet.rb', line 70 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#[].
76 77 78 |
# File 'lib/spreadsheet/worksheet.rb', line 76 def cell row, column row(row)[column] end |
#column(idx) ⇒ Object
Returns the Column at idx.
81 82 83 |
# File 'lib/spreadsheet/worksheet.rb', line 81 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.
86 87 88 |
# File 'lib/spreadsheet/worksheet.rb', line 86 def column_count dimensions[3] - dimensions[2] end |
#column_updated(idx, column) ⇒ Object
89 90 91 |
# File 'lib/spreadsheet/worksheet.rb', line 89 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.
102 103 104 |
# File 'lib/spreadsheet/worksheet.rb', line 102 def default_format @default_format || @workbook.default_format end |
#default_format=(format) ⇒ Object
Set the default Format of this Worksheet.
107 108 109 110 111 |
# File 'lib/spreadsheet/worksheet.rb', line 107 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.
94 95 96 97 98 |
# File 'lib/spreadsheet/worksheet.rb', line 94 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. )
135 136 137 |
# File 'lib/spreadsheet/worksheet.rb', line 135 def dimensions @dimensions || recalculate_dimensions end |
#each(skip = ) ⇒ 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.
145 146 147 148 149 |
# File 'lib/spreadsheet/worksheet.rb', line 145 def each skip=dimensions[0] skip.upto(dimensions[1] - 1) do |idx| yield row(idx) end end |
#encoding ⇒ Object
:nodoc:
150 151 152 |
# File 'lib/spreadsheet/worksheet.rb', line 150 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.
163 164 165 166 167 168 169 |
# File 'lib/spreadsheet/worksheet.rb', line 163 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
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 |
# File 'lib/spreadsheet/worksheet.rb', line 183 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 |
#insert_row(idx, cells = []) ⇒ Object
Insert a Row at idx (0-based) containing cells
215 216 217 218 219 |
# File 'lib/spreadsheet/worksheet.rb', line 215 def insert_row idx, cells=[] res = @rows.insert idx, Row.new(self, idx, cells) updated_from idx res end |
#inspect ⇒ Object
220 221 222 223 224 225 226 227 228 |
# File 'lib/spreadsheet/worksheet.rb', line 220 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
230 231 232 |
# File 'lib/spreadsheet/worksheet.rb', line 230 def last_row row(last_row_index) end |
#last_row_index ⇒ Object
The index of the last Row containing any data
234 235 236 |
# File 'lib/spreadsheet/worksheet.rb', line 234 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.
299 300 301 302 |
# File 'lib/spreadsheet/worksheet.rb', line 299 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
38 |
# File 'lib/spreadsheet/excel.rb', line 38 alias :new_format_column :format_column |
#protect!(password = '') ⇒ Object
Set worklist protection
119 120 121 122 123 124 125 126 127 |
# File 'lib/spreadsheet/worksheet.rb', line 119 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 |
#protected? ⇒ Boolean
Is the worksheet protected?
114 115 116 |
# File 'lib/spreadsheet/worksheet.rb', line 114 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.
240 241 242 243 244 245 |
# File 'lib/spreadsheet/worksheet.rb', line 240 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.
248 249 250 |
# File 'lib/spreadsheet/worksheet.rb', line 248 def row idx @rows[idx] || Row.new(self, idx) end |
#row_count ⇒ Object
The number of Rows in this Worksheet which contain data.
253 254 255 |
# File 'lib/spreadsheet/worksheet.rb', line 253 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.
259 260 261 262 |
# File 'lib/spreadsheet/worksheet.rb', line 259 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.
265 266 267 268 269 270 271 272 273 274 |
# File 'lib/spreadsheet/worksheet.rb', line 265 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.
278 279 280 281 282 283 284 |
# File 'lib/spreadsheet/worksheet.rb', line 278 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 |