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
-
:datetime.
-
-
#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, #reload, #remove_tmp, #to_csv, #to_xml, #to_yaml
Constructor Details
#initialize(spreadsheetkey, user = nil, password = nil) ⇒ Google
Creates a new Google spreadsheet object.
79 80 81 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 |
# File 'lib/roo/google.rb', line 79 def initialize(spreadsheetkey,user=nil,password=nil) @filename = spreadsheetkey @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) #-- ---------------------------------------------------------------------- #-- TODO: 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
119 120 121 122 123 |
# File 'lib/roo/google.rb', line 119 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?
126 127 128 129 130 |
# File 'lib/roo/google.rb', line 126 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
132 133 134 135 |
# File 'lib/roo/google.rb', line 132 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.
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 |
# File 'lib/roo/google.rb', line 141 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 yyyy,mm,dd = @cell[sheet]["#{row},#{col}"].split('-') 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
-
:datetime
166 167 168 169 170 171 172 173 174 175 |
# File 'lib/roo/google.rb', line 166 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?
252 253 254 255 256 257 258 259 260 261 262 263 |
# File 'lib/roo/google.rb', line 252 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
239 240 241 242 243 244 245 246 |
# File 'lib/roo/google.rb', line 239 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
301 302 303 304 305 306 307 308 |
# File 'lib/roo/google.rb', line 301 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
281 282 283 284 285 286 287 288 |
# File 'lib/roo/google.rb', line 281 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.
180 181 182 183 184 185 186 187 188 189 |
# File 'lib/roo/google.rb', line 180 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}"] end end |
#formula?(row, col, sheet = nil) ⇒ Boolean
true, if there is a formula
192 193 194 195 196 197 |
# File 'lib/roo/google.rb', line 192 def formula?(row,col,sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) 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
201 202 203 204 205 206 207 208 209 210 211 212 213 214 |
# File 'lib/roo/google.rb', line 201 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
311 312 313 314 315 316 317 318 |
# File 'lib/roo/google.rb', line 311 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
291 292 293 294 295 296 297 298 |
# File 'lib/roo/google.rb', line 291 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
218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 |
# File 'lib/roo/google.rb', line 218 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(…)’
267 268 269 270 271 272 273 274 275 276 277 278 |
# File 'lib/roo/google.rb', line 267 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 row,col = normalize(row,col) @gs.add_to_cell_roo(row,col,value,sheet_no) end |
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet
114 115 116 |
# File 'lib/roo/google.rb', line 114 def sheets return @gs.sheetlist end |