Repository Public
1import Foundation2import SheetCore34/// How a failed validation is presented, from `DataValidation.errorStyle`.5public enum ValidationAlertStyle: String, Sendable, Codable, CaseIterable {6 /// The entry must be rejected.7 case stop8 /// The user may keep the entry after a warning.9 case warning10 /// The entry is accepted; the message is informational.11 case information12}1314/// Result of checking a proposed value against the data-validation rule of a cell.15public struct ValidationOutcome: Sendable, Hashable {16 public var isValid: Bool17 /// Alert style of the violated rule (nil when valid).18 public var style: ValidationAlertStyle?19 /// Title for the alert: the rule's `errorTitle`, or a default.20 public var title: String?21 /// Message for the alert: the rule's `errorMessage`, or `reason` when the rule has none.22 public var message: String?23 /// Deterministic description of the violated restriction, e.g. "Enter a whole number between 1 and 10."24 public var reason: String?25 /// True when the entry may still be committed (warning and information styles).26 public var canOverride: Bool { isValid || style != .stop }2728 public static let valid = ValidationOutcome(isValid: true, style: nil, title: nil, message: nil, reason: nil)29}3031/// Checks proposed cell values against `DataValidation` rules and lists drop-down items.32///33/// Conventions34/// - The first rule of `SheetMeta.validations` whose ranges contain the cell applies.35/// - Rule formulas are written for the top-left cell of the rule's first range and shift with the36/// cell, like conditional-format formulas.37/// - A blank proposal is accepted when `allowBlank` is true. With `allowBlank` false a blank is38/// checked like any other value (it fails the numeric kinds and `list`). When `allowBlank` is39/// true and a bound refers to an empty cell, the entry is accepted (Excel's "ignore blank").40/// - `whole` requires an integer number; `decimal`, `date` and `time` require a number (dates and41/// times are serial numbers); `textLength` compares the character count of the General-format text.42/// - `list`: a literal list (`"a,b,c"`) is compared case-sensitively and a list read from cells43/// case-insensitively, as Excel does; numeric items match equal numbers.44/// - `custom`: the formula is evaluated with every single-cell reference to the validated cell45/// replaced by the proposed value, so nothing is written to the workbook and undo history is46/// untouched. Limitation: a multi-cell range that includes the validated cell (e.g.47/// `COUNTIF(A:A,A1)=1`) still reads the value currently stored there. A blank proposal is48/// substituted as empty text.49/// - Error values are rejected by every kind except `any`.50public struct Validator {51 public let engine: CalcEngine52 public init(engine: CalcEngine) { self.engine = engine }5354 /// The rule that governs a cell, if any.55 public func rule(sheet si: Int, row: Int, col: Int) -> DataValidation? {56 guard si >= 0, si < engine.workbook.sheets.count else { return nil }57 return engine.workbook.sheets[si].meta.validations.first { v in v.ranges.contains { $0.contains(row: row, col: col) } }58 }5960 /// Checks a value the user wants to store in a cell.61 public func validate(_ proposed: Value, sheet si: Int, row: Int, col: Int) -> ValidationOutcome {62 guard let rule = rule(sheet: si, row: row, col: col), !rule.ranges.isEmpty else { return .valid }63 var outcome = ValidationOutcome.valid64 CalcEngine.runDeep { outcome = self.check(proposed.scalar, rule, si, row, col) }65 return outcome66 }6768 /// Checks typed text: it is interpreted with `parser` first (numbers, dates, booleans, text);69 /// a formula is evaluated in place of the cell and its result is validated.70 public func validate(input text: String, sheet si: Int, row: Int, col: Int, parser: InputParser) -> ValidationOutcome {71 guard let parsed = try? parser.parse(text) else {72 return ValidationOutcome(isValid: false, style: .stop, title: "Invalid formula", message: "The formula could not be parsed.", reason: "The formula could not be parsed.")73 }74 switch parsed.input {75 case .blank: return validate(.blank, sheet: si, row: row, col: col)76 case .value(let v): return validate(v, sheet: si, row: row, col: col)77 case .formula(let e): return validate(engine.evaluate(e, sheet: si, at: CellRef(row, col)), sheet: si, row: row, col: col)78 }79 }8081 /// Checks the values currently stored in `range` ("circle invalid data"). Only failing cells are returned.82 public func invalidCells(sheet si: Int, in range: RangeRef) -> [CellRef: ValidationOutcome] {83 guard si >= 0, si < engine.workbook.sheets.count else { return [:] }84 let sheet = engine.workbook.sheets[si]85 if engine.pendingCount > 0 { engine.recalculate() }86 var out: [CellRef: ValidationOutcome] = [:]87 CalcEngine.runDeep {88 for rule in sheet.meta.validations where rule.kind != .any {89 for rg in rule.ranges {90 guard let window = rg.intersection(range) else { continue }91 sheet.cells.forEach(in: window) { r, c, cell in92 let ref = CellRef(r, c)93 guard cell.hasContent, out[ref] == nil, self.rule(sheet: si, row: r, col: c) == rule else { return }94 let o = self.check(cell.computed, rule, si, r, c)95 if !o.isValid { out[ref] = o }96 }97 }98 }99 }100 return out101 }102103 /// Items of the drop-down list of a cell (nil when the cell has no `list` rule).104 /// `DataValidation.showDropdown` says whether the arrow should be displayed.105 public func dropdownItems(sheet si: Int, row: Int, col: Int) -> [Value]? {106 guard let rule = rule(sheet: si, row: row, col: col), rule.kind == .list, !rule.ranges.isEmpty else { return nil }107 var items: [Value]?108 CalcEngine.runDeep { items = self.listItems(rule, si, row, col)?.items }109 return items110 }111112 // MARK: Internals113114 private func fail(_ rule: DataValidation, _ reason: String) -> ValidationOutcome {115 let style = ValidationAlertStyle(rawValue: rule.errorStyle.lowercased()) ?? .stop116 let message = (rule.errorMessage?.isEmpty == false ? rule.errorMessage : nil) ?? reason117 let title = (rule.errorTitle?.isEmpty == false ? rule.errorTitle : nil) ?? "Invalid entry"118 return ValidationOutcome(isValid: false, style: style, title: title, message: message, reason: reason)119 }120121 private func shiftedExpr(_ text: String?, _ rule: DataValidation, _ si: Int, _ row: Int, _ col: Int) -> Expr? {122 guard let text, let e = AuditRuleSupport.parse(text) else { return nil }123 let anchor = rule.ranges[0].topLeft124 return AuditRuleSupport.shifted(e, dRow: row - anchor.row, dCol: col - anchor.col, sheetName: engine.workbook.sheets[si].name)125 }126127 private enum Bound {128 case number(Double)129 case blank130 case invalid131 case missing132 }133134 private func bound(_ text: String?, _ rule: DataValidation, _ si: Int, _ row: Int, _ col: Int) -> Bound {135 guard let text, !text.trimmingCharacters(in: .whitespaces).isEmpty else { return .missing }136 guard let e = shiftedExpr(text, rule, si, row, col) else { return .invalid }137 switch engine.evaluate(e, sheet: si, at: CellRef(row, col)).scalar {138 case .number(let n): return .number(n)139 case .blank: return .blank140 case .bool(let b): return .number(b ? 1 : 0)141 case .text(let s): return Value.parseNumber(s).map { .number($0) } ?? .invalid142 default: return .invalid143 }144 }145146 private static let opWords: [String: String] = [147 "between": "between", "notBetween": "not between", "equal": "equal to", "notEqual": "not equal to",148 "greaterThan": "greater than", "lessThan": "less than", "greaterThanOrEqual": "greater than or equal to",149 "lessThanOrEqual": "less than or equal to",150 ]151152 private func describe(_ rule: DataValidation, _ noun: String, _ a: Double, _ b: Double?) -> String {153 let word = Self.opWords[rule.op] ?? rule.op154 let first = Value.generalString(a)155 if rule.op == "between" || rule.op == "notBetween", let b {156 return "Enter \(noun) \(word) \(Value.generalString(min(a, b))) and \(Value.generalString(max(a, b)))."157 }158 return "Enter \(noun) \(word) \(first)."159 }160161 private func checkNumber(_ x: Double, _ rule: DataValidation, _ noun: String, _ si: Int, _ row: Int, _ col: Int) -> ValidationOutcome {162 let needsTwo = rule.op == "between" || rule.op == "notBetween"163 let b1 = bound(rule.formula1, rule, si, row, col)164 let b2: Bound = needsTwo ? bound(rule.formula2, rule, si, row, col) : .missing165 var a = 0.0, b: Double?166 switch b1 {167 case .number(let n): a = n168 case .blank: if rule.allowBlank { return .valid } else { a = 0 }169 case .invalid, .missing: return fail(rule, "The validation criteria of this cell could not be evaluated.")170 }171 if needsTwo {172 switch b2 {173 case .number(let n): b = n174 case .blank: if rule.allowBlank { return .valid } else { b = 0 }175 case .invalid, .missing: return fail(rule, "The validation criteria of this cell could not be evaluated.")176 }177 }178 guard let ok = AuditRuleSupport.compare(rule.op, x, a, b) else {179 return fail(rule, "The validation criteria of this cell could not be evaluated.")180 }181 return ok ? .valid : fail(rule, describe(rule, noun, a, b))182 }183184 private func listItems(_ rule: DataValidation, _ si: Int, _ row: Int, _ col: Int) -> (items: [Value], literal: Bool)? {185 guard var text = rule.formula1?.trimmingCharacters(in: .whitespaces), !text.isEmpty else { return nil }186 if text.hasPrefix("=") { text.removeFirst() }187 func split(_ s: String) -> [Value] {188 s.split(separator: ",", omittingEmptySubsequences: false)189 .map { $0.trimmingCharacters(in: .whitespaces) }190 .filter { !$0.isEmpty }191 .map { Value.text($0) }192 }193 if text.count >= 2, text.hasPrefix("\""), text.hasSuffix("\"") {194 let inner = String(text.dropFirst().dropLast()).replacingOccurrences(of: "\"\"", with: "\"")195 return (split(inner), true)196 }197 guard let e = shiftedExpr(text, rule, si, row, col) else { return (split(text), true) }198 let v = engine.evaluate(e, sheet: si, at: CellRef(row, col))199 switch v {200 case .array(let a): return (a.items.filter { !$0.isBlank && !$0.isError }, false)201 case .blank, .error: return ([], false)202 default: return ([v], false)203 }204 }205206 private func check(_ v: Value, _ rule: DataValidation, _ si: Int, _ row: Int, _ col: Int) -> ValidationOutcome {207 if rule.kind == .any { return .valid }208 if v.isBlank && rule.allowBlank { return .valid }209 if case .error(let e) = v { return fail(rule, "The value is an error (\(e.rawValue)).") }210 switch rule.kind {211 case .any:212 return .valid213 case .whole:214 guard case .number(let x) = v, x == x.rounded() else { return fail(rule, "Enter a whole number.") }215 return checkNumber(x, rule, "a whole number", si, row, col)216 case .decimal:217 guard case .number(let x) = v else { return fail(rule, "Enter a number.") }218 return checkNumber(x, rule, "a number", si, row, col)219 case .date:220 guard case .number(let x) = v else { return fail(rule, "Enter a date.") }221 return checkNumber(x, rule, "a date (serial)", si, row, col)222 case .time:223 guard case .number(let x) = v else { return fail(rule, "Enter a time.") }224 return checkNumber(x, rule, "a time (serial)", si, row, col)225 case .textLength:226 let length = Double(AuditRuleSupport.display(v).count)227 return checkNumber(length, rule, "text whose length is", si, row, col)228 case .list:229 guard let (items, literal) = listItems(rule, si, row, col) else {230 return fail(rule, "The list of allowed values could not be evaluated.")231 }232 let hit = items.contains { item in233 if let a = Self.numeric(item), let b = Self.numeric(v) { return a == b }234 let x = AuditRuleSupport.display(item), y = AuditRuleSupport.display(v)235 return literal ? x == y : x.compare(y, options: .caseInsensitive) == .orderedSame236 }237 return hit ? .valid : fail(rule, "Choose a value from the list.")238 case .custom:239 guard let expr = shiftedExpr(rule.formula1, rule, si, row, col) else {240 return fail(rule, "The validation formula of this cell could not be parsed.")241 }242 let sheetName = engine.workbook.sheets[si].name243 let literal: Expr244 switch v {245 case .number(let n): literal = .number(n)246 case .bool(let b): literal = .bool(b)247 case .text(let s): literal = .text(s)248 default: literal = .text("")249 }250 let substituted = expr.map { node in251 if case .cell(let s, let a) = node, a.row == row, a.col == col,252 s == nil || s!.compare(sheetName, options: .caseInsensitive) == .orderedSame {253 return literal254 }255 return node256 }257 let result = engine.evaluate(substituted, sheet: si, at: CellRef(row, col))258 return AuditRuleSupport.truthy(result) ? .valid : fail(rule, "The value does not satisfy the validation formula.")259 }260 }261262 private static func numeric(_ v: Value) -> Double? {263 switch v {264 case .number(let n): return n265 case .text(let s): return Value.parseNumber(s)266 default: return nil267 }268 }269}270