Class: Roo::Excel

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

Overview

Class for handling Excel-Spreadsheets

Defined Under Namespace

Modules: ExcelFontExtensions

Constant Summary

Constants inherited from GenericSpreadsheet

GenericSpreadsheet::TEMP_PREFIX

Instance Attribute Summary

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line, #headers

Instance Method Summary collapse

Methods inherited from GenericSpreadsheet

#column, #each, #each_with_pagename, #empty?, #find, #first_column, #first_column_as_letter, #first_row, #info, #last_column, #last_column_as_letter, #last_row, #longest_sheet, #method_missing, #parse, #reload, #row, #row_with, #set, #sheet, #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



17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
# File 'lib/roo/excel.rb', line 17

def initialize(filename, packed = nil, file_warning = :error)
  file_type_check(filename,'.xls','an Excel', file_warning, packed)
  make_tmpdir do |tmpdir|
    filename = open_from_uri(filename, tmpdir) if uri?(filename)
    filename = open_from_stream(filename[7..-1], tmpdir) if filename[0,7] == "stream:"
    filename = unzip(filename, tmpdir) if packed == :zip

    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @workbook = Spreadsheet.open(filename)
  end
  @default_sheet = self.sheets.first
  @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 Roo::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)



53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'lib/roo/excel.rb', line 53

def cell(row,col,sheet=nil)
  sheet ||= @default_sheet
  validate_sheet!(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_encoding(@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



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/roo/excel.rb', line 83

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

#encoding=(codepage) ⇒ Object 



43
44
45
 
# File 'lib/roo/excel.rb', line 43

def encoding=(codepage)
  @workbook.encoding = codepage
end

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

Given a cell, return the cell’s font



115
116
117
118
119
120
 
# File 'lib/roo/excel.rb', line 115

def font(row, col, sheet=nil)
  sheet ||= @default_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



100
101
102
 
# File 'lib/roo/excel.rb', line 100

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) 


105
106
107
 
# File 'lib/roo/excel.rb', line 105

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

#formulas(sheet = nil) ⇒ Object

returns NO formulas in excel spreadsheets



110
111
112
 
# File 'lib/roo/excel.rb', line 110

def formulas(sheet=nil)
  wait_for_version_080
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



48
49
50
 
# File 'lib/roo/excel.rb', line 48

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



124
125
126
127
128
 
# File 'lib/roo/excel.rb', line 124

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