Repository Public
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