openoffice.rb in roo-0.8.0

- old
+ new

@@ -4,16 +4,12 @@
 require 'fileutils'
 require 'zip/zipfilesystem'
 require 'date'
 require 'base64'
 
-# The class Openoffice defines the basic functionality for the access to
-# spreadsheet documents. Other classes like _Excel_ are defined as subclasses 
-# of this class.
+class Openoffice < GenericSpreadsheet
 
-class Openoffice
-
   @@nr = 0
 
   # sets the line with attribute names (default: 1)
   attr_accessor :header_line
 
@@ -24,11 +20,11 @@
     unless File.exists?(@tmpdir)
       FileUtils::mkdir(@tmpdir)
     end
     filename = open_from_uri(filename) if filename[0,7] == "http://"
     filename = unzip(filename) if packed and packed == :zip
-    if filename[-4..-1] != ".ods"
+    if File.extname(filename) != ".ods"
       warn "are you sure, this is an openoffice file?"
     end
     #if create and ! File.exists?(filename)
     #  self.create_openoffice(filename)
     #end
@@ -43,11 +39,11 @@
     extract_content
     file = File.new(File.join(@tmpdir, @file_nr.to_s+"_roo_content.xml")) 
     @doc = REXML::Document.new file
     file.close
     #if ENV["roo_local"] != "thomas-p"
-      FileUtils::rm_r(@tmpdir)
+    FileUtils::rm_r(@tmpdir)
     #end
     @default_sheet = nil 
     # no need to set default_sheet if there is only one sheet in the document
     if self.sheets.size == 1
       @default_sheet = self.sheets.first
@@ -72,18 +68,10 @@
     f = File.open(filename,'wb')
     f.print(Base64.decode64(@@empty_spreadsheet))
     f.close
   end
 
-  # reopens and read a spreadsheet document
-  def reload
-    ds = @default_sheet
-    initialize(@filename) 
-    self.default_sheet = ds
-    #@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 the first line and first row.
   def cell(row, col, sheet=nil)
@@ -97,11 +85,11 @@
     @cell[sheet]["#{row},#{col}"]
   end
 
   # Returns the formula at (row,col).
   # Returns nil if there is no formula.
-  # The method Openoffice#formula? checks if there is a 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
@@ -151,18 +139,10 @@
     else
       @cell_type[sheet]["#{row},#{col}"]
     end
   end
 
-  # recursively removes the current temporary directory
-  # this is only needed if you work with zipped files or files via the web
-  def remove_tmp
-    if File.exists?(@tmpdir)
-      FileUtils::rm_r(@tmpdir)
-    end
-  end
-
   # returns an array of sheet names in the spreadsheet
   def sheets
     return_sheets = []
     oo_document_count = 0
     @doc.each_element do |oo_document|
@@ -250,109 +230,10 @@
       result << cell(row,columnnumber,sheet)
     end
     result
   end 
 
-  # returns the number of the last non-empty row
-  def last_row(sheet=nil)
-    sheet = @default_sheet unless sheet 
-    read_cells(sheet) unless @cells_read[sheet]
-    if @last_row[sheet]
-      return @last_row[sheet]
-    end
-    impossible_value = 0
-    result = impossible_value
-    @cell[sheet].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[sheet] = result
-    result
-  end
-
-  # returns the number of the last non-empty column
-  def last_column(sheet=nil)
-    sheet = @default_sheet unless sheet
-    read_cells(sheet) unless @cells_read[sheet]
-    if @last_column[sheet]
-      return @last_column[sheet]
-    end
-    impossible_value = 0
-    result = impossible_value
-    @cell[sheet].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[sheet] = result
-    result
-  end
-
-  # returns the number of the first non-empty row
-  def first_row(sheet=nil)
-    if sheet == nil
-      sheet = @default_sheet
-    end
-    read_cells(sheet) unless @cells_read[sheet]
-    if @first_row[sheet]
-      return @first_row[sheet]
-    end
-    impossible_value = 999_999 # more than a spreadsheet can hold
-    result = impossible_value 
-    @cell[sheet].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[sheet] = result
-    result
-  end
-
-  # returns the number of the first non-empty column
-  def first_column(sheet=nil)
-    if sheet == nil
-      sheet = @default_sheet
-    end
-    read_cells(sheet) unless @cells_read[sheet]
-    if @first_column[sheet]
-      return @first_column[sheet]
-    end
-    impossible_value = 999_999 # more than a spreadsheet can hold
-    result = impossible_value 
-    @cell[sheet].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[sheet] = result
-    result
-  end
-
-  # first non-empty column as a letter
-  def first_column_as_letter(sheet=nil)
-    Openoffice.number_to_letter(first_column(sheet))
-  end
-
-  # last non-empty column as a letter
-  def last_column_as_letter(sheet=nil)
-    Openoffice.number_to_letter(last_column(sheet))
-  end
-
-  # true if cell is empty
-  def empty?(row, col, sheet=nil)
-    sheet = @default_sheet unless sheet
-    read_cells(sheet) unless @cells_read[sheet]
-    return true unless cell(row, col, sheet)
-    return true if celltype(row, col, sheet) == :string && cell(row, col, sheet).empty?
-    false
-  end
-
   # save spreadsheet
   def save #:nodoc:
     42
   end
 
