Class: Excelx

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

Defined Under Namespace

Classes: Font

Constant Summary collapse

FORMATS = 
{
  'General' => :float,
  '0' => :float,
  '0.00' => :float,
  '#,##0' => :float,
  '#,##0.00' => :float,
  '0%' => :percentage,
  '0.00%' => :percentage,
  '0.00E+00' => :float,
  '# ?/?' => :float, #??? TODO:
  '# ??/??' => :float, #??? TODO:
  'mm-dd-yy' => :date,
  'd-mmm-yy' => :date,
  'd-mmm' => :date,
  'mmm-yy' => :date,
  'h:mm AM/PM' => :date,
  'h:mm:ss AM/PM' => :date,
  'h:mm' => :time,
  'h:mm:ss' => :time,
  'm/d/yy h:mm' => :date,
  '#,##0 ;(#,##0)' => :float,
  '#,##0 ;[Red](#,##0)' => :float,
  '#,##0.00;(#,##0.00)' => :float,
  '#,##0.00;[Red](#,##0.00)' => :float,
  'mm:ss' => :time,
  '[h]:mm:ss' => :time,
  'mmss.0' => :time,
  '##0.0E+0' => :float,
  '@' => :float,
  #-- zusaetzliche Formate, die nicht standardmaessig definiert sind:
  "yyyy\\-mm\\-dd" => :date,
  'dd/mm/yy' => :date,
  'hh:mm:ss' => :time,
  "dd/mm/yy\\ hh:mm" => :datetime,
  'dd/mmm/yy' => :date, # 2011-05-21
  'yyyy-mm-dd' => :date, # 2011-09-16
  # was used in a spreadsheet file from a windows phone
}
STANDARD_FORMATS =

was used in a spreadsheet file from a windows phone

{ 
  0 => 'General',
  1 => '0',
  2 => '0.00',
  3 => '#,##0',
  4 => '#,##0.00',
  9 => '0%',
  10 => '0.00%',
  11 => '0.00E+00',
  12 => '# ?/?',
  13 => '# ??/??',
  14 => 'mm-dd-yy',
  15 => 'd-mmm-yy',
  16 => 'd-mmm',
  17 => 'mmm-yy',
  18 => 'h:mm AM/PM',
  19 => 'h:mm:ss AM/PM',
  20 => 'h:mm',
  21 => 'h:mm:ss',
  22 => 'm/d/yy h:mm',
  37 => '#,##0 ;(#,##0)',
  38 => '#,##0 ;[Red](#,##0)',
  39 => '#,##0.00;(#,##0.00)',
  40 => '#,##0.00;[Red](#,##0.00)',
  45 => 'mm:ss',
  46 => '[h]:mm:ss',
  47 => 'mmss.0',
  48 => '##0.0E+0',
  49 => '@',
}
@@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, #last_column, #last_column_as_letter, #last_row, #reload, #remove_tmp, #row, #to_csv, #to_matrix, #to_xml, #to_yaml

Constructor Details

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

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



88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/roo/excelx.rb', line 88

def initialize(filename, packed=nil, file_warning = :error) #, create = false)
  super()
  @file_warning = file_warning
  file_type_check(filename,'.xlsx','an Excel-xlsx',packed)
  @tmpdir = GenericSpreadsheet.next_tmpdir
  @tmpdir = File.join(ENV['ROO_TMP'], @tmpdir) if ENV['ROO_TMP'] 
  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
  @cells_read = Hash.new
  @filename = filename
  unless File.file?(@filename)
    FileUtils::rm_r(@tmpdir)
    raise IOError, "file #{@filename} does not exist"
  end
  @@nr += 1
  @file_nr = @@nr
  @comments_files = Array.new
  extract_content(@filename)
  file = File.new(File.join(@tmpdir, @file_nr.to_s+"_roo_workbook.xml"))
  @workbook_doc = Nokogiri::XML(file)
  file.close
  @shared_table = []
  if File.exist?(File.join(@tmpdir, @file_nr.to_s+'_roo_sharedStrings.xml'))
    file = File.new(File.join(@tmpdir, @file_nr.to_s+'_roo_sharedStrings.xml'))
    @sharedstring_doc = Nokogiri::XML(file)
    file.close
    read_shared_strings(@sharedstring_doc)
  end
  @styles_table = []
  @style_definitions = Array.new # TODO: ??? { |h,k| h[k] = {} }
  if File.exist?(File.join(@tmpdir, @file_nr.to_s+'_roo_styles.xml'))
    file = File.new(File.join(@tmpdir, @file_nr.to_s+'_roo_styles.xml'))
    @styles_doc = Nokogiri::XML(file)
    file.close
    read_styles(@styles_doc)
  end
  @sheet_doc = []
  @sheet_files.each_with_index do |item, i|
    file = File.new(item)
    @sheet_doc[i] = Nokogiri::XML(file)
    file.close
  end
  @comments_doc = []
  @comments_files.each_with_index do |item, i|
    file = File.new(item)
    @comments_doc[i] = Nokogiri::XML(file)
    file.close
  end
  FileUtils::rm_r(@tmpdir)
  @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
  @header_line = 1
  @excelx_type = Hash.new
  @excelx_value = Hash.new
  @s_attribute = Hash.new # TODO: ggf. wieder entfernen nur lokal benoetigt
  @label = Hash.new
  @labels_read = false
  @comment = Hash.new
  @comments_read = Hash.new
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(m, *args) ⇒ Object 



