Module: OOXML::Helper::List

Included in:
Excel, Excel::Sheet
Defined in:
lib/ooxml_excel/helper/list.rb

Instance Method Summary collapse

Instance Method Details

#list_value_formula(cell_ref) ⇒ Object

Used in sheet.rb



32
33
34
35
36
37
38
39
40
41
 
# File 'lib/ooxml_excel/helper/list.rb', line 32

def list_value_formula(cell_ref)
  data_validation = data_validations.find { |data_validation| data_validation.sqref_range.include?(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.fetch(data_validation.formula)
    end
  end
end

#list_values(formula) ⇒ Object

excel.rb fetch dropdown values based on given data validation formula



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/ooxml_excel/helper/list.rb', line 7

def list_values(formula)
  # "Lists!$J$2:$J$4"

  # transform into useful info


  # for list values explicitly stated

  if formula.include?(',')
    formula.gsub('"', '').split(',')
    # invalid format

  elsif !formula.include?('!') && formula[/$/]
    puts "Warning: This formula is not yet supported: #{formula} in your Data Validation's formula."
    []
  else
    # # required for fetching values

    sheet_name = formula.gsub(/[\$\']/, '').scan(/^[^!]*/).first
    cell_range_formula = formula.gsub(/\$/, '').scan(/(?<=!).+/).first

    # fetch the sheet of the cell reference

    working_sheet = sheet(sheet_name)

    # gather values

    list_values = working_sheet.list_values_from_formula(cell_range_formula)
  end
end

#list_values_from_formula(formula) ⇒ Object 



43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/ooxml_excel/helper/list.rb', line 43

def list_values_from_formula(formula)
  return [] if formula.blank?

  # Formula values separated by comma

  if formula.include?(":")
    cell_letters = formula.gsub(/[\d]/, '').split(':')
    start_index, end_index = formula.gsub(/[^\d:]/, '').split(':').map(&:to_i)

    cell_letter = cell_letters.uniq.first
    (start_index..end_index).to_a.map do |row_index|
      row = rows[row_index-1]
      next if row.blank?
      row["#{cell_letter}#{row_index}"].value
    end
  else
    # when only one value: B2

    row_index = formula.gsub(/[^\d:]/, '').split(':').map(&:to_i).first
    row = rows[row_index-1]
    return if row.blank?
    [row[formula].value]
  end
end