class MapFormulaeToValues

Constants

DO_NOT_MAP
ERRORS
OK_CHECK_RANGE_TYPES

Attributes

original_excel_filename[RW]
replacements_made_in_the_last_pass[RW]

Public Class Methods

new() click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 15
def initialize
  @value_for_ast = MapValuesToRuby.new
  @calculator = FormulaeCalculator.new
  @replacements_made_in_the_last_pass = 0
end

Public Instance Methods

arithmetic(ast) click to toggle source
:arithmetic, left, operator, right
# File src/simplify/map_formulae_to_values.rb, line 53
def arithmetic(ast)
  left, operator, right = ast[1], ast[2], ast[3]
  l = @calculator.number_argument(value(left))
  r = @calculator.number_argument(value(right))
  if (l == :not_a_value) && (r == :not_a_value)
    return ast
  elsif (l != :not_a_value) && (r != :not_a_value)
    ast.replace(formula_value(operator.last,l,r))
  # SPECIAL CASES
  elsif l == 0
    case operator.last
    when :+ 
      ast.replace(n(right))
    when :*
      ast.replace([:number, 0])
    end
  elsif r == 0
    case operator.last
    when :+, :-
      ast.replace(n(left))
    when :* 
      ast.replace([:number, 0])
    when :/
      ast.replace([:error, :'#DIV/0!'])
    when :^
      ast.replace([:number, 1])
    end
  elsif l == 1
    case operator.last
    when :*
      ast.replace(n(right))
    when :^
      ast.replace([:number, 1])
    end
  elsif r == 1
    case operator.last
    when :*, :/, :^
      ast.replace(n(left))
    end
  end
  ast
end
array_as_values(array_mapped) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 408
def array_as_values(array_mapped)
  case array_mapped.first
  when :array
    array_mapped[1..-1].map do |row|
      row[1..-1].map do |cell|
        cell
      end
    end 
  when :cell, :sheet_reference, :blank, :number, :percentage, :string, :error, :boolean_true, :boolean_false
    [[array_mapped]]
  else
    nil
  end
end
ast_for_array(array) click to toggle source

FIXME: Assumes single column. Not wise?

# File src/simplify/map_formulae_to_values.rb, line 424
def ast_for_array(array)
  [:array,*array.map { |row| [:row, row ]}]
end
ast_for_value(value) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 468
def ast_for_value(value)
  return value if value.is_a?(Array) && value.first.is_a?(Symbol)
  @replacements_made_in_the_last_pass += 1
  ast = case value
  when Numeric; [:number,value]
  when true; [:boolean_true]
  when false; [:boolean_false]
  when Symbol; 
    raise NotSupportedException.new("Error #{value.inspect} not recognised") unless MapFormulaeToRuby::REVERSE_ERRORS[value.inspect]
    [:error,MapFormulaeToRuby::REVERSE_ERRORS[value.inspect]]
  when String; [:string,value]
  when Array; [:array,*value.map { |row| [:row, *row.map { |c| ast_for_value(c) }]}]
  when nil; [:blank]
  else
    raise NotSupportedException.new("Ast for #{value.inspect} of class #{value.class} not recognised")
  end
  CachingFormulaParser.map(ast)
end
attempt_to_reduce_sumifs(ast) click to toggle source

FIXME: Ends up making everything single column. Is that ok?!

# File src/simplify/map_formulae_to_values.rb, line 240
def attempt_to_reduce_sumifs(ast)
  return unless OK_CHECK_RANGE_TYPES.include?(ast[2].first)
  # First combine into a series of checks
  criteria_that_can_be_resolved = []
  criteria_that_cant_be_resolved = []
  ast[3..-1].each_slice(2) do |check|
    # Give up unless we have something that can actually be used
    return unless OK_CHECK_RANGE_TYPES.include?(check[0].first)
    check_range_value = value(check[0])
    check_criteria_value = value(check[1])
    if check_range_value == :not_a_value || check_criteria_value == :not_a_value
      criteria_that_cant_be_resolved << check
    else
      criteria_that_can_be_resolved << [check_range_value, check_criteria_value]
    end
  end
  return if criteria_that_can_be_resolved.empty?
  sum_range = array_as_values(ast[2]).flatten(1)
  indexes = @calculator._filtered_range_indexes(sum_range, *criteria_that_can_be_resolved.flatten(1))
  if indexes.is_a?(Symbol)
    return
  elsif indexes.empty?
    new_ast = [:number, 0]
  else
    new_ast = [:function, :SUMIFS]
    new_ast << ast_for_array(sum_range.values_at(*indexes))
    criteria_that_cant_be_resolved.each do |check|
      new_ast << ast_for_array(array_as_values(check.first).flatten(1).values_at(*indexes))
      new_ast << check.last
    end
  end
  if new_ast != ast
    @replacements_made_in_the_last_pass += 1
    ast.replace(new_ast)
  end
