Class: Openoffice

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

Overview

require ‘lib/roo/spreadsheetparser’

Direct Known Subclasses

Excel, Google

Constant Summary collapse

@@nr = 
0

Instance Method Summary collapse

Constructor Details

#initialize(filename, packed = nil) ⇒ Openoffice

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



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
55
56
 
# File 'lib/roo/openoffice.rb', line 19

def initialize(filename, packed=nil) #, create = false)
  @tmpdir = "oo_"+$$.to_s
  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"
    warn "are you sure, this is an openoffice file?"
  end
  #if create and ! File.exists?(filename)
  #  self.create_openoffice(filename)
  #end
  @cells_read = false
  @filename = filename
  @@nr += 1
  @file_nr = @@nr
  extract_content
  file = File.new(File.join(@tmpdir, @file_nr.to_s+"_roo_content.xml")) 
  @doc = REXML::Document.new file
  file.close
  @cell = Hash.new
  @cell_type = Hash.new
  @formula = Hash.new
  #if ENV["roo_local"] != "thomas-p"
    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
  end
  @first_column = @last_column = nil
  @first_row = @last_row = nil
#    trap('INT') {
#      FileUtils::rm_r(@tmpdir)
#    }
end

Instance Method Details

#cell(row, col) ⇒ 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 first line, first row



82
83
84
85
86
87
88
89
90
 
# File 'lib/roo/openoffice.rb', line 82

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

#celltype(row, col) ⇒ Object

returns the open-office type of a cell



129
130
131
132
133
134
135
136
137
 
# File 'lib/roo/openoffice.rb', line 129

def celltype(row,col)
  read_cells unless @cells_read
  row,col = normalize(row,col)
  if @formula["#{row},#{col}"]
    return :formula
  else
    @cell_type["#{row},#{col}"]
  end
end

#create_openoffice(filename) ⇒ Object

creates a new empty openoffice-spreadsheet file



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

def create_openoffice(filename)
  #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

#default_sheet=(sheet) ⇒ Object Also known as: set_default_sheet

set the working sheet in the document



173
174
175
176
177
178
179
180
181
182
183
184
 
# File 'lib/roo/openoffice.rb', line 173

def default_sheet=(sheet)
  if ! sheet.kind_of?(String)
    raise TypeError, "what are you trying to set as default sheet?"
  end
  @default_sheet = sheet
  @first_row = @last_row = @first_column = @last_column = nil
  @cells_read = false
  @cell = Hash.new
  @cell_type = Hash.new
  @formula = Hash.new
  check_default_sheet
end

#empty?(row, col) ⇒ Boolean

true if cell is empty

Returns:

  • (Boolean) 


307
308
309
310
311
312
 
# File 'lib/roo/openoffice.rb', line 307

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

#first_column ⇒ Object

returns the number of the first non-empty column



279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/roo/openoffice.rb', line 279

def first_column
  read_cells unless @cells_read
  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_column_as_letter ⇒ Object

first non-empty column as a letter



297
298
299
 
# File 'lib/roo/openoffice.rb', line 297

def first_column_as_letter
  Openoffice.number_to_letter(first_column)
end

#first_row ⇒ Object

returns the number of the first non-empty row



261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
 
# File 'lib/roo/openoffice.rb', line 261

def first_row
  read_cells unless @cells_read
  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

#formula(row, col) ⇒ Object

returns the formula at (row,col) nil if there is no formula



94
95
96
97
98
99
100
101
102
 
# File 'lib/roo/openoffice.rb', line 94

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

#formula?(row, col) ⇒ Boolean

true, if there is a formula

Returns:

  • (Boolean) 


105
106
107
108
109
 
# File 'lib/roo/openoffice.rb', line 105

def formula?(row,col)
  read_cells unless @cells_read
  row,col = normalize(row,col)
  formula(row,col) != nil
end

#formulas ⇒ Object

returns each formula in the selected sheet as an array of elements

row, col, formula 