158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/roo/excelx.rb', line 158

def method_missing(m,*args)
  # is method name a label name
  read_labels unless @labels_read
  if @label.has_key?(m.to_s)
    sheet = @default_sheet unless sheet
    read_cells(sheet) unless @cells_read[sheet]
    row,col = label(m.to_s)
    cell(row,col)
  else
    # call super for methods like #a1
    super
  end
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.



176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/roo/excelx.rb', line 176

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)
  elsif celltype(row,col,sheet) == :datetime
    date_part,time_part = @cell[sheet][[row,col]].split(' ')
    yyyy,mm,dd = date_part.split('-')
    hh,mi,ss = time_part.split(':')
    return DateTime.civil(yyyy.to_i,mm.to_i,dd.to_i,hh.to_i,mi.to_i,ss.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



281
282
283
284
285
286
287
288
289
290
 
# File 'lib/roo/excelx.rb', line 281

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

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

returns the comment at (row/col) nil if there is no comment



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

def comment(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  #read_cells(sheet) unless @cells_read[sheet]
  read_comments(sheet) unless @comments_read[sheet]
  row,col = normalize(row,col)
  return nil unless @comment[sheet]
  @comment[sheet][[row,col]]
end

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

true, if there is a comment

Returns:

  • (Boolean) 


384
385
386
387
388
389
390
 
# File 'lib/roo/excelx.rb', line 384

def comment?(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  # read_cells(sheet) unless @cells_read[sheet]
  read_comments(sheet) unless @comments_read[sheet]
  row,col = normalize(row,col)
  comment(row,col) != nil
end

#comments(sheet = nil) ⇒ Object

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

row, col, comment 


394
395
396
397
398
399
400
401
402
403
404
 
# File 'lib/roo/excelx.rb', line 394

def comments(sheet=nil)
  sheet = @default_sheet unless sheet
  read_comments(sheet) unless @comments_read[sheet]
  if @comment[sheet]
    @comment[sheet].each.collect do |elem|
      [elem[0][0],elem[0][1],elem[1]]
    end
  else
    []
  end
end

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

returns the internal format of an excel cell



313
314
315
316
317
318
319
320
 
# File 'lib/roo/excelx.rb', line 313

def excelx_format(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  s = @s_attribute[sheet][[row,col]]
  result = attribute2format(s).to_s
  result
end

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

returns the internal type of an excel cell

  • :numeric_or_formula

  • :string

Note: this is only available within the Excelx class



296
297
298
299
300
301
 
# File 'lib/roo/excelx.rb', line 296

def excelx_type(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  return @excelx_type[sheet][[row,col]]
end

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

returns the internal value of an excelx cell Note: this is only available within the Excelx class



305
306
307
308
309
310
 
# File 'lib/roo/excelx.rb', line 305

def excelx_value(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  return @excelx_value[sheet][[row,col]]
end

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

Given a cell, return the cell’s style



245
246
247
248
249
250
251
252
253
 
# File 'lib/roo/excelx.rb', line 245

def font(row, col, sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  s_attribute = @s_attribute[sheet][[row,col]]
  s_attribute ||= 0
  s_attribute = s_attribute.to_i
  @style_definitions[s_attribute]
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.



195
196
197
198
199
200
201
202
203
204
 
# File 'lib/roo/excelx.rb', line 195

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]]
  end
end

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

true, if there is a formula

Returns:

  • (Boolean) 


207
208
209
210
211
212
 
# File 'lib/roo/excelx.rb', line 207

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

row, col, formula 


216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/roo/excelx.rb', line 216

def formulas(sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  if @formula[sheet]
    @formula[sheet].each.collect do |elem|
      [elem[0][0], elem[0][1], elem[1]]
    end
  else
    []
  end
end

#label(labelname) ⇒ Object

returns the row,col values of the labelled cell (nil,nil) if label is not defined



341
342
343
344
345
346
347
348
349
350
351
352
353
 
# File 'lib/roo/excelx.rb', line 341

def label(labelname)
  read_labels unless @labels_read
  unless @label.size > 0
    return nil,nil,nil
  end
  if @label.has_key? labelname
    return @label[labelname][1].to_i,
      GenericSpreadsheet.letter_to_number(@label[labelname][2]),
      @label[labelname][0]
  else
    return nil,nil,nil
  end
end

#labels ⇒ Object

Returns an array which all labels. Each element is an array with

labelname, [sheetname,row,col] 


357
358
359
360
361
362
363
364
365
366
367
368
369
370
 
# File 'lib/roo/excelx.rb', line 357

def labels
  # sheet = @default_sheet unless sheet
  # read_cells(sheet) unless @cells_read[sheet]
  read_labels unless @labels_read
  result = []
  @label.each do |label|
    result << [ label[0], # name
      [ label[1][1].to_i, # row
        GenericSpreadsheet.letter_to_number(label[1][2]), # column
        label[1][0], # sheet
      ] ]
  end
  result
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!)



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
 
# File 'lib/roo/excelx.rb', line 257

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

returns an array of sheet names in the spreadsheet



323
324
325
326
327
328
329
 
# File 'lib/roo/excelx.rb', line 323

def sheets
  return_sheets = []
  @workbook_doc.xpath("//*[local-name()='sheet']").each do |sheet|
    return_sheets << sheet['name']
  end
  return_sheets
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



333
334
335
336
337
 
# File 'lib/roo/excelx.rb', line 333

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