The Worksheet class. Contains most of the Spreadsheet data in Rows.
Interesting Attributes
The Name of this Worksheet.
The default format used for all cells in this Workhseet that have no format set explicitly or in Spreadsheet::Row#default_format.
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.
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.
When a user chooses to print a Workbook, Excel will include all selected Worksheets. If no Worksheet is selected at Spreadsheet::Workbook#write, then the first Worksheet is selected by default.
# 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 = opts[:name] || 'Worksheet' @workbook = opts[:workbook] @rows = [] @columns = [] @links = {} @merged_cells = [] @protected = false @password_hash = 0 @visibility = opts[:visibility] end
Get the enriched value of the Cell at row, column. See also #cell, Row#[].
# File lib/spreadsheet/worksheet.rb, line 299 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 305 def []= row, column, value row(row)[column] = value end
Add a Format to the Workbook. If you use Spreadsheet::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 315 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? i = @dimensions[1] - 1 @dimensions[1] = @dimensions[0] # divide and conquer while(i - @dimensions[1] > 1) do if row(i).empty? || row(i).compact.join('').empty? i = @dimensions[1] + (((i - @dimensions[1]) + 1) / 2).to_i else _i = ((i - @dimensions[1]) / 2).to_i + 1 @dimensions[1] = i i = i + _i end end @dimensions[1] = i + 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
[ 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] skip.upto(dimensions[1] - 1) do |idx| yield row(idx) end end
# File lib/spreadsheet/excel.rb, line 39 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 194 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 226 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 231 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 241 def last_row row(last_row_index) end
The index of the last Row containing any data
# File lib/spreadsheet/worksheet.rb, line 245 def last_row_index [dimensions[1] - 1, 0].max end
Merges multiple cells into one.
# File lib/spreadsheet/worksheet.rb, line 310 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 251 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 264 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 270 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 276 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 289 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 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
# 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
# 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
# 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