Class: Roo::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 => '@',
}

Constants inherited from GenericSpreadsheet

GenericSpreadsheet::TEMP_PREFIX

Instance Attribute Summary

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line, #headers

Instance Method Summary collapse

Methods inherited from GenericSpreadsheet

#column, #each, #each_with_pagename, #empty?, #find, #first_column, #first_column_as_letter, #first_row, #info, #last_column, #last_column_as_letter, #last_row, #longest_sheet, #parse, #reload, #row, #row_with, #set, #sheet, #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



87
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
 
# File 'lib/roo/excelx.rb', line 87

def initialize(filename, packed=nil, file_warning = :error) #, create = false)
  file_type_check(filename,'.xlsx','an Excel-xlsx', file_warning, packed)
  make_tmpdir do |tmpdir|
    filename = open_from_uri(filename, tmpdir) if uri?(filename)
    filename = unzip(filename, tmpdir) if packed == :zip
    @cells_read = Hash.new
    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @comments_files = Array.new
    extract_content(tmpdir, @filename)
    @workbook_doc = File.open(File.join(tmpdir, "roo_workbook.xml")) do |file|
      Nokogiri::XML(file)
    end
    @shared_table = []
    if File.exist?(File.join(tmpdir, 'roo_sharedStrings.xml'))
      @sharedstring_doc = File.open(File.join(tmpdir, 'roo_sharedStrings.xml')) do |file|
        Nokogiri::XML(file)
      end
      read_shared_strings(@sharedstring_doc)
    end
    @styles_table = []
    @style_definitions = Array.new # TODO: ??? { |h,k| h[k] = {} }
    if File.exist?(File.join(tmpdir, 'roo_styles.xml'))
      @styles_doc = File.open(File.join(tmpdir, 'roo_styles.xml')) do |file|
        Nokogiri::XML(file)
      end
      read_styles(@styles_doc)
    end
    @sheet_doc = @sheet_files.map do |item|
      File.open(item) do |file|
        Nokogiri::XML(file)
      end
    end
    @comments_doc = @comments_files.map do |item|
      File.open(item) do |file|
        Nokogiri::XML(file)
      end
    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
  @header_line = 1
  @excelx_type = Hash.new
  @excelx_value = Hash.new
  @s_attribute = Hash.new # TODO: ggf. wieder entfernen nur lokal benoetigt
  @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 



144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/roo/excelx.rb', line 144

def method_missing(m,*args)
  # is method name a label name
  read_labels
  if @label.has_key?(m.to_s)
    sheet ||= @default_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.



162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
# File 'lib/roo/excelx.rb', line 162

def cell(row, col, sheet=nil)
  sheet ||= @default_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



249
250
251
252
253
254
255
256
257
258
 
# File 'lib/roo/excelx.rb', line 249

def celltype(row,col,sheet=nil)
  sheet ||= @default_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



334
335
336
337
338
339
340
341
 
# File 'lib/roo/excelx.rb', line 334

def comment(row,col,sheet=nil)
  sheet ||= @default_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) 


344
345
346
347
348
349
350
 
# File 'lib/roo/excelx.rb', line 344

def comment?(row,col,sheet=nil)
  sheet ||= @default_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 


354
355
356
357
358
359
360
361
362
363
364
 
# File 'lib/roo/excelx.rb', line 354

def comments(sheet=nil)
  sheet ||= @default_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



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

def excelx_format(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  s = @s_attribute[sheet][[row,col]]
  attribute2format(s).to_s
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



264
265
266
267
268
269
 
# File 'lib/roo/excelx.rb', line 264

def excelx_type(row,col,sheet=nil)
  sheet ||= @default_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



273
274
275
276
277
278
 
# File 'lib/roo/excelx.rb', line 273

def excelx_value(row,col,sheet=nil)
  sheet ||= @default_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



231
232
233
234
235
236
237
238
239
 
# File 'lib/roo/excelx.rb', line 231

def font(row, col, sheet=nil)
  sheet ||= @default_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.



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

def formula(row,col,sheet=nil)
  sheet ||= @default_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) 


193
194
195
196
197
198
 
# File 'lib/roo/excelx.rb', line 193

def formula?(row,col,sheet=nil)
  sheet ||= @default_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 


202
203
204
205
206
207
208
209
210
211
212
 
# File 'lib/roo/excelx.rb', line 202

def formulas(sheet=nil)
  sheet ||= @default_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



306
307
308
309
310
311
312
313
314
315
 
# File 'lib/roo/excelx.rb', line 306

def label(labelname)
  read_labels
  if @label.empty? || !@label.has_key?(labelname)
    return nil,nil,nil
  else
    return @label[labelname][1].to_i,
      Roo::GenericSpreadsheet.letter_to_number(@label[labelname][2]),
      @label[labelname][0]
  end
end

#labels ⇒ Object

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

labelname, [row,col,sheetname] 


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

def labels
  # sheet ||= @default_sheet
  # read_cells(sheet) unless @cells_read[sheet]
  read_labels
  @label.map do |label|
    [ label[0], # name
      [ label[1][1].to_i, # row
        Roo::GenericSpreadsheet.letter_to_number(label[1][2]), # column
        label[1][0], # sheet
      ] ]
  end
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



290
291
292
293
294
 
# File 'lib/roo/excelx.rb', line 290

def sheets
  @workbook_doc.xpath("//xmlns:sheet").map do |sheet|
    sheet['name']
  end
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



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

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