Class: Spreadsheet::Worksheet
- Inherits:
-
Object
- Object
- Spreadsheet::Worksheet
- 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
Instance Attribute Summary collapse
-
#columns ⇒ Object
readonly
Returns the value of attribute columns.
-
#name ⇒ Object
Returns the value of attribute name.
-
#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 = , &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).
-
#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.
- #new_format_column ⇒ Object
-
#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
Constructor Details
#initialize(opts = {}) ⇒ Worksheet
Returns a new instance of Worksheet.
31 32 33 34 35 36 37 38 39 40 |
# File 'lib/spreadsheet/worksheet.rb', line 31 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
#columns ⇒ Object (readonly)
Returns the value of attribute columns.
30 31 32 |
# File 'lib/spreadsheet/worksheet.rb', line 30 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.
30 31 32 |
# File 'lib/spreadsheet/worksheet.rb', line 30 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#[].
245 246 247 |
# File 'lib/spreadsheet/worksheet.rb', line 245 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#[]=.
251 252 253 |
# File 'lib/spreadsheet/worksheet.rb', line 251 def []= row, column, value row(row)[column] = value end |
#active ⇒ Object
:nodoc:
41 42 43 44 |
# File 'lib/spreadsheet/worksheet.rb', line 41 def active # :nodoc: warn "Worksheet#active is deprecated. Please use Worksheet#selected instead." selected end |
#active=(selected) ⇒ Object
:nodoc:
45 46 47 48 |
# File 'lib/spreadsheet/worksheet.rb', line 45 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.
52 53 54 |
# File 'lib/spreadsheet/worksheet.rb', line 52 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#[].
58 59 60 |
# File 'lib/spreadsheet/worksheet.rb', line 58 def cell row, column row(row)[column] end |
#column(idx) ⇒ Object
Returns the Column at idx.
63 64 65 |
# File 'lib/spreadsheet/worksheet.rb', line 63 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.
68 69 70 |
# File 'lib/spreadsheet/worksheet.rb', line 68 def column_count dimensions[3] - dimensions[2] end |
#column_updated(idx, column) ⇒ Object
71 72 73 |
# File 'lib/spreadsheet/worksheet.rb', line 71 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.
84 85 86 |
# File 'lib/spreadsheet/worksheet.rb', line 84 def default_format @default_format || @workbook.default_format end |
#default_format=(format) ⇒ Object
Set the default Format of this Worksheet.
89 90 91 92 93 |
# File 'lib/spreadsheet/worksheet.rb', line 89 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.
76 77 78 79 80 |
# File 'lib/spreadsheet/worksheet.rb', line 76 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. )
100 101 102 |
# File 'lib/spreadsheet/worksheet.rb', line 100 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.
110 111 112 113 114 |
# File 'lib/spreadsheet/worksheet.rb', line 110 def each skip=dimensions[0], &block skip.upto(dimensions[1] - 1) do |idx| block.call row(idx) end end |
#encoding ⇒ Object
:nodoc:
115 116 117 |
# File 'lib/spreadsheet/worksheet.rb', line 115 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.
128 129 130 131 132 133 134 |
# File 'lib/spreadsheet/worksheet.rb', line 128 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
148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
# File 'lib/spreadsheet/worksheet.rb', line 148 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
172 173 174 175 176 |
# File 'lib/spreadsheet/worksheet.rb', line 172 def insert_row idx, cells=[] res = @rows.insert idx, Row.new(self, idx, cells) updated_from idx res end |
#inspect ⇒ Object
177 178 179 180 181 182 183 184 185 |
# File 'lib/spreadsheet/worksheet.rb', line 177 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
187 188 189 |
# File 'lib/spreadsheet/worksheet.rb', line 187 def last_row row(last_row_index) end |
#last_row_index ⇒ Object
The index of the last Row containing any data
191 192 193 |
# File 'lib/spreadsheet/worksheet.rb', line 191 def last_row_index [dimensions[1] - 1, 0].max end |
#new_format_column ⇒ Object
32 |
# File 'lib/spreadsheet/excel.rb', line 32 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.
197 198 199 200 201 202 |
# File 'lib/spreadsheet/worksheet.rb', line 197 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.
205 206 207 |
# File 'lib/spreadsheet/worksheet.rb', line 205 def row idx @rows[idx] || Row.new(self, idx) end |
#row_count ⇒ Object
The number of Rows in this Worksheet which contain data.
210 211 212 |
# File 'lib/spreadsheet/worksheet.rb', line 210 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.
216 217 218 219 |
# File 'lib/spreadsheet/worksheet.rb', line 216 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.
222 223 224 225 226 227 228 229 230 231 |
# File 'lib/spreadsheet/worksheet.rb', line 222 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.
235 236 237 238 239 240 241 |
# File 'lib/spreadsheet/worksheet.rb', line 235 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
41 42 43 44 45 46 47 48 49 |
# File 'lib/spreadsheet/excel.rb', line 41 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
50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
# File 'lib/spreadsheet/excel.rb', line 50 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
64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
# File 'lib/spreadsheet/excel.rb', line 64 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 |