class Roo::Excelx
Constants
- ExceedsMaxError
Public Class Methods
initialization and opening of a spreadsheet file values for packed: :zip optional cell_max (int) parameter for early aborting attempts to parse enormous documents.
Roo::Base::new
# File lib/roo/excelx.rb, line 251 def initialize(filename, options = {}) packed = options[:packed] file_warning = options.fetch(:file_warning, :error) cell_max = options.delete(:cell_max) file_type_check(filename,'.xlsx','an Excel-xlsx', file_warning, packed) @tmpdir = make_tmpdir(filename.split('/').last, options[:tmpdir_root]) @filename = local_filename(filename, @tmpdir, packed) @comments_files = [] @rels_files = [] process_zipfile(@tmpdir, @filename) @sheet_names = workbook.sheets.map { |sheet| sheet['name'] } @sheets = [] @sheets_by_name = Hash[@sheet_names.map.with_index do |sheet_name, n| @sheets[n] = Sheet.new(sheet_name, @rels_files[n], @sheet_files[n], @comments_files[n], styles, shared_strings, workbook) [sheet_name, @sheets[n]] end] if cell_max cell_count = ::Roo::Utils.num_cells_in_range(sheet_for(options.delete(:sheet)).dimensions) raise ExceedsMaxError.new("Excel file exceeds cell maximum: #{cell_count} > #{cell_max}") if cell_count > cell_max end super end
Public Instance Methods
Returns the content of a spreadsheet-cell. (1,1) is the upper left corner. (1,1), (1,‘A’), (‘A’,1), (‘a’,1) all refers to the cell at the first line and first row.
# File lib/roo/excelx.rb, line 302 def cell(row, col, sheet=nil) key = normalize(row,col) cell = sheet_for(sheet).cells[key] cell.value if cell end
returns the type of a cell:
-
:float
-
:string,
-
:date
-
:percentage
-
:formula
-
:time
-
:datetime
# File lib/roo/excelx.rb, line 387 def celltype(row,col,sheet=nil) key = normalize(row, col) sheet_for(sheet).cells[key].type end
returns all values in this column as an array column numbers are 1,2,3,… like in the spreadsheet
# File lib/roo/excelx.rb, line 314 def column(column_number,sheet=nil) if column_number.is_a?(::String) column_number = ::Roo::Utils.letter_to_number(column_number) end sheet_for(sheet).column(column_number) end
returns the comment at (row/col) nil if there is no comment
# File lib/roo/excelx.rb, line 466 def comment(row,col,sheet=nil) key = normalize(row,col) sheet_for(sheet).comments[key] end
true, if there is a comment
# File lib/roo/excelx.rb, line 472 def comment?(row,col,sheet=nil) !!comment(row,col,sheet) end
# File lib/roo/excelx.rb, line 476 def comments(sheet=nil) sheet_for(sheet).comments.map do |(x, y), comment| [x, y, comment] end end
Yield an array of Excelx::Cell
Takes options for sheet, pad_cells, and max_rows
# File lib/roo/excelx.rb, line 484 def each_row_streaming(options={}) sheet_for(options.delete(:sheet)).each_row(options) { |row| yield row } end
# File lib/roo/excelx.rb, line 414 def empty?(row,col,sheet=nil) sheet = sheet_for(sheet) key = normalize(row,col) cell = sheet.cells[key] !cell || !cell.value || (cell.type == :string && cell.value.empty?) \ || (row < sheet.first_row || row > sheet.last_row || col < sheet.first_column || col > sheet.last_column) end
returns the internal format of an excel cell
# File lib/roo/excelx.rb, line 409 def excelx_format(row,col,sheet=nil) key = normalize(row,col) sheet_for(sheet).excelx_format(key) end
returns the internal type of an excel cell
-
:numeric_or_formula
-
:string
Note: this is only available within the Excelx
class
# File lib/roo/excelx.rb, line 396 def excelx_type(row,col,sheet=nil) key = normalize(row,col) sheet_for(sheet).cells[key].excelx_type end
returns the internal value of an excelx cell Note: this is only available within the Excelx
class
# File lib/roo/excelx.rb, line 403 def excelx_value(row,col,sheet=nil) key = normalize(row,col) sheet_for(sheet).cells[key].excelx_value end
returns the number of the first non-empty column
# File lib/roo/excelx.rb, line 332 def first_column(sheet=nil) sheet_for(sheet).first_column end
returns the number of the first non-empty row
# File lib/roo/excelx.rb, line 322 def first_row(sheet=nil) sheet_for(sheet).first_row end
Given a cell, return the cell’s style
# File lib/roo/excelx.rb, line 374 def font(row, col, sheet=nil) key = normalize(row,col) styles.definitions[sheet_for(sheet).cells[key].style] end
Returns the formula at (row,col). Returns nil if there is no formula. The method formula?
checks if there is a formula.
# File lib/roo/excelx.rb, line 353 def formula(row,col,sheet=nil) key = normalize(row,col) sheet_for(sheet).cells[key].formula end
Predicate methods really should return a boolean value. Hopefully no one was relying on the fact that this previously returned either nil/formula
# File lib/roo/excelx.rb, line 361 def formula?(*args) !!formula(*args) end
returns each formula in the selected sheet as an array of tuples in following format
- [row, col, formula], [row, col, formula],…
# File lib/roo/excelx.rb, line 367 def formulas(sheet=nil) sheet_for(sheet).cells.select {|_, cell| cell.formula }.map do |(x, y), cell| [x, y, cell.formula] end end
returns the hyperlink at (row/col) nil if there is no hyperlink
# File lib/roo/excelx.rb, line 459 def hyperlink(row,col,sheet=nil) key = normalize(row,col) sheet_for(sheet).hyperlinks[key] end
# File lib/roo/excelx.rb, line 453 def hyperlink?(row,col,sheet=nil) !!hyperlink(row, col, sheet) end
returns the row,col values of the labelled cell (nil,nil) if label is not defined
# File lib/roo/excelx.rb, line 430 def label(name) labels = workbook.defined_names if labels.empty? || !labels.key?(name) [nil,nil,nil] else [labels[name].row, labels[name].col, labels[name].sheet] end end
Returns an array which all labels. Each element is an array with
- labelname, [row,col,sheetname]
# File lib/roo/excelx.rb, line 443 def labels @labels ||= workbook.defined_names.map do |name, label| [ name, [ label.row, label.col, label.sheet, ] ] end end
returns the number of the last non-empty column
# File lib/roo/excelx.rb, line 337 def last_column(sheet=nil) sheet_for(sheet).last_column end
returns the number of the last non-empty row
# File lib/roo/excelx.rb, line 327 def last_row(sheet=nil) sheet_for(sheet).last_row end
Roo::Base#method_missing
# File lib/roo/excelx.rb, line 279 def method_missing(method,*args) if label = workbook.defined_names[method.to_s] sheet_for(label.sheet).cells[label.key].value else # call super for methods like #a1 super end end
# File lib/roo/excelx.rb, line 308 def row(rownumber,sheet=nil) sheet_for(sheet).row(rownumber) end
# File lib/roo/excelx.rb, line 292 def sheet_for(sheet) sheet ||= default_sheet validate_sheet!(sheet) @sheets_by_name[sheet] end
# File lib/roo/excelx.rb, line 288 def sheets @sheet_names end
shows the internal representation of all cells for debugging purposes
# File lib/roo/excelx.rb, line 424 def to_s(sheet=nil) sheet_for(sheet).cells.inspect end
Private Instance Methods
Extracts all needed files from the zip file
# File lib/roo/excelx.rb, line 491 def process_zipfile(tmpdir, zipfilename) @sheet_files = [] Zip::File.foreach(zipfilename) do |entry| path = case entry.name.downcase when /workbook.xml$/ "#{tmpdir}/roo_workbook.xml" when /sharedstrings.xml$/ "#{tmpdir}/roo_sharedStrings.xml" when /styles.xml$/ "#{tmpdir}/roo_styles.xml" when /sheet.xml$/ path = "#{tmpdir}/roo_sheet" @sheet_files.unshift(path) path when /sheet([0-9]+).xml$/ # Numbers 3.1 exports first sheet without sheet number. Such sheets # are always added to the beginning of the array which, naturally, # causes other sheets to be pushed to the next index which could # lead to sheet references getting overwritten, so we need to # handle that case specifically. nr = $1 sheet_files_index = nr.to_i - 1 sheet_files_index += 1 if @sheet_files[sheet_files_index] @sheet_files[sheet_files_index] = "#{tmpdir}/roo_sheet#{nr.to_i}" when /comments([0-9]+).xml$/ nr = $1 @comments_files[nr.to_i-1] = "#{tmpdir}/roo_comments#{nr}" when /sheet([0-9]+).xml.rels$/ nr = $1 @rels_files[nr.to_i-1] = "#{tmpdir}/roo_rels#{nr}" end if path entry.extract(path) end end end
# File lib/roo/excelx.rb, line 529 def styles @styles ||= Styles.new(File.join(@tmpdir, 'roo_styles.xml')) end
# File lib/roo/excelx.rb, line 537 def workbook @workbook ||= Workbook.new(File.join(@tmpdir, "roo_workbook.xml")) end