Class: RubyExcel::Sheet

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

Overview

The front-end class for data manipulation and output.

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(name, workbook) ⇒ Sheet

Creates a RubyExcel::Sheet instance

Parameters:

  • name (String) —

    the name of the Sheet

  • workbook (RubyExcel::Workbook) —

    the Workbook which holds this Sheet



219
220
221
222
223
224
 
# File 'lib/rubyexcel.rb', line 219

def initialize( name, workbook )
  @workbook = workbook
  @name = name
  @header_rows = nil
  @data = Data.new( self, [[]] )
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(m, *args, &block) ⇒ Object

Allow shorthand range references



559
560
561
562
563
564
565
566
567
568
569
570
 
# File 'lib/rubyexcel.rb', line 559

def method_missing(m, *args, &block)
  method_name = m.to_s.upcase.strip
  if method_name =~ /\A[A-Z]{1,3}\d+=?\z/
    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

Instance Attribute Details

#data ⇒ Object (readonly)

The Data underlying the Sheet



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

def data
  @data
end

#header_rows ⇒ Object Also known as: headers

The number of rows treated as headers



204
205
206
 
# File 'lib/rubyexcel.rb', line 204

def header_rows
  @header_rows
end

#name ⇒ Object

The name of the Sheet



201
202
203
 
# File 'lib/rubyexcel.rb', line 201

def name
  @name
end

#workbook ⇒ Object Also known as: parent

The Workbook parent of this Sheet



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

def workbook
  @workbook
end

Instance Method Details

#+(other) ⇒ RubyExcel::Sheet

Note:

When adding another Sheet it won’t import the headers unless this Sheet is empty.

Add data with the Sheet

Parameters:

Returns:



268
269
270
 
# File 'lib/rubyexcel.rb', line 268

def +( other )
  dup << other
end

#-(other) ⇒ RubyExcel::Sheet

Subtract data from the Sheet

Parameters:

Returns:



279
280
281
282
283
284
285
 
# File 'lib/rubyexcel.rb', line 279

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

Append an object to the Sheet

Parameters:

  • other (Object) —

    the object to append

Returns:

  • (self) 


296
297
298
299
 
# File 'lib/rubyexcel.rb', line 296

def <<( other )
  data << other
  self
end

#[](addr) ⇒ Object

Read a value by address

Examples:

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

Parameters:

  • addr (String) —

    the address to access



240
241
242
 
# File 'lib/rubyexcel.rb', line 240

def[]( addr )
  range( addr ).value
end

#[]=(addr, val) ⇒ Object

Write a value by address

Examples:

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

Parameters:

  • val (Object) —

    the value to write into the data

  • addr (String) —

    the address to access



256
257
258
 
# File 'lib/rubyexcel.rb', line 256

def []=( addr, val )
  range( addr ).value = val
end

#advanced_filter(*args) ⇒ Object

#advanced_filter!



303
304
305
 
# File 'lib/rubyexcel.rb', line 303

def advanced_filter( *args )
  dup.advanced_filter!( *args )
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' )

Filter on multiple criteria

Parameters:

  • header (String) —

    a header to search under

  • comparison_operator (Symbol) —

    the operator to compare with

  • search_criteria (Object) —

    the value to filter by

Raises:

  • (ArgumentError) —

    ‘Number of arguments must be a multiple of 3’

  • (ArgumentError) —

    ‘Operator must be a symbol’



321
322
323
 
# File 'lib/rubyexcel.rb', line 321

def advanced_filter!( *args )
  data.advanced_filter!( *args ); self
end

#cell(row, col) ⇒ RubyExcel::Element Also known as: cells

Note:

Indexing is 1-based like Excel VBA

Access an Element by indices.

Parameters:

  • row (Fixnum) —

    the row index

  • col (Fixnum) —

    the column index

Returns:



334
335
336
 
# File 'lib/rubyexcel.rb', line 334

def cell( row, col )
  Element.new( self, indices_to_address( row, col ) )
end

#column(index) ⇒ RubyExcel::Column

Note:

Index ‘A’ and 1 both select the 1st Column

Access a Column (Section) by its reference.

Parameters:

  • index (String, Fixnum) —

    the Column reference

Returns:



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

def column( index )
  Column.new( self, col_letter( index ) )
end

#column_by_header(header) ⇒ RubyExcel::Column Also known as: ch

Access a Column (Section) by its header.

Parameters:

  • header (String) —

    the Column header

Returns:



358
359
360
 
# File 'lib/rubyexcel.rb', line 358

def column_by_header( header )
  header.is_a?( Column ) ? header : Column.new( self, data.colref_by_header( header ) )
end

#columns(start_column = 'A', end_column = data.cols) ⇒ Object

Note:

Iterates to the last Column in the Sheet unless given a second argument.

Yields each Column to the block

Parameters:

  • start_column (String, Fixnum) (defaults to: 'A') —

    the Column to start looping from

  • end_column (String, Fixnum) (defaults to: data.cols) —

    the Column to end the loop at



