class RubyExcel::Data

The class which holds a Sheet’s data

@note This class is exposed to the API purely for debugging.

Attributes

cols[R]

The number of columns in the data

parent[RW]

The parent Sheet

rows[R]

The number of rows in the data

sheet[RW]

The parent Sheet

Public Class Methods

new( sheet, input_data ) click to toggle source

Creates a RubyExcel::Data instance

@param [RubyExcel::Sheet] sheet the parent Sheet @param [Array<Array>] input_data the multidimensional Array which holds the data

# File lib/rubyexcel/data.rb, line 32
def initialize( sheet, input_data )
  ( input_data.kind_of?( Array ) &&  input_data.all? { |el| el.kind_of?( Array ) } ) or fail ArgumentError, 'Input must be Array of Arrays'
  @sheet = sheet
  @data = input_data.dup
  calc_dimensions
end

Public Instance Methods

<<( other ) click to toggle source

Append an object to Data

@param [Object] other the data to append @return [self]

# File lib/rubyexcel/data.rb, line 46
def <<( other )
  case other
  when Array
    if multi_array?( other )
      all.all?(&:empty?) ? @data = other : @data += other
    else
      all.all?(&:empty?) ? @data = [ other ] : @data << other
    end
  when Hash   ; @data += _convert_hash( other )
  when Sheet  ; empty? ? @data = other.data.all.dup : @data += other.data.dup.no_headers
  when Row    ; @data << other.to_a.dup
  when Column ; @data.map!.with_index { |row, i| row << other[ i+1 ] }
  else        ; @data[0] << other
  end
  calc_dimensions
  self
end
[]( addr )
Alias for: read
[]=( addr, val )
Alias for: write
advanced_filter!( *args ) click to toggle source

@overload advanced_filter!( header, comparison_operator, search_criteria, … )

Filter on multiple criteria

@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' )

@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'
# File lib/rubyexcel/data.rb, line 80
def advanced_filter!( *args )
  hrows = sheet.header_rows
  args.length % 3 == 0 or fail ArgumentError, 'Number of arguments must be a multiple of 3'
  1.step( args.length - 2, 3 ) { |i| args[i].is_a?( Symbol ) or fail ArgumentError, 'Operator must be a symbol: ' + args[i].to_s }
  0.step( args.length - 3, 3 ) { |i| index_by_header( args[i] ) }
  
  @data = @data.select.with_index do |row, i|
    if hrows > i
      true
    else
      args.each_slice(3).map do |h, op, crit|
        row[ index_by_header( h ) - 1 ].send( op, crit )
      end.all?
    end
  end
  calc_dimensions
end
all() click to toggle source

Returns a copy of the data

@return [Array<Array>]

# File lib/rubyexcel/data.rb, line 104
def all
  @data.dup
end
colref_by_header( header ) click to toggle source

Finds a Column reference by a header

@param [String] header the header to search for @return [String] the Column reference @raise [NoMethodError] ‘No header rows present’ @raise [IndexError] header.to_s + ‘ is not a valid header’

# File lib/rubyexcel/data.rb, line 117
def colref_by_header( header )
  return header.idx if header.is_a?( Column )
  sheet.header_rows > 0 or fail NoMethodError, 'No header rows present'
  @data[ 0..sheet.header_rows-1 ].each { |r| idx = r.index( header ); return col_letter( idx+1 ) if idx }
  fail IndexError, header.to_s + ' is not a valid header'
end
compact!() click to toggle source

Removes empty rows and columns from the data

# File lib/rubyexcel/data.rb, line 128
def compact!
  compact_columns!
  compact_rows!
end
compact_columns!() click to toggle source

Removes empty columns from the data

# File lib/rubyexcel/data.rb, line 137
def compact_columns!
  ensure_shape
  @data = @data.transpose.delete_if { |ar| ar.all? { |el| el.to_s.empty? } || ar.empty? }.transpose
  calc_dimensions
end
compact_rows!() click to toggle source

Removes empty rows from the data

# File lib/rubyexcel/data.rb, line 147
def compact_rows!
  @data.delete_if { |ar| ar.all? { |el| el.to_s.empty? } || ar.empty? }
  calc_dimensions
end
delete( object ) click to toggle source

Deletes the data referenced by an object

