Class: Roo::Base
Overview
Base class for all other types of spreadsheets
Direct Known Subclasses
Constant Summary collapse
- TEMP_PREFIX =
'roo_'.freeze
- MAX_ROW_COL =
999_999.freeze
- MIN_ROW_COL =
0.freeze
Instance Attribute Summary collapse
-
#header_line ⇒ Object
sets the line with attribute names (default: 1).
-
#headers ⇒ Object
readonly
Returns the value of attribute headers.
Instance Method Summary collapse
- #cell_type_by_value(value) ⇒ Object
- #close ⇒ Object
-
#collect_last_row_col_for_sheet(sheet) ⇒ Object
Collect first/last row/column from sheet.
-
#column(column_number, sheet = default_sheet) ⇒ Object
returns all values in this column as an array column numbers are 1,2,3,…
- #default_sheet ⇒ Object
-
#default_sheet=(sheet) ⇒ Object
sets the working sheet in the document ‘sheet’ can be a number (1 = first sheet) or the name of a sheet.
-
#each(options = {}) ⇒ Object
you can also pass in a :clean => true option to strip the sheet of control characters and white spaces around columns.
-
#each_with_pagename ⇒ Object
iterate through all worksheets of a document.
-
#empty?(row, col, sheet = default_sheet) ⇒ 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_as_letter(sheet = default_sheet) ⇒ Object
first non-empty column as a letter.
-
#first_last_row_col_for_sheet(sheet) ⇒ Object
Set first/last row/column for sheet.
-
#info ⇒ Object
returns information of the spreadsheet document and all sheets within this document.
-
#initialize(filename, options = {}, _file_warning = :error, _tmpdir = nil) ⇒ Base
constructor
A new instance of Base.
- #inspect ⇒ Object
-
#last_column_as_letter(sheet = default_sheet) ⇒ Object
last non-empty column as a letter.
-
#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(row_number, sheet = default_sheet) ⇒ 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 = default_sheet) ⇒ 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, separator = ',', sheet = default_sheet) ⇒ 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 = default_sheet) ⇒ 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 = default_sheet) ⇒ 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, options = {}, _file_warning = :error, _tmpdir = nil) ⇒ Base
Returns a new instance of Base.
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
# File 'lib/roo/base.rb', line 21 def initialize(filename, = {}, _file_warning = :error, _tmpdir = nil) @filename = filename @options = @cell = {} @cell_type = {} @cells_read = {} @first_row = {} @last_row = {} @first_column = {} @last_column = {} @header_line = 1 rescue => e # clean up any temp files, but only if an error was raised close raise e 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)
303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 |
# File 'lib/roo/base.rb', line 303 def method_missing(m, *args) # #aa42 => #cell('aa',42) # #aa42('Sheet1') => #cell('aa',42,'Sheet1') if m =~ /^([a-z]+)(\d+)$/ col = ::Roo::Utils.letter_to_number(Regexp.last_match[1]) row = Regexp.last_match[2].to_i if args.empty? cell(row, col) else cell(row, col, args.first) end else super end end |
Instance Attribute Details
#header_line ⇒ Object
sets the line with attribute names (default: 1)
19 20 21 |
# File 'lib/roo/base.rb', line 19 def header_line @header_line end |
#headers ⇒ Object (readonly)
Returns the value of attribute headers.
16 17 18 |
# File 'lib/roo/base.rb', line 16 def headers @headers end |
Instance Method Details
#cell_type_by_value(value) ⇒ Object
224 225 226 227 228 229 230 231 |
# File 'lib/roo/base.rb', line 224 def cell_type_by_value(value) case value when Fixnum then :float when String, Float then :string else fail ArgumentError, "Type for #{value} not set" end end |
#close ⇒ Object
40 41 42 43 44 |
# File 'lib/roo/base.rb', line 40 def close return nil unless @tmpdirs @tmpdirs.each { |dir| ::FileUtils.remove_entry(dir) } nil end |
#collect_last_row_col_for_sheet(sheet) ⇒ Object
Collect first/last row/column from sheet
84 85 86 87 88 89 90 91 92 93 94 95 |
# File 'lib/roo/base.rb', line 84 def collect_last_row_col_for_sheet(sheet) first_row = first_column = MAX_ROW_COL last_row = last_column = MIN_ROW_COL @cell[sheet].each_pair do|key, value| next unless value first_row = [first_row, key.first.to_i].min last_row = [last_row, key.first.to_i].max first_column = [first_column, key.last.to_i].min last_column = [last_column, key.last.to_i].max end if @cell[sheet] { first_row: first_row, first_column: first_column, last_row: last_row, last_column: last_column } end |
#column(column_number, sheet = default_sheet) ⇒ Object
returns all values in this column as an array column numbers are 1,2,3,… like in the spreadsheet
204 205 206 207 208 209 210 211 212 |
# File 'lib/roo/base.rb', line 204 def column(column_number, sheet = default_sheet) if column_number.is_a?(::String) column_number = ::Roo::Utils.letter_to_number(column_number) end read_cells(sheet) first_row(sheet).upto(last_row(sheet)).map do |row| cell(row, column_number, sheet) end end |
#default_sheet ⇒ Object
46 47 48 |
# File 'lib/roo/base.rb', line 46 def default_sheet @default_sheet ||= sheets.first end |
#default_sheet=(sheet) ⇒ Object
sets the working sheet in the document ‘sheet’ can be a number (1 = first sheet) or the name of a sheet.
52 53 54 55 56 57 |
# File 'lib/roo/base.rb', line 52 def default_sheet=(sheet) validate_sheet!(sheet) @default_sheet = sheet @first_row[sheet] = @last_row[sheet] = @first_column[sheet] = @last_column[sheet] = nil @cells_read[sheet] = false end |
#each(options = {}) ⇒ Object
you can also pass in a :clean => true option to strip the sheet of control characters and white spaces around columns
355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 |
# File 'lib/roo/base.rb', line 355 def each( = {}) return to_enum(:each, ) unless block_given? if .empty? 1.upto(last_row) do |line| yield row(line) end else clean_sheet_if_need() search_or_set_header() 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
327 328 329 330 331 |
# File 'lib/roo/base.rb', line 327 def each_with_pagename sheets.each do |s| yield sheet(s, true) end end |
#empty?(row, col, sheet = default_sheet) ⇒ Boolean
true if cell is empty
241 242 243 244 245 246 247 |
# File 'lib/roo/base.rb', line 241 def empty?(row, col, sheet = default_sheet) read_cells(sheet) 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)
180 181 182 183 184 185 186 187 188 189 190 191 |
# File 'lib/roo/base.rb', line 180 def find(*args) # :nodoc = (args.last.is_a?(Hash) ? args.pop : {}) case args[0] when Fixnum find_by_row(args[0]) when :all find_by_conditions() else fail ArgumentError, "unexpected arg #{args[0].inspect}, pass a row index or :all" end end |
#first_column_as_letter(sheet = default_sheet) ⇒ Object
first non-empty column as a letter
60 61 62 |
# File 'lib/roo/base.rb', line 60 def first_column_as_letter(sheet = default_sheet) ::Roo::Utils.number_to_letter(first_column(sheet)) end |
#first_last_row_col_for_sheet(sheet) ⇒ Object
Set first/last row/column for sheet
70 71 72 73 74 75 76 77 78 79 80 81 |
# File 'lib/roo/base.rb', line 70 def first_last_row_col_for_sheet(sheet) @first_last_row_cols ||= {} @first_last_row_cols[sheet] ||= begin result = collect_last_row_col_for_sheet(sheet) { first_row: result[:first_row] == MAX_ROW_COL ? nil : result[:first_row], first_column: result[:first_column] == MAX_ROW_COL ? nil : result[:first_column], last_row: result[:last_row] == MIN_ROW_COL ? nil : result[:last_row], last_column: result[:last_column] == MIN_ROW_COL ? nil : result[:last_column] } end end |
#info ⇒ Object
returns information of the spreadsheet document and all sheets within this document.
251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 |
# File 'lib/roo/base.rb', line 251 def info without_changing_default_sheet do result = "File: #{File.basename(@filename)}\n"\ "Number of sheets: #{sheets.size}\n"\ "Sheets: #{sheets.join(', ')}\n" n = 1 sheets.each do|sheet| self.default_sheet = sheet result << 'Sheet ' + n.to_s + ":\n" if first_row result << " First row: #{first_row}\n" result << " Last row: #{last_row}\n" result << " First column: #{::Roo::Utils.number_to_letter(first_column)}\n" result << " Last column: #{::Roo::Utils.number_to_letter(last_column)}" else result << ' - empty -' end result << "\n" if sheet != sheets.last n += 1 end result end end |
#inspect ⇒ Object
173 174 175 |
# File 'lib/roo/base.rb', line 173 def inspect "<##{self.class}:#{object_id.to_s(8)} #{instance_variables.join(' ')}>" end |
#last_column_as_letter(sheet = default_sheet) ⇒ Object
last non-empty column as a letter
65 66 67 |
# File 'lib/roo/base.rb', line 65 def last_column_as_letter(sheet = default_sheet) ::Roo::Utils.number_to_letter(last_column(sheet)) end |
#parse(options = {}) ⇒ Object
376 377 378 379 380 381 382 383 |
# File 'lib/roo/base.rb', line 376 def parse( = {}) ary = [] each() do |row| yield(row) if block_given? ary << row end ary end |
#reload ⇒ Object
reopens and read a spreadsheet document
234 235 236 237 238 |
# File 'lib/roo/base.rb', line 234 def reload ds = default_sheet reinitialize self.default_sheet = ds end |
#row(row_number, sheet = default_sheet) ⇒ Object
returns all values in this row as an array row numbers are 1,2,3,… like in the spreadsheet
195 196 197 198 199 200 |
# File 'lib/roo/base.rb', line 195 def row(row_number, sheet = default_sheet) read_cells(sheet) first_column(sheet).upto(last_column(sheet)).map do |col| cell(row_number, col, sheet) end end |
#row_with(query, return_headers = false) ⇒ Object
385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 |
# File 'lib/roo/base.rb', line 385 def row_with(query, return_headers = false) line_no = 0 each do |row| line_no += 1 headers = query.map { |q| row.grep(q)[0] }.compact if headers.length == query.length @header_line = line_no return return_headers ? headers : line_no elsif line_no > 100 raise Roo::HeaderRowNotFoundError end end raise Roo::HeaderRowNotFoundError end |
#set(row, col, value, sheet = default_sheet) ⇒ Object
set a cell to a certain value (this will not be saved back to the spreadsheet file!)
216 217 218 219 220 221 222 |
# File 'lib/roo/base.rb', line 216 def set(row, col, value, sheet = default_sheet) #:nodoc: read_cells(sheet) row, col = normalize(row, col) cell_type = cell_type_by_value(value) 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’)
321 322 323 324 |
# File 'lib/roo/base.rb', line 321 def sheet(index, name = false) self.default_sheet = index.is_a?(::String) ? index : sheets[index] name ? [default_sheet, self] : self end |
#to_csv(filename = nil, separator = ',', sheet = default_sheet) ⇒ Object
write the current spreadsheet to stdout or into a file
141 142 143 144 145 146 147 148 149 150 151 152 153 |
# File 'lib/roo/base.rb', line 141 def to_csv(filename = nil, separator = ',', sheet = default_sheet) if filename File.open(filename, 'w') do |file| write_csv_content(file, sheet, separator) end true else sio = ::StringIO.new write_csv_content(sio, sheet, separator) sio.rewind sio.read end end |
#to_matrix(from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = default_sheet) ⇒ Object
returns a matrix object from the whole sheet or a rectangular area of a sheet
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 |
# File 'lib/roo/base.rb', line 156 def to_matrix(from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = default_sheet) require 'matrix' return Matrix.empty unless first_row from_row ||= first_row(sheet) to_row ||= last_row(sheet) from_column ||= first_column(sheet) to_column ||= last_column(sheet) Matrix.rows(from_row.upto(to_row).map do |row| from_column.upto(to_column).map do |col| cell(row, col, sheet) end end) end |
#to_xml ⇒ Object
returns an XML representation of all sheets of a spreadsheet file
276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 |
# File 'lib/roo/base.rb', line 276 def to_xml Nokogiri::XML::Builder.new do |xml| xml.spreadsheet do sheets.each do |sheet| self.default_sheet = sheet xml.sheet(name: sheet) do |x| if first_row && last_row && first_column && last_column # sonst gibt es Fehler bei leeren Blaettern first_row.upto(last_row) do |row| first_column.upto(last_column) do |col| next if empty?(row, col) x.cell(cell(row, col), row: row, column: col, type: celltype(row, col)) end 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 = default_sheet) ⇒ 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”)
109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
# File 'lib/roo/base.rb', line 109 def to_yaml(prefix = {}, from_row = nil, from_column = nil, to_row = nil, to_column = nil, sheet = default_sheet) return '' unless first_row # empty result if there is no first_row in a sheet from_row ||= first_row(sheet) to_row ||= last_row(sheet) from_column ||= first_column(sheet) to_column ||= last_column(sheet) result = "--- \n" from_row.upto(to_row) do |row| from_column.upto(to_column) do |col| next if empty?(row, col, sheet) result << "cell_#{row}_#{col}: \n" prefix.each do|k, v| result << " #{k}: #{v} \n" end result << " row: #{row} \n" result << " col: #{col} \n" result << " celltype: #{celltype(row, col, sheet)} \n" value = cell(row, col, sheet) if celltype(row, col, sheet) == :time value = integer_to_timestring(value) end result << " value: #{value} \n" end end result end |