Class: Roo::Base
Overview
Base class for all other types of spreadsheets
Direct Known Subclasses
Constant Summary collapse
- TEMP_PREFIX =
'roo_'
- 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
-
#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 |
# 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 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)
293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 |
# File 'lib/roo/base.rb', line 293 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
214 215 216 217 218 219 220 221 |
# File 'lib/roo/base.rb', line 214 def cell_type_by_value(value) case value when Fixnum then :float when String, Float then :string else raise ArgumentError, "Type for #{value} not set" end end |
#collect_last_row_col_for_sheet(sheet) ⇒ Object
Collect first/last row/column from sheet
75 76 77 78 79 80 81 82 83 84 85 86 |
# File 'lib/roo/base.rb', line 75 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
194 195 196 197 198 199 200 201 202 |
# File 'lib/roo/base.rb', line 194 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
37 38 39 |
# File 'lib/roo/base.rb', line 37 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.
43 44 45 46 47 48 |
# File 'lib/roo/base.rb', line 43 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
345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 |
# File 'lib/roo/base.rb', line 345 def each( = {}) 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
317 318 319 320 321 |
# File 'lib/roo/base.rb', line 317 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
231 232 233 234 235 236 237 |
# File 'lib/roo/base.rb', line 231 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)
170 171 172 173 174 175 176 177 178 179 180 181 |
# File 'lib/roo/base.rb', line 170 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
51 52 53 |
# File 'lib/roo/base.rb', line 51 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
61 62 63 64 65 66 67 68 69 70 71 72 |
# File 'lib/roo/base.rb', line 61 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.
241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 |
# File 'lib/roo/base.rb', line 241 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" unless first_row result << ' - empty -' else 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)}" end result << "\n" if sheet != sheets.last n += 1 end result end end |
#inspect ⇒ Object
163 164 165 |
# File 'lib/roo/base.rb', line 163 def inspect "<##{ self.class }:#{ self.object_id.to_s(8) } #{ self.instance_variables.join(' ') }>" end |
#last_column_as_letter(sheet = default_sheet) ⇒ Object
last non-empty column as a letter
56 57 58 |
# File 'lib/roo/base.rb', line 56 def last_column_as_letter(sheet = default_sheet) ::Roo::Utils.number_to_letter(last_column(sheet)) end |
#parse(options = {}) ⇒ Object
364 365 366 367 368 369 370 371 |
# File 'lib/roo/base.rb', line 364 def parse( = {}) ary = [] each() do |row| yield(row) if block_given? ary << row end ary end |
#reload ⇒ Object
reopens and read a spreadsheet document
224 225 226 227 228 |
# File 'lib/roo/base.rb', line 224 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
185 186 187 188 189 190 |
# File 'lib/roo/base.rb', line 185 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
373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 |
# File 'lib/roo/base.rb', line 373 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 fail "Couldn't find header row." end end fail "Couldn't find header row." 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!)
206 207 208 209 210 211 212 |
# File 'lib/roo/base.rb', line 206 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’)
311 312 313 314 |
# File 'lib/roo/base.rb', line 311 def sheet(index, name = false) self.default_sheet = String === index ? 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
131 132 133 134 135 136 137 138 139 140 141 142 143 |
# File 'lib/roo/base.rb', line 131 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
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
# File 'lib/roo/base.rb', line 146 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
266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 |
# File 'lib/roo/base.rb', line 266 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| unless empty?(row, col) x.cell(cell(row, col), row: row, column: col, type: celltype(row, col)) end 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”)
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
# File 'lib/roo/base.rb', line 100 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| unless 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 end result end |