Class: Excel
- Inherits:
-
GenericSpreadsheet
- Object
- GenericSpreadsheet
- Excel
- 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
Instance Method Summary collapse
-
#cell(row, col, sheet = nil) ⇒ Object
returns the content of a cell.
-
#celltype(row, col, sheet = nil) ⇒ Object
returns the type of a cell: * :float * :string, * :date * :percentage * :formula * :time * :datetime.
-
#font(row, col, sheet = nil) ⇒ Object
Given a cell, return the cell’s font.
-
#formula(row, col, sheet = nil) ⇒ Object
returns NO formula in excel spreadsheets.
-
#formula?(row, col, sheet = nil) ⇒ Boolean
raises an exception because formulas are not supported for excel files.
-
#formulas(sheet = nil) ⇒ Object
returns NO formulas in excel spreadsheets.
-
#initialize(filename, packed = nil, file_warning = :error) ⇒ Excel
constructor
Creates a new Excel spreadsheet object.
-
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet.
-
#to_s(sheet = nil) ⇒ Object
shows the internal representation of all cells mainly for debugging purposes.
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
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
# File 'lib/roo/excel.rb', line 60 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)
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
# File 'lib/roo/excel.rb', line 106 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
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
# File 'lib/roo/excel.rb', line 131 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
163 164 165 166 167 168 |
# File 'lib/roo/excel.rb', line 163 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
148 149 150 |
# File 'lib/roo/excel.rb', line 148 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
153 154 155 |
# File 'lib/roo/excel.rb', line 153 def formula?(row,col,sheet=nil) raise EXCEL_NO_FORMULAS end |
#formulas(sheet = nil) ⇒ Object
returns NO formulas in excel spreadsheets
158 159 160 |
# File 'lib/roo/excel.rb', line 158 def formulas(sheet=nil) raise EXCEL_NO_FORMULAS end |
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet
97 98 99 100 101 102 103 |
# File 'lib/roo/excel.rb', line 97 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
172 173 174 175 176 |
# File 'lib/roo/excel.rb', line 172 def to_s(sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] @cell[sheet].inspect end |