Class: Openoffice
- Inherits:
-
Object
- Object
- Openoffice
- Defined in:
- lib/roo/openoffice.rb
Constant Summary collapse
- @@nr =
0
Class Method Summary collapse
Instance Method Summary collapse
- #as_letter(n) ⇒ Object
-
#cell(row, col) ⇒ Object
return 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.
-
#celltype(row, col) ⇒ Object
returns the open-office type of a cell.
-
#default_sheet=(s) ⇒ Object
set the working sheet in the document.
- #empty?(row, col) ⇒ Boolean
-
#first_column ⇒ Object
returns the number of the first non-empty column.
- #first_column_as_letter ⇒ Object
-
#first_row ⇒ Object
returns the number of the first non-empty row.
- #first_row_as_letter ⇒ Object
-
#formula(row, col) ⇒ Object
returns the formula at (row,col).
- #formula?(row, col) ⇒ Boolean
-
#initialize(filename) ⇒ Openoffice
constructor
A new instance of Openoffice.
-
#last_column ⇒ Object
returns the number of the last non-empty column.
- #last_column_as_letter ⇒ Object
-
#last_row ⇒ Object
returns the number of the last non-empty row.
- #last_row_as_letter ⇒ Object
- #number_to_letter(n) ⇒ Object
-
#officeversion ⇒ Object
version of the openoffice document.
-
#reload ⇒ Object
reopens and read a spreadsheet document.
-
#row(rownumber) ⇒ Object
returns all values in this row as an array row numbers are 1,2,3,…
- #save ⇒ Object
- #set(row, col, value) ⇒ Object
-
#sheets ⇒ Object
returns an array of sheets in the spreadsheet.
-
#to_s ⇒ Object
shows the internal representation of all cells mainly for debugging purposes.
Constructor Details
#initialize(filename) ⇒ Openoffice
Returns a new instance of Openoffice.
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
# File 'lib/roo/openoffice.rb', line 18 def initialize(filename) @cells_read = false @filename = filename @tmpdir = "oo_"+$$.to_s unless File.exists?(@tmpdir) FileUtils::mkdir(@tmpdir) end @@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 DateTime.now > Date.new(2007,6,21) FileUtils::rm_r(@tmpdir) end @default_sheet = nil end |
Class Method Details
.letter_to_number(letters) ⇒ Object
255 256 257 258 259 260 261 262 263 264 |
# File 'lib/roo/openoffice.rb', line 255 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 |
Instance Method Details
#as_letter(n) ⇒ Object
234 235 236 |
# File 'lib/roo/openoffice.rb', line 234 def as_letter(n) number_to_letter(last_row) end |
#cell(row, col) ⇒ Object
return 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
61 62 63 64 65 66 67 68 69 |
# File 'lib/roo/openoffice.rb', line 61 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
92 93 94 95 96 97 98 99 100 101 102 |
# File 'lib/roo/openoffice.rb', line 92 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 |
#default_sheet=(s) ⇒ Object
set the working sheet in the document
131 132 133 |
# File 'lib/roo/openoffice.rb', line 131 def default_sheet=(s) @default_sheet = s end |
#empty?(row, col) ⇒ Boolean
248 249 250 251 252 253 |
# File 'lib/roo/openoffice.rb', line 248 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
207 208 209 210 211 212 213 214 215 216 |
# File 'lib/roo/openoffice.rb', line 207 def first_column read_cells unless @cells_read result = 999_999 # more than a spreadsheet can hold @cell.each_pair {|key,value| y,x = key.split(',') x = x.to_i result = [result, x].min if value } result end |
#first_column_as_letter ⇒ Object
218 219 220 |
# File 'lib/roo/openoffice.rb', line 218 def first_column_as_letter number_to_letter(first_column) end |
#first_row ⇒ Object
returns the number of the first non-empty row
195 196 197 198 199 200 201 202 203 204 |
# File 'lib/roo/openoffice.rb', line 195 def first_row read_cells unless @cells_read result = 999_999 # more than a spreadsheet can hold @cell.each_pair {|key,value| y,x = key.split(',') y = y.to_i result = [result, y].min if value } result end |
#first_row_as_letter ⇒ Object
226 227 228 |
# File 'lib/roo/openoffice.rb', line 226 def first_row_as_letter number_to_letter(first_row) end |
#formula(row, col) ⇒ Object
returns the formula at (row,col)
72 73 74 75 76 77 78 79 80 |
# File 'lib/roo/openoffice.rb', line 72 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
82 83 84 |
# File 'lib/roo/openoffice.rb', line 82 def formula?(row,col) formula(row,col) != nil end |
#last_column ⇒ Object
returns the number of the last non-empty column
183 184 185 186 187 188 189 190 191 192 |
# File 'lib/roo/openoffice.rb', line 183 def last_column read_cells unless @cells_read result = 0 @cell.each_pair {|key,value| y,x = key.split(',') x = x.to_i result = [result, x].max if value } result end |
#last_column_as_letter ⇒ Object
222 223 224 |
# File 'lib/roo/openoffice.rb', line 222 def last_column_as_letter number_to_letter(last_column) end |
#last_row ⇒ Object
returns the number of the last non-empty row
171 172 173 174 175 176 177 178 179 180 |
# File 'lib/roo/openoffice.rb', line 171 def last_row read_cells unless @cells_read result = 0 @cell.each_pair {|key,value| y,x = key.split(',') y = y.to_i result = [result, y].max if value } result end |
#last_row_as_letter ⇒ Object
230 231 232 |
# File 'lib/roo/openoffice.rb', line 230 def last_row_as_letter number_to_letter(last_row) end |
#number_to_letter(n) ⇒ Object
238 239 240 241 242 243 244 245 246 |
# File 'lib/roo/openoffice.rb', line 238 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 |
#officeversion ⇒ Object
version of the openoffice document
136 137 138 139 |
# File 'lib/roo/openoffice.rb', line 136 def officeversion read_cells unless @cells_read @officeversion end |
#reload ⇒ Object
reopens and read a spreadsheet document
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
# File 'lib/roo/openoffice.rb', line 41 def reload @cells_read = false @tmpdir = "oo_"+$$.to_s unless File.exists?(@tmpdir) FileUtils::mkdir(@tmpdir) end 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 FileUtils::rm_r(@tmpdir) @default_sheet = nil end |
#row(rownumber) ⇒ Object
returns all values in this row as an array row numbers are 1,2,3,… like in the spreadsheet
150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 |
# File 'lib/roo/openoffice.rb', line 150 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
266 267 268 |
# File 'lib/roo/openoffice.rb', line 266 def save 42 end |
#set(row, col, value) ⇒ Object
86 87 88 89 |
# File 'lib/roo/openoffice.rb', line 86 def set(row,col,value) puts "setze zelle(#{row},#{col})" @cell["#{row},#{col}"] = value end |
#sheets ⇒ Object
returns an array of sheets in the spreadsheet
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 |
# File 'lib/roo/openoffice.rb', line 106 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 |
#to_s ⇒ Object
shows the internal representation of all cells mainly for debugging purposes
143 144 145 146 |
# File 'lib/roo/openoffice.rb', line 143 def to_s read_cells unless @cells_read @cell.inspect end |