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



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

def list_value_formula(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.fetch(data_validation.formula)
    end
  end
end

#list_values(formula) ⇒ Object

excel.rb fetch dropdown values based on given data validation formula this will be depracated: use #named_range instead



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

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 



44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/ooxml_excel/helper/list.rb', line 44

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)

    # 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']]
    if cell_letters.uniq.size > 1
      start_index.upto(end_index).map do  |row_index|
        (cell_letters.first..cell_letters.last).map do |cell_letter|
            row = rows[row_index-1]
            next if row.blank?
            row["#{cell_letter}#{row_index}"].value
        end
      end
    else
      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
    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