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).
-
#export(win32ole_sheet) ⇒ Object
Export data to a specific WIN32OLE Excel Sheet.
-
#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_safe_format ⇒ Object
-
#to_safe_format! ⇒ Object
Standardise the data for safe export to Excel.
-
#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.
420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 |
# File 'lib/rubyexcel/sheet.rb', line 420 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 |
#export(win32ole_sheet) ⇒ Object
Export data to a specific WIN32OLE Excel Sheet
290 291 292 |
# File 'lib/rubyexcel/sheet.rb', line 290 def export( win32ole_sheet ) parent.dump_to_sheet( to_a, win32ole_sheet ) end |
#filter!(*headers) {|Array| ... } ⇒ self
Removes all Rows (omitting headers) where the block is falsey
302 303 304 305 |
# File 'lib/rubyexcel/sheet.rb', line 302 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
315 316 317 |
# File 'lib/rubyexcel/sheet.rb', line 315 def get_columns!( *headers ) data.get_columns!( *headers ); self end |
#insert_columns(before, number = 1) ⇒ Object
327 328 329 |
# File 'lib/rubyexcel/sheet.rb', line 327 def insert_columns( *args ) data.insert_columns( *args ); self end |
#insert_rows(before, number = 1) ⇒ Object
338 339 340 |
# File 'lib/rubyexcel/sheet.rb', line 338 def insert_rows( *args ) data.insert_rows( *args ); self end |
#inspect ⇒ Object
View the object for debugging
346 347 348 |
# File 'lib/rubyexcel/sheet.rb', line 346 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
356 357 358 |
# File 'lib/rubyexcel/sheet.rb', line 356 def last_column column( maxcol ) end |
#last_row ⇒ RubyExcel::Row
The last Row in the Sheet
367 368 369 |
# File 'lib/rubyexcel/sheet.rb', line 367 def last_row row( maxrow ) end |
#load(input_data, header_rows = 1) ⇒ Object
Populate the Sheet with data (overwrite)
378 379 380 381 382 383 |
# File 'lib/rubyexcel/sheet.rb', line 378 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
393 394 395 |
# File 'lib/rubyexcel/sheet.rb', line 393 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
410 411 412 |
# File 'lib/rubyexcel/sheet.rb', line 410 def maxcol data.cols end |
#maxrow ⇒ Object Also known as: length
The highest currently used row number
401 402 403 |
# File 'lib/rubyexcel/sheet.rb', line 401 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
465 466 467 |
# File 'lib/rubyexcel/sheet.rb', line 465 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.
485 486 487 488 |
# File 'lib/rubyexcel/sheet.rb', line 485 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
445 446 447 448 449 450 451 452 453 454 455 |
# File 'lib/rubyexcel/sheet.rb', line 445 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
494 495 496 |
# File 'lib/rubyexcel/sheet.rb', line 494 def reverse_columns! data.reverse_columns!; self end |
#reverse_rows! ⇒ Object Also known as: reverse!
Reverse the Sheet Rows (without affecting the headers)
502 503 504 |
# File 'lib/rubyexcel/sheet.rb', line 502 def reverse_rows! data.reverse_rows!; self end |
#row(index) ⇒ RubyExcel::Row
Create a Row from an index
514 515 516 |
# File 'lib/rubyexcel/sheet.rb', line 514 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
526 527 528 529 |
# File 'lib/rubyexcel/sheet.rb', line 526 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
540 541 542 |
# File 'lib/rubyexcel/sheet.rb', line 540 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)
550 551 552 553 554 555 556 557 558 559 |
# File 'lib/rubyexcel/sheet.rb', line 550 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 rescue ArgumentError => err raise( NoMethodError, 'Item not comparable in "' + headers.flatten.map(&:to_s).join(', ') + '"' ) if err. == 'comparison of Array with Array failed' raise err end |
#split(header) ⇒ RubyExcel::Workbook
Break the Sheet into a Workbook with multiple Sheets, split by the values under a header.
568 569 570 571 572 573 574 575 |
# File 'lib/rubyexcel/sheet.rb', line 568 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
585 586 587 588 589 |
# File 'lib/rubyexcel/sheet.rb', line 585 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.
598 599 600 |
# File 'lib/rubyexcel/sheet.rb', line 598 def summarise( header ) ch( header ).summarise end |
#summarise!(header) ⇒ Object Also known as: summarize!
Overwrite the sheet with the Summary of a Column
609 610 611 |
# File 'lib/rubyexcel/sheet.rb', line 609 def summarise!( header ) load( summarise( header ).to_a.unshift [ header, 'Count' ] ) end |
#to_a ⇒ Object
The Sheet as a 2D Array
618 619 620 |
# File 'lib/rubyexcel/sheet.rb', line 618 def to_a data.all end |
#to_csv ⇒ Object
The Sheet as a CSV String
626 627 628 |
# File 'lib/rubyexcel/sheet.rb', line 626 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
635 636 637 |
# File 'lib/rubyexcel/sheet.rb', line 635 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
643 644 645 |
# File 'lib/rubyexcel/sheet.rb', line 643 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)
651 652 653 |
# File 'lib/rubyexcel/sheet.rb', line 651 def to_s data.map { |ar| ar.map { |v| v.to_s.gsub(/\t|\n|\r/,' ') }.join "\t" }.join( $/ ) end |
#to_safe_format ⇒ Object
657 658 659 |
# File 'lib/rubyexcel/sheet.rb', line 657 def to_safe_format dup.to_safe_format! end |
#to_safe_format! ⇒ Object
Standardise the data for safe export to Excel.
Set each cell contents to a string and remove leading equals signs.
666 667 668 669 670 671 672 673 674 |
# File 'lib/rubyexcel/sheet.rb', line 666 def to_safe_format! rows { |r| r.map! { |v| if v.is_a?( String ) v[0] == '=' ? v.sub( /\A=/,"'=" ) : v else v.to_s end } }; self end |
#to_tsv ⇒ Object
the Sheet as a TSV String
680 681 682 |
# File 'lib/rubyexcel/sheet.rb', line 680 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
690 691 692 |
# File 'lib/rubyexcel/sheet.rb', line 690 def uniq!( header ) data.uniq!( header ); self end |
#usedrange ⇒ Range
Select the used Range in the Sheet
701 702 703 704 |
# File 'lib/rubyexcel/sheet.rb', line 701 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
714 715 716 717 |
# File 'lib/rubyexcel/sheet.rb', line 714 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 |