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, #reload, #remove_tmp, #row, #to_csv, #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



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

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

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) 


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

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
    return @cell[sheet][[row,col]]
  end
end

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

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/roo/excel.rb', line 183

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][[row,col]]
      return :formula
    else
      @cell_type[sheet][[row,col]]
    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



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

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

Raises:



200
201
202
 
# File 'lib/roo/excel.rb', line 200

def formula(row,col,sheet=nil)
  raise EXCEL_NO_FORMULAS
end

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

raises an exception because formulas are not supported for excel files

Returns:

  • (Boolean)

Raises:



205
206
207
 
# File 'lib/roo/excel.rb', line 205

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

#formulas(sheet = nil) ⇒ Object

returns NO formulas in excel spreadsheets

Raises:



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

def formulas(sheet=nil)
  raise EXCEL_NO_FORMULAS
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



149
150
151
152
153
154
155
 
# File 'lib/roo/excel.rb', line 149

def sheets
  result = []
  @workbook.worksheets.each do |worksheet| 
    result << normalize_string(worksheet.name)
  end
  return result
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



224
225
226
227
228
 
# File 'lib/roo/excel.rb', line 224

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