Class: Roo::Base
- Inherits:
-
Object
- Object
- Roo::Base
- Includes:
- Enumerable, Formatters::Base, Formatters::CSV, Formatters::Matrix, Formatters::XML, Formatters::YAML
- Defined in:
- lib/roo/base.rb
Overview
Base class for all other types of spreadsheets
Direct Known Subclasses
Constant Summary collapse
- 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.
Class Method Summary collapse
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’).
Methods included from Formatters::YAML
Methods included from Formatters::XML
Methods included from Formatters::Matrix
Methods included from Formatters::CSV
Methods included from Formatters::Base
Constructor Details
#initialize(filename, options = {}, _file_warning = :error, _tmpdir = nil) ⇒ Base
Returns a new instance of Base.
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
# File 'lib/roo/base.rb', line 39 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)
226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 |
# File 'lib/roo/base.rb', line 226 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)
28 29 30 |
# File 'lib/roo/base.rb', line 28 def header_line @header_line end |
#headers ⇒ Object (readonly)
Returns the value of attribute headers.
25 26 27 |
# File 'lib/roo/base.rb', line 25 def headers @headers end |
Class Method Details
.finalize(object_id) ⇒ Object
35 36 37 |
# File 'lib/roo/base.rb', line 35 def self.finalize(object_id) proc { finalize_tempdirs(object_id) } end |
.TEMP_PREFIX ⇒ Object
30 31 32 33 |
# File 'lib/roo/base.rb', line 30 def self.TEMP_PREFIX warn '[DEPRECATION] please access TEMP_PREFIX via Roo::TEMP_PREFIX' Roo::TEMP_PREFIX end |
Instance Method Details
#cell_type_by_value(value) ⇒ Object
173 174 175 176 177 178 179 180 |
# File 'lib/roo/base.rb', line 173 def cell_type_by_value(value) case value when Integer then :float when String, Float then :string else fail ArgumentError, "Type for #{value} not set" end end |
#close ⇒ Object
55 56 57 58 59 60 |
# File 'lib/roo/base.rb', line 55 def close if self.class.respond_to?(:finalize_tempdirs) self.class.finalize_tempdirs(object_id) end nil end |
#collect_last_row_col_for_sheet(sheet) ⇒ Object
Collect first/last row/column from sheet
100 101 102 103 104 105 106 107 108 109 110 111 |
# File 'lib/roo/base.rb', line 100 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
153 154 155 156 157 158 159 160 161 |
# File 'lib/roo/base.rb', line 153 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
62 63 64 |
# File 'lib/roo/base.rb', line 62 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.
68 69 70 71 72 73 |
# File 'lib/roo/base.rb', line 68 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
278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 |
# File 'lib/roo/base.rb', line 278 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
250 251 252 253 254 |
# File 'lib/roo/base.rb', line 250 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
190 191 192 193 194 195 196 |
# File 'lib/roo/base.rb', line 190 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)
129 130 131 132 133 134 135 136 137 138 139 140 |
# File 'lib/roo/base.rb', line 129 def find(*args) # :nodoc = (args.last.is_a?(Hash) ? args.pop : {}) case args[0] when Integer 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
76 77 78 |
# File 'lib/roo/base.rb', line 76 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
86 87 88 89 90 91 92 93 94 95 96 97 |
# File 'lib/roo/base.rb', line 86 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.
200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 |
# File 'lib/roo/base.rb', line 200 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
122 123 124 |
# File 'lib/roo/base.rb', line 122 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
81 82 83 |
# File 'lib/roo/base.rb', line 81 def last_column_as_letter(sheet = default_sheet) ::Roo::Utils.number_to_letter(last_column(sheet)) end |
#parse(options = {}) ⇒ Object
299 300 301 302 303 304 305 |
# File 'lib/roo/base.rb', line 299 def parse( = {}) results = each().map do |row| block_given? ? yield(row) : row end [:headers] == true ? results : results.drop(1) end |
#reload ⇒ Object
reopens and read a spreadsheet document
183 184 185 186 187 |
# File 'lib/roo/base.rb', line 183 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
144 145 146 147 148 149 |
# File 'lib/roo/base.rb', line 144 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
307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 |
# File 'lib/roo/base.rb', line 307 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!)
165 166 167 168 169 170 171 |
# File 'lib/roo/base.rb', line 165 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’)
244 245 246 247 |
# File 'lib/roo/base.rb', line 244 def sheet(index, name = false) self.default_sheet = index.is_a?(::String) ? index : sheets[index] name ? [default_sheet, self] : self end |