SPB Git

Repository Public

FormulaExplainer.swift 20.0 KB 370 lines swift

1import Foundation2import SheetCore34/// A value produced by the evaluator, in a displayable and serialisable form.5public struct ExplainedValue: Hashable, Sendable, Codable {6    /// Display text: numbers in General format, text in quotes, arrays as `{1,2;3,4}`, blank as "(blank)".7    public var display: String8    /// "number", "text", "boolean", "error", "array", "blank" or "lambda".9    public var type: String10    /// The number, when the value is one.11    public var number: Double?12    /// Array dimensions, when the value is an array.13    public var rows: Int?14    public var cols: Int?1516    public init(_ v: Value) {17        display = FormulaIntel.preview(v)18        type = FormulaIntel.typeName(v)19        number = v.numberValue20        if case .array(let a) = v { rows = a.rows; cols = a.cols }21    }22    public var isError: Bool { type == "error" }23}2425/// A cell or range read by a sub-expression, with its current content.26public struct ExplainedReference: Hashable, Sendable, Codable {27    /// "Sheet1!B3" or "Sheet1!B1:B3".28    public var address: String29    public var sheet: String30    public var range: RangeRef31    public var value: ExplainedValue32    /// Formula of the referenced cell (single cells only).33    public var formula: String?34}3536/// One step of a step-by-step evaluation: a sub-expression and the value the evaluator returned for it.37public struct EvaluationStep: Hashable, Sendable, Codable {38    public enum Kind: String, Sendable, Codable { case reference, range, name, `operator`, function, other }39    /// 1-based position in evaluation order (innermost sub-expressions first).40    public var index: Int41    /// The sub-expression as formula text.42    public var expression: String43    public var kind: Kind44    public var value: ExplainedValue45    /// Nesting depth in the formula (0 = the whole formula).46    public var depth: Int47    /// Cells and ranges referenced directly in this sub-expression.48    public var references: [ExplainedReference]49    /// Deterministic remark (branch taken, fallback used…).50    public var note: String?51}5253public struct FormulaExplanation: Hashable, Sendable, Codable {54    public struct FunctionNote: Hashable, Sendable, Codable {55        public var name: String56        public var signature: String57        public var summary: String58        public var isKnown: Bool59    }60    public var sheet: String61    public var cell: String62    /// Formula text with the leading "=".63    public var formula: String64    /// Value of the whole formula as returned by the evaluator.65    public var result: ExplainedValue66    public var steps: [EvaluationStep]67    /// Plain-language description generated from the syntax tree and the function registry (no AI).68    public var description: String69    public var functions: [FunctionNote]70    /// True when the step limit cut the list short.71    public var isTruncated: Bool72}7374/// Step-by-step evaluation of a formula. Every value shown is obtained by evaluating the75/// corresponding sub-expression with the real engine (`CalcEngine.evaluate(_:sheet:at:)`); nothing76/// is computed or guessed here.77///78/// Conventions: branches that the formula does not take (`IF`, `IFERROR`/`IFNA` fallback) are not79/// expanded and are mentioned in the step's note; `LET` bindings are evaluated by substituting the80/// bound expressions; `LAMBDA` bodies are opaque (only the call as a whole is evaluated); literals81/// and parentheses do not get steps of their own; identical sub-expressions are listed once.82public struct FormulaExplainer {83    public let engine: CalcEngine84    /// Maximum number of steps returned.85    public var maxSteps = 2508687    public init(engine: CalcEngine) { self.engine = engine }8889    /// Steps for the formula stored in a cell (empty when the cell has no formula).90    public func steps(for cell: CellRef, sheet: Int) -> [EvaluationStep] { explain(sheet: sheet, cell: cell)?.steps ?? [] }9192    /// Full explanation for the formula stored in a cell; nil when the cell has no formula.93    public func explain(sheet si: Int, cell: CellRef) -> FormulaExplanation? {94        guard si >= 0, si < engine.workbook.sheets.count, let f = engine.workbook.sheets[si].cell(cell.row, cell.col)?.formula else { return nil }95        return explain(expr: f.expr, sheet: si, at: cell)96    }9798    /// Explanation of formula text evaluated as if it were in `cell`; nil when the text does not parse.99    public func explain(formula text: String, sheet si: Int, at cell: CellRef, syntax: FormulaSyntax = .canonical) -> FormulaExplanation? {100        guard let e = try? FormulaParser.parse(text, syntax: syntax) else { return nil }101        return explain(expr: e, sheet: si, at: cell)102    }103104    public func explain(expr: Expr, sheet si: Int, at cell: CellRef) -> FormulaExplanation {105        var w = Walker(engine: engine, sheet: si, cell: cell, maxSteps: maxSteps)106        // one deep thread for the whole walk instead of one per sub-expression107        CalcEngine.runDeep { w.visit(expr, env: [:], depth: 0) }108        var result = Value.blank109        CalcEngine.runDeep { result = w.eval(expr, [:]) }110        var seen = Set<String>()111        var notes = [FormulaExplanation.FunctionNote]()112        expr.walk { node in113            guard case .call(let name, _) = node, seen.insert(name).inserted else { return }114            if let spec = engine.registry.lookup(name) {115                notes.append(.init(name: name, signature: spec.signature, summary: spec.summary, isKnown: true))116            } else if let builtin = FormulaExplainer.specialForms[name] {117                notes.append(.init(name: name, signature: builtin.0, summary: builtin.1, isKnown: true))118            } else {119                notes.append(.init(name: name, signature: "", summary: "", isKnown: false))120            }121        }122        let sheetName = si >= 0 && si < engine.workbook.sheets.count ? engine.workbook.sheets[si].name : ""123        return FormulaExplanation(sheet: sheetName, cell: cell.a1, formula: "=" + expr.formulaText(), result: ExplainedValue(result),124                                  steps: w.steps, description: FormulaExplainer.describe(expr, functions: notes),125                                  functions: notes, isTruncated: w.truncated)126    }127128    /// Functions evaluated by the engine itself (not present in the registry).129    static let specialForms: [String: (String, String)] = [130        "IF": ("IF(condition, value_if_true, [value_if_false])", "Returns one value when the condition is true and another when it is false."),131        "IFERROR": ("IFERROR(value, value_if_error)", "Returns the value, or the fallback when the value is an error."),132        "IFNA": ("IFNA(value, value_if_na)", "Returns the value, or the fallback when the value is #N/A."),133        "IFS": ("IFS(condition1, value1, [condition2, value2], …)", "Returns the value of the first condition that is true."),134        "SWITCH": ("SWITCH(expression, value1, result1, …, [default])", "Compares an expression with a list of values and returns the matching result."),135        "CHOOSE": ("CHOOSE(index, value1, [value2], …)", "Returns the value at the given position in the list."),136        "LET": ("LET(name1, value1, …, calculation)", "Names intermediate values and uses them in a calculation."),137        "LAMBDA": ("LAMBDA([parameter1, …], calculation)", "Defines a custom function."),138    ]139140    // MARK: Walker141142    struct Walker {143        let engine: CalcEngine144        let sheet: Int145        let cell: CellRef146        let maxSteps: Int147        var steps = [EvaluationStep]()148        var seen = Set<Expr>()149        var truncated = false150151        func eval(_ e: Expr, _ env: [String: Expr]) -> Value { engine.evaluate(Walker.substitute(e, env), sheet: sheet, at: cell) }152153        /// Replaces LET-bound names by their (parenthesised) value expressions, respecting shadowing.154        static func substitute(_ e: Expr, _ env: [String: Expr]) -> Expr {155            if env.isEmpty { return e }156            switch e {157            case .name(let n, nil):158                return env[n.uppercased()] ?? e159            case .call("LAMBDA", let args):160                var inner = env161                for p in args.dropLast() { if case .name(let n, _) = p { inner[n.uppercased()] = nil } }162                return .call("LAMBDA", args.dropLast() + [substitute(args.last ?? .missing, inner)])163            case .call("LET", let args) where args.count >= 3 && args.count % 2 == 1:164                var inner = env165                var out = [Expr]()166                var k = 0167                while k + 1 < args.count {168                    out.append(args[k])169                    out.append(substitute(args[k + 1], inner))170                    if case .name(let n, _) = args[k] { inner[n.uppercased()] = nil }171                    k += 2172                }173                out.append(substitute(args[args.count - 1], inner))174                return .call("LET", out)175            case .call(let name, let args):176                let mapped = args.map { substitute($0, env) }177                if let bound = env[name] { return .invoke(bound, mapped) }178                return .call(name, mapped)179            case .array(let rows): return .array(rows.map { $0.map { substitute($0, env) } })180            case .unary(let op, let x): return .unary(op, substitute(x, env))181            case .percent(let x): return .percent(substitute(x, env))182            case .spill(let x): return .spill(substitute(x, env))183            case .implicit(let x): return .implicit(substitute(x, env))184            case .paren(let x): return .paren(substitute(x, env))185            case .binary(let op, let a, let b): return .binary(op, substitute(a, env), substitute(b, env))186            case .invoke(let f, let args): return .invoke(substitute(f, env), args.map { substitute($0, env) })187            default: return e188            }189        }190191        mutating func record(_ e: Expr, _ env: [String: Expr], _ depth: Int, _ kind: EvaluationStep.Kind, note: String? = nil) {192            let resolved = Walker.substitute(e, env)193            guard seen.insert(resolved).inserted else { return }194            guard steps.count < maxSteps else { truncated = true; return }195            let v = engine.evaluate(resolved, sheet: sheet, at: cell)196            let wb = engine.workbook197            var refs = [ExplainedReference]()198            for r in FormulaIntel.directRefs(resolved, workbook: wb, sheet: sheet) {199                let s = wb.sheets[r.sheet]200                if r.range.isSingleCell {201                    let c = s.cell(r.range.r0, r.range.c0)202                    let rv = engine.evaluate(.cell(sheet: s.name, CellAddr(row: r.range.r0, col: r.range.c0)), sheet: sheet, at: cell)203                    refs.append(ExplainedReference(address: FormulaIntel.address(s.name, r.range), sheet: s.name, range: r.range, value: ExplainedValue(rv),204                                                   formula: c?.formula.map { "=" + $0.expr.formulaText() }))205                } else {206                    let a = CellAddr(row: r.range.r0, col: r.range.c0), b = CellAddr(row: r.range.r1, col: r.range.c1)207                    let kind: RangeKind = r.range.isWholeColumns ? .columns : (r.range.isWholeRows ? .rows : .cells)208                    let rv = engine.evaluate(.range(RangeExpr(sheet: s.name, a: a, b: b, kind: kind)), sheet: sheet, at: cell)209                    refs.append(ExplainedReference(address: FormulaIntel.address(s.name, r.range), sheet: s.name, range: r.range, value: ExplainedValue(rv), formula: nil))210                }211            }212            steps.append(EvaluationStep(index: steps.count + 1, expression: e.formulaText(), kind: kind, value: ExplainedValue(v),213                                        depth: depth, references: refs, note: note))214        }215216        mutating func visit(_ e: Expr, env: [String: Expr], depth: Int) {217            switch e {218            case .number, .text, .bool, .error, .missing, .array: return219            case .paren(let x): visit(x, env: env, depth: depth)220            case .cell: record(e, env, depth, .reference)221            case .range: record(e, env, depth, .range)222            case .structured: record(e, env, depth, .range)223            case .name(let n, let s):224                if s == nil, env[n.uppercased()] != nil { record(e, env, depth, .name, note: "Name defined by LET.") }225                else { record(e, env, depth, .name) }226            case .unary(_, .number), .percent(.number):227                return      // signed and percent literals228            case .unary(_, let x), .percent(let x):229                visit(x, env: env, depth: depth + 1)230                record(e, env, depth, .operator)231            case .spill, .implicit:232                record(e, env, depth, .reference)233            case .binary(let op, let a, let b):234                if op != .range {235                    visit(a, env: env, depth: depth + 1)236                    visit(b, env: env, depth: depth + 1)237                }238                record(e, env, depth, op == .range ? .range : .operator)239            case .invoke(_, let args):240                for a in args { visit(a, env: env, depth: depth + 1) }241                record(e, env, depth, .function)242            case .call(let name, let args):243                visitCall(e, name, args, env, depth)244            }245        }246247        private mutating func visitCall(_ e: Expr, _ name: String, _ args: [Expr], _ env: [String: Expr], _ depth: Int) {248            switch name {249            case "IF" where args.count >= 2:250                visit(args[0], env: env, depth: depth + 1)251                let cond = eval(args[0], env)252                if let truth = try? cond.scalar.truth() {253                    if truth {254                        visit(args[1], env: env, depth: depth + 1)255                        record(e, env, depth, .function, note: "Condition is TRUE: the value_if_true branch is used" + (args.count > 2 ? "; the other branch is not evaluated." : "."))256                    } else {257                        if args.count > 2 { visit(args[2], env: env, depth: depth + 1) }258                        record(e, env, depth, .function, note: args.count > 2 ? "Condition is FALSE: the value_if_false branch is used; the other branch is not evaluated."259                                                                                : "Condition is FALSE and there is no value_if_false: the result is FALSE.")260                    }261                } else {262                    record(e, env, depth, .function, note: "The condition is not a logical value.")263                }264            case "IFERROR" where args.count == 2, "IFNA" where args.count == 2:265                visit(args[0], env: env, depth: depth + 1)266                let v = eval(args[0], env).scalar267                let caught = name == "IFNA" ? v.errorValue == .na : v.isError268                if caught {269                    visit(args[1], env: env, depth: depth + 1)270                    record(e, env, depth, .function, note: "The first argument is \(v.description): the fallback is used.")271                } else {272                    record(e, env, depth, .function, note: "The first argument is not " + (name == "IFNA" ? "#N/A" : "an error") + ": the fallback is not evaluated.")273                }274            case "LET" where args.count >= 3 && args.count % 2 == 1:275                var inner = env276                var k = 0277                while k + 1 < args.count {278                    visit(args[k + 1], env: inner, depth: depth + 1)279                    if case .name(let n, _) = args[k] { inner[n.uppercased()] = .paren(Walker.substitute(args[k + 1], inner)) }280                    k += 2281                }282                visit(args[args.count - 1], env: inner, depth: depth + 1)283                record(e, env, depth, .function)284            case "LAMBDA":285                return      // a function value: nothing to show until it is called286            default:287                for a in args { visit(a, env: env, depth: depth + 1) }288                record(e, env, depth, .function)289            }290        }291    }292293    // MARK: Plain-language description294295    /// Deterministic description of what a formula computes, built from its structure.296    public static func describe(_ expr: Expr, registry: FunctionRegistry = .standard) -> String {297        var seen = Set<String>()298        var notes = [FormulaExplanation.FunctionNote]()299        expr.walk { node in300            guard case .call(let name, _) = node, seen.insert(name).inserted else { return }301            if let spec = registry.lookup(name) { notes.append(.init(name: name, signature: spec.signature, summary: spec.summary, isKnown: true)) }302            else if let b = specialForms[name] { notes.append(.init(name: name, signature: b.0, summary: b.1, isKnown: true)) }303            else { notes.append(.init(name: name, signature: "", summary: "", isKnown: false)) }304        }305        return describe(expr, functions: notes)306    }307308    static func describe(_ expr: Expr, functions: [FormulaExplanation.FunctionNote]) -> String {309        var text = "Returns " + phrase(expr) + "."310        if !functions.isEmpty {311            text += " Functions used: " + functions.map { f in312                f.isKnown ? (f.summary.isEmpty ? f.name : f.name + " — " + f.summary) : f.name + " — not a known function."313            }.map { $0.hasSuffix(".") ? $0 : $0 + "." }.joined(separator: " ")314        }315        return text316    }317318    static func phrase(_ e: Expr) -> String {319        switch e {320        case .missing: return "nothing"321        case .number, .text, .bool, .error, .array, .cell, .range, .name, .structured: return e.formulaText()322        case .paren(let x): return "(" + phrase(x) + ")"323        case .unary(let op, let x): return op == .neg ? "the negative of " + phrase(x) : phrase(x)324        case .percent(let x): return phrase(x) + " percent"325        case .spill(let x): return "the spilled range starting at " + phrase(x)326        case .implicit(let x): return "the value of " + phrase(x) + " on this row or column"327        case .invoke(let f, let args): return "the function " + f.formulaText() + " applied to " + list(args)328        case .binary(let op, let a, let b):329            let l = phrase(a), r = phrase(b)330            switch op {331            case .add: return l + " plus " + r332            case .sub: return l + " minus " + r333            case .mul: return l + " multiplied by " + r334            case .div: return l + " divided by " + r335            case .pow: return l + " raised to the power " + r336            case .concat: return l + " joined with " + r337            case .eq: return l + " equals " + r338            case .ne: return l + " is not equal to " + r339            case .lt: return l + " is less than " + r340            case .le: return l + " is at most " + r341            case .gt: return l + " is greater than " + r342            case .ge: return l + " is at least " + r343            case .range: return "the range from " + l + " to " + r344            }345        case .call(let name, let args):346            switch name {347            case "IF" where args.count >= 2:348                return "if " + phrase(args[0]) + " then " + phrase(args[1]) + (args.count > 2 ? ", otherwise " + phrase(args[2]) : ", otherwise FALSE")349            case "IFERROR" where args.count == 2: return phrase(args[0]) + ", or " + phrase(args[1]) + " if that is an error"350            case "IFNA" where args.count == 2: return phrase(args[0]) + ", or " + phrase(args[1]) + " if that is #N/A"351            case "SUM": return "the sum of " + list(args)352            case "AVERAGE": return "the average of " + list(args)353            case "MIN": return "the smallest value in " + list(args)354            case "MAX": return "the largest value in " + list(args)355            case "COUNT": return "the count of numbers in " + list(args)356            case "COUNTA": return "the count of non-empty cells in " + list(args)357            case "ABS" where args.count == 1: return "the absolute value of " + phrase(args[0])358            case "NOT" where args.count == 1: return "the opposite of " + phrase(args[0])359            default: return name + "(" + args.map(phrase).joined(separator: ", ") + ")"360            }361        }362    }363364    private static func list(_ args: [Expr]) -> String {365        let p = args.map(phrase)366        if p.count <= 1 { return p.first ?? "nothing" }367        return p.dropLast().joined(separator: ", ") + " and " + p[p.count - 1]368    }369}370
Files main
Menu

Theme