Class: Openoffice

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

Defined Under Namespace

Classes: Font

Constant Summary collapse

@@nr = 
0

Instance Attribute Summary

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line

Instance Method Summary collapse

Methods inherited from GenericSpreadsheet

#column, #empty?, #find, #first_column, #first_column_as_letter, #first_row, #info, #initialise_tmp, #last_column, #last_column_as_letter, #last_row, #reload, #remove_tmp, #row, #to_csv, #to_xml, #to_yaml

Constructor Details

#initialize(filename, packed = nil, file_warning = :error) ⇒ Openoffice

initialization and opening of a spreadsheet file values for packed: :zip



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
43
44
45
46
47
48
49
50
51
52
53
54
 
# File 'lib/roo/openoffice.rb', line 14

def initialize(filename, packed=nil, file_warning=:error) #, create = false)
  @file_warning = file_warning
  super()

  filename = open_from_uri(filename) if filename[0,7] == "http://"
  filename = unzip(filename) if packed and packed == :zip
  begin
    file_type_check(filename,'.ods','an openoffice')
    #if create and ! File.exists?(filename)
    #  self.create_openoffice(filename)
    #end
    @cells_read = Hash.new
    #TODO: @cells_read[:default] = false
    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @@nr += 1
    @file_nr = @@nr
    extract_content
    file = File.new(File.join(@tmpdir, @file_nr.to_s+"_roo_content.xml"))
    @doc = XML::Parser.io(file).parse
    file.close
  ensure
    #if ENV["roo_local"] != "thomas-p"
    FileUtils::rm_r(@tmpdir) if @tmpdir
    #end
  end
  @default_sheet = self.sheets.first
  @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
  @style = Hash.new
  @style_defaults = Hash.new { |h,k| h[k] = [] }
  @style_definitions = Hash.new 
  @header_line = 1
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.



72
73
74
75
76
77
78
79
80
81
 
# File 'lib/roo/openoffice.rb', line 72

def cell(row, col, sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  if celltype(row,col,sheet) == :date
    yyyy,mm,dd = @cell[sheet][[row,col]].split('-')
    return Date.new(yyyy.to_i,mm.to_i,dd.to_i)
  end
  @cell[sheet][[row,col]]
end

#celltype(row, col, sheet = nil) ⇒ Object

returns the type of a cell:

  • :float

  • :string

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



156
157
158
159
160
161
162
163
164
165
 
# File 'lib/roo/openoffice.rb', line 156

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

#create_openoffice(filename) ⇒ Object

creates a new empty openoffice-spreadsheet file



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

def create_openoffice(filename) #:nodoc:
  #TODO: a better way for creating the file contents
  # now you have to call mkbase64...rb to create an include file with all
  # the empty files in an openoffice zip-file
  load 'base64include.rb'
  # puts @@empty_spreadsheet
  f = File.open(filename,'wb')
  f.print(Base64.decode64(@@empty_spreadsheet))
  f.close
end

#font(row, col, sheet = nil) ⇒ Object

Given a cell, return the cell’s style



122
123
124
125
126
127
128
 
# File 'lib/roo/openoffice.rb', line 122

def font(row, col, sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  style_name = @style[sheet][[row,col]] || @style_defaults[sheet][col - 1] || 'Default'
  @style_definitions[style_name]
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.



86
87
88
89
90
91
92
93
94
95
 
# File 'lib/roo/openoffice.rb', line 86

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]]["oooc:".length..-1]
  end
end

#formula?(row, col, sheet = nil) ⇒ Boolean

true, if there is a formula

Returns:

  • (Boolean) 


98
99
100
101
102
103
 
# File 'lib/roo/openoffice.rb', line 98

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 


197
198
199
200
201
202
203
204
205
206
207
208
209
210
 
# File 'lib/roo/openoffice.rb', line 197

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

#officeversion ⇒ Object

version of the openoffice document at 2007 this is always “1.0”



177
178
179
180
 
# File 'lib/roo/openoffice.rb', line 177

def officeversion
  oo_version
  @officeversion
end

#save ⇒ Object

save spreadsheet



191
192
193
 
# File 'lib/roo/openoffice.rb', line 191

def save #:nodoc:
  42
end

#set(row, col, value, sheet = nil) ⇒ Object

set a cell to a certain value (this will not be saved back to the spreadsheet file!)



132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/roo/openoffice.rb', line 132

def set(row,col,value,sheet=nil) #:nodoc:
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  set_value(row,col,value,sheet)
  if value.class == Fixnum
    set_type(row,col,:float,sheet)
  elsif value.class == String
    set_type(row,col,:string,sheet)
  elsif value.class == Float
    set_type(row,col,:string,sheet)
  else
    raise ArgumentError, "Type for "+value.to_s+" not set"
  end
end

#sheets ⇒ Object 



167
168
169
170
171
172
173
 
# File 'lib/roo/openoffice.rb', line 167

def sheets
  return_sheets = []
  @doc.find("//*[local-name()='table']").each do |sheet|
    return_sheets << sheet.attributes['name']
  end
  return_sheets
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



184
185
186
187
188
 
# File 'lib/roo/openoffice.rb', line 184

def to_s(sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  @cell[sheet].inspect
end