Class: Roo::Excel

Inherits:
Base
  • Object
show all
Defined in:
lib/roo/xls/excel.rb

Overview

Class for handling Excel-Spreadsheets

Defined Under Namespace

Modules: ExcelFontExtensions

Constant Summary collapse

FORMULAS_MESSAGE = 
'the spreadsheet gem does not support formulas, so roo can not.'
CHARGUESS = 
begin
  require 'charguess'
  true
rescue LoadError
  false
end

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(filename, options = {}) ⇒ Excel

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



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

def initialize(filename, options = {})
  packed = options[:packed]
  file_warning = options[:file_warning] || :error
  mode = options[:mode] || 'rb+'

  if is_stream?(filename)
    @workbook = ::Spreadsheet.open(filename, mode)
  else
    file_type_check(filename, '.xls', 'an Excel', file_warning, packed)
    Dir.mktmpdir do |tmpdir|
      filename = download_uri(filename, tmpdir) if uri?(filename)
      if filename.is_a?(::String) && filename[0, 7] == 'stream:'
        filename = open_from_stream(filename[7..-1], tmpdir)
      end
      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, mode)
    end
  end

  super(filename, options)
  @formula = {}
  @fonts = {}
end

Instance Attribute Details

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook.



18
19
20
 
# File 'lib/roo/xls/excel.rb', line 18

def workbook
  @workbook
end

Instance Method Details

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

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



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'lib/roo/xls/excel.rb', line 74

def cell(row, col, sheet = default_sheet)
  validate_sheet!(sheet)

  read_cells(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
    platform_specific_encoding(@cell[sheet][[row, col]])
  else
    @cell[sheet] && @cell[sheet][[row, col]]
  end
end

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

returns the type of a cell:

  • :float

  • :string,

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



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

def celltype(row, col, sheet = default_sheet)
  read_cells(sheet)
  row, col = normalize(row, col)
  begin
    if @formula[sheet] && @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 



57
58
59
 
# File 'lib/roo/xls/excel.rb', line 57

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

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

Given a cell, return the cell’s font



126
127
128
129
130
 
# File 'lib/roo/xls/excel.rb', line 126

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

#formula(_row, _col, _sheet = nil) ⇒ Object Also known as: formula?

returns NO formula in excel spreadsheets

Raises:

  • (NotImplementedError) 


115
116
117
 
# File 'lib/roo/xls/excel.rb', line 115

def formula(_row, _col, _sheet = nil)
  raise NotImplementedError, FORMULAS_MESSAGE
end

#formulas(_sheet = nil) ⇒ Object

returns NO formulas in excel spreadsheets

Raises:

  • (NotImplementedError) 


121
122
123
 
# File 'lib/roo/xls/excel.rb', line 121

def formulas(_sheet = nil)
  raise NotImplementedError, FORMULAS_MESSAGE
end

#longest_sheet ⇒ Object

this method lets you find the worksheet with the most data



67
68
69
70
71
 
# File 'lib/roo/xls/excel.rb', line 67

def longest_sheet
  sheet(worksheets.inject do |m, o|
    o.row_count > m.row_count ? o : m
  end.name)
end

#sheets ⇒ Object

returns an array of sheet names in the spreadsheet



62
63
64
 
# File 'lib/roo/xls/excel.rb', line 62

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

#to_s(sheet = default_sheet) ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



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

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

#worksheets ⇒ Object 



53
54
55
 
# File 'lib/roo/xls/excel.rb', line 53

def worksheets
  @worksheets ||= workbook.worksheets
end