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, #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.



373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
# File 'lib/rubyexcel/sheet.rb', line 373

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

#dataObject (readonly)

The Data underlying the Sheet



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

def data
  @data
end

#header_rowsObject 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

#nameObject

The name of the Sheet



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

def name
  @name
end

#workbookObject 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

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



180
181
182
# File 'lib/rubyexcel/sheet.rb', line 180

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:



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

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



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

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



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

def compact!
  data.compact!; self
end

#deleteObject

Removes Sheet from the parent Workbook



222
223
224
# File 'lib/rubyexcel/sheet.rb', line 222

def delete
  workbook.delete self
end

#delete_columns_ifObject

Deletes each Column where the block is true



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

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_ifObject

Deletes each Row where the block is true



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

def delete_rows_if
  return to_enum( :delete_rows_if ) unless block_given?
  rows.reverse_each { |r| r.delete if yield r }; self
end

#dupRubyExcel::Sheet

Return a copy of self

Returns:



250
251
252
253
254
255
256
257
258
259
# File 'lib/rubyexcel/sheet.rb', line 250

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



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

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)


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

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



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

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



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

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



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

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

#inspectObject

View the object for debugging



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

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



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

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



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

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

#maxcolObject Also known as: maxcolumn

The highest currently used column number



364
365
366
# File 'lib/rubyexcel/sheet.rb', line 364

def maxcol
  data.cols
end

#maxrowObject

The highest currently used row number



356
357
358
# File 'lib/rubyexcel/sheet.rb', line 356

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:



418
419
420
# File 'lib/rubyexcel/sheet.rb', line 418

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:



438
439
440
441
# File 'lib/rubyexcel/sheet.rb', line 438

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)


398
399
400
401
402
403
404
405
406
407
408
# File 'lib/rubyexcel/sheet.rb', line 398

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



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

def reverse_columns!
  data.reverse_columns!; self
end

#reverse_rows!Object

Reverse the Sheet Rows (without affecting the headers)



455
456
457
# File 'lib/rubyexcel/sheet.rb', line 455

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:



466
467
468
# File 'lib/rubyexcel/sheet.rb', line 466

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



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

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


490
491
492
493
494
495
496
# File 'lib/rubyexcel/sheet.rb', line 490

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:



505
506
507
508
509
510
511
512
# File 'lib/rubyexcel/sheet.rb', line 505

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



522
523
524
525
526
# File 'lib/rubyexcel/sheet.rb', line 522

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)


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

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

#to_aObject

The Sheet as a 2D Array



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

def to_a
  data.all
end

#to_excelObject

Note:

This requires Windows and MS Excel

The Sheet as a WIN32OLE Excel Workbook



552
553
554
# File 'lib/rubyexcel/sheet.rb', line 552

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

#to_htmlObject

The Sheet as a String containing an HTML Table



560
561
562
# File 'lib/rubyexcel/sheet.rb', line 560

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_sObject

The Sheet as a Tab Seperated Value String



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

def to_s
  data.map { |ar| ar.map { |v| v.to_s.gsub(/\t|\n|\r/,' ') }.join "\t" }.join( $/ )
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



578
579
580
# File 'lib/rubyexcel/sheet.rb', line 578

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



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

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