Class: Excelx

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

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,
}
STANDARD_FORMATS = 
{ 
  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_xml, #to_yaml

Constructor Details

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

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



83
84
85
86
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
143
144
145
146
147
 
# File 'lib/roo/excelx.rb', line 83

def initialize(filename, packed=nil, file_warning = :error) #, create = false)
  super()
  @file_warning = file_warning
  @tmpdir = "oo_"+$$.to_s
  @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
  begin
    file_type_check(filename,'.xlsx','an Excel-xlsx')
    @cells_read = Hash.new
    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @@nr += 1
    @file_nr = @@nr
    extract_content(@filename)
    file = File.new(File.join(@tmpdir, @file_nr.to_s+"_roo_workbook.xml"))
    @workbook_doc = REXML::Document.new 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 = REXML::Document.new file
      file.close
      read_shared_strings(@sharedstring_doc)
    end
    @styles_table = []
    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 = REXML::Document.new 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] = REXML::Document.new file
      file.close
    end
  ensure
    #if ENV["roo_local"] != "thomas-p"
    FileUtils::rm_r(@tmpdir)
    #end
  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
  @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
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.



153
154
155
156
157
158
159
160
161
162
 
# File 'lib/roo/excelx.rb', line 153

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



212
213
214
215
216
217
218
219
220
221
 
# File 'lib/roo/excelx.rb', line 212

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

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

returns the internal format of an excel cell



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

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)
  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



227
228
229
230
231
232
 
# File 'lib/roo/excelx.rb', line 227

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



236
237
238
239
240
241
 
# File 'lib/roo/excelx.rb', line 236

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

#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.



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

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) 


179
180
181
182
183
184
 
# File 'lib/roo/excelx.rb', line 179

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 


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

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

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

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



188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
 
# File 'lib/roo/excelx.rb', line 188

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



254
255
256
257
258
259
260
261
262
263
264
265
266
 
# File 'lib/roo/excelx.rb', line 254

def sheets
  return_sheets = []
  @workbook_doc.each_element do |workbook|
    workbook.each_element do |el|
      if el.name == "sheets"
        el.each_element do |sheet|
          return_sheets << sheet.attributes['name']
        end
      end
    end
  end
  return_sheets
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes



270
271
272
273
274
 
# File 'lib/roo/excelx.rb', line 270

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