Class: Roo::Google

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

Constant Summary

Constants inherited from GenericSpreadsheet

Roo::GenericSpreadsheet::TEMP_PREFIX

Instance Attribute Summary collapse

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line, #headers

Instance Method Summary collapse

Methods inherited from GenericSpreadsheet

#column, #each, #each_with_pagename, #find, #first_column_as_letter, #info, #last_column_as_letter, #longest_sheet, #method_missing, #parse, #reload, #row, #row_with, #sheet, #to_csv, #to_matrix, #to_xml, #to_yaml

Constructor Details

#initialize(spreadsheetkey, user = nil, password = nil) ⇒ Google

Creates a new Google spreadsheet object.



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
# File 'lib/roo/google.rb', line 15

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
  unless user and user.size > 0
   warn "user not set"
  end
  unless password and password.size > 0
   warn "password not set"
  end
  @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'
  session = GoogleSpreadsheet.(user, password)
  @sheetlist = []
  session.spreadsheet_by_key(@spreadsheetkey).worksheets.each { |sheet|
    @sheetlist << sheet.title
  }
  @default_sheet = self.sheets.first
  @worksheets = session.spreadsheet_by_key(@spreadsheetkey).worksheets
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method in the class Roo::GenericSpreadsheet

Instance Attribute Details

#date_format ⇒ Object

Returns the value of attribute date_format.



12
13
14
 
# File 'lib/roo/google.rb', line 12

def date_format
  @date_format
end

#datetime_format ⇒ Object

Returns the value of attribute datetime_format.



12
13
14
 
# File 'lib/roo/google.rb', line 12

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.



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 93

def cell(row, col, sheet=nil)
  sheet ||= @default_sheet
  validate_sheet!(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



123
124
125
126
127
128
129
130
131
132
 
# File 'lib/roo/google.rb', line 123

def celltype(row, col, sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  if @formula.size > 0 && @formula[sheet]["#{row},#{col}"]
    return :formula
  else
    @cell_type[sheet]["#{row},#{col}"]
  end
end

#date?(string) ⇒ Boolean

Returns:

  • (Boolean) 


58
59
60
61
62
63
 
# File 'lib/roo/google.rb', line 58

def date?(string)
  Date.strptime(string, @date_format)
  true
rescue
  false
end

#datetime?(string) ⇒ Boolean

Returns:

  • (Boolean) 


73
74
75
76
77
78
 
# File 'lib/roo/google.rb', line 73

def datetime?(string)
  DateTime.strptime(string, @datetime_format)
  true
rescue
  false
end

#empty?(row, col, sheet = nil) ⇒ Boolean

true, if the cell is empty

Returns:

  • (Boolean) 


157
158
159
160
161
162
163
164
 
# File 'lib/roo/google.rb', line 157

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



213
214
215
216
217
218
219
220
221
 
# File 'lib/roo/google.rb', line 213

def first_column(sheet=nil)
  sheet ||= @default_sheet
  unless @first_column[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      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



191
192
193
194
195
196
197
198
199
 
# File 'lib/roo/google.rb', line 191

def first_row(sheet=nil)
  sheet ||= @default_sheet
  unless @first_row[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      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.



137
138
139
140
141
142
143
144
145
146
 
# File 'lib/roo/google.rb', line 137

def formula(row,col,sheet=nil)
  sheet ||= @default_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) 


149
150
151
152
153
154
 
# File 'lib/roo/google.rb', line 149

def formula?(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  formula(row,col) != nil
end

#last_column(sheet = nil) ⇒ Object

returns the last non-empty column in a sheet



224
225
226
227
228
229
230
231
232
 
# File 'lib/roo/google.rb', line 224

def last_column(sheet=nil)
  sheet ||= @default_sheet
  unless @last_column[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      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



202
203
204
205
206
207
208
209
210
 
# File 'lib/roo/google.rb', line 202

def last_row(sheet=nil)
  sheet ||= @default_sheet
  unless @last_row[sheet]
    sheet_no = sheets.index(sheet) + 1
    @first_row[sheet], @last_row[sheet], @first_column[sheet], @last_column[sheet] =
      oben_unten_links_rechts(sheet_no)
  end
  return @last_row[sheet]
end

#numeric?(string) ⇒ Boolean

Returns:

  • (Boolean) 


80
81
82
 
# File 'lib/roo/google.rb', line 80

def numeric?(string)
  string =~ /^[0-9]+[\.]*[0-9]*$/
end

#set(row, col, value, sheet = nil) ⇒ Object

sets the cell to the content of ‘value’ a formula can be set in the form of ‘=SUM(…)’



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
 
# File 'lib/roo/google.rb', line 168

def set(row,col,value,sheet=nil)
  sheet ||= @default_sheet
  validate_sheet!(sheet)

  sheet_no = sheets.index(sheet)+1
  row,col = normalize(row,col)
  add_to_cell_roo(row,col,value,sheet_no)
  # re-read the portion of the document that has changed
  if @cells_read[sheet]
    value, value_type = determine_datatype(value.to_s)

    _set_value(col,row,value,sheet)
    set_type(col,row,value_type,sheet)
  end
end

#set_value(row, col, value, sheet = nil) ⇒ Object

DEPRECATED: Use Roo::Google#set instead



185
186
187
188
 
# File 'lib/roo/google.rb', line 185

def set_value(row,col,value,sheet=nil)
  warn "[DEPRECATION] `set_value` is deprecated.  Please use `set` instead."
  set(row,col,value,sheet)
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



54
55
56
 
# File 'lib/roo/google.rb', line 54

def sheets
  @sheetlist
end

#time?(string) ⇒ Boolean

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

Returns:

  • (Boolean) 


66
67
68
69
70
71
 
# File 'lib/roo/google.rb', line 66

def time?(string)
  DateTime.strptime(string, @time_format)
  true
rescue
  false
end

#timestring_to_seconds(value) ⇒ Object 



84
85
86
87
 
# File 'lib/roo/google.rb', line 84

def timestring_to_seconds(value)
  hms = value.split(':')
  hms[0].to_i*3600 + hms[1].to_i*60 + hms[2].to_i
end