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.
-
#label(labelname, sheet = nil) ⇒ Object
(nil,nil) if label is not defined sheet parameter is not really needed because label names are global to the whole spreadsheet.
-
#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, #method_missing, #reload, #remove_tmp, #row, #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
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 |
# File 'lib/roo/excel.rb', line 100 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 |
Dynamic Method Handling
This class handles dynamic methods through the method_missing method in the class 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)
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 |
# File 'lib/roo/excel.rb', line 146 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
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
# File 'lib/roo/excel.rb', line 171 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
203 204 205 206 207 208 |
# File 'lib/roo/excel.rb', line 203 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
188 189 190 |
# File 'lib/roo/excel.rb', line 188 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
193 194 195 |
# File 'lib/roo/excel.rb', line 193 def formula?(row,col,sheet=nil) raise EXCEL_NO_FORMULAS end |
#formulas(sheet = nil) ⇒ Object
returns NO formulas in excel spreadsheets
198 199 200 |
# File 'lib/roo/excel.rb', line 198 def formulas(sheet=nil) raise EXCEL_NO_FORMULAS end |
#label(labelname, sheet = nil) ⇒ Object
(nil,nil) if label is not defined sheet parameter is not really needed because label names are global to the whole spreadsheet
222 223 224 225 226 227 228 229 230 231 232 |
# File 'lib/roo/excel.rb', line 222 def label(labelname,sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] if @labels.has_key? labelname return @labels[labelname][1].to_i, GenericSpreadsheet.letter_to_number(@labels[labelname][2]), @labels[labelname][0] else return nil,nil,nil end end |
#sheets ⇒ Object
returns an array of sheet names in the spreadsheet
137 138 139 140 141 142 143 |
# File 'lib/roo/excel.rb', line 137 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
212 213 214 215 216 |
# File 'lib/roo/excel.rb', line 212 def to_s(sheet=nil) sheet = @default_sheet unless sheet read_cells(sheet) unless @cells_read[sheet] @cell[sheet].inspect end |