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
- MIN_ROW_COL =
0
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 (0 = 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.
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
# File 'lib/roo/base.rb', line 37 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)
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 |
# File 'lib/roo/base.rb', line 228 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)
26 27 28 |
# File 'lib/roo/base.rb', line 26 def header_line @header_line end |
#headers ⇒ Object (readonly)
Returns the value of attribute headers.
23 24 25 |
# File 'lib/roo/base.rb', line 23 def headers @headers end |
Class Method Details
.finalize(object_id) ⇒ Object
33 34 35 |
# File 'lib/roo/base.rb', line 33 def self.finalize(object_id) proc { finalize_tempdirs(object_id) } end |
.TEMP_PREFIX ⇒ Object
28 29 30 31 |
# File 'lib/roo/base.rb', line 28 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
175 176 177 178 179 180 181 182 |
# File 'lib/roo/base.rb', line 175 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
53 54 55 56 57 58 59 60 61 62 63 |
# File 'lib/roo/base.rb', line 53 def close if self.class.respond_to?(:finalize_tempdirs) self.class.finalize_tempdirs(object_id) end instance_variables.each do |instance_variable| instance_variable_set(instance_variable, nil) end nil end |
#collect_last_row_col_for_sheet(sheet) ⇒ Object
Collect first/last row/column from sheet
103 104 105 106 107 108 109 110 111 112 113 114 |
# File 'lib/roo/base.rb', line 103 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
155 156 157 158 159 160 161 162 163 |
# File 'lib/roo/base.rb', line 155 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
65 66 67 |
# File 'lib/roo/base.rb', line 65 def default_sheet @default_sheet ||= sheets.first end |
#default_sheet=(sheet) ⇒ Object
sets the working sheet in the document ‘sheet’ can be a number (0 = first sheet) or the name of a sheet.
71 72 73 74 75 76 |
# File 'lib/roo/base.rb', line 71 def default_sheet=(sheet) validate_sheet!(sheet) @default_sheet = sheet.is_a?(String) ? sheet : sheets[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
282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 |
# File 'lib/roo/base.rb', line 282 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 || (first_column..last_column).each_with_object({}) do |col, hash| hash[cell(@header_line, col)] = col end @header_line.upto(last_row) do |line| yield(headers.each_with_object({}) { |(k, v), hash| hash[k] = cell(line, v) }) end end end |
#each_with_pagename ⇒ Object
iterate through all worksheets of a document
252 253 254 255 256 257 258 |
# File 'lib/roo/base.rb', line 252 def each_with_pagename return to_enum(:each_with_pagename) { sheets.size } unless block_given? sheets.each do |s| yield sheet(s, true) end end |
#empty?(row, col, sheet = default_sheet) ⇒ Boolean
true if cell is empty
192 193 194 195 196 197 198 |
# File 'lib/roo/base.rb', line 192 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)
131 132 133 134 135 136 137 138 139 140 141 142 |
# File 'lib/roo/base.rb', line 131 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
79 80 81 |
# File 'lib/roo/base.rb', line 79 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
89 90 91 92 93 94 95 96 97 98 99 100 |
# File 'lib/roo/base.rb', line 89 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.
202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 |
# File 'lib/roo/base.rb', line 202 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
124 125 126 |
# File 'lib/roo/base.rb', line 124 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
84 85 86 |
# File 'lib/roo/base.rb', line 84 def last_column_as_letter(sheet = default_sheet) ::Roo::Utils.number_to_letter(last_column(sheet)) end |
#parse(options = {}) ⇒ Object
303 304 305 306 307 308 309 |
# File 'lib/roo/base.rb', line 303 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
185 186 187 188 189 |
# File 'lib/roo/base.rb', line 185 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
146 147 148 149 150 151 |
# File 'lib/roo/base.rb', line 146 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
311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 |
# File 'lib/roo/base.rb', line 311 def row_with(query, return_headers = false) line_no = 0 closest_mismatched_headers = [] 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 else closest_mismatched_headers = headers if headers.length > closest_mismatched_headers.length if line_no > 100 break end end end missing_headers = query.select { |q| closest_mismatched_headers.grep(q).empty? } raise Roo::HeaderRowNotFoundError, missing_headers 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!)
167 168 169 170 171 172 173 |
# File 'lib/roo/base.rb', line 167 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’)
246 247 248 249 |
# File 'lib/roo/base.rb', line 246 def sheet(index, name = false) self.default_sheet = index.is_a?(::String) ? index : sheets[index] name ? [default_sheet, self] : self end |