371
372
373
374
375
 
# File 'lib/rubyexcel.rb', line 371

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

#compact!



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

def compact
  dup.compact!
end

#compact! ⇒ Object

Removes empty Columns and Rows



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

def compact!
  data.compact!; self
end

#delete ⇒ Object

Removes Sheet from the parent Workbook



395
396
397
 
# File 'lib/rubyexcel.rb', line 395

def delete
  workbook.delete self
end

#delete_columns_if ⇒ Object

Deletes each Column where the block is true



412
413
414
415
 
# File 'lib/rubyexcel.rb', line 412

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

Deletes each Row where the block is true



403
404
405
406
 
# File 'lib/rubyexcel.rb', line 403

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 ⇒ RubyExcel::Sheet

Return a copy of self

Returns:



423
424
425
426
427
428
429
430
431
432
 
# File 'lib/rubyexcel.rb', line 423

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

#empty? ⇒ Boolean

Check whether the Sheet contains data

Returns:

  • (Boolean) —

    if there is any data



440
441
442
 
# File 'lib/rubyexcel.rb', line 440

def empty?
  data.empty?
end

#filter(header, &block) ⇒ Object

#filter!



446
447
448
 
# File 'lib/rubyexcel.rb', line 446

def filter( header, &block )
  dup.filter!( header, &block )
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) 


458
459
460
 
# File 'lib/rubyexcel.rb', line 458

def filter!( header, &block )
  data.filter!( header, &block ); self
end

#get_columns(*headers) ⇒ Object Also known as: gc

#get_columns!



464
465
466
 
# File 'lib/rubyexcel.rb', line 464

def get_columns( *headers )
  dup.get_columns!( *headers )
end

#get_columns!(*headers) ⇒ Object Also known as: gc!

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



477
478
479
 
# File 'lib/rubyexcel.rb', line 477

def get_columns!( *headers )
  data.get_columns!( *headers ); self
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



489
490
491
 
# File 'lib/rubyexcel.rb', line 489

def insert_columns( *args )
  data.insert_columns( *args ); self
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



500
501
502
 
# File 'lib/rubyexcel.rb', line 500

def insert_rows( *args )
  data.insert_rows( *args ); self
end

#inspect ⇒ Object

View the object for debugging



508
509
510
 
# File 'lib/rubyexcel.rb', line 508

def inspect
  "#{ self.class }:0x#{ '%x' % (object_id << 1) }: #{ name }"
end

#load(input_data, header_rows = 1) ⇒ Object

Populate the Sheet with data (overwrite)

Parameters:

  • input_data (Array<Array>, Hash<Hash>) —

    the data to fill the Sheet with

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

    the number of Rows to be treated as headers



519
520
521
522
523
524
 
# File 'lib/rubyexcel.rb', line 519

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) { ... } ⇒ Fixnum?

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

Parameters:

  • header (String) —

    the header of the Column to pass to the block

Yields:

  • yields each value in the Column to the block

Returns:

  • (Fixnum, nil) —

    the row number of the first match or nil if nothing is found



534
535
536
 
# File 'lib/rubyexcel.rb', line 534

def match( header, &block )
  row_id( column_by_header( header ).find( &block ) )
end

#maxcol ⇒ Object Also known as: maxcolumn

The highest currently used column number



550
551
552
 
# File 'lib/rubyexcel.rb', line 550

def maxcol
  data.cols
end

#maxrow ⇒ Object

The highest currently used row number



542
543
544
 
# File 'lib/rubyexcel.rb', line 542

def maxrow
  data.rows
end

#partition(header) {|value| ... } ⇒ Array<RubyExcel::Sheet, RubyExcel::Sheet>

Split the Sheet into two Sheets 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

Returns:



592
593
594
 
# File 'lib/rubyexcel.rb', line 592

def partition( header, &block )
  data.partition( header, &block ).map { |d| dup.load( d ) }
end

#range(first_cell, last_cell = nil) ⇒ RubyExcel::Element

Note:

These are all valid arguments: (‘A1’) (‘A1:B2’) (‘A:A’) (‘1:1’) (‘A1’, ‘B2’) (cell1) (cell1, cell2)

Access an Element by address.

Parameters:

  • first_cell (String, Element) —

    the first Cell or Address in the Range

  • last_cell (String, Element) (defaults to: nil) —

    the last Cell or Address in the Range

Returns:



612
613
614
 
# File 'lib/rubyexcel.rb', line 612

def range( first_cell, last_cell=nil )
  Element.new( self, to_range_address( first_cell, last_cell ) )
end

#respond_to?(meth) ⇒ Boolean

Allow for certain method_missing calls

Returns:

  • (Boolean) 


576
577
578
579
580
581
582
 
# File 'lib/rubyexcel.rb', line 576

