lib/roo/openoffice.rb in roo-0.3.0 vs lib/roo/openoffice.rb in roo-0.4.0
- old
+ new
@@ -2,22 +2,22 @@
require 'rubygems'
require 'rexml/document'
require 'fileutils'
require 'zip/zipfilesystem'
require 'date'
+require 'llip'
-class Fixnum
- def as_letter
- "ABCDEFGHIJKLMNOPQRSTUVWXYZ"[self-1,1]
- end
-end
+require 'lib/roo/spreadsheetparser'
class Openoffice
@@nr = 0
def initialize(filename)
+ if filename[-4..-1] != ".ods"
+ warn "are you sure, this is an openoffice file?"
+ end
@cells_read = false
@filename = filename
@tmpdir = "oo_"+$$.to_s
unless File.exists?(@tmpdir)
FileUtils::mkdir(@tmpdir)
@@ -29,17 +29,20 @@
@doc = REXML::Document.new file
file.close
@cell = Hash.new
@cell_type = Hash.new
@formula = Hash.new
- if DateTime.now > Date.new(2007,6,21)
+# if ENV["roo_local"] != "thomas-p"
FileUtils::rm_r(@tmpdir)
- end
+# end
@default_sheet = nil
+ @first_column = @last_column = nil
+ @first_row = @last_row = nil
end
# reopens and read a spreadsheet document
+ if false
def reload
@cells_read = false
@tmpdir = "oo_"+$$.to_s
unless File.exists?(@tmpdir)
FileUtils::mkdir(@tmpdir)
@@ -50,13 +53,24 @@
file.close
@cell = Hash.new
@cell_type = Hash.new
FileUtils::rm_r(@tmpdir)
@default_sheet = nil
+ @first_column = @last_column = nil
+ @first_row = @last_row = nil
end
+ end
- # return the content of a spreadsheet-cell
+ def reload
+ default_sheet = @default_sheet
+ initialize(@filename)
+ self.default_sheet = default_sheet
+ @first_row = @last_row = @first_column = @last_column = nil
+
+ 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 first line, first row
def cell(row,col)
read_cells unless @cells_read
@@ -67,44 +81,56 @@
end
@cell["#{row},#{col}"]
end
# returns the formula at (row,col)
+ # nil if there is no formula
def formula(row,col)
read_cells unless @cells_read
row,col = normalize(row,col)
if @formula["#{row},#{col}"] == nil
return nil
else
return @formula["#{row},#{col}"]["oooc:".length..-1]
end
end
+ # true, if there is a formula
def formula?(row,col)
+ read_cells unless @cells_read
+ row,col = normalize(row,col)
formula(row,col) != nil
end
+ # set a cell to a certain value
+ # (this will not be saved back to the spreadsheet file!)
def set(row,col,value)
- puts "setze zelle(#{row},#{col})"
- @cell["#{row},#{col}"] = value
+ row,col = normalize(row,col)
+ set_value(row,col,value)
+ if value.class == Fixnum
+ set_type(row,col,:float)
+ elsif value.class == String
+ set_type(row,col,:string)
+ elsif value.class == Float
+ set_type(row,col,:string)
+ else
+ raise ArgumentError, "Typ fuer "+value.to_s+" nicht gesetzt"
+ end
end
# returns the open-office type of a cell
def celltype(row,col)
read_cells unless @cells_read
row,col = normalize(row,col)
-
- # p @formula["#{row},#{col}"]
if @formula["#{row},#{col}"]
return :formula
else
@cell_type["#{row},#{col}"]
end
end
-
- # returns an array of sheets in the spreadsheet
+ # returns an array of sheet names in the spreadsheet
def sheets
return_sheets = []
oo_document_count = 0
@doc.each_element do |oo_document|
oo_document_count += 1
@@ -128,15 +154,21 @@
end
# set the working sheet in the document
def default_sheet=(s)
@default_sheet = s
+ @first_row = @last_row = @first_column = @last_column = nil
+ @cells_read = false
+ @cell = Hash.new
+ @cell_type = Hash.new
+ @formula = Hash.new
end
# version of the openoffice document
+ # at 2007 this is always "1.0"
def officeversion
- read_cells unless @cells_read
+ read_cells(:ignore_default_sheet => true) unless @cells_read
@officeversion
end
# shows the internal representation of all cells
# mainly for debugging purposes
@@ -150,11 +182,10 @@
def row(rownumber)
read_cells unless @cells_read
result = []
tmp_arr = []
@cell.each_pair {|key,value|
-
y,x = key.split(',')
x = x.to_i
y = y.to_i
if y == rownumber
tmp_arr[x] = value
@@ -168,147 +199,188 @@
end
# returns the number of the last non-empty row
def last_row
read_cells unless @cells_read
- result = 0
+ if @last_row
+ return @last_row
+ end
+ impossible_value = 0
+ result = impossible_value
@cell.each_pair {|key,value|
y,x = key.split(',')
y = y.to_i
result = [result, y].max if value
}
+ result = nil if result == impossible_value
+ @last_row = result
result
end
# returns the number of the last non-empty column
def last_column
read_cells unless @cells_read
- result = 0
+ if @last_column
+ return @last_column
+ end
+ impossible_value = 0
+ result = impossible_value
@cell.each_pair {|key,value|
y,x = key.split(',')
x = x.to_i
result = [result, x].max if value
}
+ result = nil if result == impossible_value
+ @last_column = result
result
end
# returns the number of the first non-empty row
def first_row
read_cells unless @cells_read
- result = 999_999 # more than a spreadsheet can hold
+ if @first_row
+ return @first_row
+ end
+ impossible_value = 999_999 # more than a spreadsheet can hold
+ result = impossible_value
@cell.each_pair {|key,value|
y,x = key.split(',')
y = y.to_i
result = [result, y].min if value
}
+ result = nil if result == impossible_value
+ @first_row = result
result
end
# returns the number of the first non-empty column
def first_column
read_cells unless @cells_read
- result = 999_999 # more than a spreadsheet can hold
+ if @first_column
+ return @first_column
+ end
+ impossible_value = 999_999 # more than a spreadsheet can hold
+ result = impossible_value
@cell.each_pair {|key,value|
y,x = key.split(',')
x = x.to_i
result = [result, x].min if value
}
+ result = nil if result == impossible_value
+ @first_column = result
result
end
+ # first non-empty column as a letter
def first_column_as_letter
- number_to_letter(first_column)
+ Openoffice.number_to_letter(first_column)
end
+ # last non-empty column as a letter
def last_column_as_letter
- number_to_letter(last_column)
+ Openoffice.number_to_letter(last_column)
end
- def first_row_as_letter
- number_to_letter(first_row)
+ # true if cell is empty
+ def empty?(row, col)
+ read_cells unless @cells_read
+ return true unless cell(row, col)
+ return true if celltype(row, col) == "string" && cell(row, col).empty?
+ false
end
- def last_row_as_letter
- number_to_letter(last_row)
+=begin
+ # save spreadsheet
+ def save
+ 42
end
+=end
- def as_letter(n)
- number_to_letter(last_row)
+ # evaluate the formula at this cell
+ # experimental: DO NOT USE THIS!
+ def solve(row,col)
+ parser = SpreadsheetParser.new
+ visitor = Visitor.new
+ #puts cell(row,col)
+ puts formula(row,col)
+ formula = formula(row,col)[1..-1] # .downcase
+ puts formula
+ #eval formula
+ #parser.parse(formula)
+ parser.parse(formula).accept(visitor)
end
- def number_to_letter(n)
- letters=""
- while n > 0
- num = n%26
- letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"[num-1,1] + letters
- n = n.div(26)
- end
- letters
- end
-
- def empty?(row, col)
+ # returns each formula in the selected sheet as an array of elements
+ # [row, col, formula]
+ def formulas
+ theformulas = Array.new
read_cells unless @cells_read
- return true unless cell(row, col)
- return true if celltype(row, col) == "string" && cell(row, col).empty?
- false
+ first_row.upto(last_row) {|row|
+ first_column.upto(last_column) {|col|
+ if formula?(row,col)
+ f = [row, col, formula(row,col)]
+ theformulas << f
+ end
+ }
+ }
+ theformulas
end
-
- def Openoffice.letter_to_number(letters)
- result = 0
- while letters && letters.length > 0
- character = letters[0,1].upcase
- num = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".index(character)+1
- result = result * 26 + num
- letters = letters[1..-1]
+
+ # returns a rectangular area (default: all cells) as yaml-output
+ # you can add additional attributes with the prefix parameter like:
+ # oo.to_yaml({"file"=>"flightdata_2007-06-26", "sheet" => "1"})
+ def to_yaml(prefix={}, from_row=nil, from_column=nil, to_row=nil, to_column=nil)
+ result = "--- \n"
+ (from_row||first_row).upto(to_row||last_row) do |row|
+ (from_column||first_column).upto(to_column||last_column) do |col|
+ unless self.empty?(row,col)
+ result << "cell_#{row}_#{col}: \n"
+ prefix.each {|k,v|
+ result << " #{k}: #{v} \n"
+ }
+ result << " row: #{row} \n"
+ result << " col: #{col} \n"
+ result << " celltype: #{self.celltype(row,col)} \n"
+ result << " value: #{self.cell(row,col)} \n"
+ end
+ end
end
result
end
- def save
- 42
- end
-
private
# read all cells in the selected sheet
- def read_cells
+ def read_cells(*args)
+ if :ignore_default_sheet == false
+ raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil
+ end
oo_document_count = 0
@doc.each_element do |oo_document|
@officeversion = oo_document.attributes['version']
oo_document_count += 1
oo_element_count = 0
oo_document.each_element do |oo_element|
oo_element_count += 1
- # p oo_element.name
if oo_element.name == "body"
- # puts "Body gefunden "
oo_element.each_element do |be|
- # p be.name
if be.name == "spreadsheet"
be.each_element do |se|
- # p se
if se.name == "table"
if se.attributes['name']==@default_sheet
x=1
y=1
- # puts "table gefunden"
- #se.each_element
se.each_element do |te|
- # p te.name
if te.name == "table-column"
- # p te.attributes
rep = te.attributes["number-columns-repeated"]
- # p "rep = "+rep.to_s
elsif te.name == "table-row"
if te.attributes['number-rows-repeated']
skip_y = te.attributes['number-rows-repeated'].to_i
y = y + skip_y - 1 # minus 1 because this line will be counted as a line element
end
- # p te
te.each_element do |tr|
- # p tr
if tr.name == 'table-cell'
skip = tr.attributes['number-columns-repeated']
formula = tr.attributes['formula']
vt = tr.attributes['value-type']
v = tr.attributes['value']
@@ -327,18 +399,11 @@
@cell_type["#{y},#{x+i}"] = vt
@formula["#{y},#{x+i}"] = formula if formula
if @cell_type["#{y},#{x+i}"] == 'float'
@cell["#{y},#{x+i}"] = v.to_f
elsif @cell_type["#{y},#{x+i}"] == 'string'
- # puts "in string zweig..."
- #tr.each_element do |str|
- # if str.name == 'p'
- # @cell["#{y},#{x+i}"] = str.text
- # end
- #end
@cell["#{y},#{x+i}"] = v
-
elsif @cell_type["#{y},#{x+i}"] == 'date'
@cell["#{y},#{x+i}"] = tr.attributes['date-value']
else
@cell["#{y},#{x+i}"] = v
end
@@ -418,6 +483,38 @@
col = Openoffice.letter_to_number(col)
end
return row,col
end
-end
+ # convert a number to something like this: 'AB'
+ def Openoffice.number_to_letter(n)
+ letters=""
+ while n > 0
+ num = n%26
+ letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"[num-1,1] + letters
+ n = n.div(26)
+ end
+ letters
+ end
+
+ # convert letters like 'AB' to a number
+ def Openoffice.letter_to_number(letters)
+ result = 0
+ while letters && letters.length > 0
+ character = letters[0,1].upcase
+ num = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".index(character)+1
+ result = result * 26 + num
+ letters = letters[1..-1]
+ end
+ result
+ end
+
+
+ def set_value(row,col,value)
+ @cell["#{row},#{col}"] = value
+ end
+
+ def set_type(row,col,type)
+ @cell_type["#{row},#{col}"] = type
+ end
+
+end # class