SPB Git

Repository Public

Validator.swift 13.9 KB 270 lines swift

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
Files main
Menu

Theme