Class: RubyExcel::Data

Inherits:
Object
  • Object
show all
Includes:
Enumerable, Address
Defined in:
lib/rubyexcel/data.rb

Overview

Note:

This class is exposed to the API purely for debugging.

The class which holds a Sheet’s data

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Address

#address_to_col_index, #address_to_indices, #col_index, #col_letter, #column_id, #expand, #indices_to_address, #multi_array?, #offset, #row_id, #to_range_address

Constructor Details

#initialize(sheet, input_data) ⇒ Data

Creates a RubyExcel::Data instance

Parameters:

  • sheet (RubyExcel::Sheet) —

    the parent Sheet

  • input_data (Array<Array>) —

    the multidimensional Array which holds the data



32
33
34
35
36
37
 
# 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

Instance Attribute Details

#cols ⇒ Object (readonly)

The number of columns in the data



19
20
21
 
# File 'lib/rubyexcel/data.rb', line 19

def cols
  @cols
end

#rows ⇒ Object (readonly)

The number of rows in the data



16
17
18
 
# File 'lib/rubyexcel/data.rb', line 16

def rows
  @rows
end

#sheet ⇒ Object Also known as: parent

The parent Sheet



22
23
24
 
# File 'lib/rubyexcel/data.rb', line 22

def sheet
  @sheet
end

Instance Method Details

#<<(other) ⇒ self

Append an object to Data

Parameters:

  • other (Object) —

    the data to append

Returns:

  • (self) 


46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# 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

#advanced_filter!(header, comparison_operator, search_criteria, ...) ⇒ Object

Examples:

Filter to ‘Part’: ‘Type1’ and ‘Type3’, with ‘Qty’ greater than 1

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

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'

Filter on multiple criteria



80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
# 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 ⇒ Array<Array>

Returns a copy of the data

Returns:

  • (Array<Array>) 


104
105
106
 
# File 'lib/rubyexcel/data.rb', line 104

def all
  @data.dup
end

#colref_by_header(header) ⇒ String

Finds a Column reference by a header

Parameters:

  • header (String) —

    the header to search for

Returns:

  • (String) —

    the Column reference

Raises:

  • (NoMethodError) —

    ‘No header rows present’

  • (IndexError) —

    header.to_s + ‘ is not a valid header’



117
118
119
120
121
122
 
# 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! ⇒ Object

Removes empty rows and columns from the data



128
129
130
131
 
# File 'lib/rubyexcel/data.rb', line 128

def compact!
  compact_columns!
  compact_rows!
end

#compact_columns! ⇒ Object

Removes empty columns from the data



137
138
139
140
141
 
# 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! ⇒ Object

Removes empty rows from the data



147
148
149
150
 
# 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) ⇒ Object

Deletes the data referenced by an object

Parameters:

Raises:

  • (NoMethodError) —

    object.class.to_s + ‘ is not supported“



159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# 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_column(ref) ⇒ Object

Deletes the data referenced by a column id



181
182
183
 
# File 'lib/rubyexcel/data.rb', line 181

def delete_column( ref )
  delete( Column.new( sheet, ref ) )
end

#delete_range(ref) ⇒ Object

Deletes the data referenced by an address



197
198
199
 
# File 'lib/rubyexcel/data.rb', line 197

def delete_range( ref )
  delete( Element.new( sheet, ref ) )
end

#delete_row(ref) ⇒ Object

Deletes the data referenced by a row id



189
190
191
 
# File 'lib/rubyexcel/data.rb', line 189

def delete_row( ref )
  delete( Row.new( sheet, ref ) )
end

#dup ⇒ RubyExcel::Data

Return a copy of self

Returns:



207
208
209
 
# File 'lib/rubyexcel/data.rb', line 207

def dup
  Data.new( sheet, @data.map(&:dup) )
end

#each ⇒ Object

Yields each “Row” as an Array



225
226
227
228
 
# File 'lib/rubyexcel/data.rb', line 225

def each
  return to_enum( :each ) unless block_given?
  @data.each { |ar| yield ar }
end

#empty? ⇒ Boolean

Check whether the data (without headers) is empty

Returns:

  • (Boolean) 


217
218
219
 
# File 'lib/rubyexcel/data.rb', line 217

def empty?
  no_headers.empty? rescue true
end

#filter!(header) {|Object| ... } ⇒ self

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

Parameters:

  • header (String) —

    the header of the Column to pass to the block

Yields:

  • (Object) —

    the value at the intersection of Column and Row

Returns:

  • (self) 


238
239
240
241
242
243
 
# File 'lib/rubyexcel/data.rb', line 238

def filter!( header )
  hrows = sheet.header_rows
  idx = index_by_header( header )
  @data = @data.select.with_index { |row, i| hrows > i || yield( row[ idx -1 ] ) }
  calc_dimensions
end

