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