class AstExpandArrayFormulae

Constants

FUNCTIONS_THAT_ACCEPT_RANGES_FOR_ALL_ARGUMENTS

Public Instance Methods

arithmetic(ast) click to toggle source

Format [:arithmetic, left, operator, right]

# File src/rewrite/ast_expand_array_formulae.rb, line 17
def arithmetic(ast)
  ast.each {|a| map(a) }
  return unless array?(ast[1], ast[3])
  
  ast.replace(
    map_arrays([ast[1],ast[3]]) do |arrayed|
      [:arithmetic,arrayed[0],ast[2],arrayed[1]]
    end
  )
end
comparison(ast) click to toggle source

Format [:comparison, left, operator, right]

# File src/rewrite/ast_expand_array_formulae.rb, line 29
def comparison(ast)
  ast.each {|a| map(a) }
  return unless array?(ast[1], ast[3])
  
  ast.replace(
    map_arrays([ast[1],ast[3]]) do |arrayed|
      [:comparison,arrayed[0],ast[2],arrayed[1]]
    end
  )
end
function(ast) click to toggle source

Format [:function, function_name, arg1, arg2, …]

# File src/rewrite/ast_expand_array_formulae.rb, line 78
def function(ast)
  name = ast[1]
  arguments = ast[2..-1]
  if FUNCTIONS_THAT_ACCEPT_RANGES_FOR_ALL_ARGUMENTS.has_key?(name)
    ast.each { |a| map(a) }
    return # No need to alter anything
  elsif respond_to?("map_#{name.downcase}")
    # These typically have some arguments that accept ranges, but not all
    send("map_#{name.downcase}",ast)
  else
    function_that_does_not_accept_ranges(ast)
  end
