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 Spreadsheet::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 Spreadsheet::Workbook#write, then the first Worksheet is selected by default.

Attributes

columns[R]
froze_left[R]
froze_top[R]
margins[R]
merged_cells[R]
name[RW]
pagesetup[R]
password_hash[RW]
rows[R]
selected[RW]
workbook[RW]

Public Class Methods

new(opts={}) click to toggle source
# 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

Public Instance Methods

[](row, column) click to toggle source

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
[]=(row, column, value) click to toggle source

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_format(fmt) click to toggle source

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
cell(row, column) click to toggle source

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

# File lib/spreadsheet/worksheet.rb, line 87
def cell row, column
  row(row)[column]
end
column(idx) click to toggle source

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
column_count() click to toggle source

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
column_updated(idx, column) click to toggle source
# File lib/spreadsheet/worksheet.rb, line 100
def column_updated idx, column
  @columns[idx] = column
end
compact!() click to toggle source
# 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
default_format() click to toggle source

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

# File lib/spreadsheet/worksheet.rb, line 113
def default_format
  @default_format || @workbook.default_format
end
default_format=(format) click to toggle source

Set the default Format of this Worksheet.

# File lib/spreadsheet/worksheet.rb, line 118
def default_format= format
  @default_format = format
  add_format format
  format
end
delete_row(idx) click to toggle source

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

# File lib/spreadsheet/worksheet.rb, line 105
def delete_row idx
  res = @rows.delete_at idx
  updated_from idx
  res
end
dimensions() click to toggle source
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
each(skip=dimensions[0]) { |row(idx)| ... } click to toggle source

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
format_column(column, width=nil, format=nil) click to toggle source
# 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
Also aliased as: new_format_column
format_dates!(format=nil) click to toggle source

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
freeze!(top, left) click to toggle source
# File lib/spreadsheet/worksheet.rb, line 66
def freeze!(top, left)
  @froze_top = top.to_i
  @froze_left = left.to_i
end
has_frozen_panel?() click to toggle source
# File lib/spreadsheet/worksheet.rb, line 62
def has_frozen_panel?
  @froze_top > 0 or @froze_left > 0
end
insert_row(idx, cells=[]) click to toggle source

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
inspect() click to toggle source
# 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
last_row() click to toggle source

The last Row containing any data

# File lib/spreadsheet/worksheet.rb, line 241
def last_row
  row(last_row_index)
end
last_row_index() click to toggle source

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
merge_cells(start_row, start_col, end_row, end_col) click to toggle source

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
new_format_column(column, width=nil, format=nil)
Alias for: format_column
protect!(password = '') click to toggle source

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
protected?() click to toggle source

Is the worksheet protected?

# File lib/spreadsheet/worksheet.rb, line 125
def protected?
  @protected
end
replace_row(idx, *cells) click to toggle source

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
row(idx) click to toggle source

The Row at idx or a new Row.

# File lib/spreadsheet/worksheet.rb, line 259
def row idx
  @rows[idx] || Row.new(self, idx)
end
row_count() click to toggle source

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
row_updated(idx, row) click to toggle source

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
update_row(idx, *cells) click to toggle source

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
updated_from(index) click to toggle source

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
write(row, col, data=nil, format=nil) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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) click to toggle source
# 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