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.
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.
-
#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_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 |
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.
136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 |
# File 'lib/roo/google.rb', line 136 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
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?
251 252 253 254 255 256 257 258 259 260 261 262 |
# File 'lib/roo/google.rb', line 251 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 |
# 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 value.empty? end |
#first_column(sheet = nil) ⇒ Object
returns the first non-empty column in a sheet
299 300 301 302 303 304 305 306 |
# File 'lib/roo/google.rb', line 299 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
279 280 281 282 283 284 285 286 |
# File 'lib/roo/google.rb', line 279 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}"] #TODO: ["oooc:".length..-1] 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 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
309 310 311 312 313 314 315 316 |
# File 'lib/roo/google.rb', line 309 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
289 290 291 292 293 294 295 296 |
# File 'lib/roo/google.rb', line 289 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(…)’
266 267 268 269 270 271 272 273 274 275 276 |
# File 'lib/roo/google.rb', line 266 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 |