Class: Google

Inherits:
GenericSpreadsheet show all
Defined in:
lib/roo/google.rb

Overview

module

Instance Attribute Summary

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line

Class Method Summary collapse

Instance Method Summary collapse

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

Returns:

  • (Boolean) 


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

.datetime?(string) ⇒ Boolean

is String a date+time with format DD/MM/YYYY HH:MM:SS

Returns:

  • (Boolean) 


133
134
135
136
137
 
# File 'lib/roo/google.rb', line 133

def Google.datetime?(string)
  return false if string.class == Float
  return true if string.class == Date
  return string.strip =~ /^([0-9]+)\/([0-9]+)\/([0-9]+)\ ([0-9]+):([0-9]+):([0-9]+)$/
end

.time?(string) ⇒ Boolean

is String a time with format HH:MM:SS?

Returns:

  • (Boolean) 


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 



140
141
142
143
 
# File 'lib/roo/google.rb', line 140

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.



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/roo/google.rb', line 149

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
  elsif celltype(row,col,sheet) == :datetime
    begin
      date_part,time_part = @cell[sheet]["#{row},#{col}"].split(' ')
      yyyy,mm,dd = date_part.split('-')
      hh,mi,ss = time_part.split(':')
      return DateTime.civil(yyyy.to_i,mm.to_i,dd.to_i,hh.to_i,mi.to_i,ss.to_i)
    rescue ArgumentError
      raise "Invalid date parameter: #{yyyy}, #{mm}, #{dd}, #{hh}, #{mi}, #{ss}"
    end
  end 
  return @cell[sheet]["#{row},#{col}"]
end

#celltype(row, col, sheet = nil) ⇒ Object

returns the type of a cell:

  • :float

  • :string

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



182
183
184
185
186
187
188
189
190
191
 
# File 'lib/roo/google.rb', line 182

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?



268
269
270
271
272
273
274
275
276
277
278
279
 
# File 'lib/roo/google.rb', line 268

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

Returns:

  • (Boolean) 


255
256
257
258
259
260
261
262
 
# File 'lib/roo/google.rb', line 255

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



317
318
319
320
321
322
323
324
 
# File 'lib/roo/google.rb', line 317

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



297
298
299
300
301
302
303
304
 
# File 'lib/roo/google.rb', line 297

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.



196
197
198
199
200
201
202
203
204
205
 
# File 'lib/roo/google.rb', line 196

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

Returns:

  • (Boolean) 


208
209
210
211
212
213
 
# File 'lib/roo/google.rb', line 208

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 


217
218
219
220
221
222
223
224
225
226
227
228
229
230
 
# File 'lib/roo/google.rb', line 217

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



327
328
329
330
331
332
333
334
 
# File 'lib/roo/google.rb', line 327

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



307
308
309
310
311
312
313
314
 
# File 'lib/roo/google.rb', line 307

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



234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
 
# File 'lib/roo/google.rb', line 234

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(…)’

Raises:

  • (RangeError) 


283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/roo/google.rb', line 283

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