@param [RubyExcel::Column, RubyExcel::Element, RubyExcel::Row] object the object to delete @raise [NoMethodError] object.class.to_s + ‘ is not supported“

# File lib/rubyexcel/data.rb, line 159
def delete( object )
  case object
  when Row
    @data.slice!( object.idx - 1 )
  when Column
    idx = col_index( object.idx ) - 1
    @data.each { |r| r.slice! idx }
  when Element
    addresses = expand( object.address )
    indices = [ address_to_indices( addresses.first.first ), address_to_indices( addresses.last.last ) ].flatten.map { |n| n-1 }
    @data[ indices[0]..indices[2] ].each { |r| r.slice!( indices[1], indices[3] - indices[1] + 1 ) }
    @data.delete_if.with_index { |r,i| r.empty? && i.between?( indices[0], indices[2] ) }
  else
    fail NoMethodError, object.class.to_s + ' is not supported'
  end
  calc_dimensions
end
delete_all() click to toggle source

Wipe all data

# File lib/rubyexcel/data.rb, line 181
def delete_all
  @data = [[]]
end
delete_column( ref ) click to toggle source

Deletes the data referenced by a column id

# File lib/rubyexcel/data.rb, line 189
def delete_column( ref )
  delete( Column.new( sheet, ref ) )
end
delete_range( ref ) click to toggle source

Deletes the data referenced by an address

# File lib/rubyexcel/data.rb, line 205
def delete_range( ref )
  delete( Element.new( sheet, ref ) )
end
delete_row( ref ) click to toggle source

Deletes the data referenced by a row id

# File lib/rubyexcel/data.rb, line 197
def delete_row( ref )
  delete( Row.new( sheet, ref ) )
end
dup() click to toggle source

Return a copy of self

@return [RubyExcel::Data]

# File lib/rubyexcel/data.rb, line 215
def dup
  Data.new( sheet, @data.map(&:dup) )
end
each() { |ar| ... } click to toggle source

Yields each “Row” as an Array

# File lib/rubyexcel/data.rb, line 233
def each
  return to_enum( :each ) unless block_given?
  @data.each { |ar| yield ar }
end
empty?() click to toggle source

Check whether the data (without headers) is empty

@return [Boolean]

# File lib/rubyexcel/data.rb, line 225
def empty?
  no_headers.empty? rescue true
