class Spreadsheet::Worksheet
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, callupdated_from
with the smallest modified index. columns
-
The
Column
formatting in thisWorksheet
.Column
instances may appear at more than one position incolumns
. If you modify aColumn
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 noWorksheet
is selected atWorkbook#write
, then the firstWorksheet
is selected by default.
Attributes
Public Class Methods
# File lib/spreadsheet/worksheet.rb, line 35 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
Public Instance Methods
# File lib/spreadsheet/worksheet.rb, line 235 def << cells=[] insert_row @rows.size, cells end
Get the enriched value of the Cell at row, column. See also Worksheet#cell
, Row#[].
# File lib/spreadsheet/worksheet.rb, line 306 def [] row, column row(row)[column] end
Set the value of the Cell at row, column to value. See also Row#[]=.
# File lib/spreadsheet/worksheet.rb, line 312 def []= row, column, value row(row)[column] = value end
Add a Format
to the Workbook
. If you use Row#set_format
, you should not need to use this Method.
# File lib/spreadsheet/worksheet.rb, line 81 def add_format fmt @workbook.add_format fmt if fmt end
Get the enriched value of the Cell at row, column. See also Worksheet#[]
, Row#[].
# File lib/spreadsheet/worksheet.rb, line 87 def cell row, column row(row)[column] end
Returns the Column
at idx.
# File lib/spreadsheet/worksheet.rb, line 92 def column idx @columns[idx] || Column.new(idx, default_format, :worksheet => self) end
The number of columns in this Worksheet
which contain data.
# File lib/spreadsheet/worksheet.rb, line 97 def column_count dimensions[3] - dimensions[2] end
# File lib/spreadsheet/worksheet.rb, line 100 def column_updated idx, column @columns[idx] = column end
# File lib/spreadsheet/worksheet.rb, line 322 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) do if row(row_size).empty? || row(row_size).compact.join('').empty? row_size = @dimensions[1] + (((row_size - @dimensions[1]) + 1) / 2).to_i else _i = ((row_size - @dimensions[1]) / 2).to_i + 1 @dimensions[1] = row_size row_size = row_size + _i 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] = @dimensions[3] end end
- 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. )
# File lib/spreadsheet/worksheet.rb, line 146 def dimensions @dimensions || recalculate_dimensions end
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
.
# File lib/spreadsheet/worksheet.rb, line 156 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
# File lib/spreadsheet/excel.rb, line 33 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
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
# File lib/spreadsheet/worksheet.rb, line 198 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
# File lib/spreadsheet/worksheet.rb, line 66 def freeze!(top, left) @froze_top = top.to_i @froze_left = left.to_i end
# File lib/spreadsheet/worksheet.rb, line 62 def has_frozen_panel? @froze_top > 0 or @froze_left > 0 end
Insert a Row
at idx (0-based) containing cells
# File lib/spreadsheet/worksheet.rb, line 230 def insert_row idx, cells=[] res = @rows.insert idx, Row.new(self, idx, cells) updated_from idx res end
# File lib/spreadsheet/worksheet.rb, line 238 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
The last Row
containing any data
# File lib/spreadsheet/worksheet.rb, line 248 def last_row row(last_row_index) end
The index of the last Row
containing any data
# File lib/spreadsheet/worksheet.rb, line 252 def last_row_index [dimensions[1] - 1, 0].max end
Merges multiple cells into one.
# File lib/spreadsheet/worksheet.rb, line 317 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
Set worklist protection
# File lib/spreadsheet/worksheet.rb, line 130 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
Is the worksheet protected?
# File lib/spreadsheet/worksheet.rb, line 125 def protected? @protected end
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
.
# File lib/spreadsheet/worksheet.rb, line 258 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
The number of Rows in this Worksheet
which contain data.
# File lib/spreadsheet/worksheet.rb, line 271 def row_count dimensions[1] - dimensions[0] end
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.
# File lib/spreadsheet/worksheet.rb, line 277 def row_updated idx, row @dimensions = nil @rows[idx] = row end
Updates the Row
at idx with the following arguments.
# File lib/spreadsheet/worksheet.rb, line 283 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
Renumbers all Rows starting at idx and calls row_updated
for each of them.
# File lib/spreadsheet/worksheet.rb, line 296 def updated_from index index.upto(@rows.size - 1) do |idx| row = row(idx) row.idx = idx row_updated idx, row end end
# 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
# 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
# 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
# File lib/spreadsheet/excel.rb, line 78 def write_url row, col, url, string=url, format=nil row(row)[col] = Link.new url, string end