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