Class: LiquidXlsx::FormulaTranslator

Inherits:
Object
  • Object
show all
Defined in:
lib/liquid_xlsx/formula_translator.rb

Overview

Translates Excel formulas when rows are copied or shifted, and widens ranges that reference loop bodies after loops have expanded.

Loop expansions are registered in RENDER coordinates: for each template row of a loop body we know the list of rendered row numbers it produced. A range like SUM(E3:E3) whose rows point into a loop body is rewritten to span from the first to the last rendered body row; all other relative references are shifted by the formula's own row delta.

Constant Summary collapse

CELL_REF =

Pattern for cell references including sheet-qualified ones. Guards: a reference must not be preceded or followed by an identifier character, so tails of function names (LOG10, ATAN2) and defined names are not mistaken for cell references.

/
  (?<![A-Za-z0-9_$])
  (?<sheet>(?:'[^']*'|[A-Za-z0-9_.]+)!)?  # optional sheet name
  (?<col_abs>\$)?(?<col>[A-Z]{1,3})
  (?<row_abs>\$)?(?<row>\d+)
  (?![A-Za-z0-9_(])
/x

Instance Method Summary collapse

Constructor Details

#initialize ⇒ FormulaTranslator

Returns a new instance of FormulaTranslator.



26
27
28
29
 
# File 'lib/liquid_xlsx/formula_translator.rb', line 26

def initialize
  # Each: { rows: {template_row => [rendered_row, ...]}, block_first:, block_last:, path: }
  @expansions = []
end

Instance Method Details

#copied_row?(template_row) ⇒ Boolean

Whether a template row lies inside a registered loop block (i.e. the row was copied per iteration rather than moved).

Returns:

  • (Boolean) 


43
44
45
46
47
 
# File 'lib/liquid_xlsx/formula_translator.rb', line 43

def copied_row?(template_row)
  return false unless template_row

  @expansions.any? { |e| template_row.between?(e[:block_first], e[:block_last]) }
end

#register_expansion(rows:, block_first:, block_last:, path: []) ⇒ Object

Register a loop expansion.

Parameters:

  • rows (Hash{Integer => Array<Integer>}) —

    template body row => rendered rows

  • block_first (Integer) —

    template row of for %

  • block_last (Integer) —

    template row of endfor %

  • path (Array<Integer>) (defaults to: []) —

    iteration indices of enclosing loops



36
37
38
39
 
# File 'lib/liquid_xlsx/formula_translator.rb', line 36

def register_expansion(rows:, block_first:, block_last:, path: [])
  @expansions << { rows: rows, block_first: block_first,
                   block_last: block_last, path: path.dup.freeze }
end

#translate(formula, row_delta, col_delta = 0, template_row: nil, copied: true, formula_path: []) ⇒ String

Translate a formula.

Parameters:

  • formula (String) —

    the original formula (without leading =)

  • row_delta (Integer) —

    how many rows to shift (positive = down)

  • col_delta (Integer) (defaults to: 0) —

    how many columns to shift

  • template_row (Integer, nil) (defaults to: nil) —

    template row the formula lives on (used to skip loop expansions for formulas inside the loop itself)

  • copied (Boolean) (defaults to: true) —

    copy semantics (references to other sheets shift too, as in Excel copy/paste); move semantics leaves them untouched

  • formula_path (Array<Integer>) (defaults to: []) —

    iteration path of the formula cell

Returns:

  • (String) —

    translated formula



59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/liquid_xlsx/formula_translator.rb', line 59

def translate(formula, row_delta, col_delta = 0, template_row: nil, copied: true, formula_path: [])
  refs = scan_refs(formula)
  return formula if refs.empty?

  mark_expanded_ranges(formula, refs, template_row, formula_path)

  out = +""
  last_pos = 0
  refs.each do |ref|
    out << formula[last_pos...ref[:pos]]
    out << rebuild_ref(ref, row_delta, col_delta, copied)
    last_pos = ref[:end_pos]
  end
  out << formula[last_pos..]
  out
end