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_as_letter, #info, #last_column_as_letter, #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



40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
# File 'lib/roo/excel.rb', line 40

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


90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
# File 'lib/roo/excel.rb', line 90

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



115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/roo/excel.rb', line 115

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

#first_column(sheet = nil) ⇒ Object

returns the first non empty column



132
133
134
135
136
137
 
# File 'lib/roo/excel.rb', line 132

def first_column(sheet=nil)
  sheet = @default_sheet unless sheet
  return @first_column[sheet] if @first_column[sheet]
  fr, lr, fc, lc = get_firsts_lasts(sheet)
  fc
end

#first_row(sheet = nil) ⇒ Object

returns the first non empty row



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

def first_row(sheet=nil)
  sheet = @default_sheet unless sheet
  return @first_row[sheet] if @first_row[sheet]
  fr, lr, fc, lc = get_firsts_lasts(sheet)
  fr
end

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

Given a cell, return the cell’s font



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

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:



164
165
166
 
# File 'lib/roo/excel.rb', line 164

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:



169
170
171
 
# File 'lib/roo/excel.rb', line 169

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

#formulas(sheet = nil) ⇒ Object

returns NO formulas in excel spreadsheets

Raises:



174
175
176
 
# File 'lib/roo/excel.rb', line 174

def formulas(sheet=nil)
  raise EXCEL_NO_FORMULAS
end

#last_column(sheet = nil) ⇒ Object

returns the last non empty column



140
141
142
143
144
145
 
# File 'lib/roo/excel.rb', line 140

def last_column(sheet=nil)
  sheet = @default_sheet unless sheet
  return @last_column[sheet] if @last_column[sheet]
  fr, lr, fc, lc = get_firsts_lasts(sheet)
  lc
end

#last_row(sheet = nil) ⇒ Object

returns the last non empty row



156
157
158
159
160
161
 
# File 'lib/roo/excel.rb', line 156

def last_row(sheet=nil)
  sheet = @default_sheet unless sheet
  return @last_row[sheet] if @last_row[sheet]
  fr, lr, fc, lc = get_firsts_lasts(sheet)
  lr
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



81
82
83
84
85
86
87
 
# File 'lib/roo/excel.rb', line 81

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



188
189
190
191
192
 
# File 'lib/roo/excel.rb', line 188

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