Demo of using lua formulas for a simple spreadsheet app

SimpleLuaSpreadsheet.cs

using System.Text.RegularExpressions;
using NLua;

Dictionary<string, Cell> Spreadsheet = new();
Spreadsheet["EngineeringHours"] = new StaticCell(200);
Spreadsheet["QAMultiplier"] = new StaticCell(0.4);
Spreadsheet["QAHours"] = new FormulaCell("EngineeringHours * QAMultiplier");
Spreadsheet["EngineeringRate"] = new StaticCell(130);
Spreadsheet["QARate"] = new StaticCell(100);
Spreadsheet["TotalCost"] = new FormulaCell("(EngineeringHours * EngineeringRate + QAHours * QARate) * 1.2");

var evaluator = new Evaluator(Spreadsheet);
evaluator.Evaluate();

foreach (var key in Spreadsheet.Keys)
{
    var cell = Spreadsheet[key];
    if (cell is StaticCell staticCell)
    {
        Console.WriteLine($"{key,-15} = {staticCell.Value}");
    }
    else if (cell is FormulaCell formulaCell)
    {
        Console.WriteLine($"{key,-15} = {formulaCell.Formula} => {formulaCell.CachedValue}");
    }

}


class Evaluator
{
    public Dictionary<string, Cell> Sheet { get; }

    public Evaluator(Dictionary<string, Cell> sheet)
    {
        Sheet = sheet;
    }

    public void Evaluate()
    {
        foreach (var cell in Sheet.Keys)
        {
            EvaluateCell(cell);
        }
    }

    private void EvaluateCell(string name)
    {
        var cell = Sheet[name];
        switch (cell)
        {
            case StaticCell:
            case FormulaCell formula when formula.CachedValue.HasValue:
                return;
            case FormulaCell formula:
            {
                var variablesRegex = new Regex(@"\b[A-Z][a-zA-Z]*\b"); // PascalCase words
                var dependencies = variablesRegex.Matches(formula.Formula).Select(m => m.Value).ToArray();
                foreach (var dependency in dependencies)
                {
                    // WARNING possible infinite recursion, production grade tool would need a cycle detection
                    EvaluateCell(dependency);
                }
            
                var result = EvaluateFormula(dependencies, formula.Formula);
                Sheet[name] = formula with { CachedValue = result };
                break;
            }
        }
    }

    private double EvaluateFormula(string[] variables, string formula)
    {
        using var lua = new Lua();
        foreach (var variable in variables)
        {
            lua[variable] = GetValue(variable);
        }

        var result = lua.DoString("return " + formula);
        return (double)result[0];
    }

    private double GetValue(string name)
    {
        var cell = Sheet[name];
        if (cell is StaticCell staticCell) return staticCell.Value;
        if (cell is FormulaCell formulaCell)
        {
            if (!formulaCell.CachedValue.HasValue)
            {
                throw new InvalidOperationException("Formula cell has not been evaluated yet");
            }
            return formulaCell.CachedValue.Value;
        }
        throw new InvalidOperationException("Unknown cell type");
    }
}

abstract record Cell();
record StaticCell(double Value) : Cell;
record FormulaCell(string Formula, double? CachedValue = null) : Cell;