#get_columns!(*headers) ⇒ Object

Note:

This method can accept either a list of arguments or an Array

Note:

Invalid headers will be skipped

Select and re-order Columns by a list of headers

Parameters:

  • headers (Array<String>) —

    the ordered list of headers to keep



253
254
255
256
257
258
259
260
 
# File 'lib/rubyexcel/data.rb', line 253

def get_columns!( *headers )
  headers = headers.flatten
  hrow = sheet.header_rows - 1
  ensure_shape
  @data = @data.transpose.select{ |col| headers.include?( col[hrow] ) }
  @data = @data.sort_by{ |col| headers.index( col[hrow] ) || col[hrow] }.transpose
  calc_dimensions
end

#headers ⇒ Object

Return the header section of the data



266
267
268
269
 
# File 'lib/rubyexcel/data.rb', line 266

def headers
  return nil if sheet.header_rows.nil? || sheet.header_rows.zero?
   @data[ 0..sheet.header_rows-1 ]
end

#index_by_header(header) ⇒ Fixnum

Find a Column index by header

Parameters:

  • header (String) —

    the Column header to search for

Returns:

  • (Fixnum) —

    the index of the given header



278
279
280
281
 
# File 'lib/rubyexcel/data.rb', line 278

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) ⇒ Object

Insert blank Columns into the data

Parameters:

  • before (String, Fixnum) —

    the Column reference to insert before.

  • number (Fixnum) (defaults to: 1) —

    the number of new Columns to insert



290
291
292
293
294
295
 
# File 'lib/rubyexcel/data.rb', line 290

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) ⇒ Object

Insert blank Rows into the data

Parameters:

  • before (Fixnum) —

    the Row index to insert before.

  • number (Fixnum) (defaults to: 1) —

    the number of new Rows to insert



304
305
306
307
 
# File 'lib/rubyexcel/data.rb', line 304

def insert_rows( before, number=1 )
  @data = @data.insert( ( col_index( before ) - 1 ), *Array.new( number, [nil] ) )
  calc_dimensions
end

#no_headers ⇒ Object

Return the data without headers



313
314
315
316
 
# File 'lib/rubyexcel/data.rb', line 313

def no_headers
  return @data unless sheet.header_rows
  @data[ sheet.header_rows..-1 ]
end

#partition(header) {|value| ... } ⇒ Object

Split the data into two sections by evaluating each value in a column

Parameters:

  • header (String) —

    the header of the Column which contains the yield value

Yields:

  • (value) —

    yields the value of each row under the given header



325
326
327
328
329
330
 
# File 'lib/rubyexcel/data.rb', line 325

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) ⇒ Object Also known as: []

Read a value by address



336
337
338
339
 
# File 'lib/rubyexcel/data.rb', line 336

def read( addr )
  row_idx, col_idx = address_to_indices( addr )
  @data[ row_idx-1 ][ col_idx-1 ]
end

#reverse_columns! ⇒ Object

Reverse the data Columns



346
347
348
349
 
# File 'lib/rubyexcel/data.rb', line 346

def reverse_columns!
  ensure_shape
  @data = @data.transpose.reverse.transpose
end

#reverse_rows! ⇒ Object

Reverse the data Rows (without affecting the headers)



355
356
357
 
# File 'lib/rubyexcel/data.rb', line 355

def reverse_rows!
  @data = skip_headers &:reverse
end

#skip_headers {|data| ... } ⇒ Array<Array>

Perform an operation on the data without affecting the headers

Yields:

  • (data) —

    yield the data without the headers

Returns:

  • (Array<Array>) —

    returns the data with the block operation performed on it, and the headers back in place



366
367
368
369
370
371
372
373
374
 
# File 'lib/rubyexcel/data.rb', line 366

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) ⇒ Object

Sort the data according to the block



380
381
382
 
# File 'lib/rubyexcel/data.rb', line 380

def sort!( &block )
  @data = skip_headers { |d| d.sort( &block ) }; self
end

#sort_by!(&block) ⇒ Object

Sort the data according to the block value



388
389
390
 
# File 'lib/rubyexcel/data.rb', line 388

def sort_by!( &block )
  @data = skip_headers { |d| d.sort_by( &block ) }; self
end

#uniq!(header) ⇒ Object Also known as: unique!

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



396
397
398
399
400
 
# File 'lib/rubyexcel/data.rb', line 396

def uniq!( header )
  column = col_index( colref_by_header( header ) )
  @data = skip_headers { |d| d.uniq { |row| row[ column - 1 ] } }
  calc_dimensions
end

#write(addr, val) ⇒ Object Also known as: []=

Write a value into the data

Parameters:

  • addr (String) —

    the address to write the value to

  • val —

    the value to write to the address



410
411
412
413
414
415
416
 
# File 'lib/rubyexcel/data.rb', line 410

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