Class: RubyExcel::Sheet

Inherits:
Object
  • Object
show all
Includes:
Address
Defined in:
lib/rubyexcel/sheet.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, #step_index, #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



36
37
38
39
40
41
 
# File 'lib/rubyexcel/sheet.rb', line 36

def initialize( name, workbook )
  @workbook = workbook
  @name = name
  @header_rows = 1
  @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 and non-bang versions of bang methods.



405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
 
# File 'lib/rubyexcel/sheet.rb', line 405

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

Instance Attribute Details

#data ⇒ Object (readonly)

The Data underlying the Sheet



15
16
17
 
# File 'lib/rubyexcel/sheet.rb', line 15

def data
  @data
end

#header_rows ⇒ Object Also known as: headers

The number of rows treated as headers



21
22
23
 
# File 'lib/rubyexcel/sheet.rb', line 21

def header_rows
  @header_rows
end

#name ⇒ Object

The name of the Sheet



18
19
20
 
# File 'lib/rubyexcel/sheet.rb', line 18

def name
  @name
end

#workbook ⇒ Object Also known as: parent

The Workbook parent of this Sheet



24
25
26
 
# File 'lib/rubyexcel/sheet.rb', line 24

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:



85
86
87
 
# File 'lib/rubyexcel/sheet.rb', line 85

def +( other )
  dup << other
end

#-(other) ⇒ RubyExcel::Sheet

Subtract data from the Sheet

Parameters:

Returns:



96
97
98
99
100
101
102
 
# 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) ⇒ 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) 


113
114
115
116
 
# File 'lib/rubyexcel/sheet.rb', line 113

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



57
58
59
 
# File 'lib/rubyexcel/sheet.rb', line 57

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



73
74
75
 
# File 'lib/rubyexcel/sheet.rb', line 73

def []=( addr, val )
  range( addr ).value = val
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’



131
132
133
 
# File 'lib/rubyexcel/sheet.rb', line 131

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

#averageif(find_header, avg_header) { ... } ⇒ Object

Average the values in a Column by searching another Column

Parameters:

  • find_header (String) —

    the header of the Column to yield to the block

  • avg_header (String) —

    the header of the Column to average

Yields:

  • yields the find_header column values to the block



143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/rubyexcel/sheet.rb', line 143

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) ⇒ RubyExcel::Cell Also known as: cells

Note:

Indexing is 1-based like Excel VBA

Access an Cell by indices.

Parameters:

  • row (Fixnum) —

    the row index

  • col (Fixnum) —

    the column index

Returns:



167
168
169
 
# File 'lib/rubyexcel/sheet.rb', line 167

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

#clear_all ⇒ Object Also known as: delete_all

Delete all data and headers from Sheet



176
177
178
179
 
# File 'lib/rubyexcel/sheet.rb', line 176

def clear_all
  data.delete_all
  self
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:



190
191
192
 
# File 'lib/rubyexcel/sheet.rb', line 190

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:



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

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



214
215
216
217
218
 
# File 'lib/rubyexcel/sheet.rb', line 214

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

Removes empty Columns and Rows



224
225
226
 
# File 'lib/rubyexcel/sheet.rb', line 224

def compact!
  data.compact!; self
end

#delete ⇒ Object

Removes Sheet from the parent Workbook



232
233
234
 
# File 'lib/rubyexcel/sheet.rb', line 232

def delete
  workbook.delete self
end

#delete_columns_if ⇒ Object

Deletes each Column where the block is true



249
250
251
252
 
# File 'lib/rubyexcel/sheet.rb', line 249

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



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

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:



260
261
262
263
264
265
266
267
268
269
 
# File 'lib/rubyexcel/sheet.rb', line 260

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 (not counting headers)

Returns:

  • (Boolean) —

    if there is any data



277
278
279
 
# File 'lib/rubyexcel/sheet.rb', line 277

def empty?
  data.empty?
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) 


289
290
291
292
 
# File 'lib/rubyexcel/sheet.rb', line 289

def filter!( header, &block )
  return to_enum( :filter!, header ) unless block_given?
  data.filter!( header, &block ); self
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



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

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



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

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



325
326
327
 
# File 'lib/rubyexcel/sheet.rb', line 325

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

#inspect ⇒ Object

View the object for debugging



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

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

#last_column ⇒ RubyExcel::Column Also known as: last_col

The last Column in the Sheet

Returns:



343
344
345
 
# File 'lib/rubyexcel/sheet.rb', line 343

def last_column
  column( maxcol )
end

#last_row ⇒ RubyExcel::Row

The last Row in the Sheet

Returns:



354
355
356
 
# File 'lib/rubyexcel/sheet.rb', line 354

def last_row
  row( maxrow )
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