def respond_to?(meth)
  if meth.to_s.upcase.strip =~ /\A[A-Z]{1,3}\d+=?\z/
    true
  else
    super
  end
end

#reverse_columns! ⇒ Object

Reverse the Sheet Columns



620
621
622
 
# File 'lib/rubyexcel.rb', line 620

def reverse_columns!
  data.reverse_columns!; self
end

#reverse_rows! ⇒ Object

Reverse the Sheet Rows (without affecting the headers)



628
629
630
 
# File 'lib/rubyexcel.rb', line 628

def reverse_rows!
  data.reverse_rows!; self
end

#row(index) ⇒ RubyExcel::Row

Create a Row from an index

Parameters:

  • index (Fixnum) —

    the Row index

Returns:



639
640
641
 
# File 'lib/rubyexcel.rb', line 639

def row( index )
  Row.new( self, index )
end

#rows(start_row = 1, end_row = data.rows) ⇒ Object Also known as: each

Note:

Iterates to the last Row in the Sheet unless given a second argument.

Yields each Row to the block

Parameters:

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

    the Row to start looping from

  • end_row (Fixnum) (defaults to: data.rows) —

    the Row to end the loop at



651
652
653
654
 
# File 'lib/rubyexcel.rb', line 651

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

#sort(&block) ⇒ Object

#sort!



659
660
661
 
# File 'lib/rubyexcel.rb', line 659

def sort( &block )
  dup.sort!( &block )
end

#sort!(&block) ⇒ Object

Sort the data according to a block (avoiding headers)



667
668
669
 
# File 'lib/rubyexcel.rb', line 667

def sort!( &block )
  data.sort!( &block ); self
end

#sort_by(header) ⇒ Object

#sort_by!



673
674
675
 
# File 'lib/rubyexcel.rb', line 673

def sort_by( header )
  dup.sort_by!( header )
end

#sort_by!(header) ⇒ Object

Sort the data by a column, selected by header

Parameters:

  • header (String) —

    the header to sort the Sheet by



683
684
685
686
687
688
 
# File 'lib/rubyexcel.rb', line 683

def sort_by!( header )
  idx = data.index_by_header( header ) - 1
  sort_method = lambda { |array| array[idx] }
  data.sort_by!( &sort_method )
  self
end

#split(header) ⇒ RubyExcel::Workbook

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

Parameters:

  • header (String) —

    the header to split by

Returns:



697
698
699
700
701
702
703
704
 
# File 'lib/rubyexcel.rb', line 697

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) { ... } ⇒ Object

Sum the values in a Column by searching another Column

Parameters:

  • find_header (String) —

    the header of the Column to yield to the block

  • sum_header (String) —

    the header of the Column to sum

Yields:

  • yields the find_header column values to the block



714
715
716
717
718
 
# File 'lib/rubyexcel.rb', line 714

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

Summarise the values of a Column into a Hash

Parameters:

  • header (String) —

    the header of the Column to summarise

Returns:

  • (Hash) 


727
728
729
 
# File 'lib/rubyexcel.rb', line 727

def summarise( header )
  ch( header ).summarise
end

#to_a ⇒ Object

The Sheet as a 2D Array



735
736
737
 
# File 'lib/rubyexcel.rb', line 735

def to_a
  data.all
end

#to_excel ⇒ Object

Note:

This requires Windows and MS Excel

The Sheet as a WIN32OLE Excel Workbook



744
745
746
 
# File 'lib/rubyexcel.rb', line 744

def to_excel
  workbook.dup.clear_all.add( self.dup ).workbook.to_excel
end

#to_html ⇒ Object

The Sheet as a String containing an HTML Table



752
753
754
 
# File 'lib/rubyexcel.rb', line 752

def to_html
  "<table>\n" + data.map { |row| '<tr>' + row.map { |v| '<td>' + CGI.escapeHTML(v.to_s) }.join() + "\n" }.join() + '</table>'
end

#to_s ⇒ Object

The Sheet as a Tab Seperated Value String



760
761
762
 
# File 'lib/rubyexcel.rb', line 760

def to_s
  data.map { |ar| ar.map { |v| v.to_s.gsub(/\t|\n/,' ') }.join "\t" }.join( $/ )
end

#uniq(header) ⇒ Object

#uniq!



766
767
768
 
# File 'lib/rubyexcel.rb', line 766

def uniq( header )
  dup.uniq!( header )
end

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

Remove any Rows with duplicate values within a Column

Parameters:

  • header (String) —

    the header of the Column to check for duplicates



776
777
778
 
# File 'lib/rubyexcel.rb', line 776

def uniq!( header )
  data.uniq!( header ); self
end

#vlookup(find_header, return_header) { ... } ⇒ Object

Find a value within a Column by searching another Column

Parameters:

  • find_header (String) —

    the header of the Column to search

  • return_header (String) —

    the header of the return value Column

Yields:

  • the first matching value



789
790
791
792
 
# File 'lib/rubyexcel.rb', line 789

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