Class: RubyExcel::Data
- Inherits:
-
Object
- Object
- RubyExcel::Data
- Includes:
- Enumerable, Address
- Defined in:
- lib/rubyexcel/data.rb
Overview
This class is exposed to the API purely for debugging.
The class which holds a Sheet’s data
Instance Attribute Summary collapse
-
#cols ⇒ Object
readonly
The number of columns in the data.
-
#rows ⇒ Object
readonly
The number of rows in the data.
-
#sheet ⇒ Object
(also: #parent)
The parent Sheet.
Instance Method Summary collapse
-
#<<(other) ⇒ self
Append an object to Data.
-
#advanced_filter!(header, comparison_operator, search_criteria, ...) ⇒ Object
Filter on multiple criteria.
-
#all ⇒ Array<Array>
Returns a copy of the data.
-
#colref_by_header(header) ⇒ String
Finds a Column reference by a header.
-
#compact! ⇒ Object
Removes empty rows and columns from the data.
-
#compact_columns! ⇒ Object
Removes empty columns from the data.
-
#compact_rows! ⇒ Object
Removes empty rows from the data.
-
#delete(object) ⇒ Object
Deletes the data referenced by an object.
-
#delete_all ⇒ Object
Wipe all data.
-
#delete_column(ref) ⇒ Object
Deletes the data referenced by a column id.
-
#delete_range(ref) ⇒ Object
Deletes the data referenced by an address.
-
#delete_row(ref) ⇒ Object
Deletes the data referenced by a row id.
-
#dup ⇒ RubyExcel::Data
Return a copy of self.
-
#each ⇒ Object
Yields each “Row” as an Array.
-
#empty? ⇒ Boolean
Check whether the data (without headers) is empty.
-
#filter!(*headers) {|Array| ... } ⇒ self
Removes all Rows (omitting headers) where the block is falsey.
-
#get_columns!(*headers) ⇒ Object
Select and re-order Columns by a list of headers.
-
#headers ⇒ Object
Return the header section of the data.
-
#index_by_header(header) ⇒ Fixnum
Find a Column index by header.
-
#initialize(sheet, input_data) ⇒ Data
constructor
Creates a RubyExcel::Data 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.
-
#no_headers ⇒ Object
Return the data without headers.
-
#partition(header) {|value| ... } ⇒ Object
Split the data into two sections by evaluating each value in a column.
-
#read(addr) ⇒ Object
(also: #[])
Read a value by address.
-
#reverse_columns! ⇒ Object
Reverse the data Columns.
-
#reverse_rows! ⇒ Object
Reverse the data Rows (without affecting the headers).
-
#skip_headers {|data| ... } ⇒ Array<Array>
Perform an operation on the data without affecting the headers.
-
#sort!(&block) ⇒ Object
Sort the data according to the block.
-
#sort_by!(&block) ⇒ Object
Sort the data according to the block value.
-
#uniq!(header) ⇒ Object
(also: #unique!)
Unique the rows according to the values within a Column, selected by header.
-
#write(addr, val) ⇒ Object
(also: #[]=)
Write a value into the data.
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(sheet, input_data) ⇒ Data
Creates a RubyExcel::Data instance
32 33 34 35 36 37 |
# File 'lib/rubyexcel/data.rb', line 32 def initialize( sheet, input_data ) ( input_data.kind_of?( Array ) && input_data.all? { |el| el.kind_of?( Array ) } ) or fail ArgumentError, 'Input must be Array of Arrays' @sheet = sheet @data = input_data.dup calc_dimensions end |
Instance Attribute Details
#cols ⇒ Object (readonly)
The number of columns in the data
19 20 21 |
# File 'lib/rubyexcel/data.rb', line 19 def cols @cols end |
#rows ⇒ Object (readonly)
The number of rows in the data
16 17 18 |
# File 'lib/rubyexcel/data.rb', line 16 def rows @rows end |
#sheet ⇒ Object Also known as: parent
The parent Sheet
22 23 24 |
# File 'lib/rubyexcel/data.rb', line 22 def sheet @sheet end |
Instance Method Details
#<<(other) ⇒ self
Append an object to Data
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
# File 'lib/rubyexcel/data.rb', line 46 def <<( other ) case other when Array if multi_array?( other ) all.all?(&:empty?) ? @data = other : @data += other else all.all?(&:empty?) ? @data = [ other ] : @data << other end when Hash ; @data += _convert_hash( other ) when Sheet ; empty? ? @data = other.data.all.dup : @data += other.data.dup.no_headers when Row ; @data << other.to_a.dup when Column ; @data.map!.with_index { |row, i| row << other[ i+1 ] } else ; @data[0] << other end calc_dimensions self end |
#advanced_filter!(header, comparison_operator, search_criteria, ...) ⇒ Object
80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
# File 'lib/rubyexcel/data.rb', line 80 def advanced_filter!( *args ) hrows = sheet.header_rows args.length % 3 == 0 or fail ArgumentError, 'Number of arguments must be a multiple of 3' 1.step( args.length - 2, 3 ) { |i| args[i].is_a?( Symbol ) or fail ArgumentError, 'Operator must be a symbol: ' + args[i].to_s } 0.step( args.length - 3, 3 ) { |i| index_by_header( args[i] ) } @data = @data.select.with_index do |row, i| if hrows > i true else args.each_slice(3).map do |h, op, crit| row[ index_by_header( h ) - 1 ].send( op, crit ) end.all? end end calc_dimensions end |
#all ⇒ Array<Array>
Returns a copy of the data
104 105 106 |
# File 'lib/rubyexcel/data.rb', line 104 def all @data.dup end |
#colref_by_header(header) ⇒ String
Finds a Column reference by a header
117 118 119 120 121 122 |
# File 'lib/rubyexcel/data.rb', line 117 def colref_by_header( header ) return header.idx if header.is_a?( Column ) sheet.header_rows > 0 or fail NoMethodError, 'No header rows present' @data[ 0..sheet.header_rows-1 ].each { |r| idx = r.index( header ); return col_letter( idx+1 ) if idx } fail IndexError, header.to_s + ' is not a valid header' end |
#compact! ⇒ Object
Removes empty rows and columns from the data
128 129 130 131 |
# File 'lib/rubyexcel/data.rb', line 128 def compact! compact_columns! compact_rows! end |
#compact_columns! ⇒ Object
Removes empty columns from the data
137 138 139 140 141 |
# File 'lib/rubyexcel/data.rb', line 137 def compact_columns! ensure_shape @data = @data.transpose.delete_if { |ar| ar.all? { |el| el.to_s.empty? } || ar.empty? }.transpose calc_dimensions end |
#compact_rows! ⇒ Object
Removes empty rows from the data
147 148 149 150 |
# File 'lib/rubyexcel/data.rb', line 147 def compact_rows! @data.delete_if { |ar| ar.all? { |el| el.to_s.empty? } || ar.empty? } calc_dimensions end |
#delete(object) ⇒ Object
Deletes the data referenced by an object
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
# File 'lib/rubyexcel/data.rb', line 159 def delete( object ) case object when Row @data.slice!( object.idx - 1 ) when Column idx = col_index( object.idx ) - 1 @data.each { |r| r.slice! idx } when Element addresses = ( object.address ) indices = [ address_to_indices( addresses.first.first ), address_to_indices( addresses.last.last ) ].flatten.map { |n| n-1 } @data[ indices[0]..indices[2] ].each { |r| r.slice!( indices[1], indices[3] - indices[1] + 1 ) } @data.delete_if.with_index { |r,i| r.empty? && i.between?( indices[0], indices[2] ) } else fail NoMethodError, object.class.to_s + ' is not supported' end calc_dimensions end |
#delete_all ⇒ Object
Wipe all data
181 182 183 |
# File 'lib/rubyexcel/data.rb', line 181 def delete_all @data = [[]] end |
#delete_column(ref) ⇒ Object
Deletes the data referenced by a column id
189 190 191 |
# File 'lib/rubyexcel/data.rb', line 189 def delete_column( ref ) delete( Column.new( sheet, ref ) ) end |
#delete_range(ref) ⇒ Object
Deletes the data referenced by an address
205 206 207 |
# File 'lib/rubyexcel/data.rb', line 205 def delete_range( ref ) delete( Element.new( sheet, ref ) ) end |
#delete_row(ref) ⇒ Object
Deletes the data referenced by a row id
197 198 199 |
# File 'lib/rubyexcel/data.rb', line 197 def delete_row( ref ) delete( Row.new( sheet, ref ) ) end |
#dup ⇒ RubyExcel::Data
Return a copy of self
215 216 217 |
# File 'lib/rubyexcel/data.rb', line 215 def dup Data.new( sheet, @data.map(&:dup) ) end |
#each ⇒ Object
Yields each “Row” as an Array
233 234 235 236 |
# File 'lib/rubyexcel/data.rb', line 233 def each return to_enum( :each ) unless block_given? @data.each { |ar| yield ar } end |
#empty? ⇒ Boolean
Check whether the data (without headers) is empty
225 226 227 |
# File 'lib/rubyexcel/data.rb', line 225 def empty? no_headers.empty? rescue true end |
#filter!(*headers) {|Array| ... } ⇒ self
Removes all Rows (omitting headers) where the block is falsey
246 247 248 249 250 251 |
# File 'lib/rubyexcel/data.rb', line 246 def filter!( *headers ) hrows = sheet.header_rows idx_array = headers.flatten.map { |header| index_by_header( header ) }.compact @data = @data.select.with_index { |row, i| hrows > i || yield( idx_array.length == 1 ? row[ idx_array[0] - 1 ] : idx_array.map { |idx| row[ idx -1 ] } ) } calc_dimensions end |
#get_columns!(*headers) ⇒ Object
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
261 262 263 264 265 266 267 268 |
# File 'lib/rubyexcel/data.rb', line 261 def get_columns!( *headers ) headers = headers.flatten hrow = sheet.header_rows - 1 ensure_shape @data = @data.transpose.select{ |col| col[0..hrow].any?{ |val| headers.include?( val ) } } @data = @data.sort_by{ |col| headers.index( col[0..hrow].select { |val| headers.include?( val ) }.first ) || headers.length }.transpose calc_dimensions end |
#headers ⇒ Object
Return the header section of the data
274 275 276 277 |
# File 'lib/rubyexcel/data.rb', line 274 def headers return nil if sheet.header_rows.nil? || sheet.header_rows.zero? @data[ 0..sheet.header_rows-1 ] end |
#index_by_header(header) ⇒ Fixnum
Find a Column index by header
286 287 288 289 |
# File 'lib/rubyexcel/data.rb', line 286 def index_by_header( header ) sheet.header_rows > 0 or fail NoMethodError, 'No header rows present' col_index( colref_by_header( header ) ) end |
#insert_columns(before, number = 1) ⇒ Object
Insert blank Columns into the data
298 299 300 301 302 303 |
# File 'lib/rubyexcel/data.rb', line 298 def insert_columns( before, number=1 ) a = Array.new( number, nil ) before = col_index( before ) - 1 @data.map! { |row| row.insert( before, *a ) } calc_dimensions end |
#insert_rows(before, number = 1) ⇒ Object
Insert blank Rows into the data
312 313 314 315 |
# File 'lib/rubyexcel/data.rb', line 312 def insert_rows( before, number=1 ) @data = @data.insert( ( col_index( before ) - 1 ), *Array.new( number, [nil] ) ) calc_dimensions end |
#no_headers ⇒ Object
Return the data without headers
321 322 323 324 |
# File 'lib/rubyexcel/data.rb', line 321 def no_headers return @data unless sheet.header_rows @data[ sheet.header_rows..-1 ] end |
#partition(header) {|value| ... } ⇒ Object
Split the data into two sections by evaluating each value in a column
333 334 335 336 337 338 |
# File 'lib/rubyexcel/data.rb', line 333 def partition( header, &block ) copy = dup idx = index_by_header( header ) d1, d2 = copy.no_headers.partition { |row| yield row[ idx -1 ] } [ copy.headers + d1, copy.headers.map(&:dup) + d2 ] if headers end |
#read(addr) ⇒ Object Also known as: []
Read a value by address
344 345 346 347 348 |
# File 'lib/rubyexcel/data.rb', line 344 def read( addr ) row_idx, col_idx = address_to_indices( addr ) return nil if row_idx > rows @data[ row_idx-1 ][ col_idx-1 ] end |
#reverse_columns! ⇒ Object
Reverse the data Columns
355 356 357 358 |
# File 'lib/rubyexcel/data.rb', line 355 def reverse_columns! ensure_shape @data = @data.transpose.reverse.transpose end |
#reverse_rows! ⇒ Object
Reverse the data Rows (without affecting the headers)
364 365 366 |
# File 'lib/rubyexcel/data.rb', line 364 def reverse_rows! @data = skip_headers &:reverse end |
#skip_headers {|data| ... } ⇒ Array<Array>
Perform an operation on the data without affecting the headers
375 376 377 378 379 380 381 382 383 |
# File 'lib/rubyexcel/data.rb', line 375 def skip_headers return to_enum(:skip_headers) unless block_given? hr = sheet.header_rows if hr > 0 @data[ 0..hr - 1 ] + yield( @data[ hr..-1 ] ) else yield( @data ) end end |
#sort!(&block) ⇒ Object
Sort the data according to the block
389 390 391 |
# File 'lib/rubyexcel/data.rb', line 389 def sort!( &block ) @data = skip_headers { |d| d.sort( &block ) }; self end |
#sort_by!(&block) ⇒ Object
Sort the data according to the block value
397 398 399 |
# File 'lib/rubyexcel/data.rb', line 397 def sort_by!( &block ) @data = skip_headers { |d| d.sort_by( &block ) }; self end |
#uniq!(header) ⇒ Object Also known as: unique!
Unique the rows according to the values within a Column, selected by header
405 406 407 408 409 |
# File 'lib/rubyexcel/data.rb', line 405 def uniq!( header ) column = col_index( colref_by_header( header ) ) @data = skip_headers { |d| d.uniq { |row| row[ column - 1 ] } } calc_dimensions end |
#write(addr, val) ⇒ Object Also known as: []=
Write a value into the data
419 420 421 422 423 424 425 |
# File 'lib/rubyexcel/data.rb', line 419 def write( addr, val ) row_idx, col_idx = address_to_indices( addr ) ( row_idx - rows ).times { @data << [] } @data[ row_idx-1 ][ col_idx-1 ] = val calc_dimensions if row_idx > rows || col_idx > cols val end |