335
336
337
338
339
340
341
342
343
344
345
346
347
 
# File 'lib/roo/openoffice.rb', line 335

def formulas
  theformulas = Array.new
  read_cells unless @cells_read
  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

#last_column ⇒ Object

returns the number of the last non-empty column



243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/roo/openoffice.rb', line 243

def last_column
  read_cells unless @cells_read
  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

#last_column_as_letter ⇒ Object

last non-empty column as a letter



302
303
304
 
# File 'lib/roo/openoffice.rb', line 302

def last_column_as_letter
  Openoffice.number_to_letter(last_column)
end

#last_row ⇒ Object

returns the number of the last non-empty row



225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
 
# File 'lib/roo/openoffice.rb', line 225

def last_row
  read_cells unless @cells_read
  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

#officeversion ⇒ Object

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



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

def officeversion
  # read_cells(false) unless @cells_read
  oo_version
  @officeversion
end

#reload ⇒ Object

reopens and read a spreadsheet document



71
72
73
74
75
76
 
# File 'lib/roo/openoffice.rb', line 71

def reload
  default_sheet = @default_sheet
  initialize(@filename) 
  self.default_sheet = default_sheet
  @first_row = @last_row = @first_column = @last_column = nil
end

#remove_tmp ⇒ Object

recursively removes the current temporary directory this is only needed if you work with zipped files or files via the web



141
142
143
144
145
 
# File 'lib/roo/openoffice.rb', line 141

def remove_tmp
  if File.exists?(@tmpdir)
    FileUtils::rm_r(@tmpdir)
  end
end

#row(rownumber) ⇒ Object

returns all values in this row as an array row numbers are 1,2,3,… like in the spreadsheet



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
 
# File 'lib/roo/openoffice.rb', line 205

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
    end
  }
  result = tmp_arr[1..-1]
  while result[-1] == nil
    result = result[0..-2]
  end
  result
end

#save ⇒ Object

save spreadsheet



315
316
317
 
# File 'lib/roo/openoffice.rb', line 315

def save
  42
end

#set(row, col, value) ⇒ Object

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



113
114
115
116
117
118
119
120
121
122
123
124
125
126
 
# File 'lib/roo/openoffice.rb', line 113

def set(row,col,value)
  read_cells unless @cells_read
  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

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/roo/openoffice.rb', line 148

def sheets
  return_sheets = []
  oo_document_count = 0
  @doc.each_element do |oo_document|
    oo_document_count += 1
    oo_element_count = 0
    oo_document.each_element do |oo_element|
      oo_element_count += 1
      if oo_element.name == "body"
        oo_element.each_element do |be|
          if be.name == "spreadsheet"
            be.each_element do |se|
              if se.name == "table"
                return_sheets << se.attributes['name']
              end
            end
          end
        end
      end
    end
  end
  return_sheets
end

#solve(row, col) ⇒ Object

evaluate the formula at this cell experimental: DO NOT USE THIS!



321
322
323
324
325
326
327
328
329
330
331
 
# File 'lib/roo/openoffice.rb', line 321

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

#to_csv(filename = nil) ⇒ Object

write the current spreadsheet to stdout or into a file



372
373
374
375
376
377
378
379
380
381
 
# File 'lib/roo/openoffice.rb', line 372

def to_csv(filename=nil)
  if filename
    file = File.open(filename,"w") # do |file|
    write_csv_content(file)
    file.close
  else
    write_csv_content
  end
  true
end

#to_s ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



198
199
200
201
 
# File 'lib/roo/openoffice.rb', line 198

def to_s
  read_cells unless @cells_read
  @cell.inspect
end

#to_yaml(prefix = {}, from_row = nil, from_column = nil, to_row = nil, to_column = nil) ⇒ Object

returns a rectangular area (default: all cells) as yaml-output you can add additional attributes with the prefix parameter like: oo.to_yaml(“sheet” => “1”)



352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
 
# File 'lib/roo/openoffice.rb', line 352

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