Class: Excel

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

Overview

Class for handling Excel-Spreadsheets

Constant Summary collapse

EXCEL_NO_FORMULAS = 
'Formulas are not supported for excel spreadsheets.'

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, #method_missing, #reload, #remove_tmp, #row, #to_csv, #to_matrix, #to_xml, #to_yaml

Constructor Details

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

Creates a new Excel spreadsheet object. Parameter packed: :zip - File is a zip-file



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

def initialize(filename, packed = nil, file_warning = :error)
  super()
  @file_warning = file_warning
  file_type_check(filename,'.xls','an Excel',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 = open_from_stream(filename[7..-1]) if filename[0,7] == "stream:"
  filename = unzip(filename) if packed and packed == :zip
  @filename = filename
  unless File.file?(@filename)
    FileUtils::rm_r(@tmpdir)
    raise IOError, "file #{@filename} does not exist"
  end
  begin
    @workbook = Spreadsheet.open(filename)
  rescue Ole::Storage::FormatError
    FileUtils::rm_r(@tmpdir)
    raise # nach aussen weiterhin sichtbar
  end
  @default_sheet = self.sheets.first
  FileUtils::rm_r(@tmpdir)
  @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
  @cells_read = Hash.new
  @fonts = Hash.new
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method in the class GenericSpreadsheet

Instance Method Details

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

returns the content of a cell. The upper left corner is (1,1) or (‘A’,1)

Raises:

  • (ArgumentError) 


143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'lib/roo/excel.rb', line 143

def cell(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  raise ArgumentError unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  raise "should be read" 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
  if celltype(row,col,sheet) == :string
    return platform_specific_iconv(@cell[sheet][[row,col]])
  else
    if @cell[sheet] and @cell[sheet][[row,col]]
      return @cell[sheet][[row,col]]
    else
      return nil
    end
  end
end

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

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



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

def celltype(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  begin
    if @formula[sheet] and @formula[sheet][[row,col]]
      return :formula
    else
      if @cell_type[sheet] and @cell_type[sheet][[row,col]]
        return @cell_type[sheet][[row,col]]
      else
        return nil
      end
    end
  rescue
    puts "Error in sheet #{sheet}, row #{row}, col #{col}"
    raise
  end
end

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

Given a cell, return the cell’s font



208
209
210
211
212
213
 
# File 'lib/roo/excel.rb', line 208

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

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

returns NO formula in excel spreadsheets



193
194
195
 
# File 'lib/roo/excel.rb', line 193

def formula(row,col,sheet=nil)
  wait_for_version_080
end

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

raises an exception because formulas are not supported for excel files

Returns:

  • (Boolean) 


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

def formula?(row,col,sheet=nil)
  wait_for_version_080
end

#formulas(sheet = nil) ⇒ Object

returns NO formulas in excel spreadsheets



203
204
205
 
# File 'lib/roo/excel.rb', line 203

def formulas(sheet=nil)
  wait_for_version_080
end

#label(labelname, sheet = nil) ⇒ Object

returns the row,col values of the labelled cell (nil,nil) if label is not defined sheet parameter is not really needed because label names are global to the whole spreadsheet



227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/roo/excel.rb', line 227

def label(labelname,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  if @labels.has_key? labelname
    return @labels[labelname][1].to_i,
      GenericSpreadsheet.letter_to_number(@labels[labelname][2]),
      @labels[labelname][0]
  else
    return nil,nil,nil
  end
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



138
139
140
 
# File 'lib/roo/excel.rb', line 138

def sheets
  @workbook.worksheets.collect {|worksheet| normalize_string(worksheet.name)}
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



217
218
219
220
221
 
# File 'lib/roo/excel.rb', line 217

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