Module: OOXL::ListHelper

Included in:
OOXL
Defined in:
lib/ooxl/list_helper.rb

Instance Method Summary collapse

Instance Method Details

#list_values(formula) ⇒ Object

fetch dropdown values based on given data validation formula



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
# File 'lib/ooxl/list_helper.rb', line 4

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

  # for list values explicitly stated
  if formula.include?(',')
    formula.gsub('"', '').split(',')
  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.gsub(/\$/, '').scan(/(?<=!).+/).first

    # fetch the sheet of the cell reference
    working_sheet = sheet(sheet_name)

    # gather values
    list_values = working_sheet.list_values_from_cell_range(cell_range)
  end
end