end
filter!( *headers ) { |length == 1 ? row[ idx_array : map { |idx| row }| ... } 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/data.rb, line 246
def filter!( *headers )
  hrows = sheet.header_rows
  idx_array = headers.flatten.map { |header| index_by_header( header ) }.compact
  @data = @data.select.with_index { |row, i| hrows > i || yield( idx_array.length == 1 ? row[ idx_array[0] - 1 ] : idx_array.map { |idx| row[ idx -1 ] } ) }
  calc_dimensions
end
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/data.rb, line 261
def get_columns!( *headers )
  headers = headers.flatten
  hrow = sheet.header_rows - 1
  ensure_shape
  @data = @data.transpose.select{ |col| col[0..hrow].any?{ |val| headers.include?( val ) } }
  @data = @data.sort_by{ |col| headers.index( col[0..hrow].select { |val| headers.include?( val ) }.first ) || headers.length }.transpose
  calc_dimensions
end
headers() click to toggle source

Return the header section of the data

# File lib/rubyexcel/data.rb, line 274
def headers
  return nil if sheet.header_rows.nil? || sheet.header_rows.zero?
   @data[ 0..sheet.header_rows-1 ]
end
index_by_header( header ) click to toggle source

Find a Column index by header

@param [String] header the Column header to search for @return [Fixnum] the index of the given header

# File lib/rubyexcel/data.rb, line 286
def index_by_header( header )
  sheet.header_rows > 0 or fail NoMethodError, 'No header rows present'
  col_index(  colref_by_header( header ) )
end
insert_columns( before, number=1 ) click to toggle source

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/data.rb, line 298
def insert_columns( before, number=1 )
  a = Array.new( number, nil )
  before = col_index( before ) - 1
  @data.map! { |row|  row.insert( before, *a ) }
  calc_dimensions
end
insert_rows( before, number=1 ) click to toggle source

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/data.rb, line 312
def insert_rows( before, number=1 )
  @data = @data.insert( ( col_index( before ) - 1 ), *Array.new( number, [nil] ) )
  calc_dimensions
end
no_headers() click to toggle source

Return the data without headers

# File lib/rubyexcel/data.rb, line 321
def no_headers
  return @data unless sheet.header_rows
  @data[ sheet.header_rows..-1 ]
end
partition( header ) { |row| ... } click to toggle source

Split the data into two sections 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

# File lib/rubyexcel/data.rb, line 333
def partition( header, &block )
  copy = dup
  idx = index_by_header( header )
  d1, d2 = copy.no_headers.partition { |row| yield row[ idx -1 ] }
  [ copy.headers + d1, copy.headers.map(&:dup) + d2 ] if headers
end
read( addr ) click to toggle source

Read a value by address

# File lib/rubyexcel/data.rb, line 344
def read( addr )
  row_idx, col_idx = address_to_indices( addr )
  return nil if row_idx > rows
  @data[ row_idx-1 ][ col_idx-1 ]
end
Also aliased as: []
reverse_columns!() click to toggle source

Reverse the data Columns

# File lib/rubyexcel/data.rb, line 355
def reverse_columns!
  ensure_shape
  @data = @data.transpose.reverse.transpose
end
reverse_rows!() click to toggle source

Reverse the data Rows (without affecting the headers)

# File lib/rubyexcel/data.rb, line 364
def reverse_rows!
  @data = skip_headers &:reverse
end
skip_headers() { |data| ... } click to toggle source

Perform an operation on the data without affecting the headers

@yield [data] yield the data without the headers @return [Array<Array>] returns the data with the block operation performed on it, and the headers back in place

# File lib/rubyexcel/data.rb, line 375
def skip_headers
  return to_enum(:skip_headers) unless block_given?
  hr = sheet.header_rows
  if hr > 0
    @data[ 0..hr - 1 ] + yield( @data[ hr..-1 ] )
  else
    yield( @data )
  end 
end
sort!( &block ) click to toggle source

Sort the data according to the block

# File lib/rubyexcel/data.rb, line 389
def sort!( &block )
  @data = skip_headers { |d| d.sort( &block ) }; self
end
sort_by!( &block ) click to toggle source

Sort the data according to the block value

# File lib/rubyexcel/data.rb, line 397
def sort_by!( &block )
  @data = skip_headers { |d| d.sort_by( &block ) }; self
end
uniq!( header ) click to toggle source

Unique the rows according to the values within a Column, selected by header

# File lib/rubyexcel/data.rb, line 405
def uniq!( header )
  column = col_index( colref_by_header( header ) )
  @data = skip_headers { |d| d.uniq { |row| row[ column - 1 ] } }
  calc_dimensions
end
Also aliased as: unique!
unique!( header )
Alias for: uniq!
write( addr, val ) click to toggle source

Write a value into the data

@param [String] addr the address to write the value to @param val the value to write to the address

# File lib/rubyexcel/data.rb, line 419
def write( addr, val )
  row_idx, col_idx = address_to_indices( addr )
  ( row_idx - rows ).times { @data << [] }
  @data[ row_idx-1 ][ col_idx-1 ] = val
  calc_dimensions if row_idx > rows || col_idx > cols
  val
end
Also aliased as: []=

Private Instance Methods

_convert_hash(h) click to toggle source
# File lib/rubyexcel/data.rb, line 441
def _convert_hash(h)
  _hash_to_a(h).each_slice(2).map { |a1,a2| a1 << a2.last }
end
_hash_to_a(h) click to toggle source
# File lib/rubyexcel/data.rb, line 445
def _hash_to_a(h)
  h.map { |k,v| v.is_a?(Hash) ? _hash_to_a(v).map { |val| ([ k ] + [ val ]).flatten(1) } : [ k, v ] }.flatten(1)
end
calc_dimensions() click to toggle source
# File lib/rubyexcel/data.rb, line 430
def calc_dimensions
  @rows = ( @data.length rescue 0 )
  @cols = ( @data.max_by { |row| row.length }.length rescue 0 )
  self
end
ensure_shape() click to toggle source
# File lib/rubyexcel/data.rb, line 436
def ensure_shape
  calc_dimensions
  @data = @data.map { |ar| ar.length == cols ? ar : ar + Array.new( cols - ar.length, nil) }
end