365
366
367
368
369
370
 
# File 'lib/rubyexcel/sheet.rb', line 365

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



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

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

#maxcol ⇒ Object Also known as: maxcolumn

The highest currently used column number



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

def maxcol
  data.cols
end

#maxrow ⇒ Object

The highest currently used row number



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

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:



450
451
452
 
# File 'lib/rubyexcel/sheet.rb', line 450

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

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

Note:

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

Access a Range by address.

Parameters:

  • first_cell (String, Cell, Range) —

    the first Cell or Address in the Range

  • last_cell (String, Cell, Range) (defaults to: nil) —

    the last Cell or Address in the Range

Returns:



470
471
472
473
 
# File 'lib/rubyexcel/sheet.rb', line 470

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

Allow for certain method_missing calls

Returns:

  • (Boolean) 


430
431
432
433
434
435
436
437
438
439
440
 
# File 'lib/rubyexcel/sheet.rb', line 430

def respond_to?( m )

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

Reverse the Sheet Columns



479
480
481
 
# File 'lib/rubyexcel/sheet.rb', line 479

def reverse_columns!
  data.reverse_columns!; self
end

#reverse_rows! ⇒ Object Also known as: reverse!

Reverse the Sheet Rows (without affecting the headers)



487
488
489
 
# File 'lib/rubyexcel/sheet.rb', line 487

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:



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

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



511
512
513
514
 
# File 'lib/rubyexcel/sheet.rb', line 511

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

#save_excel(filename = nil, invisible = false) ⇒ WIN32OLE::Workbook

Save the RubyExcel::Sheet as an Excel Workbook

Parameters:

  • filename (String) (defaults to: nil) —

    the filename to save as

  • invisible (Boolean) (defaults to: false) —

    leave Excel invisible if creating a new instance

Returns:

  • (WIN32OLE::Workbook) —

    the Workbook, saved as filename.



525
526
527
 
# File 'lib/rubyexcel/sheet.rb', line 525

def save_excel( filename = nil, invisible = false )      
  workbook.dup.clear_all.add( self.dup ).workbook.save_excel( filename, invisible )
end

#sort_by!(*headers) ⇒ Object

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

Parameters:

  • headers (String, Array<String>) —

    the header(s) to sort the Sheet by

Raises:

  • (ArgumentError) 


535
536
537
538
539
540
541
 
# File 'lib/rubyexcel/sheet.rb', line 535

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
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:



550
551
552
553
554
555
556
557
 
# File 'lib/rubyexcel/sheet.rb', line 550

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



567
568
569
570
571
 
# File 'lib/rubyexcel/sheet.rb', line 567

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 Also known as: summarize

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

Parameters:

  • header (String) —

    the header of the Column to summarise

Returns:

  • (Hash) 


580
581
582
 
# File 'lib/rubyexcel/sheet.rb', line 580

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

#summarise!(header) ⇒ Object Also known as: summarize!

Overwrite the sheet with the Summary of a Column

Parameters:

  • header (String) —

    the header of the Column to summarise



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

def summarise!( header )
  load( summarise( header ).to_a.unshift [ header, 'Count' ] )
end

#to_a ⇒ Object

The Sheet as a 2D Array



600
601
602
 
# File 'lib/rubyexcel/sheet.rb', line 600

def to_a
  data.all
end

#to_csv ⇒ Object

The Sheet as a CSV String



608
609
610
 
# File 'lib/rubyexcel/sheet.rb', line 608

def to_csv
  CSV.generate { |csv| to_a.each { |r| csv << r } }
end

#to_excel ⇒ Object

Note:

This requires Windows and MS Excel

The Sheet as a WIN32OLE Excel Workbook



617
618
619
 
# File 'lib/rubyexcel/sheet.rb', line 617

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



625
626
627
 
# File 'lib/rubyexcel/sheet.rb', line 625

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

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



633
634
635
 
# File 'lib/rubyexcel/sheet.rb', line 633

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

#to_tsv ⇒ Object

the Sheet as a TSV String



641
642
643
 
# File 'lib/rubyexcel/sheet.rb', line 641

def to_tsv
  CSV.generate( :col_sep => "\t" ) { |csv| to_a.each { |r| csv << r } }
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



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

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

#usedrange ⇒ Range

Select the used Range in the Sheet

Returns:

  • (Range) —

    the Sheet’s contents in Range

Raises:

  • (NoMethodError) 


662
663
664
665
 
# File 'lib/rubyexcel/sheet.rb', line 662

def usedrange
  raise NoMethodError, 'Sheet is empty' if empty?
  Range.new( self, 'A1:' + indices_to_address( maxrow, maxcol ) )
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



675
676
677
678
 
# File 'lib/rubyexcel/sheet.rb', line 675

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