class RubyExcel::Sheet

The front-end class for data manipulation and output.

Attributes

data[R]

The Data underlying the Sheet

header_rows[RW]

The number of rows treated as headers

headers[RW]

The number of rows treated as headers

headers=[RW]

The number of rows treated as headers

name[RW]

The name of the Sheet

parent[RW]

The Workbook parent of this Sheet

parent=[RW]

The Workbook parent of this Sheet

workbook[RW]

The Workbook parent of this Sheet

Public Class Methods

new( name, workbook ) click to toggle source

Creates a RubyExcel::Sheet instance

@param [String] name the name of the Sheet @param [RubyExcel::Workbook] workbook the Workbook which holds this Sheet

# File lib/rubyexcel/sheet.rb, line 36
def initialize( name, workbook )
  @workbook = workbook
  @name = name
  @header_rows = 1
  @data = Data.new( self, [[]] )
end

Public Instance Methods

+( other ) click to toggle source

Add data with the Sheet

@param [Array<Array>, Hash<Hash>, RubyExcel::Sheet] other the data to add @return [RubyExcel::Sheet] returns a new Sheet @note When adding another Sheet it won’t import the headers unless this Sheet is empty.

# File lib/rubyexcel/sheet.rb, line 85
def +( other )
  dup << other
end
-( other ) click to toggle source

Subtract data from the Sheet

@param [Array<Array>, RubyExcel::Sheet] other the data to subtract @return [RubyExcel::Sheet] returns a new Sheet

# File lib/rubyexcel/sheet.rb, line 96
def -( other )
  case other
  when Array ; Workbook.new.load( data.all - other )
  when Sheet ; Workbook.new.load( data.all - other.data.no_headers )
  else       ; fail ArgumentError, "Unsupported class: #{ other.class }"
  end
end
<<( other ) click to toggle source

Append an object to the Sheet

@param [Object] other the object to append @return [self] @note When adding another Sheet it won’t import the headers unless this Sheet is empty. @note Anything other than an an Array, Hash, Row, Column or Sheet will be appended to the first row

# File lib/rubyexcel/sheet.rb, line 113
def <<( other )
  data << other
  self
end
[]( addr ) click to toggle source

Read a value by address

@example

sheet['A1']
#=> "Part"

@example

sheet['A1:B2']
#=> [["Part", "Ref1"], ["Type1", "QT1"]]

@param [String] addr the address to access

# File lib/rubyexcel/sheet.rb, line 57
def[]( addr )
  range( addr ).value
end
[]=( addr, val ) click to toggle source

Write a value by address

@example

sheet['A1'] = "Bart"
sheet['A1']
#=> "Bart"

@param (see []) @param [Object] val the value to write into the data

# File lib/rubyexcel/sheet.rb, line 73
def []=( addr, val )
  range( addr ).value = val
end
advanced_filter!( *args ) click to toggle source

