Class: Google

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

Instance Attribute Summary collapse

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line

Instance Method Summary collapse

Methods inherited from GenericSpreadsheet

#column, #find, #first_column_as_letter, #info, #last_column_as_letter, #method_missing, #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.



12
13
14
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
 
# File 'lib/roo/google.rb', line 12

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
  @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 GenericSpreadsheet

Instance Attribute Details

#date_format ⇒ Object

Returns the value of attribute date_format.



9
10
11
 
# File 'lib/roo/google.rb', line 9

def date_format
  @date_format
end

#datetime_format ⇒ Object

Returns the value of attribute datetime_format.



9
10
11
 
# File 'lib/roo/google.rb', line 9

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.



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/roo/google.rb', line 90

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



120
121
122
123
124
125
126
127
128
129
 
# File 'lib/roo/google.rb', line 120

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.size > 0 && @formula[sheet]["#{row},#{col}"]
    return :formula
  else
    @cell_type[sheet]["#{row},#{col}"]
  end
end

#date?(string) ⇒ Boolean

Returns:

  • (Boolean) 


49
50
51
52
53
54
55
56
 
# File 'lib/roo/google.rb', line 49

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

#datetime?(string) ⇒ Boolean

Returns:

  • (Boolean) 


68
69
70
71
72
73
74
75
 
# File 'lib/roo/google.rb', line 68

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

Returns:

  • (Boolean) 


171
172
173
174
175
176
177
178
 
# File 'lib/roo/google.rb', line 171

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



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

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] =
        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



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

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] =
        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.



134
135
136
137
138
139
140
141
142
143
 
# File 'lib/roo/google.rb', line 134

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) 


146
147
148
149
150
151
 
# File 'lib/roo/google.rb', line 146

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 


155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/roo/google.rb', line 155

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



236
237
238
239
240
241
242
243
244
 
# File 'lib/roo/google.rb', line 236

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] =
      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



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

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] =
        oben_unten_links_rechts(sheet_no)
  end
  return @last_row[sheet]
end

#numeric?(string) ⇒ Boolean

Returns:

  • (Boolean) 


77
78
79
 
# File 'lib/roo/google.rb', line 77

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

Raises:

  • (RangeError) 


182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/roo/google.rb', line 182

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)
  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



45
46
47
 
# File 'lib/roo/google.rb', line 45

def sheets
  @sheetlist
end

#time?(string) ⇒ Boolean

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

Returns:

  • (Boolean) 


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

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

#timestring_to_seconds(value) ⇒ Object 



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

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