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
deprecated
Deprecated.
Please use #filter! instead
-
#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.
-
#clear_all ⇒ Object
(also: #delete_all)
Delete all data and headers from Sheet.
-
#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 (not counting headers).
-
#filter!(*headers) {|Array| ... } ⇒ self
Removes all Rows (omitting headers) where the block is falsey.
-
#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.
-
#last_column ⇒ RubyExcel::Column
(also: #last_col)
The last Column in the Sheet.
-
#last_row ⇒ RubyExcel::Row
The last Row in the Sheet.
-
#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, #width)
The highest currently used column number.
-
#maxrow ⇒ Object
(also: #length)
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, include_private = false) ⇒ Boolean
Allow for certain method_missing calls.
-
#reverse_columns! ⇒ Object
Reverse the Sheet Columns.
-
#reverse_rows! ⇒ Object
(also: #reverse!)
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.
-
#save_excel(filename = nil, invisible = false) ⇒ WIN32OLE::Workbook
Save the RubyExcel::Sheet as an Excel Workbook.
-
#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
(also: #summarize)
Return a Hash containing the Column values and the number of times each appears.
-
#summarise!(header) ⇒ Object
(also: #summarize!)
Overwrite the sheet with the Summary of a Column.
-
#to_a ⇒ Object
The Sheet as a 2D Array.
-
#to_csv ⇒ Object
The Sheet as a CSV String.
-
#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 (Strips extra whitespace).
-
#to_tsv ⇒ Object
the Sheet as a TSV String.
-
#uniq!(header) ⇒ Object
(also: #unique!)
Remove any Rows with duplicate values within a Column.
-
#usedrange ⇒ Range
Select the used Range in the Sheet.
-
#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, #step_index, #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.
409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 |
# File 'lib/rubyexcel/sheet.rb', line 409 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
Please use #filter! instead
132 133 134 135 |
# File 'lib/rubyexcel/sheet.rb', line 132 def advanced_filter!( *args ) warn "[DEPRECATION] `advanced_filter!` is deprecated. Please use `filter!` instead." data.advanced_filter!( *args ); self end |
#averageif(find_header, avg_header) { ... } ⇒ Object
Average the values in a Column by searching another Column
145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
# File 'lib/rubyexcel/sheet.rb', line 145 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.
169 170 171 |
# File 'lib/rubyexcel/sheet.rb', line 169 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
178 179 180 181 |
# File 'lib/rubyexcel/sheet.rb', line 178 def clear_all data.delete_all self end |
#column(index) ⇒ RubyExcel::Column
Index ‘A’ and 1 both select the 1st Column
Access a Column (Section) by its reference.
192 193 194 |
# File 'lib/rubyexcel/sheet.rb', line 192 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.
203 204 205 |
# File 'lib/rubyexcel/sheet.rb', line 203 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
216 217 218 219 220 |
# File 'lib/rubyexcel/sheet.rb', line 216 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
226 227 228 |
# File 'lib/rubyexcel/sheet.rb', line 226 def compact! data.compact!; self end |
#delete ⇒ Object
Removes Sheet from the parent Workbook
234 235 236 |
# File 'lib/rubyexcel/sheet.rb', line 234 def delete workbook.delete self end |
#delete_columns_if ⇒ Object
Deletes each Column where the block is true
251 252 253 254 |
# File 'lib/rubyexcel/sheet.rb', line 251 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
242 243 244 245 |
# File 'lib/rubyexcel/sheet.rb', line 242 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
262 263 264 265 266 267 268 269 270 271 |
# File 'lib/rubyexcel/sheet.rb', line 262 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)
279 280 281 |
# File 'lib/rubyexcel/sheet.rb', line 279 def empty? data.empty? end |
#filter!(*headers) {|Array| ... } ⇒ self
Removes all Rows (omitting headers) where the block is falsey
291 292 293 294 |
# File 'lib/rubyexcel/sheet.rb', line 291 def filter!( *headers, &block ) return to_enum( :filter!, headers ) unless block_given? data.filter!( *headers, &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
304 305 306 |
# File 'lib/rubyexcel/sheet.rb', line 304 def get_columns!( *headers ) data.get_columns!( *headers ); self end |
#insert_columns(before, number = 1) ⇒ Object
316 317 318 |
# File 'lib/rubyexcel/sheet.rb', line 316 def insert_columns( *args ) data.insert_columns( *args ); self end |
#insert_rows(before, number = 1) ⇒ Object
327 328 329 |
# File 'lib/rubyexcel/sheet.rb', line 327 def insert_rows( *args ) data.insert_rows( *args ); self end |
#inspect ⇒ Object
View the object for debugging
335 336 337 |
# File 'lib/rubyexcel/sheet.rb', line 335 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
345 346 347 |
# File 'lib/rubyexcel/sheet.rb', line 345 def last_column column( maxcol ) end |
#last_row ⇒ RubyExcel::Row
The last Row in the Sheet
356 357 358 |
# File 'lib/rubyexcel/sheet.rb', line 356 def last_row row( maxrow ) end |
#load(input_data, header_rows = 1) ⇒ Object
Populate the Sheet with data (overwrite)
367 368 369 370 371 372 |
# File 'lib/rubyexcel/sheet.rb', line 367 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
382 383 384 |
# File 'lib/rubyexcel/sheet.rb', line 382 def match( header, &block ) row_id( column_by_header( header ).find( &block ) ) rescue nil end |
#maxcol ⇒ Object Also known as: maxcolumn, width
The highest currently used column number
399 400 401 |
# File 'lib/rubyexcel/sheet.rb', line 399 def maxcol data.cols end |
#maxrow ⇒ Object Also known as: length
The highest currently used row number
390 391 392 |
# File 'lib/rubyexcel/sheet.rb', line 390 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
454 455 456 |
# File 'lib/rubyexcel/sheet.rb', line 454 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.
474 475 476 477 |
# File 'lib/rubyexcel/sheet.rb', line 474 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, include_private = false) ⇒ Boolean
Allow for certain method_missing calls
434 435 436 437 438 439 440 441 442 443 444 |
# File 'lib/rubyexcel/sheet.rb', line 434 def respond_to?( m, include_private = false ) 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
483 484 485 |
# File 'lib/rubyexcel/sheet.rb', line 483 def reverse_columns! data.reverse_columns!; self end |
#reverse_rows! ⇒ Object Also known as: reverse!
Reverse the Sheet Rows (without affecting the headers)
491 492 493 |
# File 'lib/rubyexcel/sheet.rb', line 491 def reverse_rows! data.reverse_rows!; self end |
#row(index) ⇒ RubyExcel::Row
Create a Row from an index
503 504 505 |
# File 'lib/rubyexcel/sheet.rb', line 503 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
515 516 517 518 |
# File 'lib/rubyexcel/sheet.rb', line 515 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
529 530 531 |
# File 'lib/rubyexcel/sheet.rb', line 529 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)
539 540 541 542 543 544 545 |
# File 'lib/rubyexcel/sheet.rb', line 539 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.
554 555 556 557 558 559 560 561 |
# File 'lib/rubyexcel/sheet.rb', line 554 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
571 572 573 574 575 |
# File 'lib/rubyexcel/sheet.rb', line 571 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.
584 585 586 |
# File 'lib/rubyexcel/sheet.rb', line 584 def summarise( header ) ch( header ).summarise end |
#summarise!(header) ⇒ Object Also known as: summarize!
Overwrite the sheet with the Summary of a Column
595 596 597 |
# File 'lib/rubyexcel/sheet.rb', line 595 def summarise!( header ) load( summarise( header ).to_a.unshift [ header, 'Count' ] ) end |
#to_a ⇒ Object
The Sheet as a 2D Array
604 605 606 |
# File 'lib/rubyexcel/sheet.rb', line 604 def to_a data.all end |
#to_csv ⇒ Object
The Sheet as a CSV String
612 613 614 |
# File 'lib/rubyexcel/sheet.rb', line 612 def to_csv CSV.generate { |csv| to_a.each { |r| csv << r } } end |
#to_excel ⇒ Object
This requires Windows and MS Excel
The Sheet as a WIN32OLE Excel Workbook
621 622 623 |
# File 'lib/rubyexcel/sheet.rb', line 621 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
629 630 631 |
# File 'lib/rubyexcel/sheet.rb', line 629 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)
637 638 639 |
# File 'lib/rubyexcel/sheet.rb', line 637 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
645 646 647 |
# File 'lib/rubyexcel/sheet.rb', line 645 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
655 656 657 |
# File 'lib/rubyexcel/sheet.rb', line 655 def uniq!( header ) data.uniq!( header ); self end |
#usedrange ⇒ Range
Select the used Range in the Sheet
666 667 668 669 |
# File 'lib/rubyexcel/sheet.rb', line 666 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
679 680 681 682 |
# File 'lib/rubyexcel/sheet.rb', line 679 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 |