@deprecated Please use {#filter!} instead @overload advanced_filter!( header, comparison_operator, search_criteria, … )

Filter on multiple criteria

@param [String] header a header to search under @param [Symbol] comparison_operator the operator to compare with @param [Object] search_criteria the value to filter by @raise [ArgumentError] ‘Number of arguments must be a multiple of 3’ @raise [ArgumentError] ‘Operator must be a symbol’ @example Filter to ‘Part’: ‘Type1’ and ‘Type3’, with Qty greater than 1

s.advanced_filter!( 'Part', :=~, /Type[13]/, 'Qty', :>, 1 )

@example Filter to ‘Part’: ‘Type1’, with ‘Ref1’ containing ‘X’

s.advanced_filter!( 'Part', :==, 'Type1', 'Ref1', :include?, 'X' )
# File lib/rubyexcel/sheet.rb, line 132
def advanced_filter!( *args )
  warn "[DEPRECATION] `advanced_filter!` is deprecated.  Please use `filter!` instead."
  data.advanced_filter!( *args ); self
end
averageif( find_header, avg_header ) { |value| ... } click to toggle source

Average the values in a Column by searching another Column

@param [String] find_header the header of the Column to yield to the block @param [String] avg_header the header of the Column to average @yield yields the find_header column values to the block

# File lib/rubyexcel/sheet.rb, line 145
def averageif( find_header, avg_header )
  return to_enum( :sumif ) unless block_given?
  find_col, avg_col  = ch( find_header ), ch( avg_header )
  sum = find_col.each_cell_wh.inject([0,0]) do |sum,ce|
    if yield( ce.value )
      sum[0] += avg_col[ ce.row ]
      sum[1] += 1
      sum 
    else
      sum
    end
  end
  sum.first.to_f / sum.last
end
cell( row, col ) click to toggle source

Access an Cell by indices.

@param [Fixnum] row the row index @param [Fixnum] col the column index @return [RubyExcel::Cell] @note Indexing is 1-based like Excel VBA

# File lib/rubyexcel/sheet.rb, line 169
def cell( row, col )
  Cell.new( self, indices_to_address( row, col ) )
end
Also aliased as: cells
cells( row, col )
Alias for: cell
ch( header )
Alias for: column_by_header
clear_all() click to toggle source

Delete all data and headers from Sheet

# File lib/rubyexcel/sheet.rb, line 178
def clear_all
  data.delete_all
  self
end
Also aliased as: delete_all
column( index ) click to toggle source

Access a Column (Section) by its reference.

@param [String, Fixnum] index the Column reference @return [RubyExcel::Column] @note Index ‘A’ and 1 both select the 1st Column

# File lib/rubyexcel/sheet.rb, line 192
def column( index )
  Column.new( self, col_letter( index ) )
end
column_by_header( header ) click to toggle source

Access a Column (Section) by its header.

@param [String] header the Column header @return [RubyExcel::Column]

# File lib/rubyexcel/sheet.rb, line 203
def column_by_header( header )
  header.is_a?( Column ) ? header : Column.new( self, data.colref_by_header( header ) )
end
Also aliased as: ch
columns( start_column = 'A', end_column = data.cols ) { |column( idx )| ... } click to toggle source

Yields each Column to the block

@param [String, Fixnum] start_column the Column to start looping from @param [String, Fixnum] end_column the Column to end the loop at @note Iterates to the last Column in the Sheet unless given a second argument.

# File lib/rubyexcel/sheet.rb, line 216
def columns( start_column = 'A', end_column = data.cols )
  return to_enum( :columns, start_column, end_column ) unless block_given?
  ( col_letter( start_column )..col_letter( end_column ) ).each { |idx| yield column( idx ) }
  self
end
compact!() click to toggle source

Removes empty Columns and Rows

# File lib/rubyexcel/sheet.rb, line 226
def compact!
  data.compact!; self
end
delete() click to toggle source

Removes Sheet from the parent Workbook

# File lib/rubyexcel/sheet.rb, line 234
def delete
  workbook.delete self
end
delete_all()
Alias for: clear_all
delete_columns_if() { |c| ... } click to toggle source

Deletes each Column where the block is true

# File lib/rubyexcel/sheet.rb, line 251
def delete_columns_if
  return to_enum( :delete_columns_if ) unless block_given?
  columns.reverse_each { |c| c.delete if yield c }; self
end
delete_rows_if() { |r| ... } click to toggle source

Deletes each Row where the block is true

# File lib/rubyexcel/sheet.rb, line 242
def delete_rows_if
  return to_enum( :delete_rows_if ) unless block_given?
  rows.reverse_each { |r| r.delete if yield r }; self
end
dup() click to toggle source

Return a copy of self

@return [RubyExcel::Sheet]

# File lib/rubyexcel/sheet.rb, line 262
def dup
  s = Sheet.new( name, workbook )
  d = data
  unless d.nil?
    d = d.dup
    s.load( d.all, header_rows )
    d.sheet = s
  end
  s
end
each( start_row = 1, end_row = data.rows )
Alias for: rows
empty?() click to toggle source

Check whether the Sheet contains data (not counting headers)

@return [Boolean] if there is any data

# File lib/rubyexcel/sheet.rb, line 279
def empty?
  data.empty?
end
export( win32ole_sheet ) click to toggle source

Export data to a specific WIN32OLE Excel Sheet

@param win32ole_sheet the Sheet to export to @return WIN32OLE Sheet

# File lib/rubyexcel/sheet.rb, line 290
def export( win32ole_sheet )
  parent.dump_to_sheet( to_a, win32ole_sheet )
end
filter!( *headers, &block ) click to toggle source

Removes all Rows (omitting headers) where the block is falsey

@param [String, Array] headers splat of the headers for the Columns to filter by @yield [Array] the values at the intersections of Column and Row @return [self]

# File lib/rubyexcel/sheet.rb, line 302
def filter!( *headers, &block )
  return to_enum( :filter!, headers ) unless block_given?
  data.filter!( *headers, &block ); self
end
gc!( *headers )
Alias for: get_columns!
get_columns!( *headers ) click to toggle source

Select and re-order Columns by a list of headers

@param [Array<String>] headers the ordered list of headers to keep @note This method can accept either a list of arguments or an Array @note Invalid headers will be skipped

# File lib/rubyexcel/sheet.rb, line 315
def get_columns!( *headers )
  data.get_columns!( *headers ); self
end
Also aliased as: gc!
insert_columns( *args ) click to toggle source

@overload insert_columns( before, number=1 )

Insert blank Columns into the data

@param [String, Fixnum] before the Column reference to insert before.
@param [Fixnum] number the number of new Columns to insert
# File lib/rubyexcel/sheet.rb, line 327
def insert_columns( *args )
  data.insert_columns( *args ); self
end
insert_rows( *args ) click to toggle source

@overload insert_rows( before, number=1 )

Insert blank Rows into the data

@param [Fixnum] before the Row index to insert before.
@param [Fixnum] number the number of new Rows to insert
# File lib/rubyexcel/sheet.rb, line 338
def insert_rows( *args )
  data.insert_rows( *args ); self
end
inspect() click to toggle source

View the object for debugging

# File lib/rubyexcel/sheet.rb, line 346
def inspect
  "#{ self.class }:0x#{ '%x' % (object_id << 1) }: #{ name }"
end
last_col()
Alias for: last_column
last_column() click to toggle source

The last Column in the Sheet

@return [RubyExcel::Column]

# File lib/rubyexcel/sheet.rb, line 356
def last_column
  column( maxcol )
end
Also aliased as: last_col
last_row() click to toggle source

The last Row in the Sheet

@return [RubyExcel::Row]

# File lib/rubyexcel/sheet.rb, line 367
def last_row
  row( maxrow )
end
length()
Alias for: maxrow
load( input_data, header_rows=1 ) click to toggle source

Populate the Sheet with data (overwrite)

@param [Array<Array>, Hash<Hash>] input_data the data to fill the Sheet with @param header_rows [Fixnum] the number of Rows to be treated as headers

# File lib/rubyexcel/sheet.rb, line 378
def load( input_data, header_rows=1 )
  input_data = _convert_hash(input_data) if input_data.is_a?(Hash)
  input_data.is_a?(Array) or fail ArgumentError, 'Input must be an Array or Hash'
  @header_rows = header_rows
  @data = Data.new( self, input_data ); self
end
match( header, &block ) click to toggle source

Find the row number by looking up a value in a Column

@param [String] header the header of the Column to pass to the block @yield yields each value in the Column to the block @return [Fixnum, nil] the row number of the first match or nil if nothing is found

# File lib/rubyexcel/sheet.rb, line 393
def match( header, &block )
  row_id( column_by_header( header ).find( &block ) ) rescue nil
end
maxcol() click to toggle source

The highest currently used column number

# File lib/rubyexcel/sheet.rb, line 410
def maxcol
  data.cols
end
Also aliased as: maxcolumn, width
maxcolumn()
Alias for: maxcol
maxrow() click to toggle source

The highest currently used row number

# File lib/rubyexcel/sheet.rb, line 401
def maxrow
  data.rows
end
Also aliased as: length
method_missing(m, *args, &block) click to toggle source

Allow shorthand range references and non-bang versions of bang methods.

Calls superclass method
# File lib/rubyexcel/sheet.rb, line 420
def method_missing(m, *args, &block)
  method_name = m.to_s
  
  if method_name[-1] != '!' && respond_to?( method_name + '!' )
  
    dup.send( method_name + '!', *args, &block )
    
  elsif method_name =~ /\A[A-Z]{1,3}\d+=?\z/i
  
    method_name.upcase!
    if method_name[-1] == '='
      range( method_name.chop ).value = ( args.length == 1 ? args.first : args )
    else
      range( method_name ).value
    end
    
  else
    super
  end
end
partition( header, &block ) click to toggle source

Split the Sheet into two Sheets by evaluating each value in a column

@param [String] header the header of the Column which contains the yield value @yield [value] yields the value of each row under the given header @return [Array<RubyExcel::Sheet, RubyExcel::Sheet>] Two Sheets: true and false. Headers included.

# File lib/rubyexcel/sheet.rb, line 465
def partition( header, &block )
  data.partition( header, &block ).map { |d| dup.load( d ) }
end
range( first_cell, last_cell=nil ) click to toggle source

Access a Range by address.

@param [String, Cell, Range] first_cell the first Cell or Address in the Range @param [String, Cell, Range] last_cell the last Cell or Address in the Range @return [RubyExcel::Range] @note These are all valid arguments:

('A1') 
('A1:B2') 
('A:A')
('1:1')
('A1', 'B2') 
(cell1) 
(cell1, cell2)
# File lib/rubyexcel/sheet.rb, line 485
def range( first_cell, last_cell=nil )
  addr = to_range_address( first_cell, last_cell )
  addr.include?(':') ? Range.new( self, addr ) : Cell.new( self, addr )
end
respond_to?( m, include_private = false ) click to toggle source

Allow for certain method_missing calls

Calls superclass method
# File lib/rubyexcel/sheet.rb, line 445
def respond_to?( m, include_private = false )

  if m[-1] != '!' && respond_to?( m.to_s + '!' )
    true
  elsif m.to_s.upcase.strip =~ /\A[A-Z]{1,3}\d+=?\z/
    true
  else
    super
  end
  
end
reverse!()
Alias for: reverse_rows!
reverse_columns!() click to toggle source

Reverse the Sheet Columns

# File lib/rubyexcel/sheet.rb, line 494
def reverse_columns!
  data.reverse_columns!; self
end
reverse_rows!() click to toggle source

Reverse the Sheet Rows (without affecting the headers)

# File lib/rubyexcel/sheet.rb, line 502
def reverse_rows!
  data.reverse_rows!; self
end
Also aliased as: reverse!
row( index ) click to toggle source

Create a Row from an index

@param [Fixnum] index the Row index @return [RubyExcel::Row]

# File lib/rubyexcel/sheet.rb, line 514
def row( index )
  Row.new( self, index )
end
rows( start_row = 1, end_row = data.rows ) { |row( idx )| ... } click to toggle source

Yields each Row to the block

@param [Fixnum] start_row the Row to start looping from @param [Fixnum] end_row the Row to end the loop at @note Iterates to the last Row in the Sheet unless given a second argument.

# File lib/rubyexcel/sheet.rb, line 526
def rows( start_row = 1, end_row = data.rows )
  return to_enum(:rows, start_row, end_row) unless block_given?
  ( start_row..end_row ).each { |idx| yield row( idx ) }; self
end
Also aliased as: each
save_excel( filename = nil, invisible = false ) click to toggle source

Save the RubyExcel::Sheet as an Excel Workbook

@param [String] filename the filename to save as @param [Boolean] invisible leave Excel invisible if creating a new instance @return [WIN32OLE::Workbook] the Workbook, saved as filename.

# File lib/rubyexcel/sheet.rb, line 540
def save_excel( filename = nil, invisible = false )      
  workbook.dup.clear_all.add( self.dup ).workbook.save_excel( filename, invisible )
end
sort_by!( *headers ) click to toggle source

Sort the data by a column, selected by header(s)

@param [String, Array<String>] headers the header(s) to sort the Sheet by

# File lib/rubyexcel/sheet.rb, line 550
def sort_by!( *headers )
  raise ArgumentError, 'Sheet#sort_by! does not support blocks.' if block_given?
  idx_array = headers.flatten.map { |header| data.index_by_header( header ) - 1 }
  sort_method = lambda { |array| idx_array.map { |idx| array[idx] } }
  data.sort_by!( &sort_method )
  self
rescue ArgumentError => err
  raise( NoMethodError, 'Item not comparable in "' + headers.flatten.map(&:to_s).join(', ') + '"' ) if err.message == 'comparison of Array with Array failed'
  raise err
end
split( header ) click to toggle source

Break the Sheet into a Workbook with multiple Sheets, split by the values under a header.

@param [String] header the header to split by @return [RubyExcel::Workbook] a new workbook containing the split Sheets (each with headers)

# File lib/rubyexcel/sheet.rb, line 568
def split( header )
  wb = Workbook.new
  ch( header ).each_wh.to_a.uniq.each { |name| wb.add( name ).load( data.headers ) }
  rows( header_rows+1 ) do |row|
    wb.sheets( row.val( header ) ) << row
  end
  wb
end
sumif( find_header, sum_header ) { |value| ... } click to toggle source

Sum the values in a Column by searching another Column

@param [String] find_header the header of the Column to yield to the block @param [String] sum_header the header of the Column to sum @yield yields the find_header column values to the block

# File lib/rubyexcel/sheet.rb, line 585
def sumif( find_header, sum_header )
  return to_enum( :sumif ) unless block_given?
  find_col, sum_col  = ch( find_header ), ch( sum_header )
  find_col.each_cell.inject(0) { |sum,ce| yield( ce.value ) && ce.row > header_rows ? sum + sum_col[ ce.row ] : sum }
end
summarise( header ) click to toggle source

Return a Hash containing the Column values and the number of times each appears.

@param [String] header the header of the Column to summarise @return [Hash]

# File lib/rubyexcel/sheet.rb, line 598
def summarise( header )
  ch( header ).summarise
end
Also aliased as: summarize
summarise!( header ) click to toggle source

Overwrite the sheet with the Summary of a Column

@param [String] header the header of the Column to summarise

# File lib/rubyexcel/sheet.rb, line 609
def summarise!( header )
  load( summarise( header ).to_a.unshift [ header, 'Count' ] )
end
Also aliased as: summarize!
summarize( header )
Alias for: summarise
summarize!( header )
Alias for: summarise!
to_a() click to toggle source

The Sheet as a 2D Array

# File lib/rubyexcel/sheet.rb, line 618
def to_a
  data.all
end
to_csv() click to toggle source

The Sheet as a CSV String

# File lib/rubyexcel/sheet.rb, line 626
def to_csv
  CSV.generate { |csv| to_a.each { |r| csv << r } }
end
to_excel() click to toggle source

The Sheet as a WIN32OLE Excel Workbook @note This requires Windows and MS Excel

# File lib/rubyexcel/sheet.rb, line 635
def to_excel
  workbook.dup.clear_all.add( self.dup ).workbook.to_excel
end
to_html() click to toggle source

The Sheet as a String containing an HTML Table

# File lib/rubyexcel/sheet.rb, line 643
def to_html
  %Q|<table border=1>\n<caption>#@name</caption>\n| + data.map { |row| '<tr>' + row.map { |v| '<td>' + CGI.escapeHTML(v.to_s) }.join }.join("\n") + "\n</table>"
end
to_s() click to toggle source

The Sheet as a Tab Seperated Value String (Strips extra whitespace)

# File lib/rubyexcel/sheet.rb, line 651
def to_s
  data.map { |ar| ar.map { |v| v.to_s.gsub(/\t|\n|\r/,' ') }.join "\t" }.join( $/ )
end
to_safe_format() click to toggle source

{Sheet#to_safe_format!}

# File lib/rubyexcel/sheet.rb, line 657
def to_safe_format
  dup.to_safe_format!
end
to_safe_format!() click to toggle source

Standardise the data for safe export to Excel.

Set each cell contents to a string and remove leading equals signs.
# File lib/rubyexcel/sheet.rb, line 666
def to_safe_format!
  rows { |r| r.map! { |v|
    if v.is_a?( String )
      v[0] == '=' ? v.sub( /\A=/,"'=" ) : v
    else
      v.to_s
    end
  } }; self
end
to_tsv() click to toggle source

the Sheet as a TSV String

# File lib/rubyexcel/sheet.rb, line 680
def to_tsv
  CSV.generate( :col_sep => "\t" ) { |csv| to_a.each { |r| csv << r } }
end
uniq!( header ) click to toggle source

Remove any Rows with duplicate values within a Column

@param [String] header the header of the Column to check for duplicates

# File lib/rubyexcel/sheet.rb, line 690
def uniq!( header )
  data.uniq!( header ); self
end
Also aliased as: unique!
unique!( header )
Alias for: uniq!
usedrange() click to toggle source

Select the used Range in the Sheet

@return [Range] the Sheet’s contents in Range

# File lib/rubyexcel/sheet.rb, line 701
def usedrange
  raise NoMethodError, 'Sheet is empty' if empty?
  Range.new( self, 'A1:' + indices_to_address( maxrow, maxcol ) )
end
vlookup( find_header, return_header, &block ) click to toggle source

Find a value within a Column by searching another Column

@param [String] find_header the header of the Column to search @param [String] return_header the header of the return value Column @yield the first matching value

# File lib/rubyexcel/sheet.rb, line 714
def vlookup( find_header, return_header, &block )
  find_col, return_col  = ch( find_header ), ch( return_header )
  return_col[ row_id( find_col.find( &block ) ) ] rescue nil
end
width()
Alias for: maxcol