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



23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/roo/excel.rb', line 23

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 = 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::ParseExcel.parse(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
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)



82
83
84
85
86
87
88
89
90
91
 
# File 'lib/roo/excel.rb', line 82

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

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

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time



100
101
102
103
104
105
106
107
108
109
 
# File 'lib/roo/excel.rb', line 100

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

#first_column(sheet = nil) ⇒ Object

returns the first non empty column



112
113
114
115
116
117
 
# File 'lib/roo/excel.rb', line 112

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



128
129
130
131
132
133
 
# File 'lib/roo/excel.rb', line 128

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

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

returns NO formula in excel spreadsheets

Raises:



144
145
146
 
# File 'lib/roo/excel.rb', line 144

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:



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

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

#formulas(sheet = nil) ⇒ Object

returns NO formulas in excel spreadsheets

Raises:



154
155
156
 
# File 'lib/roo/excel.rb', line 154

def formulas(sheet=nil)
  raise EXCEL_NO_FORMULAS
end

#last_column(sheet = nil) ⇒ Object

returns the last non empty column



120
121
122
123
124
125
 
# File 'lib/roo/excel.rb', line 120

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



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

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



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/roo/excel.rb', line 62

def sheets
  result = []
  0.upto(@workbook.sheet_count - 1) do |i|
    # TODO: is there a better way to do conversion?
    if CHARGUESS
      encoding = CharGuess::guess(@workbook.worksheet(i).name)
      encoding = 'unicode' unless encoding


      result << Iconv.new('utf-8',encoding).iconv(
        @workbook.worksheet(i).name
      )
    else
      result << platform_specific_iconv(@workbook.worksheet(i).name)
    end
  end
  return result
end