@@ -385,34 +266,13 @@
       }
     } 
     theformulas
   end
   
-  # 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,sheet=nil)
-    sheet = @default_sheet unless sheet
-    result = "--- \n"
-    (from_row||first_row(sheet)).upto(to_row||last_row(sheet)) do |row|
-      (from_column||first_column(sheet)).upto(to_column||last_column(sheet)) do |col|
-        unless empty?(row,col,sheet)
-          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,sheet)} \n" 
-          result << "  value: #{self.cell(row,col,sheet)} \n" 
-        end
-      end
-    end
-    result
-  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)
@@ -454,11 +314,11 @@
           end
         }
         tmp[x] = cell(rownum,j) 
       } 
       result = [ tmp ] # row(rownum)
-    #-- :all
+      #-- :all
     elsif args[0] == :all 
       if args[1].class == Hash
         args[1].each {|key,val|
           if key == :conditions
             column_with = {}
@@ -500,71 +360,22 @@
       end
     end
     result
   end
 
-  # Returns information of the spreadsheet document and all sheets within
-  # this document.
-  def info
-    result = "File: #{@filename}\n"+
-    "Number of sheets: #{sheets.size}\n"+
-    "Sheets: #{sheets.map{|sheet| sheet+", "}.to_s[0..-3]}\n"
-    n = 1
-    sheets.each {|sheet|
-      self.default_sheet = sheet
-      result << "Sheet " + n.to_s + ":\n"
-      result << "  First row: #{first_row}\n"
-      result << "  Last row: #{last_row}\n"
-      result << "  First column: #{Openoffice.number_to_letter(first_column)}\n"
-      result << "  Last column: #{Openoffice.number_to_letter(last_column)}"
-      result << "\n" if sheet != sheets.last
-      n += 1
-    }
-    result
-  end
+  
+  private
 
-private
-
-  def process_zipfile_packed(zip, path='') 
-    ret=nil
-    if zip.file.file? path
-      # extract and return filename
-      @tmpdir = "oo_"+$$.to_s
-      unless File.exists?(@tmpdir)
-        FileUtils::mkdir(@tmpdir)
-      end
-      file = File.open(File.join(@tmpdir, path),"wb")
-      file.write(zip.read(path))
-      file.close
-      return File.join(@tmpdir, path)
-    else
-      unless path.empty?
-        path += '/'
-      end
-      zip.dir.foreach(path) do |filename|
-        ret = process_zipfile_packed(zip, path + filename)
-      end
-    end
-    ret
-  end
-
-  def unzip(filename) 
-    ret = nil
-    Zip::ZipFile.open(filename) do |zip|
-      ret = process_zipfile_packed zip
-    end
-    ret
-  end
-
   # read the version of the OO-Version
   def oo_version
     #sheet_found = false
     @doc.each_element do |oo_document|
       @officeversion = oo_document.attributes['version']
     end
   end
 
+  # helper function to set the internal representation of cells
   def set_cell_values(sheet,x,y,i,v,vt,formula,tr,str_v)
     key = "#{y},#{x+i}"
     @cell_type[sheet] = {} unless @cell_type[sheet]
     @cell_type[sheet][key] = Openoffice.oo_type_2_roo_type(vt) 
     @formula[sheet] = {} unless @formula[sheet]
@@ -719,60 +530,16 @@
         process_zipfile(zip, path+filename)
       end 
     end 
   end
 
-
   def extract_content
     Zip::ZipFile.open(@filename) do |zip|
       process_zipfile(zip)
     end
   end
 
-  # converts cell coordinate to numeric values of row,col
-  def normalize(row,col)
-    if row.class == String
-      if col.class == Fixnum
-        # ('A',1):
-        # ('B', 5) -> (5, 2)
-        row, col = col, row
-      else
-        raise ArgumentError
-      end
-    end
-    if col.class == String
-      col = Openoffice.letter_to_number(col)
-    end 
-    return row,col
-  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)
-      raise ArgumentError, "invalid column character '#{letters[0,1]}'" if num == nil
-      num += 1
-      result = result * 26 + num
-      letters = letters[1..-1]
-    end
-    result
-  end
-
-  
   def set_value(row,col,value,sheet=nil)
     sheet = @default_value unless sheet
     @cell[sheet]["#{row},#{col}"] = value
   end
 
@@ -790,26 +557,12 @@
 
   def Openoffice.oo_type_2_roo_type(ootype)
     return A_ROO_TYPE[ootype]
   end
 
-  
-  def open_from_uri(uri)
-    require 'open-uri' ;
-    tempfilename = File.join(@tmpdir, File.basename(uri))
-    f = File.open(tempfilename,"wb")
-    begin
-    open(uri) do |net|
-      f.write(net.read)
-    end
-    rescue
-      raise "could not open #{uri}"
-    end
-    f.close
-    File.join(@tmpdir, File.basename(uri))
-  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|
@@ -821,9 +574,10 @@
         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