Class: Google
- Inherits:
-
GenericSpreadsheet
- Object
- GenericSpreadsheet
- Defined in:
- lib/roo/google.rb
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, #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.login(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
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
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
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
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
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(…)’
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?
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 |