end
function_that_does_not_accept_ranges(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 92
def function_that_does_not_accept_ranges(ast)
  return if ast.length == 2
  name = ast[1]
  arguments = ast[2..-1]
  array_map(ast, *Array.new(arguments.length,false))
end
map(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 5
def map(ast)
  return ast unless ast.is_a?(Array)
  operator = ast[0]
  if respond_to?(operator)
    send(operator, ast) 
  else
    ast.each {|a| map(a) }
  end
  ast
end
map_arrays(arrays, &block) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 51
def map_arrays(arrays, &block)
  # Turn them into ruby arrays
  arrays = arrays.map { |a| array_ast_to_ruby_array(a) }

  # Find the largest one
  max_rows = arrays.max { |a,b| a.length <=> b.length }.length
  max_columns = arrays.max { |a,b| a.first.length <=> b.first.length }.first.length
  
  # Convert any single rows into an array of the right size
  arrays = arrays.map { |a| a.length == 1 ? Array.new(max_rows,a.first) : a }
  
  # Convert any single columns into an array of the right size
  arrays = arrays.map { |a| a.first.length == 1 ? Array.new(max_columns,a.flatten(1)).transpose : a }
  
  # Now iterate through
  return [:array, *max_rows.times.map do |row|
    [:row, *max_columns.times.map do |column| 
      block.call(arrays.map do |a|
        (a[row] && a[row][column]) || CachingFormulaParser.map([:error, :"#N/A"])
      end)
    end]
  end]
end
map_countif(*args) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 119
def map_countif(*args)
  array_map args, 'COUNTIF', true, true
end
map_index(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 111
def map_index(ast)
  array_map ast, true, false, false
end
map_match(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 99
def map_match(ast)
  array_map(ast, false, true, false)
end
map_offset(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 103
def map_offset(ast)
  array_map(ast, true, false, false, false, false)
end
map_subtotal(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 107
def map_subtotal(ast)
  array_map ast, false, *Array.new(ast.length-3,true)
end
map_sumif(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 115
def map_sumif(ast)
  array_map ast, true, false, true
end
map_sumifs(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 123
def map_sumifs(ast)
  if ast.length > 5
    array_map ast, true, true, false, *([true,false]*((ast.length-5)/2))
  else
    array_map ast, true, true, false
  end
end
map_vlookup(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 131
def map_vlookup(ast)
  array_map ast, false, true, false, false
end
string_join(ast) click to toggle source

Format [:string_join, stringA, stringB, …]

# File src/rewrite/ast_expand_array_formulae.rb, line 41
def string_join(ast)
  ast.each {|a| map(a) }
  return unless array?(*ast[1..-1])
  ast.replace(
    map_arrays(ast[1..-1]) do |arrayed_strings|
      [:string_join, *arrayed_strings]
    end
  )
end

Private Instance Methods

array?(*args) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 188
def array?(*args)
  args.any? { |a| a.first == :array || function_that_returns_an_array?(a) }
end
array_ast_to_ruby_array(array_ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 201
def array_ast_to_ruby_array(array_ast)
  return function_to_ruby_array(array_ast) if function_that_returns_an_array?(array_ast)
  return [[array_ast]] unless array_ast.first == :array
  array_ast[1..-1].map do |row_ast|
    row_ast[1..-1].map do |cell|
      cell
    end
  end
end
array_map(ast,*ok_to_be_an_array) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 146
def array_map(ast,*ok_to_be_an_array)
  ast.each { |a| map(a) }

  return if no_need_to_array?(ast[2..-1],ok_to_be_an_array)

  # Turn the relevant arguments into ruby arrays and store the dimensions
  # Enumerable#max and Enumerable#min don't return Enumerators, so can't do it using those methods
  max_rows = 1
  max_columns = 1
  args = ast[2..-1].map.with_index do |a,i| 
    unless ok_to_be_an_array[i]
      a = array_ast_to_ruby_array(a)
      r = a.length
      c = a.first.length
      max_rows = r if r > max_rows
      max_columns = c if c > max_columns
    end
    a
  end
      
  # Convert any single rows into an array of the right size
  args = args.map.with_index { |a,i| (!ok_to_be_an_array[i] && a.length == 1) ? Array.new(max_rows,a.first) : a }
  
  # Convert any single columns into an array of the right size
  args = args.map.with_index { |a,i| (!ok_to_be_an_array[i] && a.first.length == 1) ? Array.new(max_columns,a.flatten(1)).transpose : a }
  
  # Now iterate through
  ast.replace( [:array, *max_rows.times.map do |row|
    [:row, *max_columns.times.map do |column| 
      [:function, ast[1], *args.map.with_index do |a,i|
        if ok_to_be_an_array[i]
          a
        elsif a[row]
          a[row][column] || [:error, :"#N/A"]
        else
          [:error, :"#N/A"]
        end
      end]
    end]
  end])
end
function_that_returns_an_array?(ast) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 192
def function_that_returns_an_array?(ast)
  return false unless ast[0] == :function
  return false unless ast[1] == :INDEX
  return false if ast.length < 4
  return false if (ast[3][0] == :number && ast[3][1].to_f != 0.0) && (ast[4][0] == :number && ast[4][1].to_f != 0.0)
  # Might contain a zero or null for the row or column number
  true
end
function_to_ruby_array(ast) click to toggle source

This handles the special case of INDEX which might return an array

# File src/rewrite/ast_expand_array_formulae.rb, line 212
def function_to_ruby_array(ast)
  return [[ast]] unless ast[0] == :function && ast[1] == :INDEX
  return [[ast]] unless ast[2][0] == :array
  rows = ast[2].length - 1
  columns = ast[2][1].length - 1
  array = Array.new(rows) { Array.new(columns) }
  1.upto(rows).each do |row|
    1.upto(columns).each do |column|
      array[row-1][column-1] = [:function, :INDEX, ast, [:number, row], [:number, column]]
    end
  end
  array
end
no_need_to_array?(args, ok_to_be_an_array) click to toggle source
# File src/rewrite/ast_expand_array_formulae.rb, line 137
def no_need_to_array?(args, ok_to_be_an_array)
  ok_to_be_an_array.each_with_index do |array_ok,i|
    next if array_ok
    break unless args[i]
    return false if args[i].first == :array
  end
  true
end