Class: Google
- Inherits:
-
GenericSpreadsheet
- Object
- GenericSpreadsheet
- Defined in:
- lib/roo/google.rb
Overview
module
Instance Attribute Summary
Attributes inherited from GenericSpreadsheet
Class Method Summary collapse
-
.date?(string) ⇒ Boolean
is String a date with format DD/MM/YYYY.
-
.time?(string) ⇒ Boolean
is String a time with format HH:MM:SS?.
- .timestring_to_seconds(value) ⇒ Object
Instance Method Summary collapse
-
#cell(row, col, sheet = nil) ⇒ Object
Returns the content of a spreadsheet-cell.
-
#celltype(row, col, sheet = nil) ⇒ Object
returns the type of a cell: * :float * :string, * :date * :percentage * :time.
-
#column(columnnumber, sheet = nil) ⇒ Object
returns all values in this column as an array column numbers are 1,2,3,…
-
#empty?(row, col, sheet = nil) ⇒ Boolean
true, if the cell is empty.
-
#first_column(sheet = nil) ⇒ Object
returns the first non-empty column in a sheet.
-
#first_row(sheet = nil) ⇒ Object
returns the first non-empty row in a sheet.
-
#formula(row, col, sheet = nil) ⇒ Object
Returns the formula at (row,col).
-
#formula?(row, col, sheet = nil) ⇒ Boolean
true, if there is a formula.
-
#formulas(sheet = nil) ⇒ Object
returns each formula in the selected sheet as an array of elements [row, col, formula].
-
#initialize(spreadsheetkey, user = nil, password = nil) ⇒ Google
constructor
Creates a new Google spreadsheet object.
-
#last_column(sheet = nil) ⇒ Object
returns the last non-empty column in a sheet.
-
#last_row(sheet = nil) ⇒ Object
returns the last non-empty row in a sheet.
-
#row(rownumber, sheet = nil) ⇒ Object
returns all values in this row as an array row numbers are 1,2,3,…
-
#set_value(row, col, value, sheet = nil) ⇒ Object
sets the cell to the content of ‘value’ a formula can be set in the form of ‘=SUM(…)’.
-
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet.
Methods inherited from GenericSpreadsheet
#find, #first_column_as_letter, #info, #last_column_as_letter, letter_to_number, #normalize, number_to_letter, #open_from_uri, #reload, #remove_tmp, #to_csv, #to_xml, #to_yaml
Constructor Details
#initialize(spreadsheetkey, user = nil, password = nil) ⇒ Google
Creates a new Google spreadsheet object.
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
# File 'lib/roo/google.rb', line 82 def initialize(spreadsheetkey,user=nil,password=nil) @filename = spreadsheetkey # $log = Logger.new("my_log") # $log.outputters = FileOutputter.new("f1", :filename => "roo.log") # $log.debug("created a new Google-object") @spreadsheetkey = spreadsheetkey @user = user @password = password unless user user = ENV['GOOGLE_MAIL'] end unless password password = ENV['GOOGLE_PASSWORD'] end @default_sheet = nil @cell = Hash.new @cell_type = Hash.new @formula = Hash.new @first_row = Hash.new @last_row = Hash.new @first_column = Hash.new @last_column = Hash.new @cells_read = Hash.new @header_line = 1 @gs = GData::Spreadsheet.new(spreadsheetkey) @gs.authenticate(user, password) #-- ---------------------------------------------------------------------- #-- Behandlung von Berechtigungen hier noch einbauen ??? #-- ---------------------------------------------------------------------- if self.sheets.size == 1 @default_sheet = self.sheets.first end end |
Class Method Details
.date?(string) ⇒ Boolean
is String a date with format DD/MM/YYYY
126 127 128 129 130 |
# File 'lib/roo/google.rb', line 126 def Google.date?(string) return false if string.class == Float return true if string.class == Date return string.strip =~ /^([0-9]+)\/([0-9]+)\/([0-9]+)$/ end |
.time?(string) ⇒ Boolean
is String a time with format HH:MM:SS?
133 134 135 136 137 |
# File 'lib/roo/google.rb', line 133 def Google.time?(string) return false if string.class == Float return true if string.class == Date return string.strip =~ /^([0-9]+):([0-9]+):([0-9]+)$/ end |
.timestring_to_seconds(value) ⇒ Object
139 140 141 142 |
# File 'lib/roo/google.rb', line 139 def Google.timestring_to_seconds(value) hms = value.split(':') hms[0].to_i*3600 + hms[1].to_i*60 + hms[2].to_i end |
Instance Method Details
#cell(row, col, sheet = nil) ⇒ Object
Returns the content of a spreadsheet-cell. (1,1) is the upper left corner. (1,1), (1,‘A’), (‘A’,1), (‘a’,1) all refers to the cell at the first line and first row.
148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 |
# File 'lib/roo/google.rb', line 148 def cell(row, col, sheet=nil) sheet = @default_sheet unless sheet check_default_sheet #TODO: 2007-12-16 read_cells(sheet) unless @cells_read[sheet] row,col = normalize(row,col) if celltype(row,col,sheet) == :date # $log.debug("splitte Datumsfeld auf (#{row},#{col}): #{ @cell[sheet]["#{row},#{col}"]}") yyyy,mm,dd = @cell[sheet]["#{row},#{col}"].split('-') # $log.debug(yyyy) # $log.debug(mm) # $log.debug(dd) #TODO: #if dd.to_i < 1 or dd.to_i >31 or mm.to_i < 1 or mm.to_i > 12 or yyyy.to_i < 1900 or yyyy.to_i > 3000 # raise "Invalid date parameter: #{yyyy}, #{mm}, #{dd}" #end begin return Date.new(yyyy.to_i,mm.to_i,dd.to_i) rescue ArgumentError raise "Invalid date parameter: #{yyyy}, #{mm}, #{dd}" end end # celltype date return @cell[sheet]["#{row},#{col}"] end |
#celltype(row, col, sheet = nil) ⇒ Object
returns the type of a cell:
-
:float
-
:string,
-
:date
-
:percentage
-
:time
179 180 181 182 183 184 185 186 187 188 |
# File 'lib/roo/google.rb', line 179 def celltype(row, col, sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] row,col = normalize(row,col) if @formula[sheet]["#{row},#{col}"] return :formula else @cell_type[sheet]["#{row},#{col}"] end end |
#column(columnnumber, sheet = nil) ⇒ Object
returns all values in this column as an array column numbers are 1,2,3,… like in the spreadsheet – TODO: refactoring nach GenericSpreadsheet?
265 266 267 268 269 270 271 272 273 274 275 276 |
# File 'lib/roo/google.rb', line 265 def column(columnnumber, sheet=nil) if columnnumber.class == String columnnumber = GenericSpreadsheet.letter_to_number(columnnumber) end sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] result = [] first_row(sheet).upto(last_row(sheet)) do |row| result << cell(row,columnnumber,sheet) end result end |
#empty?(row, col, sheet = nil) ⇒ Boolean
true, if the cell is empty
252 253 254 255 256 257 258 259 |
# File 'lib/roo/google.rb', line 252 def empty?(row, col, sheet=nil) value = cell(row, col, sheet) return true unless value return false if value.class == Date # a date is never empty return false if value.class == Float return false if celltype(row,col,sheet) == :time value.empty? end |
#first_column(sheet = nil) ⇒ Object
returns the first non-empty column in a sheet
313 314 315 316 317 318 319 320 |
# File 'lib/roo/google.rb', line 313 def first_column(sheet=nil) sheet = @default_sheet unless sheet unless @first_column[sheet] sheet_no = sheets.index(sheet) + 1 @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no) end return @first_column[sheet] end |
#first_row(sheet = nil) ⇒ Object
returns the first non-empty row in a sheet
293 294 295 296 297 298 299 300 |
# File 'lib/roo/google.rb', line 293 def first_row(sheet=nil) sheet = @default_sheet unless sheet unless @first_row[sheet] sheet_no = sheets.index(sheet) + 1 @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no) end return @first_row[sheet] end |
#formula(row, col, sheet = nil) ⇒ Object
Returns the formula at (row,col). Returns nil if there is no formula. The method #formula? checks if there is a formula.
193 194 195 196 197 198 199 200 201 202 |
# File 'lib/roo/google.rb', line 193 def formula(row,col,sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] row,col = normalize(row,col) if @formula[sheet]["#{row},#{col}"] == nil return nil else return @formula[sheet]["#{row},#{col}"] #TODO: ["oooc:".length..-1] end end |
#formula?(row, col, sheet = nil) ⇒ Boolean
true, if there is a formula
205 206 207 208 209 210 |
# File 'lib/roo/google.rb', line 205 def formula?(row,col,sheet=nil) sheet = @default_sheet unless sheet read_cells unless @cells_read[sheet] row,col = normalize(row,col) formula(row,col) != nil end |
#formulas(sheet = nil) ⇒ Object
returns each formula in the selected sheet as an array of elements
- row, col, formula
214 215 216 217 218 219 220 221 222 223 224 225 226 227 |
# File 'lib/roo/google.rb', line 214 def formulas(sheet=nil) theformulas = Array.new sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] first_row(sheet).upto(last_row(sheet)) {|row| first_column(sheet).upto(last_column(sheet)) {|col| if formula?(row,col,sheet) f = [row, col, formula(row,col,sheet)] theformulas << f end } } theformulas end |
#last_column(sheet = nil) ⇒ Object
returns the last non-empty column in a sheet
323 324 325 326 327 328 329 330 |
# File 'lib/roo/google.rb', line 323 def last_column(sheet=nil) sheet = @default_sheet unless sheet unless @last_column[sheet] sheet_no = sheets.index(sheet) + 1 @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no) end return @last_column[sheet] end |
#last_row(sheet = nil) ⇒ Object
returns the last non-empty row in a sheet
303 304 305 306 307 308 309 310 |
# File 'lib/roo/google.rb', line 303 def last_row(sheet=nil) sheet = @default_sheet unless sheet unless @last_row[sheet] sheet_no = sheets.index(sheet) + 1 @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] = @gs.oben_unten_links_rechts(sheet_no) end return @last_row[sheet] 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
231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 |
# File 'lib/roo/google.rb', line 231 def row(rownumber,sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] result = [] tmp_arr = [] @cell[sheet].each_pair {|key,value| y,x = key.split(',') x = x.to_i y = y.to_i if y == rownumber tmp_arr[x] = value end } result = tmp_arr[1..-1] while result[-1] == nil result = result[0..-2] end result end |
#set_value(row, col, value, sheet = nil) ⇒ Object
sets the cell to the content of ‘value’ a formula can be set in the form of ‘=SUM(…)’
280 281 282 283 284 285 286 287 288 289 290 |
# File 'lib/roo/google.rb', line 280 def set_value(row,col,value,sheet=nil) sheet = @default_sheet unless sheet raise RangeError, "sheet not set" unless sheet #@@ Set and pass sheet_no begin sheet_no = sheets.index(sheet)+1 rescue raise RangeError, "invalid sheet '"+sheet.to_s+"'" end @gs.add_to_cell_roo(row,col,value,sheet_no) end |
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet
121 122 123 |
# File 'lib/roo/google.rb', line 121 def sheets return @gs.sheetlist end |