Class: OOXL::Sheet

Inherits:
Object
  • Object
show all
Includes:
Enumerable, Util
Defined in:
lib/ooxl/xl_objects/sheet.rb,
lib/ooxl/xl_objects/sheet/data_validation.rb

Defined Under Namespace

Classes: DataValidation

Constant Summary

Constants included from Util

Util::COLUMN_LETTERS

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Util

#column_letter_to_number, #column_number_to_letter, #letter_equivalent, #letter_index, #node_attribute_value, #node_value_extractor, #to_column_letter, #uniform_reference

Constructor Details

#initialize(xml, shared_strings, options = {}) ⇒ Sheet

Returns a new instance of Sheet.



13
14
15
16
17
18
19
20
21
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 13

def initialize(xml, shared_strings, options={})
  @xml = Nokogiri.XML(xml).remove_namespaces!
  @shared_strings = shared_strings
  @comments = {}
  @defined_names = {}
  @styles = []
  @options = options
  @row_cache = RowCache.new(@xml, @shared_strings, options)
end

Instance Attribute Details

#columns ⇒ Object (readonly)

Returns the value of attribute columns.



9
10
11
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 9

def columns
  @columns
end

#comments ⇒ Object

Returns the value of attribute comments.



10
11
12
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 10

def comments
  @comments
end

#data_validations ⇒ Object (readonly)

Returns the value of attribute data_validations.



9
10
11
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 9

def data_validations
  @data_validations
end

#defined_names ⇒ Object

Returns the value of attribute defined_names.



10
11
12
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 10

def defined_names
  @defined_names
end

#name ⇒ Object

Returns the value of attribute name.



10
11
12
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 10

def name
  @name
end

#shared_strings ⇒ Object (readonly)

Returns the value of attribute shared_strings.



9
10
11
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 9

def shared_strings
  @shared_strings
end

#styles ⇒ Object

Returns the value of attribute styles.



9
10
11
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 9

def styles
  @styles
end

Class Method Details

.load_from_stream(xml_stream, shared_strings) ⇒ Object 



180
181
182
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 180

def self.load_from_stream(xml_stream, shared_strings)
  self.new(xml_stream, shared_strings)
end

Instance Method Details

#cell(cell_id) ⇒ Object 



69
70
71
72
73
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 69

def cell(cell_id)
  column_letter, row_index = cell_id.partition(/\d+/)
  current_row = row(row_index)
  current_row.cell(column_letter) unless current_row.nil?
end

#cell_range(cell_ref) ⇒ Object Also known as: list_value_formula

a shortcut for: formula = data_validation(‘A1’).formula ooxl.named_range(formula)



113
114
115
116
117
118
119
120
121
122
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 113

def cell_range(cell_ref)
  data_validation = data_validations.find { |data_validation| data_validation.in_sqref_range?(cell_ref)}
  if data_validation.respond_to?(:type) && data_validation.type == "list"
    if data_validation.formula[/[\s\$\,\:]/]
      (data_validation.formula[/\$/].present?) ? "#{name}!#{data_validation.formula}" : data_validation.formula
    else
      @defined_names[data_validation.formula]
    end
  end
end

#cells_by_column(column_letter) ⇒ Object

test mode



55
56
57
58
59
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 55

def cells_by_column(column_letter)
  rows.map do |row|
    row.cells.find { |cell| to_column_letter(cell.id) == column_letter}
  end
end

#code_name ⇒ Object 



23
24
25
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 23

def code_name
  @code_name ||= @xml.xpath('//sheetPr').attribute('codeName').try(:value)
end

#column(id) ⇒ Object 



35
36
37
38
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 35

def column(id)
  uniformed_reference = uniform_reference(id)
  columns.find { |column| column.id_range.include?(uniformed_reference)}
end

#comment(cell_ref) ⇒ Object 



27
28
29
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 27

