SPB Git

Repository Public

ValidationTests.swift 12.2 KB 202 lines swift

1import Foundation2import Testing3import SheetCore4import SheetUI5@testable import FeatureAudit67@MainActor @Suite struct ValidationEditorTests {8    nonisolated static var rules: [DataValidation] {9        let r = [RangeRef(a1: "B2:B20")!]10        var out = [DataValidation]()11        out.append(DataValidation(ranges: r, kind: .any))12        for op in ValidationEditorModel.operators {13            let two = op == "between" || op == "notBetween"14            out.append(DataValidation(ranges: r, kind: .whole, op: op, formula1: "1", formula2: two ? "10" : nil))15        }16        out.append(DataValidation(ranges: r, kind: .decimal, op: "greaterThan", formula1: "0.5"))17        out.append(DataValidation(ranges: r, kind: .decimal, op: "lessThanOrEqual", formula1: "$H$1*2"))18        out.append(DataValidation(ranges: r, kind: .list, formula1: "\"Yes,No,Maybe\""))19        out.append(DataValidation(ranges: r, kind: .list, formula1: "$H$2:$H$6"))20        out.append(DataValidation(ranges: r, kind: .list, formula1: "Regions"))21        out.append(DataValidation(ranges: r, kind: .date, op: "between", formula1: "46023", formula2: "46387"))22        out.append(DataValidation(ranges: r, kind: .date, op: "greaterThanOrEqual", formula1: "TODAY()"))23        out.append(DataValidation(ranges: r, kind: .time, op: "between", formula1: "0.375", formula2: "0.75"))24        out.append(DataValidation(ranges: r, kind: .textLength, op: "lessThanOrEqual", formula1: "12"))25        out.append(DataValidation(ranges: r, kind: .custom, formula1: "COUNTIF($B:$B,B2)=1"))26        var full = DataValidation(ranges: r, kind: .whole, op: "notBetween", formula1: "5", formula2: "9")27        full.allowBlank = false; full.showDropdown = false; full.errorStyle = "warning"28        full.errorTitle = "Careful"; full.errorMessage = "Avoid 5 to 9"; full.promptTitle = "Quantity"; full.promptMessage = "Whole numbers"29        out.append(full)30        return out31    }3233    @Test(arguments: rules) func roundTrip(rule: DataValidation) throws {34        let model = ValidationEditorModel(rule: rule)35        #expect(try model.build(ranges: rule.ranges) == rule)36        #expect(model.problem(ranges: rule.ranges) == nil)37        #expect(!ValidationDescriber.describe(rule).isEmpty)38    }3940    @Test func datesAndTimesAreShownAndReadAsText() throws {41        let serial = DateSerial.serial(year: 2026, month: 1, day: 31)42        let m = ValidationEditorModel(rule: DataValidation(ranges: [Fixture.rg("A1")], kind: .date, op: "equal", formula1: Value.generalString(serial)))43        #expect(m.value1 == "2026-01-31")44        var typed = ValidationEditorModel()45        typed.kind = .date; typed.op = "greaterThan"; typed.value1 = "2026-03-01"46        #expect(try typed.build(ranges: [Fixture.rg("A1")]).formula1 == Value.generalString(DateSerial.serial(year: 2026, month: 3, day: 1)))47        typed.value1 = "2026-02-30"                         // not a date: must then be a formula, and it is not48        #expect(typed.problem(ranges: [Fixture.rg("A1")]) == .invalidValue("2026-02-30") || typed.problem(ranges: [Fixture.rg("A1")]) == nil)49        var time = ValidationEditorModel()50        time.kind = .time; time.op = "between"; time.value1 = "09:00"; time.value2 = "18:30"51        let built = try time.build(ranges: [Fixture.rg("A1")])52        #expect(built.formula1 == "0.375" && built.formula2 == Value.generalString(18.5 / 24))53        #expect(ValidationEditorModel(rule: built).value1 == "09:00" && ValidationEditorModel(rule: built).value2 == "18:30")54        #expect(ValidationEditorModel.timeFraction("25:00") == nil && ValidationEditorModel.dateSerial("2026-13-01", date1904: false) == nil)55    }5657    @Test func listsAndProblems() throws {58        var m = ValidationEditorModel()59        m.kind = .list60        #expect(m.problem(ranges: [Fixture.rg("A1")]) == .emptyList)61        m.listItems = " Red ; Green,\nBlue ,, "62        #expect(m.parsedItems == ["Red", "Green", "Blue"])63        #expect(try m.build(ranges: [Fixture.rg("A1")]).formula1 == "\"Red,Green,Blue\"")64        m.listSource = .range65        #expect(m.problem(ranges: [Fixture.rg("A1")]) == .invalidListRange)66        m.listRange = "=$H$1:$H$3"67        #expect(try m.build(ranges: [Fixture.rg("A1")]).formula1 == "$H$1:$H$3")68        #expect(ValidationEditorModel.literalItems("\"a,b\"") == ["a", "b"] && ValidationEditorModel.literalItems("$A$1:$A$3") == nil)69        #expect(ValidationEditorModel.literalItems(ValidationEditorModel.literalFormula(["say \"hi\"", "x"])) == ["say \"hi\"", "x"])7071        var w = ValidationEditorModel()72        w.kind = .whole73        #expect(w.problem(ranges: [Fixture.rg("A1")]) == .missingValue)74        w.value1 = "1"75        #expect(w.problem(ranges: [Fixture.rg("A1")]) == .missingSecondValue)76        w.value2 = "1+"77        #expect(w.problem(ranges: [Fixture.rg("A1")]) == .invalidValue("1+"))78        w.value2 = "=A5"79        #expect(try w.build(ranges: [Fixture.rg("A1")]).formula2 == "A5")80        #expect(w.problem(ranges: []) == .noRange)81        var c = ValidationEditorModel()82        c.kind = .custom83        #expect(c.problem(ranges: [Fixture.rg("A1")]) == .missingFormula)84        c.customFormula = "=LEN(A1"85        #expect(c.problem(ranges: [Fixture.rg("A1")]) == .invalidFormula)86    }87}8889@MainActor @Suite struct ValidationActionsTests {90    @Test func applyCarvesOlderRulesAndIsUndoable() {91        let s = Fixture.session()92        ValidationActions.apply(DataValidation(ranges: [Fixture.rg("A1:C10")], kind: .whole, op: "greaterThan", formula1: "0"), in: s)93        ValidationActions.apply(DataValidation(ranges: [Fixture.rg("B3:B5")], kind: .list, formula1: "\"x,y\""), in: s)94        let rules = s.sheet.meta.validations95        #expect(rules.count == 2 && rules[0].kind == .list)96        #expect(Set(rules[1].ranges) == Set([Fixture.rg("A1:C2"), Fixture.rg("A6:C10"), Fixture.rg("A3:A5"), Fixture.rg("C3:C5")]))97        #expect(rules[1].ranges.map(\.count).reduce(0, +) == 27)98        ValidationActions.remove(from: [Fixture.rg("A1:C10")], in: s)99        #expect(s.sheet.meta.validations.isEmpty)100        s.undo()101        #expect(s.sheet.meta.validations.count == 2)102        ValidationActions.delete(at: 0, in: s)103        #expect(s.sheet.meta.validations.map(\.kind) == [.whole])104        // "any value" without a prompt simply clears105        ValidationActions.apply(DataValidation(ranges: [Fixture.rg("A1:C10")], kind: .any), in: s)106        #expect(s.sheet.meta.validations.isEmpty)107    }108109    @Test func circlesInvalidData() {110        let s = Fixture.session()111        Fixture.fill(s, [["5", "ok"], ["12", "toolongtext"], ["2.5", ""], ["-1", "fine"]])112        ValidationActions.apply(DataValidation(ranges: [Fixture.rg("A1:A10")], kind: .whole, op: "between", formula1: "1", formula2: "10"), in: s)113        ValidationActions.apply(DataValidation(ranges: [Fixture.rg("B1:B10")], kind: .textLength, op: "lessThanOrEqual", formula1: "5"), in: s)114        let v = ValidationActions.violations(in: s)115        #expect(v.total == 4)116        #expect(v.items.map(\.cell.a1) == ["A2", "B2", "A3", "A4"])117        #expect(v.items.allSatisfy { !$0.reason.isEmpty && !$0.value.isEmpty })118        #expect(ValidationActions.violations(in: s, limit: 2).items.count == 2)119        s.perform("fix") { e in e.set(sheet: 0, row: 1, col: 0, .value(.number(7))) }120        #expect(ValidationActions.violations(in: s).items.map(\.cell.a1) == ["B2", "A3", "A4"])121    }122}123124@MainActor @Suite struct RangeMathTests {125    @Test func subtract() {126        let r = Fixture.rg("A1:D10")127        #expect(RangeMath.subtract(r, minus: Fixture.rg("F1:G2")) == [r])128        #expect(RangeMath.subtract(r, minus: r).isEmpty)129        #expect(RangeMath.subtract(r, minus: Fixture.rg("A1:Z100")).isEmpty)130        let parts = RangeMath.subtract(r, minus: Fixture.rg("B3:C4"))131        #expect(parts.map(\.count).reduce(0, +) == 40 - 4)132        for (i, a) in parts.enumerated() { for b in parts[(i + 1)...] { #expect(!a.intersects(b)) } }133        #expect(parts.allSatisfy { !$0.intersects(Fixture.rg("B3:C4")) && r.contains($0) })134        #expect(RangeMath.subtract([r, Fixture.rg("F1:F3")], minus: [Fixture.rg("A1:D9"), Fixture.rg("F2")]) == [Fixture.rg("A10:D10"), Fixture.rg("F1"), Fixture.rg("F3")])135    }136137    @Test func parsingAndText() {138        #expect(RangeMath.parseList("A1:B5, D2:D9") == [Fixture.rg("A1:B5"), Fixture.rg("D2:D9")])139        #expect(RangeMath.parseList("$A$1;c:c") == [Fixture.rg("A1"), Fixture.rg("C:C")])140        #expect(RangeMath.parseList("") == nil && RangeMath.parseList("A1, nope") == nil)141        #expect(RangeMath.text([Fixture.rg("A1:B5"), Fixture.rg("D2")]) == "A1:B5, D2")142        #expect(RangeMath.absoluteA1(Fixture.rg("B2:C9")) == "$B$2:$C$9" && RangeMath.absoluteA1(Fixture.rg("B2")) == "$B$2" && RangeMath.absoluteA1(Fixture.rg("B:C")) == "$B:$C")143    }144}145146@MainActor @Suite struct NameTests {147    @Test func nameValidation() {148        let s = Fixture.sales()149        s.perform("t") { e in150            e.setDefinedName(DefinedName(name: "TaxRate", formula: "Sales!$C$2"))151            _ = try? TableActions.create(engine: e, sheet: 0, range: Fixture.rg("A1:D7"), options: .init(name: "SalesData"))152        }153        let wb = s.workbook154        #expect(NameRules.validate(name: "Growth", in: wb) == nil)155        #expect(NameRules.validate(name: "_x.y_2", in: wb) == nil)156        #expect(NameRules.validate(name: "Taux_Élevé", in: wb) == nil)157        #expect(NameRules.validate(name: "  ", in: wb) == .empty)158        #expect(NameRules.validate(name: "2fast", in: wb) == .invalidStart)159        #expect(NameRules.validate(name: "my name", in: wb) == .invalidCharacter(" "))160        #expect(NameRules.validate(name: "a-b", in: wb) == .invalidCharacter("-"))161        #expect(NameRules.validate(name: "A1", in: wb) == .looksLikeReference)162        #expect(NameRules.validate(name: "xfd1048576", in: wb) == .looksLikeReference)163        #expect(NameRules.validate(name: "R1C1", in: wb) == .looksLikeReference)164        #expect(NameRules.validate(name: "c", in: wb) == .looksLikeReference)165        #expect(NameRules.validate(name: "Rate", in: wb) == nil)166        #expect(NameRules.validate(name: "true", in: wb) == .reserved)167        #expect(NameRules.validate(name: "taxrate", in: wb) == .duplicate)168        #expect(NameRules.validate(name: "taxrate", in: wb, replacing: "TaxRate") == nil)169        #expect(NameRules.validate(name: "salesdata", in: wb) == .tableConflict)170        #expect(NameRules.validate(name: String(repeating: "a", count: 256), in: wb) == .tooLong)171        #expect(NameRules.validate(reference: "=Sales!$A$1:$B$5") == .success("Sales!$A$1:$B$5"))172        #expect(NameRules.validate(reference: " ") == .failure(.emptyReference))173        #expect(NameRules.validate(reference: "SUM(") == .failure(.invalidReference))174        #expect(NameRules.reference(sheet: "My Sheet", range: Fixture.rg("B2:C3")) == "'My Sheet'!$B$2:$C$3")175        #expect(NameRules.suggestion(for: Fixture.rg("B2:B7"), in: s.sheet, workbook: wb) == "Units")176    }177178    @Test func addEditDeleteNavigate() {179        let s = Fixture.sales()180        #expect(NameActions.add(name: "Units", reference: "=Sales!$B$2:$B$7", in: s) == nil)181        #expect(NameActions.add(name: "Units", reference: "Sales!$B$2", in: s) == .duplicate)182        #expect(NameActions.add(name: "Bad name", reference: "Sales!$B$2", in: s) == .invalidCharacter(" "))183        #expect(NameActions.add(name: "Other", reference: "((", in: s) == .invalidReference)184        var entries = NameActions.entries(s)185        #expect(entries.map(\.name.name) == ["Units"] && entries[0].preview == "6 × 1 cells" && !entries[0].isBroken)186        Fixture.fill(s, at: "F1", [["=SUM(Units)"]])187        #expect(Fixture.value(s, "F1") == .number(95))188        #expect(NameActions.update(entries[0].name, reference: "Sales!$B$2:$B$3", in: s) == nil)189        #expect(Fixture.value(s, "F1") == .number(30))190        s.undo()191        #expect(Fixture.value(s, "F1") == .number(95))192        s.activateSheet(1)193        #expect(NameActions.go(to: entries[0].name, in: s))194        #expect(s.sheetIndex == 0 && s.selection.primary == Fixture.rg("B2:B7"))195        #expect(NameActions.add(name: "Lost", reference: "Nowhere!$A$1", in: s) == nil)196        entries = NameActions.entries(s)197        #expect(entries.first { $0.name.name == "Lost" }?.isBroken == true)198        NameActions.delete(entries.first { $0.name.name == "Lost" }!.name, in: s)199        #expect(NameActions.entries(s).map(\.name.name) == ["Units"])200    }201}202
Files main
Menu

Theme