Class: RubyExcel::Sheet
- Inherits:
-
Object
- Object
- RubyExcel::Sheet
- Includes:
- Address
- Defined in:
- lib/rubyexcel/sheet.rb
Overview
The front-end class for data manipulation and output.
Instance Attribute Summary collapse
-
#data ⇒ Object
readonly
The Data underlying the Sheet.
-
#header_rows ⇒ Object
(also: #headers)
The number of rows treated as headers.
-
#name ⇒ Object
The name of the Sheet.
-
#workbook ⇒ Object
(also: #parent)
The Workbook parent of this Sheet.
Instance Method Summary collapse
-
#+(other) ⇒ RubyExcel::Sheet
Add data with the Sheet.
-
#-(other) ⇒ RubyExcel::Sheet
Subtract data from the Sheet.
-
#<<(other) ⇒ self
Append an object to the Sheet.
-
#[](addr) ⇒ Object
Read a value by address.
-
#[]=(addr, val) ⇒ Object
Write a value by address.
-
#advanced_filter!(header, comparison_operator, search_criteria, ...) ⇒ Object
Filter on multiple criteria.
-
#averageif(find_header, avg_header) { ... } ⇒ Object
Average the values in a Column by searching another Column.
-
#cell(row, col) ⇒ RubyExcel::Cell
(also: #cells)
Access an Cell by indices.
-
#column(index) ⇒ RubyExcel::Column
Access a Column (Section) by its reference.
-
#column_by_header(header) ⇒ RubyExcel::Column
(also: #ch)
Access a Column (Section) by its header.
-
#columns(start_column = 'A', end_column = data.cols) ⇒ Object
Yields each Column to the block.
-
#compact! ⇒ Object
Removes empty Columns and Rows.
-
#delete ⇒ Object
Removes Sheet from the parent Workbook.
-
#delete_columns_if ⇒ Object
Deletes each Column where the block is true.
-
#delete_rows_if ⇒ Object
Deletes each Row where the block is true.
-
#dup ⇒ RubyExcel::Sheet
Return a copy of self.
-
#empty? ⇒ Boolean
Check whether the Sheet contains data.
-
#filter!(header) {|Object| ... } ⇒ self
Removes all Rows (omitting headers) where the block is false.
-
#get_columns!(*headers) ⇒ Object
(also: #gc!)
Select and re-order Columns by a list of headers.
-
#initialize(name, workbook) ⇒ Sheet
constructor
Creates a RubyExcel::Sheet instance.
-
#insert_columns(before, number = 1) ⇒ Object
Insert blank Columns into the data.
-
#insert_rows(before, number = 1) ⇒ Object
Insert blank Rows into the data.
-
#inspect ⇒ Object
View the object for debugging.
-
#load(input_data, header_rows = 1) ⇒ Object
Populate the Sheet with data (overwrite).
-
#match(header) { ... } ⇒ Fixnum?
Find the row number by looking up a value in a Column.
-
#maxcol ⇒ Object
(also: #maxcolumn)
The highest currently used column number.
-
#maxrow ⇒ Object
The highest currently used row number.
-
#method_missing(m, *args, &block) ⇒ Object
Allow shorthand range references and non-bang versions of bang methods.
-
#partition(header) {|value| ... } ⇒ Array<RubyExcel::Sheet, RubyExcel::Sheet>
Split the Sheet into two Sheets by evaluating each value in a column.
-
#range(first_cell, last_cell = nil) ⇒ RubyExcel::Range
Access a Range by address.
-
#respond_to?(m) ⇒ Boolean
Allow for certain method_missing calls.
-
#reverse_columns! ⇒ Object
Reverse the Sheet Columns.
-
#reverse_rows! ⇒ Object
Reverse the Sheet Rows (without affecting the headers).
-
#row(index) ⇒ RubyExcel::Row
Create a Row from an index.
-
#rows(start_row = 1, end_row = data.rows) ⇒ Object
(also: #each)
Yields each Row to the block.
-
#sort_by!(*headers) ⇒ Object
Sort the data by a column, selected by header(s).
-
#split(header) ⇒ RubyExcel::Workbook
Break the Sheet into a Workbook with multiple Sheets, split by the values under a header.
-
#sumif(find_header, sum_header) { ... } ⇒ Object
Sum the values in a Column by searching another Column.
-
#summarise(header) ⇒ Hash
Summarise the values of a Column into a Hash.
-
#to_a ⇒ Object
The Sheet as a 2D Array.
-
#to_excel ⇒ Object
The Sheet as a WIN32OLE Excel Workbook.
-
#to_html ⇒ Object
The Sheet as a String containing an HTML Table.
-
#to_s ⇒ Object
The Sheet as a Tab Seperated Value String.
-
#uniq!(header) ⇒ Object
(also: #unique!)
Remove any Rows with duplicate values within a Column.
-
#vlookup(find_header, return_header) { ... } ⇒ Object
Find a value within a Column by searching another Column.
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
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
#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
When adding another Sheet it won’t import the headers unless this Sheet is empty.
Add data with the Sheet
85 86 87 |
# File 'lib/rubyexcel/sheet.rb', line 85 def +( other ) dup << other end |
#-(other) ⇒ RubyExcel::Sheet
Subtract data from the Sheet
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
When adding another Sheet it won’t import the headers unless this Sheet is empty.
Anything other than an an Array, Hash, Row, Column or Sheet will be appended to the first row
Append an object to the Sheet
113 114 115 116 |
# File 'lib/rubyexcel/sheet.rb', line 113 def <<( other ) data << other self end |
#[](addr) ⇒ Object
Read a value by address
57 58 59 |
# File 'lib/rubyexcel/sheet.rb', line 57 def[]( addr ) range( addr ).value end |
#[]=(addr, val) ⇒ Object
Write a value by address
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
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
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
Indexing is 1-based like Excel VBA
Access an Cell by indices.
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
Index ‘A’ and 1 both select the 1st Column
Access a Column (Section) by its reference.
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.
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
Iterates to the last Column in the Sheet unless given a second argument.
Yields each Column to the block
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 |
#delete ⇒ Object
Removes Sheet from the parent Workbook
222 223 224 |
# File 'lib/rubyexcel/sheet.rb', line 222 def delete workbook.delete self end |
#delete_columns_if ⇒ Object
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_if ⇒ Object
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 |
#dup ⇒ RubyExcel::Sheet
Return a copy of self
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
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
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!
This method can accept either a list of arguments or an Array
Invalid headers will be skipped
Select and re-order Columns by a list of headers
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
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
314 315 316 |
# File 'lib/rubyexcel/sheet.rb', line 314 def insert_rows( *args ) data.insert_rows( *args ); self end |
#inspect ⇒ Object
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)
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
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 |
#maxcol ⇒ Object 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 |
#maxrow ⇒ Object
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
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
These are all valid arguments: (‘A1’) (‘A1:B2’) (‘A:A’) (‘1:1’) (‘A1’, ‘B2’) (cell1) (cell1, cell2)
Access a Range by address.
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
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
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
Iterates to the last Row in the Sheet unless given a second argument.
Yields each Row to the block
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)
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.
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
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
535 536 537 |
# File 'lib/rubyexcel/sheet.rb', line 535 def summarise( header ) ch( header ).summarise end |
#to_a ⇒ Object
The Sheet as a 2D Array
543 544 545 |
# File 'lib/rubyexcel/sheet.rb', line 543 def to_a data.all end |
#to_excel ⇒ Object
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_html ⇒ Object
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_s ⇒ Object
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
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
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 |