Class: Roo::GenericSpreadsheet
- Inherits:
-
Object
- Object
- Roo::GenericSpreadsheet
- Includes:
- Enumerable
- Defined in:
- lib/roo/generic_spreadsheet.rb
Overview
Base class for all other types of spreadsheets
Direct Known Subclasses
Constant Summary collapse
- TEMP_PREFIX =
"oo_"
Instance Attribute Summary collapse
-
#default_sheet ⇒ Object
Returns the value of attribute default_sheet.
-
#header_line ⇒ Object
sets the line with attribute names (default: 1).
-
#headers ⇒ Object
readonly
Returns the value of attribute headers.
Instance Method Summary collapse
-
#column(columnnumber, sheet = nil) ⇒ Object
returns all values in this column as an array column numbers are 1,2,3,…
-
#each(options = {}) ⇒ Object
you can also pass in a :clean => true option to strip the sheet of odd unicode characters and white spaces around columns.
-
#each_with_pagename ⇒ Object
iterate through all worksheets of a document.
-
#empty?(row, col, sheet = nil) ⇒ Boolean
true if cell is empty.
-
#find(*args) ⇒ Object
find a row either by row number or a condition Caution: this works only within the default sheet -> set default_sheet before you call this method (experimental. see examples in the test_roo.rb file).
-
#first_column(sheet = nil) ⇒ Object
returns the number of the first non-empty column.
-
#first_column_as_letter(sheet = nil) ⇒ Object
first non-empty column as a letter.
-
#first_row(sheet = nil) ⇒ Object
returns the number of the first non-empty row.
-
#info ⇒ Object
returns information of the spreadsheet document and all sheets within this document.
-
#initialize(filename, packed = nil, file_warning = :error, tmpdir = nil) ⇒ GenericSpreadsheet
constructor
A new instance of GenericSpreadsheet.
-
#last_column(sheet = nil) ⇒ Object
returns the number of the last non-empty column.
-
#last_column_as_letter(sheet = nil) ⇒ Object
last non-empty column as a letter.
-
#last_row(sheet = nil) ⇒ Object
returns the number of the last non-empty row.
-
#longest_sheet ⇒ Object
this method lets you find the worksheet with the most data.
-
#method_missing(m, *args) ⇒ Object
when a method like spreadsheet.a42 is called convert it to a call of spreadsheet.cell(‘a’,42).
- #parse(options = {}) ⇒ Object
-
#reload ⇒ Object
reopens and read a spreadsheet document.
-
#row(rownumber, sheet = nil) ⇒ Object
returns all values in this row as an array row numbers are 1,2,3,…
- #row_with(query, return_headers = false) ⇒ Object
-
#set(row, col, value, sheet = nil) ⇒ Object
set a cell to a certain value (this will not be saved back to the spreadsheet file!).
-
#sheet(index, name = false) ⇒ Object
access different worksheets by calling spreadsheet.sheet(1) or spreadsheet.sheet(‘SHEETNAME’).
-
#to_csv(filename = nil, sheet = nil) ⇒ Object
write the current spreadsheet to stdout or into a file.
-
#to_matrix(from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = nil) ⇒ Object
returns a matrix object from the whole sheet or a rectangular area of a sheet.
-
#to_xml ⇒ Object
returns an XML representation of all sheets of a spreadsheet file.
-
#to_yaml(prefix = {}, from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = nil) ⇒ Object
returns a rectangular area (default: all cells) as yaml-output you can add additional attributes with the prefix parameter like: oo.to_yaml(“sheet” => “1”).
Constructor Details
#initialize(filename, packed = nil, file_warning = :error, tmpdir = nil) ⇒ GenericSpreadsheet
Returns a new instance of GenericSpreadsheet.
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
# File 'lib/roo/generic_spreadsheet.rb', line 40 def initialize(filename, packed=nil, file_warning=:error, tmpdir=nil) @cell = Hash.new{|h,k| h[k] = {}} @cell_type = Hash.new{|h,k| h[k] = {}} @cells_read = {} @first_row = {} @last_row = {} @first_column = {} @last_column = {} @style = {} @style_defaults = Hash.new { |h,k| h[k] = [] } @style_definitions = {} @default_sheet = self.sheets.first @formula = {} @header_line = 1 end |
Dynamic Method Handling
This class handles dynamic methods through the method_missing method
#method_missing(m, *args) ⇒ Object
when a method like spreadsheet.a42 is called convert it to a call of spreadsheet.cell(‘a’,42)
369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 |
# File 'lib/roo/generic_spreadsheet.rb', line 369 def method_missing(m, *args) # #aa42 => #cell('aa',42) # #aa42('Sheet1') => #cell('aa',42,'Sheet1') if m =~ /^([a-z]+)(\d)$/ col = Roo::GenericSpreadsheet.letter_to_number($1) row = $2.to_i if args.empty? cell(row,col) else cell(row,col,args.first) end else super end end |
Instance Attribute Details
#default_sheet ⇒ Object
Returns the value of attribute default_sheet.
13 14 15 |
# File 'lib/roo/generic_spreadsheet.rb', line 13 def default_sheet @default_sheet end |
#header_line ⇒ Object
sets the line with attribute names (default: 1)
16 17 18 |
# File 'lib/roo/generic_spreadsheet.rb', line 16 def header_line @header_line end |
#headers ⇒ Object (readonly)
Returns the value of attribute headers.
13 14 15 |
# File 'lib/roo/generic_spreadsheet.rb', line 13 def headers @headers end |
Instance Method Details
#column(columnnumber, sheet = nil) ⇒ Object
returns all values in this column as an array column numbers are 1,2,3,… like in the spreadsheet
266 267 268 269 270 271 272 273 274 275 |
# File 'lib/roo/generic_spreadsheet.rb', line 266 def column(columnnumber,sheet=nil) if columnnumber.class == String columnnumber = Roo::Excel.letter_to_number(columnnumber) end sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] first_row(sheet).upto(last_row(sheet)).map do |row| cell(row,columnnumber,sheet) end end |
#each(options = {}) ⇒ Object
you can also pass in a :clean => true option to strip the sheet of odd unicode characters and white spaces around columns
449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 |
# File 'lib/roo/generic_spreadsheet.rb', line 449 def each(={}) if .empty? 1.upto(last_row) do |line| yield row(line) end else if [:clean] .delete(:clean) @cleaned ||= {} @cleaned[@default_sheet] || clean_sheet(@default_sheet) end if [:header_search] @headers = nil @header_line = row_with([:header_search]) elsif [:first_row,true].include?([:headers]) @headers = [] row(first_row).each_with_index {|x,i| @headers << [x,i + 1]} else set_headers() end headers = @headers || Hash[(first_column..last_column).map do |col| [cell(@header_line,col), col] end] @header_line.upto(last_row) do |line| yield(Hash[headers.map {|k,v| [k,cell(line,v)]}]) end end end |
#each_with_pagename ⇒ Object
iterate through all worksheets of a document
420 421 422 423 424 |
# File 'lib/roo/generic_spreadsheet.rb', line 420 def each_with_pagename self.sheets.each do |s| yield sheet(s,true) end end |
#empty?(row, col, sheet = nil) ⇒ Boolean
true if cell is empty
308 309 310 311 312 313 314 315 |
# File 'lib/roo/generic_spreadsheet.rb', line 308 def empty?(row, col, sheet=nil) sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] or self.class == Roo::Excel row,col = normalize(row,col) contents = cell(row, col, sheet) !contents || (celltype(row, col, sheet) == :string && contents.empty?) \ || (row < first_row(sheet) || row > last_row(sheet) || col < first_column(sheet) || col > last_column(sheet)) end |
#find(*args) ⇒ Object
find a row either by row number or a condition Caution: this works only within the default sheet -> set default_sheet before you call this method (experimental. see examples in the test_roo.rb file)
212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 |
# File 'lib/roo/generic_spreadsheet.rb', line 212 def find(*args) # :nodoc = (args.last.is_a?(Hash) ? args.pop : {}) result_array = [:array] header_for = Hash[1.upto(last_column).map do |col| [col, cell(@header_line,col)] end] #-- id if args[0].class == Fixnum rownum = args[0] if @header_line [Hash[1.upto(self.row().size).map {|j| [header_for.fetch(j), cell(rownum,j)] }]] else self.row(rownum).size.times.map {|j| cell(rownum,j + 1) } end #-- :all elsif args[0] == :all rows = first_row.upto(last_row) # are all conditions met? if (conditions = [:conditions]) && !conditions.empty? column_with = header_for.invert rows = rows.select do |i| conditions.all? { |key,val| cell(i,column_with[key]) == val } end end rows.map do |i| if result_array self.row(i) else Hash[1.upto(self.row(i).size).map do |j| [header_for.fetch(j), cell(i,j)] end] end end end end |
#first_column(sheet = nil) ⇒ Object
returns the number of the first non-empty column
115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
# File 'lib/roo/generic_spreadsheet.rb', line 115 def first_column(sheet=nil) sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @first_column[sheet] return @first_column[sheet] end impossible_value = 999_999 # more than a spreadsheet can hold result = impossible_value @cell[sheet].each_pair {|key,value| x = key.last.to_i # _to_string(key).split(',') result = [result, x].min if value } if @cell[sheet] result = nil if result == impossible_value @first_column[sheet] = result result end |
#first_column_as_letter(sheet = nil) ⇒ Object
first non-empty column as a letter
69 70 71 |
# File 'lib/roo/generic_spreadsheet.rb', line 69 def first_column_as_letter(sheet=nil) Roo::GenericSpreadsheet.number_to_letter(first_column(sheet)) end |
#first_row(sheet = nil) ⇒ Object
returns the number of the first non-empty row
79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
# File 'lib/roo/generic_spreadsheet.rb', line 79 def first_row(sheet=nil) sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @first_row[sheet] return @first_row[sheet] end impossible_value = 999_999 # more than a spreadsheet can hold result = impossible_value @cell[sheet].each_pair {|key,value| y = key.first.to_i # _to_string(key).split(',') result = [result, y].min if value } if @cell[sheet] result = nil if result == impossible_value @first_row[sheet] = result result end |
#info ⇒ Object
returns information of the spreadsheet document and all sheets within this document.
319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 |
# File 'lib/roo/generic_spreadsheet.rb', line 319 def info result = "File: #{File.basename(@filename)}\n"+ "Number of sheets: #{sheets.size}\n"+ "Sheets: #{sheets.join(', ')}\n" n = 1 sheets.each {|sheet| self.default_sheet = sheet result << "Sheet " + n.to_s + ":\n" unless first_row result << " - empty -" else result << " First row: #{first_row}\n" result << " Last row: #{last_row}\n" result << " First column: #{Roo::GenericSpreadsheet.number_to_letter(first_column)}\n" result << " Last column: #{Roo::GenericSpreadsheet.number_to_letter(last_column)}" end result << "\n" if sheet != sheets.last n += 1 } result end |
#last_column(sheet = nil) ⇒ Object
returns the number of the last non-empty column
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
# File 'lib/roo/generic_spreadsheet.rb', line 133 def last_column(sheet=nil) sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @last_column[sheet] return @last_column[sheet] end impossible_value = 0 result = impossible_value @cell[sheet].each_pair {|key,value| x = key.last.to_i # _to_string(key).split(',') result = [result, x].max if value } if @cell[sheet] result = nil if result == impossible_value @last_column[sheet] = result result end |
#last_column_as_letter(sheet = nil) ⇒ Object
last non-empty column as a letter
74 75 76 |
# File 'lib/roo/generic_spreadsheet.rb', line 74 def last_column_as_letter(sheet=nil) Roo::GenericSpreadsheet.number_to_letter(last_column(sheet)) end |
#last_row(sheet = nil) ⇒ Object
returns the number of the last non-empty row
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
# File 'lib/roo/generic_spreadsheet.rb', line 97 def last_row(sheet=nil) sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @last_row[sheet] return @last_row[sheet] end impossible_value = 0 result = impossible_value @cell[sheet].each_pair {|key,value| y = key.first.to_i # _to_string(key).split(',') result = [result, y].max if value } if @cell[sheet] result = nil if result == impossible_value @last_row[sheet] = result result end |
#longest_sheet ⇒ Object
this method lets you find the worksheet with the most data
513 514 515 516 517 |
# File 'lib/roo/generic_spreadsheet.rb', line 513 def longest_sheet sheet(@workbook.worksheets.inject {|m,o| o.row_count > m.row_count ? o : m }.name) end |
#parse(options = {}) ⇒ Object
482 483 484 485 486 487 488 489 490 |
# File 'lib/roo/generic_spreadsheet.rb', line 482 def parse(={}) ary = [] if block_given? each() {|row| ary << yield(row)} else each() {|row| ary << row} end ary end |
#reload ⇒ Object
reopens and read a spreadsheet document
295 296 297 298 299 300 301 302 303 304 305 |
# File 'lib/roo/generic_spreadsheet.rb', line 295 def reload # von Abfrage der Klasse direkt auf .to_s == '..' umgestellt ds = @default_sheet if self.class.to_s == 'Google' initialize(@spreadsheetkey,@user,@password) else initialize(@filename) end self.default_sheet = ds #@first_row = @last_row = @first_column = @last_column = nil end |
#row(rownumber, sheet = nil) ⇒ Object
returns all values in this row as an array row numbers are 1,2,3,… like in the spreadsheet
256 257 258 259 260 261 262 |
# File 'lib/roo/generic_spreadsheet.rb', line 256 def row(rownumber,sheet=nil) sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] first_column(sheet).upto(last_column(sheet)).map do |col| cell(rownumber,col,sheet) end end |
#row_with(query, return_headers = false) ⇒ Object
492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 |
# File 'lib/roo/generic_spreadsheet.rb', line 492 def row_with(query,return_headers=false) query.map! {|x| Array(x.split('*'))} line_no = 0 each do |row| line_no += 1 # makes sure headers is the first part of wildcard search for priority # ex. if UPC and SKU exist for UPC*SKU search, UPC takes the cake headers = query.map do |q| q.map {|i| row.grep(/#{i}/i)[0]}.compact[0] end.compact if headers.length == query.length @header_line = line_no return return_headers ? headers : line_no elsif line_no > 100 raise "Couldn't find header row." end end end |
#set(row, col, value, sheet = nil) ⇒ Object
set a cell to a certain value (this will not be saved back to the spreadsheet file!)
279 280 281 282 283 284 285 286 287 288 289 290 291 292 |
# File 'lib/roo/generic_spreadsheet.rb', line 279 def set(row,col,value,sheet=nil) #:nodoc: sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] row, col = normalize(row,col) cell_type = case value when Fixnum then :float when String, Float then :string else raise ArgumentError, "Type for #{value} not set" end set_value(row,col,value,sheet) set_type(row,col,cell_type,sheet) end |
#sheet(index, name = false) ⇒ Object
access different worksheets by calling spreadsheet.sheet(1) or spreadsheet.sheet(‘SHEETNAME’)
414 415 416 417 |
# File 'lib/roo/generic_spreadsheet.rb', line 414 def sheet(index,name=false) @default_sheet = String === index ? index : self.sheets[index] name ? [@default_sheet,self] : self end |
#to_csv(filename = nil, sheet = nil) ⇒ Object
write the current spreadsheet to stdout or into a file
180 181 182 183 184 185 186 187 188 189 190 191 192 193 |
# File 'lib/roo/generic_spreadsheet.rb', line 180 def to_csv(filename=nil,sheet=nil) sheet ||= @default_sheet if filename File.open(filename,"w") do |file| write_csv_content(file,sheet) end return true else sio = StringIO.new write_csv_content(sio,sheet) sio.rewind return sio.read end end |
#to_matrix(from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = nil) ⇒ Object
returns a matrix object from the whole sheet or a rectangular area of a sheet
196 197 198 199 200 201 202 203 204 205 206 207 |
# File 'lib/roo/generic_spreadsheet.rb', line 196 def to_matrix(from_row=nil, from_column=nil, to_row=nil, to_column=nil,sheet=nil) require 'matrix' sheet ||= @default_sheet return Matrix.empty unless first_row Matrix.rows((from_row||first_row(sheet)).upto(to_row||last_row(sheet)).map do |row| (from_column||first_column(sheet)).upto(to_column||last_column(sheet)).map do |col| cell(row,col) end end) end |
#to_xml ⇒ Object
returns an XML representation of all sheets of a spreadsheet file
342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 |
# File 'lib/roo/generic_spreadsheet.rb', line 342 def to_xml Nokogiri::XML::Builder.new do |xml| xml.spreadsheet { self.sheets.each do |sheet| self.default_sheet = sheet xml.sheet(:name => sheet) { |x| if first_row and last_row and first_column and last_column # sonst gibt es Fehler bei leeren Blaettern first_row.upto(last_row) do |row| first_column.upto(last_column) do |col| unless empty?(row,col) x.cell(cell(row,col), :row =>row, :column => col, :type => celltype(row,col)) end end end end } end } end.to_xml end |
#to_yaml(prefix = {}, from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = nil) ⇒ Object
returns a rectangular area (default: all cells) as yaml-output you can add additional attributes with the prefix parameter like: oo.to_yaml(“sheet” => “1”)
153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
# File 'lib/roo/generic_spreadsheet.rb', line 153 def to_yaml(prefix={}, from_row=nil, from_column=nil, to_row=nil, to_column=nil,sheet=nil) sheet ||= @default_sheet result = "--- \n" return '' unless first_row # empty result if there is no first_row in a sheet (from_row||first_row(sheet)).upto(to_row||last_row(sheet)) do |row| (from_column||first_column(sheet)).upto(to_column||last_column(sheet)) do |col| unless empty?(row,col,sheet) result << "cell_#{row}_#{col}: \n" prefix.each {|k,v| result << " #{k}: #{v} \n" } result << " row: #{row} \n" result << " col: #{col} \n" result << " celltype: #{self.celltype(row,col,sheet)} \n" if self.celltype(row,col,sheet) == :time result << " value: #{Roo::GenericSpreadsheet.integer_to_timestring( self.cell(row,col,sheet))} \n" else result << " value: #{self.cell(row,col,sheet)} \n" end end end end result end |