end
comparison(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 106
def comparison(ast)
  left, operator, right = ast[1], ast[2], ast[3]
  l = value(left)
  r = value(right)
  return ast if (l == :not_a_value) || (r == :not_a_value)
  ast.replace(formula_value(operator.last,l,r))
end
extract_values_from_array(ast, inlined_blank = 0) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 453
def extract_values_from_array(ast, inlined_blank = 0)
  ast[1..-1].map do |row|
    row[1..-1].map do |cell|
      v = value(cell, inlined_blank)
      return :not_a_value if v == :not_a_value
      v
    end
  end 
end
filter_numbers_and_not(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 366
def filter_numbers_and_not(ast)
  number_total = 0
  not_number_array = []
  case ast.first
  when :array
    array_total = 0
    array_not_numbers = []
    # First we just have a go at splitting the array into a list of numbers
    # and not numbers.
    array_as_values(ast).each do |row|
      row.each do |c|
        result = filter_numbers_and_not(c)
        array_total += result.first
        array_not_numbers.concat(result.last)
      end
    end
    # If there are no not_numbers, or only one, we are good
    if array_not_numbers.length <= 1
      number_total += array_total
      not_number_array.concat(array_not_numbers)
    # If there are more than on not_numbers we aren't neccessarily good
    # unless all those not numbers are simple
    elsif array_not_numbers.all? { |c| [:cell, :area, :sheet_reference].include?(c.first)}
      number_total += array_total
      not_number_array.concat(array_not_numbers)
    # Otherwise, leave that array alone
    else
      not_number_array.push(ast)
    end
  when :blank, :number, :percentage, :string, :boolean_true, :boolean_false
    number = @calculator.number_argument(value(ast))
    if number.is_a?(Symbol)
      not_number_array.push(ast)
    else
      number_total += number
    end
  else
    not_number_array.push(ast)
  end
  [number_total, not_number_array]
end
formula_value(ast_name,*arguments) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 463
def formula_value(ast_name,*arguments)
  raise NotSupportedException.new("#{ast_name} function not recognised in #{MapFormulaeToRuby::FUNCTIONS.inspect}") unless MapFormulaeToRuby::FUNCTIONS.has_key?(ast_name)
  ast_for_value(@calculator.send(MapFormulaeToRuby::FUNCTIONS[ast_name],*arguments))
end
function(ast) click to toggle source
:function, function_name, arg1, arg2, …
# File src/simplify/map_formulae_to_values.rb, line 129
def function(ast)
  name = ast[1]
  return if name == :INDIRECT
  return if name == :OFFSET
  return if name == :COLUMN
  return if name == :ROW
  if respond_to?("map_#{name.to_s.downcase}")
    send("map_#{name.to_s.downcase}",ast)
  else
    normal_function(ast)
  end
end
map(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 33
def map(ast)
  ast[1..-1].each do |a| 
    next unless a.is_a?(Array)
    next if DO_NOT_MAP[(a[0])]
    map(a)
  end # Depth first best in this case?
  send(ast[0], ast) if respond_to?(ast[0])
  ast
end
map_count(ast) click to toggle source
:function, “COUNT”, range
# File src/simplify/map_formulae_to_values.rb, line 279
def map_count(ast)
  values = ast[2..-1].map { |a| value(a, nil) }
  return if values.any? { |a| a == :not_a_value }
  ast.replace(formula_value( ast[1],*values))
end
map_find(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 191
def map_find(ast)
  normal_function(ast, "")
end
map_hlookup(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 203
def map_hlookup(ast)
  normal_function(ast, nil)
end
map_if(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 154
def map_if(ast)
  condition_ast = ast[2]
  true_option_ast = ast[3]
  false_option_ast = ast[4] || [:boolean_false]

  condition_value = value(condition_ast)
  return if condition_value == :not_a_value

  case condition_value
  when Symbol
    ast.replace(condition_ast)
  when String
    ast.replace([:error, :"#VALUE!"])
  when false, 0
    ast.replace(false_option_ast)
  else
    ast.replace(true_option_ast)
  end
  ast
end
map_index(ast) click to toggle source
:function, “INDEX”, array, row_number, column_number
# File src/simplify/map_formulae_to_values.rb, line 286
def map_index(ast)
  return map_index_with_only_two_arguments(ast) if ast.length == 4

  array_mapped = ast[2] 
  return ast.replace(ast[2]) if ast[2].first == :error
  row_as_number = value(ast[3])
  column_as_number = value(ast[4])

  return if row_as_number == :not_a_value 
  return if column_as_number == :not_a_value

  array_as_values = array_as_values(array_mapped)
  return unless array_as_values

  result = @calculator.send(MapFormulaeToRuby::FUNCTIONS[:INDEX],array_as_values,row_as_number,column_as_number)
  result = [:number, 0] if result == [:blank]
  result = ast_for_value(result)
  ast.replace(result)
end
map_index_with_only_two_arguments(ast) click to toggle source
:function, “INDEX”, array, row_number
# File src/simplify/map_formulae_to_values.rb, line 307
def map_index_with_only_two_arguments(ast)
  array_mapped = ast[2]
  return ast.replace(ast[2]) if ast[2].first == :error
  row_as_number = value(ast[3])
  return if row_as_number == :not_a_value
  array_as_values = array_as_values(array_mapped)
  return unless array_as_values
  result = @calculator.send(MapFormulaeToRuby::FUNCTIONS[:INDEX],array_as_values,row_as_number)
  result = [:number, 0] if result == [:blank]
  result = ast_for_value(result)
  ast.replace(result)
end
map_isblank(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 195
def map_isblank(ast)
  normal_function(ast,nil)
end
map_left(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 179
def map_left(ast)
  normal_function(ast, "")
end
map_len(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 187
def map_len(ast)
  normal_function(ast, "")
end
map_mid(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 183
def map_mid(ast)
  normal_function(ast, "")
end
map_right(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 175
def map_right(ast)
  normal_function(ast, "")
end
map_sum(ast) click to toggle source
:function, :SUM, a, b, c…
# File src/simplify/map_formulae_to_values.rb, line 321
def map_sum(ast)
    values = ast[2..-1].map { |a| value(a) }
    return partially_map_sum(ast) if values.any? { |a| a == :not_a_value }
    ast.replace(formula_value(:SUM,*values))
end
map_sumifs(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 209
def map_sumifs(ast)
  values = ast[3..-1].map.with_index { |a,i| value(a, (i % 2) == 0 ? 0 : nil ) }
  return if values.all? { |a| a == :not_a_value } # Nothing to be done
  return attempt_to_reduce_sumifs(ast) if values.any? { |a| a == :not_a_value } # Maybe a reduction to be done
  return unless OK_CHECK_RANGE_TYPES.include?(ast[2].first)
  sum_value = value(ast[2])
  if sum_value == :not_a_value # i.e., a sheet_reference, :cell or :area
    partially_map_sumifs(ast)
  else
    ast.replace(formula_value( ast[1], sum_value, *values))
  end
end
map_text(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 148
def map_text(ast)
  values = ast[2..-1].map { |a| value(a, nil) }
  return if values.any? { |a| a == :not_a_value }
  ast.replace(formula_value( ast[1],*values))
end
map_vlookup(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 199
def map_vlookup(ast)
  normal_function(ast,nil)
end
n(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 96
def n(ast)
  return ast if ast[0] == :function && ast[1] == :ENSURE_IS_NUMBER
  [:function, :ENSURE_IS_NUMBER, ast]
end
normal_function(ast, inlined_blank = 0) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 142
def normal_function(ast, inlined_blank = 0)
  values = ast[2..-1].map { |a| value(a, inlined_blank) }
  return if values.any? { |a| a == :not_a_value }
  ast.replace(formula_value( ast[1],*values))
end
number_or_zero(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 101
def number_or_zero(ast)
  return ast if ast[0] == :function && ast[1] == :NUMBER_OR_ZERO
  [:function, :NUMBER_OR_ZERO, ast]
end
original_excel_filename=(new_filename) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 21
def original_excel_filename=(new_filename)
  @original_excel_filename = new_filename
  @calculator.original_excel_filename = new_filename
end
partially_map_sum(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 327
def partially_map_sum(ast)
  number_total = 0
  not_number_array = []
  ast[2..-1].each do |a|
    result = filter_numbers_and_not(a)
    number_total += result.first
    not_number_array.concat(result.last)
  end
  if number_total == 0 && not_number_array.empty?
    new_ast = [:number, number_total]
    if new_ast != ast
      @replacements_made_in_the_last_pass += 1
      ast.replace(new_ast)
    end
  # FIXME: Will I be haunted by this? What if doing a sum of something that isn't a number
  # and so what is expected is a VALUE error?. YES. This doesn't work well.
  elsif ast.length == 3 && [:cell, :sheet_reference].include?(ast[2].first)
    new_ast = number_or_zero(ast[2])
    if new_ast != ast
      @replacements_made_in_the_last_pass += 1
      ast.replace(new_ast)
    end
  elsif ast.length == 3 && ast[2][0] == :function && ast[2][1] == :NUMBER_OR_ZERO
    new_ast = ast[2]
    if new_ast != ast
      @replacements_made_in_the_last_pass += 1
      ast.replace(new_ast)
    end
  else
    new_ast = [:function, :SUM].concat(not_number_array)
    new_ast.push([:number, number_total]) unless number_total == 0
    if new_ast != ast
      @replacements_made_in_the_last_pass += 1
      ast.replace(new_ast)
    end
  end
  ast
end
partially_map_sumifs(ast) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 222
def partially_map_sumifs(ast)
  values = ast[3..-1].map.with_index { |a,i| value(a, (i % 2) == 0 ? 0 : nil ) }
  sum_range = array_as_values(ast[2]).flatten(1)
  indexes = @calculator._filtered_range_indexes(sum_range, *values)
  if indexes.is_a?(Symbol)
    new_ast = ast_for_value(indexes)
  elsif indexes.empty?
    new_ast = [:number, 0]
  else
    new_ast = [:function, :SUM, *sum_range.values_at(*indexes)]
  end
  if new_ast != ast
    @replacements_made_in_the_last_pass += 1
    ast.replace(new_ast)
  end
end
percentage(ast) click to toggle source
:percentage, number
# File src/simplify/map_formulae_to_values.rb, line 115
def percentage(ast)
  ast.replace(ast_for_value(value([:percentage, ast[1]])))
end
prefix(ast) click to toggle source
:prefix, operator, argument
# File src/simplify/map_formulae_to_values.rb, line 44
def prefix(ast)
  operator, argument = ast[1], ast[2]
  argument_value = value(argument)
  return if argument_value == :not_a_value
  return ast.replace(ast_for_value(argument_value || 0)) if operator == "+"
  ast.replace(ast_for_value(@calculator.negative(argument_value)))
end
reset() click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 26
def reset
  # Not used any more
  # FIXME: Remove references to this method
end
string_join(ast) click to toggle source
:string_join, stringA, stringB, …
# File src/simplify/map_formulae_to_values.rb, line 120
def string_join(ast)
  values = ast[1..-1].map do |a| 
      value(a, "")
  end
  return if values.any? { |a| a == :not_a_value }
  ast.replace(ast_for_value(@calculator.string_join(*values)))
end
value(ast, inlined_blank = 0) click to toggle source
# File src/simplify/map_formulae_to_values.rb, line 437
def value(ast, inlined_blank = 0)
  return extract_values_from_array(ast, inlined_blank) if ast.first == :array
  case ast.first
  when :blank; nil
  when :inlined_blank; inlined_blank
  when :null; nil
  when :number; ast[1]
  when :percentage; ast[1]/100.0
  when :string; ast[1]
  when :error; ERRORS[ast[1]]
  when :boolean_true; true
  when :boolean_false; false
  else return :not_a_value
  end
end