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.
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
# File 'lib/roo/generic_spreadsheet.rb', line 39 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)
368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 |
# File 'lib/roo/generic_spreadsheet.rb', line 368 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.
12 13 14 |
# File 'lib/roo/generic_spreadsheet.rb', line 12 def default_sheet @default_sheet end |
#header_line ⇒ Object
sets the line with attribute names (default: 1)
15 16 17 |
# File 'lib/roo/generic_spreadsheet.rb', line 15 def header_line @header_line end |
#headers ⇒ Object (readonly)
Returns the value of attribute headers.
12 13 14 |
# File 'lib/roo/generic_spreadsheet.rb', line 12 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
265 266 267 268 269 270 271 272 273 274 |
# File 'lib/roo/generic_spreadsheet.rb', line 265 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
448 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 |
# File 'lib/roo/generic_spreadsheet.rb', line 448 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
419 420 421 422 423 |
# File 'lib/roo/generic_spreadsheet.rb', line 419 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
307 308 309 310 311 312 313 314 |
# File 'lib/roo/generic_spreadsheet.rb', line 307 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)
211 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 |
# File 'lib/roo/generic_spreadsheet.rb', line 211 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
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
# File 'lib/roo/generic_spreadsheet.rb', line 114 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
68 69 70 |
# File 'lib/roo/generic_spreadsheet.rb', line 68 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
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
# File 'lib/roo/generic_spreadsheet.rb', line 78 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.
318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 |
# File 'lib/roo/generic_spreadsheet.rb', line 318 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
132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
# File 'lib/roo/generic_spreadsheet.rb', line 132 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
73 74 75 |
# File 'lib/roo/generic_spreadsheet.rb', line 73 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
96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
# File 'lib/roo/generic_spreadsheet.rb', line 96 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
512 513 514 515 516 |
# File 'lib/roo/generic_spreadsheet.rb', line 512 def longest_sheet sheet(@workbook.worksheets.inject {|m,o| o.row_count > m.row_count ? o : m }.name) end |
#parse(options = {}) ⇒ Object
481 482 483 484 485 486 487 488 489 |
# File 'lib/roo/generic_spreadsheet.rb', line 481 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
294 295 296 297 298 299 300 301 302 303 304 |
# File 'lib/roo/generic_spreadsheet.rb', line 294 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
255 256 257 258 259 260 261 |
# File 'lib/roo/generic_spreadsheet.rb', line 255 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
491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 |
# File 'lib/roo/generic_spreadsheet.rb', line 491 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!)
278 279 280 281 282 283 284 285 286 287 288 289 290 291 |
# File 'lib/roo/generic_spreadsheet.rb', line 278 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’)
413 414 415 416 |
# File 'lib/roo/generic_spreadsheet.rb', line 413 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
179 180 181 182 183 184 185 186 187 188 189 190 191 192 |
# File 'lib/roo/generic_spreadsheet.rb', line 179 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
195 196 197 198 199 200 201 202 203 204 205 206 |
# File 'lib/roo/generic_spreadsheet.rb', line 195 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
341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 |
# File 'lib/roo/generic_spreadsheet.rb', line 341 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”)
152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 |
# File 'lib/roo/generic_spreadsheet.rb', line 152 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 |