SPB Git

Repository Public

ErrorExplainer.swift 15.5 KB 259 lines swift

1import Foundation2import SheetCore34/// Deterministic explanation of an error value in context.5public struct ErrorExplanation: Hashable, Sendable, Codable {6    public struct Cause: Hashable, Sendable, Codable {7        /// The sub-expression (or cell content) responsible.8        public var expression: String9        /// What is wrong with it.10        public var detail: String11        /// Addresses of the cells involved ("Sheet1!B3").12        public var cells: [String]13    }14    public var error: CellError15    /// Address of the cell that shows the error.16    public var cell: String17    /// One-sentence meaning of the error code.18    public var summary: String19    /// Specific causes found in the origin cell, most specific first. Empty when only the generic meaning is known.20    public var causes: [Cause]21    /// Path from the cell to the cell where the error is born (both included); one element when the cell is the origin.22    public var chain: [String]23    /// The last element of `chain`.24    public var originCell: String25    public var suggestion: String26}2728/// Explains why a cell shows an error by evaluating the sub-expressions of its formula with the real29/// engine and following erroneous precedents back to the cell where the error originates.30public struct ErrorExplainer {31    public let engine: CalcEngine32    /// Maximum number of cells followed from the error back to its origin.33    public var maxChain = 643435    public init(engine: CalcEngine) { self.engine = engine }3637    /// Explanation for the error shown by a cell; nil when the cell does not hold an error.38    public func explain(sheet si: Int, cell: CellRef) -> ErrorExplanation? {39        let wb = engine.workbook40        guard si >= 0, si < wb.sheets.count else { return nil }41        if engine.hasPendingWork { engine.recalculate() }42        guard let err = wb.sheets[si].value(cell.row, cell.col).errorValue else { return nil }43        var out: ErrorExplanation?44        CalcEngine.runDeep { out = self.build(si, cell, err) }45        return out46    }4748    /// Generic meaning of each error code.49    public static func meaning(_ e: CellError) -> String {50        switch e {51        case .div0: "#DIV/0! — a number is divided by zero or by an empty cell."52        case .value: "#VALUE! — a value has the wrong type for the operation (for example text where a number is needed)."53        case .ref: "#REF! — the formula refers to a cell, sheet or table that no longer exists."54        case .name: "#NAME? — the formula uses a function or name that is not recognised."55        case .num: "#NUM! — a calculation produced an invalid or out-of-range number."56        case .na: "#N/A — a value is not available (typically a lookup that found no match)."57        case .null: "#NULL! — two ranges that do not intersect were combined with the intersection operator."58        case .spill: "#SPILL! — the formula returns several values but the cells they need are not empty."59        case .calc: "#CALC! — the calculation engine cannot produce a result for this expression."60        case .circular: "#CIRC! — the formula depends on its own result (circular reference)."61        }62    }6364    static func suggestion(_ e: CellError) -> String {65        switch e {66        case .div0: "Fill in or correct the denominator, or guard the division: =IF(denominator=0, \"\", numerator/denominator)."67        case .value: "Convert the text to a number (or fix the cell that should contain a number)."68        case .ref: "Re-point the reference to an existing cell, sheet or table, or restore what was deleted."69        case .name: "Check the spelling of the function or name, or define the missing name."70        case .num: "Check the arguments: they are outside the range the calculation accepts."71        case .na: "Check the lookup value and the lookup range, or handle the missing case with IFNA."72        case .null: "Use a comma (union) or a colon (range) instead of a space between the references."73        case .spill: "Clear the cells in the way of the result, or move the formula."74        case .calc: "Simplify the expression; check LAMBDA and array arguments."75        case .circular: "Break the loop: one of the cells in the cycle must stop referring back to the others."76        }77    }7879    // MARK: Analysis8081    private struct Loc: Hashable { var sheet: Int; var cell: CellRef }8283    private func addr(_ l: Loc) -> String { FormulaIntel.address(engine.workbook.sheets[l.sheet].name, l.cell) }8485    private func build(_ si: Int, _ cell: CellRef, _ err: CellError) -> ErrorExplanation {86        var chain = [Loc(sheet: si, cell: cell)]87        var visited: Set<Loc> = [chain[0]]88        var causes = [ErrorExplanation.Cause]()89        var cyclic = false90        while chain.count < maxChain {91            let here = chain[chain.count - 1]92            let sheet = engine.workbook.sheets[here.sheet]93            guard let c = sheet.cell(here.cell.row, here.cell.col) else { break }94            guard let f = c.formula else {95                if c.isSpill { causes = [.init(expression: here.cell.a1, detail: "This cell is filled by a spilled array whose element is \(c.value.description).", cells: [addr(here)])] }96                else { causes = [.init(expression: c.value.description, detail: "The cell contains the error value \(c.value.description) typed in directly (not the result of a formula).", cells: [addr(here)])] }97                break98            }99            let e = f.value.errorValue ?? err100            let culprits = minimal(f.expr, here, e)101            // an erroneous precedent cell → keep following102            var next: Loc?103            for culprit in culprits {104                if let l = errorSource(culprit, here, e), !visited.contains(l) { next = l; break }105                if let l = errorSource(culprit, here, e), visited.contains(l) { cyclic = true }106            }107            if let next { visited.insert(next); chain.append(next); continue }108            causes = culprits.flatMap { describe($0, here, e) }109            if causes.isEmpty, e == .spill {110                causes = [.init(expression: "=" + f.expr.formulaText(), detail: "The formula returns an array but the cells it would fill are occupied, merged or inside a table.", cells: [addr(here)])]111            }112            break113        }114        if err == .circular || cyclic {115            let path = chain.map(addr)116            causes.insert(.init(expression: path.joined(separator: " → "), detail: "These cells depend on each other in a loop.", cells: path), at: 0)117        }118        let origin = chain[chain.count - 1]119        return ErrorExplanation(error: err, cell: addr(chain[0]), summary: ErrorExplainer.meaning(err), causes: causes,120                                chain: chain.map(addr), originCell: addr(origin), suggestion: ErrorExplainer.suggestion(err))121    }122123    private func eval(_ e: Expr, _ at: Loc) -> Value { engine.evaluate(e, sheet: at.sheet, at: at.cell) }124125    /// Sub-expressions that can be evaluated on their own (bodies of LET/LAMBDA are not, and untaken IF branches are skipped).126    private func children(_ e: Expr, _ at: Loc) -> [Expr] {127        switch e {128        case .unary(_, let x), .percent(let x), .paren(let x), .implicit(let x), .spill(let x): return [x]129        case .binary(let op, let a, let b): return op == .range ? [] : [a, b]130        case .call("LET", _), .call("LAMBDA", _), .invoke: return []131        case .call("IF", let args) where args.count >= 2:132            guard let t = try? eval(args[0], at).scalar.truth() else { return [args[0]] }133            return [args[0]] + (t ? [args[1]] : (args.count > 2 ? [args[2]] : []))134        case .call(_, let args): return args.filter { if case .missing = $0 { false } else { true } }135        default: return []136        }137    }138139    private func yields(_ e: Expr, _ at: Loc, _ err: CellError) -> Bool {140        let v = eval(e, at)141        if v.errorValue == err { return true }142        if case .array(let a) = v { return a.items.contains { $0.errorValue == err } }143        return false144    }145146    /// Deepest sub-expressions that produce the error while none of their own parts does.147    private func minimal(_ e: Expr, _ at: Loc, _ err: CellError) -> [Expr] {148        let bad = children(e, at).filter { yields($0, at, err) }149        if bad.isEmpty { return [e] }150        var out = [Expr]()151        for b in bad { for m in minimal(b, at, err) where !out.contains(m) { out.append(m) } }152        return out153    }154155    /// When the culprit is a reference to a cell (or range) that itself holds the error, that cell.156    private func errorSource(_ e: Expr, _ at: Loc, _ err: CellError) -> Loc? {157        let wb = engine.workbook158        for r in FormulaIntel.directRefs(e, workbook: wb, sheet: at.sheet) {159            switch e {160            case .cell, .range, .spill, .implicit: break161            default: return nil162            }163            var found: CellRef?164            wb.sheets[r.sheet].cells.forEach(in: r.range) { row, col, c in165                if c.computed.errorValue == err { let x = CellRef(row, col); if found == nil || x < found! { found = x } }166            }167            if let found { return Loc(sheet: r.sheet, cell: found) }168        }169        return nil170    }171172    /// Cells referenced in an expression, as "address (value)" strings and plain addresses.173    private func sources(_ e: Expr, _ at: Loc) -> (text: String, cells: [String]) {174        let wb = engine.workbook175        var parts = [String](), cells = [String]()176        for r in FormulaIntel.directRefs(e, workbook: wb, sheet: at.sheet).prefix(6) {177            let name = wb.sheets[r.sheet].name178            let a = FormulaIntel.address(name, r.range)179            cells.append(a)180            if r.range.isSingleCell {181                let c = wb.sheets[r.sheet].cell(r.range.r0, r.range.c0)182                let v = c?.computed ?? .blank183                let content = (c?.hasContent ?? false) ? "contains " + FormulaIntel.preview(v, limit: 30) : "is empty"184                parts.append(a + " " + content + (c?.formula != nil ? " (result of =" + c!.formula!.expr.formulaText() + ")" : ""))185            } else {186                parts.append(a)187            }188        }189        return (parts.joined(separator: "; "), cells)190    }191192    private func describe(_ e: Expr, _ at: Loc, _ err: CellError) -> [ErrorExplanation.Cause] {193        let text = e.formulaText()194        let here = addr(at)195        let wb = engine.workbook196        switch e {197        case .error(let lit):198            let d = lit == .ref ? "The formula contains the literal #REF!: the cell, range or sheet it pointed to was deleted."199                                : "The formula contains the error literal \(lit.rawValue)."200            return [.init(expression: text, detail: d, cells: [here])]201        case .cell(let s?, _) where wb.sheetIndex(named: s) == nil, .name(_, let s?) where wb.sheetIndex(named: s) == nil:202            return [.init(expression: text, detail: "The sheet \"\(s)\" does not exist.", cells: [here])]203        case .range(let r) where r.sheet != nil && wb.sheetIndex(named: r.sheet!) == nil:204            return [.init(expression: text, detail: "The sheet \"\(r.sheet!)\" does not exist.", cells: [here])]205        case .name(let n, _):206            if err == .name { return [.init(expression: text, detail: "The name \"\(n)\" is not defined (and is not a table).", cells: [here])] }207            return [.init(expression: text, detail: "The name \"\(n)\" evaluates to \(err.rawValue).", cells: [here])]208        case .structured:209            return [.init(expression: text, detail: "The structured reference cannot be resolved: the table or column does not exist, or [@…] is used outside the table's rows.", cells: [here])]210        case .binary(.div, _, let den) where err == .div0:211            let v = eval(den, at)212            let src = sources(den, at)213            let what = v.isBlank ? "is empty (treated as 0)" : "evaluates to " + FormulaIntel.preview(v)214            return [.init(expression: text, detail: "The denominator \(den.formulaText()) \(what)." + (src.text.isEmpty ? "" : " " + src.text + "."), cells: src.cells.isEmpty ? [here] : src.cells)]215        case .binary(.pow, let base, let exp) where err == .div0 || err == .num:216            let src = sources(e, at)217            let d = err == .div0 ? "0 is raised to a negative power: \(base.formulaText()) is \(FormulaIntel.preview(eval(base, at))) and \(exp.formulaText()) is \(FormulaIntel.preview(eval(exp, at)))."218                                 : "The power \(base.formulaText())^\(exp.formulaText()) = \(FormulaIntel.preview(eval(base, at)))^\(FormulaIntel.preview(eval(exp, at))) is not a real number."219            return [.init(expression: text, detail: d, cells: src.cells.isEmpty ? [here] : src.cells)]220        case .binary(let op, let a, let b) where err == .value:221            var out = [ErrorExplanation.Cause]()222            for side in [a, b] {223                let v = eval(side, at).scalar224                let bad: Bool225                if op == .concat { bad = false } else if case .text(let s) = v { bad = Value.parseNumber(s) == nil } else { bad = false }226                if bad {227                    let src = sources(side, at)228                    out.append(.init(expression: text, detail: "\(side.formulaText()) is the text \(FormulaIntel.preview(v)), which cannot be used as a number with \"\(op.symbol)\"." + (src.text.isEmpty ? "" : " " + src.text + "."),229                                     cells: src.cells.isEmpty ? [here] : src.cells))230                }231            }232            if !out.isEmpty { return out }233        case .unary(_, let x), .percent(let x):234            if err == .value, case .text(let s) = eval(x, at).scalar, Value.parseNumber(s) == nil {235                let src = sources(x, at)236                return [.init(expression: text, detail: "\(x.formulaText()) is the text \"\(s)\", which cannot be used as a number." + (src.text.isEmpty ? "" : " " + src.text + "."), cells: src.cells.isEmpty ? [here] : src.cells)]237            }238        case .call(let name, let args):239            let known = engine.registry.lookup(name) != nil || FormulaExplainer.specialForms[name] != nil240            if !known, wb.definedName(name, sheetID: wb.sheets[at.sheet].id) == nil {241                return [.init(expression: text, detail: "\(name) is not a known function.", cells: [here])]242            }243            if let spec = engine.registry.lookup(name), args.count < spec.minArgs || args.count > spec.maxArgs {244                return [.init(expression: text, detail: "\(name) received \(args.count) argument\(args.count == 1 ? "" : "s"); it expects \(spec.signature).", cells: [here])]245            }246            let src = sources(e, at)247            if name == "AVERAGE", err == .div0 {248                return [.init(expression: text, detail: "AVERAGE found no numbers to average in its arguments (empty cells and text are ignored).", cells: src.cells.isEmpty ? [here] : src.cells)]249            }250            if name == "NA", err == .na { return [.init(expression: text, detail: "NA() deliberately returns #N/A to mark a missing value.", cells: [here])] }251            let argText = args.enumerated().map { "argument \($0.offset + 1) (\($0.element.formulaText())) = \(FormulaIntel.preview(eval($0.element, at), limit: 30))" }.joined(separator: ", ")252            return [.init(expression: text, detail: "\(name) returns \(err.rawValue) for these arguments: \(argText).", cells: src.cells.isEmpty ? [here] : src.cells)]253        default: break254        }255        let src = sources(e, at)256        return [.init(expression: text, detail: "This part of the formula evaluates to \(err.rawValue)." + (src.text.isEmpty ? "" : " " + src.text + "."), cells: src.cells.isEmpty ? [here] : src.cells)]257    }258}259
Files main
Menu

Theme