lib/roo/google.rb in roo-0.7.0 vs lib/roo/google.rb in roo-0.8.0
- old
+ new
@@ -1,101 +1,182 @@
+require 'rubygems' #TODO:
+require 'gdata/spreadsheet'
+#require 'log4r'
-#require 'net/http'
-#require 'net/https'
-#require 'uri'
-require 'rubygems'
-#require 'hpricot'
-require 'timeout'
-#require 'GData'
-require 'gdata'
+# overwrite some methods from the gdata-gem:
+module GData
+ class Spreadsheet < GData::Base
+ #-- modified
+ def evaluate_cell(cell, sheet_no=1)
+ raise ArgumentError, "invalid cell: #{cell}" unless cell
+ raise ArgumentError, "invalid sheet_no: #{sheet_no}" unless sheet_no >0 and sheet_no.class == Fixnum
+ path = "/feeds/cells/#{@spreadsheet_id}/#{sheet_no}/#{@headers ? "private" : "public"}/basic/#{cell}"
+
+ doc = Hpricot(request(path))
+ result = (doc/"content").inner_html
+ end
-# Make it easy to use some of the convenience methods using https
-#
-#module Net class HTTPS < HTTP
-# def initialize(address, port = nil)
-# super(address, port)
-# self.use_ssl = true
-# end
-# end
-#end
-#
-#class GoogleSpreadSheet
-# GOOGLE_LOGIN_URL = URI.parse('https://www.google.com/accounts/ClientLogin')
-#
-# def initialize(spreadsheet_key)
-# @spreadsheet_key = spreadsheet_key
-# @headers = nil
-# @default_sheet = nil
-# end
-#
-# def default_sheet=(numberofsheet)
-# @default_sheet = numberofsheet
-# end
-#
-# def authenticate(email, password)
-# $VERBOSE = nil
-# response = Net::HTTPS.post_form(GOOGLE_LOGIN_URL,
-# {'Email' => email,
-# 'Passwd' => password,
-# 'source' => "formula",
-# 'service' => 'wise' })
-# @headers = { 'Authorization' => "GoogleLogin auth=#{response.body.split(/=/).last}",
-# 'Content-Type' => 'application/atom+xml'
-# }
-# end
-#
-# def evaluate_cell(cell)
-# path = "/feeds/cells/#{@spreadsheet_key}/#{@default_sheet}/#{@headers ? "private" : "public"}/basic/#{cell}"
-#
-# doc = Hpricot(request(path))
-# result = (doc/"content[@type='text']").inner_html
-# end
-#
-# def set_entry(entry)
-# path = "/feeds/cells/#{@spreadsheet_key}/#{@default_sheet}/#{@headers ? 'private' : 'public'}/full"
-#
-# post(path, entry)
-# end
-#
-# def entry(formula, row=1, col=1)
-# <<XML
-#<?xml version='1.0' ?>
-#<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
-# <gs:cell row='#{row}' col='#{col}' inputValue='=#{formula}' />
-#</entry>
-#XML
-# end
-#
-# def add_to_cell(formula) #puts entry(formula)
-# set_entry(entry(formula))
-# end
-#
-# private
-# def request(path)
-# response, data = get_http.get(path, @headers)
-# data
-# end
-#
-# def post(path, entry)
-# get_http.post(path, entry, @headers)
-# end
-#
-# def get_http
-# http = Net::HTTP.new('spreadsheets.google.com', 80)
-# #http.set_debug_output $stderr
-# http
-# end
-#end
+ #-- new
+ def sheetlist
+ path = "/feeds/worksheets/#{@spreadsheet_id}/private/basic"
+ doc = Hpricot(request(path))
+ result = []
+ (doc/"content").each { |elem|
+ result << elem.inner_html
+ }
+ result
+ end
-class Google < Openoffice #:nodoc:
+ # #-- new (testing only)
+ # def raw_url(url)
+ # path = "/feeds/worksheets/#{@spreadsheet_id}/private/basic"
+ # path = url
+ # doc = Hpricot(request(path))
+ # end
- TIMEOUT_IN_SECONDS = 2
+ #-- new
+ def save_entry_roo(entry)
+ path = "/feeds/cells/#{@spreadsheet_id}/1/#{@headers ? 'private' : 'public'}/full"
+ post(path, entry)
+ end
+
+ #-- new
+ def entry_roo(formula, row=1, col=1)
+ <<XML
+ <entry xmlns='http://www.w3.org/2005/Atom' xmlns:gs='http://schemas.google.com/spreadsheets/2006'>
+ <gs:cell row='#{row}' col='#{col}' inputValue='#{formula}' />
+ </entry>
+XML
+ end
+
+ #-- new
+ def add_to_cell_roo(row,col,value)
+ save_entry_roo(entry_roo(value,row,col))
+ end
- def initialize(user, password, spreadsheetkey, spreadsheetname)
- @cells_read = false
+ #-- new
+ def get_one_sheet
+ ath = "/feeds/worksheets/#{@spreadsheet_id}/private/basic"
+ path = "/feeds/cells/#{@spreadsheet_id}/1/private/full"
+ doc = Hpricot(request(path))
+ #pp doc
+ #p doc # TODO:prey:
+ # result = (doc/"content[@type='text']").inner_html
+ # p (doc/"content").inner_html
+ #result = (doc/"content").inner_html.each { |item| item + '#'}
+ #-- result = []
+ #-- (doc/"content").each { |elem|
+ #-- #p elem
+ #-- result << elem.inner_html
+ #-- }
+ #-- result
+ end
+
+ #new
+ def oben_unten_links_rechts(sheet_no)
+ #sheet_no = sheets.index(sheet) + 1
+ path = "/feeds/cells/#{@spreadsheet_id}/#{sheet_no}/private/full"
+ doc = Hpricot(request(path))
+ #(doc/"entry").each {|item|
+ rows = []
+ cols = []
+ (doc/"gs:cell").each {|item|
+ # p item
+ #p item['row']
+ rows.push item['row'].to_i
+ #p item['col']
+ cols.push item['col'].to_i
+ }
+ # $log.debug("row min: #{rows.min}")
+ # $log.debug("row max: #{rows.max}")
+ # $log.debug("col min: #{cols.min}")
+ # $log.debug("col max: #{cols.max}")
+ return rows.min, rows.max, cols.min, cols.max
+ end
+
+ def fulldoc(sheet_no)
+ path = "/feeds/cells/#{@spreadsheet_id}/#{sheet_no}/private/full"
+ doc = Hpricot(request(path))
+ return doc
+ #(doc/"entry").each {|item|
+ rows = []
+ cols = []
+ (doc/"gs:cell").each {|item|
+ # p item
+ #p item['row']
+ rows.push item['row'].to_i
+ #p item['col']
+ cols.push item['col'].to_i
+ }
+ # $log.debug("row min: #{rows.min}")
+ # $log.debug("row max: #{rows.max}")
+ # $log.debug("col min: #{cols.min}")
+ # $log.debug("col max: #{cols.max}")
+ return rows.min, rows.max, cols.min, cols.max
+ end
+ end # class
+end # module
+
+class Google < GenericSpreadsheet
+ #include Log4r
+
+ attr_accessor :header_line
+
+ # TIMEOUT_IN_SECONDS = 42
+ # GOOGLE_MAX_ROWS = 99
+ # GOOGLE_MAX_COLS = 10 # TODO:
+
+ # Creates a new Google spreadsheet object.
+ def initialize(spreadsheetkey,user=nil,password=nil)
+ @filename = spreadsheetkey
+ # $log = Logger.new("my_log")
+ # $log.outputters = FileOutputter.new("f1", :filename => "roo.log")
+
+ # $log.debug("created a new Google-object")
+ @spreadsheetkey = spreadsheetkey
+ @user = user
+ @password = password
+ unless user
+ user = ENV['GOOGLE_MAIL']
+ end
+ unless password
+ password = ENV['GOOGLE_PASSWORD']
+ end
+ #p spreadsheetkey
+ #p user
+ #p password
+ #spreadsheetkey = ENV['GOOGLE_KEY']
+ @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
+ #??? @sheets = Array.new
+
+ @gs = GData::Spreadsheet.new(spreadsheetkey)
+ #@column = FromGData::Spreadsheet.new(spreadsheetkey)
+ @gs.authenticate(user, password)
+
+ #--Test only
+ #@gs.get_one_sheet
+ #@gs.oben_unten_links_rechts
+ #--
+ #-- ----------------------------------------------------------------------
+ #-- Behandlung von Berechtigungen hier noch einbauen
+ #-- ----------------------------------------------------------------------
+
+ #result = gs.add_to_cell formula
+ #puts gs.evaluate_cell('A1')
+ #p gs.evaluate_cell('A1')
+ #puts gs.evaluate_cell('A2')
+ #puts gs.evaluate_cell('A3')
+
#Timeout.timeout(TIMEOUT_IN_SECONDS) {
# @gs = GoogleSpreadSheet.new(spreadsheetkey)
# @gs.authenticate(user, password)
# @default_sheet = nil
#}
@@ -103,92 +184,526 @@
# @spreadsheets= GData::Spreadsheet.spreadsheets("emmanuel.pirsch@gmail.com", "secret") # funktioniert anscheinend noch nicht!!!!!!!!!
# ein einzelnes Spreadsheet bei Google
# @spreadsheet= spreadsheet[spreadsheetname]
#gb = GData::Base.new
- #gb.authenticate("thopre@gmail.com","nora3033")
+ #gb.authenticate("thopre@gmail.com","secret")
- g = GData::Spreadsheet.new("ttt")
- @default_sheet = nil
+ #g = GData::Spreadsheet.new("ttt")
+ #@default_sheet = nil
+ if self.sheets.size == 1
+ @default_sheet = self.sheets.first
+ end
end
+ #def gssheetlist
+ # @gs.sheetlist
+ #end
+
+ # returns an array of sheet names in the spreadsheet
def sheets
- # http://spreadsheets.google.com/feeds/worksheets/ptu6bbahNZpYrdGHwteUNCw/private/full
- #if DateTime.now < Date.new(2007,6,15)
- # return ["Sheet eins","Sheet zwei","Sheet drei"]
- #else
- # return []
- #end
- @spreadsheet
+ #if @sheets == []
+ # @sheets = @gs.sheetlist
+ #end
+ #return @sheets
+ #p gssheetlist
+ return @gs.sheetlist
end
- def default_sheet=(numberofsheet)
- @gs.default_sheet = numberofsheet
+ #def reload
+ # sav_default_sheet = default_sheet
+ # super
+ # @sheets = sheets #[]
+ # puts "DEBUG: reload: erneutes Einlesen ergibt @sheets=#{@sheets.to_s}"
+ # @default_sheet = sav_default_sheet
+ #end
+
+ # set the working sheet in the document
+ #--
+ # TODO: eigenlich identisch mit Openoffice => refactoring
+ def default_sheet=(sheet)
+ #puts "DEBUG: default_sheet=#{sheet}"
+ if ! sheet.kind_of?(String)
+ raise TypeError, "what are you trying to set as default sheet?"
+ end
+ @default_sheet = sheet
+ check_default_sheet
+ @first_row[sheet] = @last_row[sheet] = @first_column[sheet] = @last_column[sheet] = nil
+ #--TODO: @cells_read[sheet] = false
end
- def cell(row, col)
+ # is String a date with format DD/MM/YYYY
+ 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
+
+ # 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.
+ 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)
- # formula = 42 || 'sin(0.2)'
- # gs = GoogleSpreadSheet.new("ptu6bbahNZpYrdGHwteUNCw")
- # gs.authenticate('thopre@gmail.com', 'nora3033')
- # gs.add_to_cell formula
- cellname = number_to_letter(col)+row.to_s
- Timeout.timeout(TIMEOUT_IN_SECONDS) {
- return @gs.evaluate_cell(cellname) # 'A1')
- }
+ if celltype(row,col,sheet) == :date
+ # $log.debug("splitte Datumsfeld auf (#{row},#{col}): #{ @cell[sheet]["#{row},#{col}"]}")
+ yyyy,mm,dd = @cell[sheet]["#{row},#{col}"].split('-')
+ # $log.debug(yyyy)
+ # $log.debug(mm)
+ # $log.debug(dd)
+#TODO:
+if dd.to_i < 1 or dd.to_i >31 or mm.to_i < 1 or mm.to_i > 12 or yyyy.to_i < 1900 or yyyy.to_i > 3000
+ raise "Invalid date parameter: #{yyyy}, #{mm}, #{dd}"
+end
+ return Date.new(yyyy.to_i,mm.to_i,dd.to_i)
+ end
+ return @cell[sheet]["#{row},#{col}"]
end
- def celltype(row, col)
- "string"
+ # returns the type of a cell:
+ # * :float
+ # * :string,
+ # * :date
+ # * :percentage
+ 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
+
+ # Returns the formula at (row,col).
+ # Returns nil if there is no formula.
+ # The method #formula? checks if there is a formula.
+ 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}"] #TODO: ["oooc:".length..-1]
+ end
+ end
+
+ # true, if there is a formula
+ def formula?(row,col,sheet=nil)
+ sheet = @default_sheet unless sheet
+ read_cells unless @cells_read[sheet]
+ row,col = normalize(row,col)
+ formula(row,col) != nil
+ end
+
+ # returns each formula in the selected sheet as an array of elements
+ # [row, col, formula]
+ 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
+
+# # returns all values in this row as an array
+# # row numbers are 1,2,3,... like in the spreadsheet
+# def row_old(rownumber,sheet=nil)
+# sheet = @default_sheet unless sheet
+# result = []
+# 1.upto(GOOGLE_MAX_ROWS) { |col| # TODO: maximum size of x-coordinate of google spreadsheet
+# result[col] = cell(rownumber,col,sheet)
+# }
+# result = result[1..-1]
+# while result[-1] == '' #TODO: besser?
+# result = result[0..-2]
+# end
+# result
+# end
+
+ # returns all values in this row as an array
+ # row numbers are 1,2,3,... like in the spreadsheet
+ 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
- def empty?(row, col)
- cell(row, col).empty?
+ # true, if the cell is empty
+ 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
+ value.empty?
end
+
+ # returns all values in this column as an array
+ # column numbers are 1,2,3,... like in the spreadsheet
+ #--
+ #TODO: refactoring nach GenericSpreadsheet?
+ 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
- def read_cells
-# http://spreadsheets.google.com/feeds/list/ptu6bbahNZpYrdGHwteUNCw/1/private/full
- file = File.new("/home/tp/aaa.xml")
- @doc = REXML::Document.new file
- file.close
- @doc.each_element do |element|
- if element.name == "feed"
- p "feed gefunden"
- element.each_element do |feed|
- p feed
- feed.each_element do |fe|
- p fe.name
+ # sets the cell to the content of 'value'
+ # a formula can be set in the form of '=SUM(...)'
+ def set_value(row,col,value)
+ @gs.add_to_cell_roo(row,col,value)
+ end
+
+ # returns the first non-empty row in a sheet
+ 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
+
+ # returns the last non-empty row in a sheet
+ 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
+
+ # returns the first non-empty column in a sheet
+ 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
+
+ # returns the last non-empty column in a sheet
+ 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
+
+ # write the current spreadsheet to stdout or into a file
+ #--
+ #TODO: refactoring --> GenericSpreadsheet
+ def to_csv(filename=nil,sheet=nil)
+ sheet = @default_sheet unless sheet
+ if filename
+ file = File.open(filename,"w") # do |file|
+ write_csv_content(file,sheet)
+ file.close
+ else
+ write_csv_content(STDOUT,sheet)
+ end
+ true
+ end
+
+
+ # find a row either by row number or a condition
+ # Caution: this works only within the default sheet -> set default_sheet before you call this method
+ # (experimental. see examples in the test_roo.rb file)
+ #--
+ # -----------------------------------------------------
+ # !!!TODO: should be factored out to GenericSpreadsheet
+ # also in Openofiffe
+ # -----------------------------------------------------
+ #++
+ def find(*args)
+ result_array = false
+ args.each {|arg,val|
+ if arg.class == Hash
+ arg.each { |hkey,hval|
+ if hkey == :array and hval == true
+ result_array = true
end
- end
+ }
end
-
+ }
+ column_with = {}
+ 1.upto(last_column) do |col|
+ column_with[cell(@header_line,col)] = col
end
- # @cell["1,2"] = 42
- # @cell_type["1,2"] = "string"
- # @cells_read = true
- # return
+ result = Array.new
+ #-- id
+ if args[0].class == Fixnum
+ rownum = args[0]
+ tmp = {}
+ 1.upto(self.row(rownum).size) {|j|
+ x = ''
+ column_with.each { |key,val|
+ if val == j
+ x = key
+ end
+ }
+ tmp[x] = cell(rownum,j)
+ }
+ result = [ tmp ] # row(rownum)
+ #-- :all
+ elsif args[0] == :all
+ if args[1].class == Hash
+ args[1].each {|key,val|
+ if key == :conditions
+ column_with = {}
+ 1.upto(last_column) do |col|
+ column_with[cell(@header_line,col)] = col
+ end
+ conditions = val
+ first_row.upto(last_row) do |i|
+ # are all conditions met?
+ found = 1
+ conditions.each { |key,val|
+ if cell(i,column_with[key]) == val
+ found *= 1
+ else
+ found *= 0
+ end
+ }
+ # p self.row(i) if found > 0
+ if found > 0
+ tmp = {}
+ 1.upto(self.row(i).size) {|j|
+ x = ''
+ column_with.each { |key,val|
+ if val == j
+ x = key
+ end
+ }
+ tmp[x] = cell(i,j)
+ }
+ if result_array
+ result << self.row(i)
+ else
+ result << tmp
+ end
+ end
+ end
+ end # :conditions
+ }
+ end
+ end
+ result
+ end
+ private
- #TODO: die Grenzen sind noch fix
- 1.upto(10) do |y|
- Openoffice.letter_to_number('A').upto(Openoffice.letter_to_number('H')) do |x|
- # p x.to_s+", "+y.to_s
- # unless empty?(y,x)
- # @cell["#{y},#{x}"] = cell(y,x)
- # @cell_type["#{y},#{x}"] = cell(y,x)
- # end
+# def read_cells_old(sheet=nil) #TODO:
+# sheet = @default_sheet unless sheet
+# sheet_found = true # TODO:
+# #TODO: raise ArgumentError falls sheet nicht existiert - siehe openoffice
+# #TODO: boundaries !!!
+# 1.upto(25) { |y|
+# 1.upto(20) { |x|
+# key = "#{y},#{x}"
+# @cell[sheet] = {} unless @cell[sheet]
+# value = cell(y,x,sheet)
+# @cell[sheet][key] = value unless value == "" or value == nil
+# }
+# }
+# if !sheet_found
+# raise RangeErrror, "invalid sheet name"
+# end
+# @cells_read[sheet] = true
+# end
+
+# read all cells in a sheet
+ def read_cells(sheet=nil)
+ sheet = @default_sheet unless 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)
+ #@first_row[sheet].upto(@last_row[sheet]) { |y|
+ # @first_column[sheet].upto(@last_column[sheet]) { |x|
+ # key = "#{y},#{x}"
+ # @cell[sheet] = {} unless @cell[sheet]
+ # value = cell(y,x,sheet)
+ # @cell[sheet][key] = value unless value == "" or value == nil
+ # }
+ #}
+ sheet_no = sheets.index(sheet)+1
+ doc = @gs.fulldoc(sheet_no)
+ (doc/"gs:cell").each {|item|
+ row = item['row']
+ col = item['col']
+ value = item['inputvalue']
+ numericvalue = item['numericvalue']
+ # puts numericvalue
+ # puts value
+ # puts value[0,1]
+ if value[0,1] == '='
+ formula = value
+# $log.debug("formula: <#{formula}>")
+ # puts "Formel gefunden"
+ else
+ formula = nil
end
- end
- @cells_read = true
+ # puts formula
+ #--
+ @cell_type[sheet] = {} unless @cell_type[sheet]
+ if formula
+ ty = :formula
+if numeric?(numericvalue)
+ value = numericvalue.to_f
+else
+ value = numericvalue
+end
+ elsif Google.date?(value)
+ ty = :date
+ elsif numeric?(value) # or o.class ???
+ ty = :float
+ value = value.to_f
+ else
+ ty = :string
+ end
+ key = "#{row},#{col}"
+ @cell[sheet] = {} unless @cell[sheet]
+ if ty == :date
+ dd,mm,yyyy = value.split('/')
+ @cell[sheet][key] = sprintf("%04d-%02d-%02d",yyyy.to_i,mm.to_i,dd.to_i)
+ else
+ @cell[sheet][key] = value unless value == "" or value == nil
+ end
+ @cell_type[sheet][key] = ty # Openoffice.oo_type_2_roo_type(vt)
+ @formula[sheet] = {} unless @formula[sheet]
+# $log.debug("formula vor belegen @formula: <#{formula}>") if formula
+ @formula[sheet][key] = formula if formula
+ # $log.debug("@formula[#{sheet}][#{key}] = #{formula}") if formula
+ # $log.debug("#{@formula[sheet][key]}") if formula
+
+
+ }
+ @cells_read[sheet] = true
end
- def first_row
- read_cells unless @cells_read
- 1
+ # Checks if the default_sheet exists. Otherwise a RangeError exception is
+ # raised
+ def check_default_sheet
+ sheet_found = false
+ raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil
+ if sheets.index(@default_sheet)
+ sheet_found = true
+ end
+ if ! sheet_found
+ raise RangeError, "sheet '#{@default_sheet}' not found"
+ end
end
- def last_row
- read_cells unless @cells_read
- 100
+ def numeric?(string)
+ string =~ /^[0-9]+[\.]*[0-9]*$/
+ end
+
+ # convert string DD/MM/YYYY into a Date-object
+ #TODO: was ist mit verschiedenen Typen der Datumseingabe bei Google?
+ def Google.to_date(string)
+ if string.strip =~ /^([0-9]+)\/([0-9]+)\/([0-9]+)$/
+ return Date.new($3.to_i,$2.to_i,$1.to_i)
+ else
+ return nil
+ end
+ end
+
+ #TODO: refactoring to GenericSpreadsheet?
+ def write_csv_content(file=nil,sheet=nil)
+ file = STDOUT unless file
+ if first_row # sheet is not empty
+ first_row(sheet).upto(last_row(sheet)) do |row|
+ 1.upto(last_column(sheet)) do |col|
+ file.print(",") if col > 1
+ onecell = cell(row,col,sheet)
+ onecelltype = celltype(row,col,sheet)
+ file.print one_cell_output(onecelltype,onecell,empty?(row,col,sheet))
+ end
+ file.print("\n")
+ end # sheet not empty
+ end
+ end
+
+ #TODO: refactor to Generic....
+ def one_cell_output(onecelltype,onecell,empty)
+ str = ""
+ if empty
+ str += ''
+ else
+ case onecelltype
+ when :string
+ if onecell == ""
+ str << ''
+ else
+ onecell.gsub!(/"/,'""')
+ str << ('"'+onecell+'"')
+ end
+ when :float,:percentage
+ if onecell == onecell.to_i
+ str << onecell.to_i.to_s
+ else
+ str << onecell.to_s
+ end
+ when :formula
+ if onecell.class == String
+ if onecell == ""
+ str << ''
+ else
+ onecell.gsub!(/"/,'""')
+ str << '"'+onecell+'"'
+ end
+ elsif onecell.class == Float
+ if onecell == onecell.to_i
+ str << onecell.to_i.to_s
+ else
+ str << onecell.to_s
+ end
+ else
+ raise "unhandled onecell-class "+onecell.class.to_s
+ end
+ when :date
+ str << '"'+onecell.to_s+'"'
+ else
+ raise "unhandled celltype "+onecelltype.to_s
+ end
+ end
+ #cells << onecell
+ str
end
end # class