Class: Google
- Inherits:
-
GenericSpreadsheet
- Object
- GenericSpreadsheet
- Defined in:
- lib/roo/google.rb
Overview
module
Instance Attribute Summary collapse
-
#date_format ⇒ Object
Returns the value of attribute date_format.
-
#datetime_format ⇒ Object
Returns the value of attribute datetime_format.
Attributes inherited from GenericSpreadsheet
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 * :formula * :time * :datetime.
- #date?(string) ⇒ Boolean
- #datetime?(string) ⇒ Boolean
-
#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.
- #numeric?(string) ⇒ Boolean
-
#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.
-
#time?(string) ⇒ Boolean
is String a time with format HH:MM:SS?.
- #timestring_to_seconds(value) ⇒ Object
Methods inherited from GenericSpreadsheet
#column, #find, #first_column_as_letter, #info, #last_column_as_letter, #reload, #remove_tmp, #row, #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 |
# File 'lib/roo/google.rb', line 82 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 {|h,k| h[k]=Hash.new} @cell_type = Hash.new {|h,k| h[k]=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 @date_format = '%d/%m/%Y' @datetime_format = '%d/%m/%Y %H:%M:%S' @time_format = '%H:%M:%S' @gs = GData::Spreadsheet.new(spreadsheetkey) @gs.authenticate(user, password) @sheetlist = @gs.sheetlist #-- ---------------------------------------------------------------------- #-- TODO: Behandlung von Berechtigungen hier noch einbauen ??? #-- ---------------------------------------------------------------------- @default_sheet = self.sheets.first end |
Instance Attribute Details
#date_format ⇒ Object
Returns the value of attribute date_format.
79 80 81 |
# File 'lib/roo/google.rb', line 79 def date_format @date_format end |
#datetime_format ⇒ Object
Returns the value of attribute datetime_format.
79 80 81 |
# File 'lib/roo/google.rb', line 79 def datetime_format @datetime_format 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.
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 |
# File 'lib/roo/google.rb', line 161 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) value = @cell[sheet]["#{row},#{col}"] if celltype(row,col,sheet) == :date begin return Date.strptime(value, @date_format) rescue ArgumentError raise "Invalid Date #{sheet}[#{row},#{col}] #{value} using format '{@date_format}'" end elsif celltype(row,col,sheet) == :datetime begin return DateTime.strptime(value, @datetime_format) rescue ArgumentError raise "Invalid DateTime #{sheet}[#{row},#{col}] #{value} using format '{@datetime_format}'" end end return value end |
#celltype(row, col, sheet = nil) ⇒ Object
returns the type of a cell:
-
:float
-
:string
-
:date
-
:percentage
-
:formula
-
:time
-
:datetime
191 192 193 194 195 196 197 198 199 200 |
# File 'lib/roo/google.rb', line 191 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 |
#date?(string) ⇒ Boolean
120 121 122 123 124 125 126 127 |
# File 'lib/roo/google.rb', line 120 def date?(string) begin Date.strptime(string, @date_format) true rescue false end end |
#datetime?(string) ⇒ Boolean
139 140 141 142 143 144 145 146 |
# File 'lib/roo/google.rb', line 139 def datetime?(string) begin DateTime.strptime(string, @datetime_format) true rescue false end end |
#empty?(row, col, sheet = nil) ⇒ Boolean
true, if the cell is empty
242 243 244 245 246 247 248 249 |
# File 'lib/roo/google.rb', line 242 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
294 295 296 297 298 299 300 301 |
# File 'lib/roo/google.rb', line 294 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
274 275 276 277 278 279 280 281 |
# File 'lib/roo/google.rb', line 274 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.
205 206 207 208 209 210 211 212 213 214 |
# File 'lib/roo/google.rb', line 205 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
217 218 219 220 221 222 |
# File 'lib/roo/google.rb', line 217 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
226 227 228 229 230 231 232 233 234 235 236 237 238 239 |
# File 'lib/roo/google.rb', line 226 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
304 305 306 307 308 309 310 311 |
# File 'lib/roo/google.rb', line 304 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
284 285 286 287 288 289 290 291 |
# File 'lib/roo/google.rb', line 284 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 |
#numeric?(string) ⇒ Boolean
148 149 150 |
# File 'lib/roo/google.rb', line 148 def numeric?(string) string =~ /^[0-9]+[\.]*[0-9]*$/ 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(…)’
253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 |
# File 'lib/roo/google.rb', line 253 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) # re-read the portion of the document that has changed if @cells_read[sheet] key = "#{row},#{col}" (value, value_type) = determine_datatype(value.to_s) @cell[sheet][key] = value @cell_type[sheet][key] = value_type end end |
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet
116 117 118 |
# File 'lib/roo/google.rb', line 116 def sheets @sheetlist end |
#time?(string) ⇒ Boolean
is String a time with format HH:MM:SS?
130 131 132 133 134 135 136 137 |
# File 'lib/roo/google.rb', line 130 def time?(string) begin DateTime.strptime(string, @time_format) true rescue false end end |
#timestring_to_seconds(value) ⇒ Object
152 153 154 155 |
# File 'lib/roo/google.rb', line 152 def timestring_to_seconds(value) hms = value.split(':') hms[0].to_i*3600 + hms[1].to_i*60 + hms[2].to_i end |