def comment(cell_ref)
  @comments[cell_ref] unless @comments.blank?
end

#data_validation(cell_ref) ⇒ Object 



31
32
33
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 31

def data_validation(cell_ref)
  data_validations.find { |data_validation| data_validation.in_sqref_range?(cell_ref)}
end

#fill(cell_reference) ⇒ Object 



83
84
85
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 83

def fill(cell_reference)
  cell(cell_reference).try(:fill)
end

#font(cell_reference) ⇒ Object 



79
80
81
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 79

def font(cell_reference)
  cell(cell_reference).try(:font)
end

#formula(cell_id) ⇒ Object 



75
76
77
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 75

def formula(cell_id)
  cell(cell_id).try(:formula)
end

#in_merged_cells?(cell_id) ⇒ Boolean

Returns:

  • (Boolean) 


174
175
176
177
178
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 174

def in_merged_cells?(cell_id)
  column_letter, column_index = cell_id.partition(/\d+/)
  range = merged_cells.find { |column_range, index_range| column_range.cover?(column_letter) && index_range.cover?(column_index) }
  range.present?
end

#last_column(row_index = 1) ⇒ Object 



61
62
63
64
65
66
67
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 61

def last_column(row_index=1)
  @last_column ||= {}
  @last_column[row_index] ||= begin
    cells = row(row_index).try(:cells) 
    cells.last.column if cells.present?
  end
end

#list_values_from_cell(cell_ref) ⇒ Object 



167
168
169
170
171
172
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 167

def list_values_from_cell(cell_ref)
  row_index = cell_ref.gsub(/[^\d:]/, '').split(':').map(&:to_i).first
  row = row(row_index)
  return if row.blank?
  [row[cell_ref].value]
end

#list_values_from_cell_range(cell_range) ⇒ Object Also known as: list_values_from_formula 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 125

def list_values_from_cell_range(cell_range)
  return [] if cell_range.blank?

  # cell_range values separated by comma
  if cell_range.include?(":")
    cell_letters = cell_range.gsub(/[\d]/, '').split(':')
    start_index, end_index = cell_range[/[A-Z]{1,}\d+/] ? cell_range.gsub(/[^\d:]/, '').split(':').map(&:to_i) : [1, @row_cache.max_row_index]
    if cell_letters.uniq.size > 1
      list_values_from_rectangle(cell_letters, start_index, end_index)
    else
      list_values_from_column(cell_letters.uniq.first, start_index, end_index)
    end
  else
    # when only one value: B2
    list_values_from_cell(cell_range)
  end
end

#list_values_from_column(column_letter, start_index, end_index) ⇒ Object 



161
162
163
164
165
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 161

def list_values_from_column(column_letter, start_index, end_index)
  @row_cache.row_range(start_index, end_index).map do |row|
    row["#{column_letter}#{row.id}"].value
  end
end

#list_values_from_rectangle(cell_letters, start_index, end_index) ⇒ Object

This will allow values from this pattern ‘SheetName!A1:C3’ The number after the cell letter will be the index 1 => start_index 3 => end_index Expected output would be: [[‘value’, ‘value’, ‘value’], [‘value’, ‘value’, ‘value’], [‘value’, ‘value’, ‘value’]]



150
151
152
153
154
155
156
157
158
159
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 150

def list_values_from_rectangle(cell_letters, start_index, end_index)
  start_col = column_letter_to_number(cell_letters.first)
  end_col = column_letter_to_number(cell_letters.last)
  @row_cache.row_range(start_index, end_index).map do |row|
    (start_col..end_col).map do |col_index|
      col_letter = column_number_to_letter(col_index)
      row["#{col_letter}#{row.id}"].value
    end
  end
end

#stream_row(index) ⇒ Object

DEPRECATED: stream is no longer separate



50
51
52
 
# File 'lib/ooxl/xl_objects/sheet.rb', line 50

def stream_row(index)
  row(index)
end