Repository Public
1import Foundation2import Testing3import SheetCore4@testable import Audit56/// One-sheet workbook harness for the rules and tables tests.7final class RulesTestBook {8 let wb = Workbook(sheetNames: ["Sheet1"])9 let engine: CalcEngine10 let parser = InputParser(locale: Locale(identifier: "en_US"))11 var sheet: Sheet { wb.sheets[0] }12 init() { engine = CalcEngine(workbook: wb) }1314 func set(_ a1: String, _ text: String) {15 let ref = CellRef(a1: a1)!16 try! engine.setInput(sheet: 0, row: ref.row, col: ref.col, text, parser: parser)17 engine.recalculate()18 }19 func set(_ a1: String, _ v: Value) { engine.set(sheet: 0, a1, .value(v)) }20 /// Fills a column downwards from `start` with typed texts ("" leaves the cell empty).21 func column(_ start: String, _ texts: [String]) {22 let ref = CellRef(a1: start)!23 for (i, t) in texts.enumerated() where !t.isEmpty {24 try! engine.setInput(sheet: 0, row: ref.row + i, col: ref.col, t, parser: parser)25 }26 engine.recalculate()27 }28 func rules(_ list: [ConditionalFormat]) { engine.editMeta(sheet: 0) { $0.conditionalFormats = list } }29 func rule(_ ranges: String, _ kind: ConditionalRuleKind, style: DiffStyle = DiffStyle(bold: true), priority: Int = 1, stop: Bool = false) -> ConditionalFormat {30 var cf = ConditionalFormat(ranges: ranges.split(separator: ",").map { RangeRef(a1: String($0))! }, kind: kind, style: style, priority: priority)31 cf.stopIfTrue = stop32 return cf33 }34 /// A1 names of the cells of `range` that received any effect, sorted row-major.35 func hits(_ f: ConditionalFormatter, _ range: String) -> [String] {36 f.effects(sheet: 0, visible: RangeRef(a1: range)!).keys.sorted().map(\.a1)37 }38 func effect(_ f: ConditionalFormatter, _ a1: String) -> ConditionalEffect? {39 let r = CellRef(a1: a1)!40 return f.effect(sheet: 0, row: r.row, col: r.col)41 }42}4344private let red = RGBA(r: 255, g: 0, b: 0), green = RGBA(r: 0, g: 255, b: 0), yellow = RGBA(r: 255, g: 255, b: 0), blue = RGBA(r: 0, g: 0, b: 255)4546@Suite struct ConditionalFormatterTests {47 /// A1:A10 = 1…10.48 func oneToTen() -> RulesTestBook {49 let b = RulesTestBook()50 b.column("A1", (1...10).map(String.init))51 return b52 }5354 @Test func cellIsOperators() {55 let b = oneToTen()56 let f = ConditionalFormatter(engine: b.engine)57 let cases: [(String, [String], [String])] = [58 ("greaterThan", ["7"], ["A8", "A9", "A10"]),59 ("lessThan", ["3"], ["A1", "A2"]),60 ("greaterThanOrEqual", ["9"], ["A9", "A10"]),61 ("lessThanOrEqual", ["2"], ["A1", "A2"]),62 ("equal", ["4"], ["A4"]),63 ("notEqual", ["4"], ["A1", "A2", "A3", "A5", "A6", "A7", "A8", "A9", "A10"]),64 ("between", ["3", "5"], ["A3", "A4", "A5"]),65 ("between", ["5", "3"], ["A3", "A4", "A5"]), // bounds given in reverse order66 ("notBetween", ["2", "9"], ["A1", "A10"]),67 ("equal", ["2*3"], ["A6"]), // operand is an expression68 ]69 for (op, operands, expected) in cases {70 b.rules([b.rule("A1:A10", .cellIs(op: op, formulas: operands))])71 #expect(b.hits(f, "A1:A10") == expected, "\(op) \(operands)")72 }73 b.rules([b.rule("A1:A10", .cellIs(op: "noSuchOp", formulas: ["1"]))])74 #expect(b.hits(f, "A1:A10").isEmpty)75 b.rules([b.rule("A1:A10", .cellIs(op: "between", formulas: ["1"]))]) // missing second operand76 #expect(b.hits(f, "A1:A10").isEmpty)77 }7879 @Test func cellIsEffectCarriesTheRuleStyle() {80 let b = oneToTen()81 let f = ConditionalFormatter(engine: b.engine)82 var style = DiffStyle(bold: true, italic: nil, textColor: red, fill: yellow)83 style.strikethrough = true84 style.numberFormat = "0.00"85 b.rules([b.rule("A1:A10", .cellIs(op: "equal", formulas: ["10"]), style: style)])86 let e = b.effect(f, "A10")87 #expect(e?.style == style)88 #expect(e?.effectiveFill == yellow)89 #expect(e?.matchedRules == [0])90 #expect(e?.scaleFill == nil && e?.dataBar == nil && e?.icon == nil)91 #expect(b.effect(f, "A9") == nil)92 }9394 @Test func cellIsRelativeAndAbsoluteOperands() {95 let b = RulesTestBook()96 b.column("A1", ["5", "5", "5", "5", "5"])97 b.column("B1", ["1", "9", "5", "6", "2"])98 let f = ConditionalFormatter(engine: b.engine)99 // relative: each B cell is compared with the A cell of its own row100 b.rules([b.rule("B1:B5", .cellIs(op: "greaterThan", formulas: ["A1"]))])101 #expect(b.hits(f, "A1:C5") == ["B2", "B4"])102 // absolute: every cell is compared with $B$4 (= 6)103 b.rules([b.rule("B1:B5", .cellIs(op: "lessThan", formulas: ["$B$4"]))])104 #expect(b.hits(f, "A1:C5") == ["B1", "B3", "B5"])105 // mixed: $A1 keeps the column, shifts the row106 b.set("A2", "10")107 b.rules([b.rule("B1:B5", .cellIs(op: "lessThan", formulas: ["$A1"]))])108 #expect(b.hits(f, "A1:C5") == ["B1", "B2", "B5"])109 }110111 @Test func cellIsBlanksTextAndErrors() {112 let b = RulesTestBook()113 b.column("A1", ["3", "", "abc", "=1/0", "9"])114 let f = ConditionalFormatter(engine: b.engine)115 // a blank compares as 0, like the formula =A2<5 would116 b.rules([b.rule("A1:A5", .cellIs(op: "lessThan", formulas: ["5"]))])117 #expect(b.hits(f, "A1:A5") == ["A1", "A2"])118 // text sorts after every number in the spreadsheet ordering; errors never match119 b.rules([b.rule("A1:A5", .cellIs(op: "greaterThan", formulas: ["5"]))])120 #expect(b.hits(f, "A1:A5") == ["A3", "A5"])121 b.rules([b.rule("A1:A5", .cellIs(op: "equal", formulas: ["\"ABC\""]))])122 #expect(b.hits(f, "A1:A5") == ["A3"]) // case-insensitive123 b.rules([b.rule("A1:A5", .cellIs(op: "greaterThan", formulas: ["1/0"]))]) // operand is an error124 #expect(b.hits(f, "A1:A5").isEmpty)125 }126127 @Test func formulaRulesShiftRelativeReferences() {128 let b = oneToTen()129 b.column("B1", ["x", "x", "x", "x", "x"])130 b.column("C1", ["0", "5", "0", "5", "0"])131 let f = ConditionalFormatter(engine: b.engine)132 // $A1: the whole row is formatted when its A cell exceeds 3133 b.rules([b.rule("A1:C5", .formula("=$A1>3"))])134 #expect(b.hits(f, "A1:C10") == ["A4", "B4", "C4", "A5", "B5", "C5"])135 // fully relative reference combined with an absolute range136 b.rules([b.rule("A1:A10", .formula("=A1=MAX($A$1:$A$10)"))])137 #expect(b.hits(f, "A1:A10") == ["A10"])138 // reference to the next row: A1 is formatted when A2 is even-indexed 5 in column C139 b.rules([b.rule("A1:A4", .formula("=C2=5"))])140 #expect(b.hits(f, "A1:A10") == ["A1", "A3"])141 // numbers are truthy when non-zero; text, errors and unparsable formulas are not142 b.rules([b.rule("C1:C5", .formula("=C1"))])143 #expect(b.hits(f, "C1:C5") == ["C2", "C4"])144 b.rules([b.rule("B1:B5", .formula("=B1"))])145 #expect(b.hits(f, "B1:B5").isEmpty)146 b.rules([b.rule("A1:A5", .formula("=1/0"))])147 #expect(b.hits(f, "A1:A5").isEmpty)148 b.rules([b.rule("A1:A5", .formula("=SUM("))])149 #expect(b.hits(f, "A1:A5").isEmpty)150 b.rules([b.rule("A1:A5", .formula("=TRUE"))])151 #expect(b.hits(f, "A1:A5").count == 5)152 }153154 @Test func formulaRuleWithSeveralRangesUsesTheFirstRangeAsAnchor() {155 let b = RulesTestBook()156 b.column("A1", ["1", "2", "3"])157 b.column("C1", ["5", "0", "7"])158 let f = ConditionalFormatter(engine: b.engine)159 b.rules([b.rule("A1:A3,C1:C3", .formula("=A1>1"))])160 // in column C the reference has shifted two columns right: C1>1, C2>1, C3>1161 #expect(b.hits(f, "A1:C3") == ["C1", "A2", "A3", "C3"])162 // a reference shifted off the sheet becomes #REF! and the rule does not apply163 b.rules([b.rule("B2:B3,A1:A1", .formula("=A1>0"))]) // for A1: offset (−1, −1) from anchor B2164 #expect(b.hits(f, "A1:C3").isEmpty == false)165 #expect(b.effect(f, "A1") == nil)166 }167168 @Test func twoPointColorScale() {169 let b = oneToTen()170 b.set("A11", "text")171 let f = ConditionalFormatter(engine: b.engine)172 b.rules([b.rule("A1:A12", .colorScale(points: [ScalePoint(kind: "min", color: red), ScalePoint(kind: "max", color: green)]))])173 #expect(b.effect(f, "A1")?.scaleFill == red)174 #expect(b.effect(f, "A10")?.scaleFill == green)175 #expect(b.effect(f, "A4")?.scaleFill == RGBA(r: 170, g: 85, b: 0)) // (4−1)/9 = 1/3 of the way176 #expect(b.effect(f, "A7")?.scaleFill == RGBA(r: 85, g: 170, b: 0))177 #expect(b.effect(f, "A4")?.style == nil)178 #expect(b.effect(f, "A4")?.effectiveFill == RGBA(r: 170, g: 85, b: 0))179 #expect(b.effect(f, "A11") == nil) // text180 #expect(b.effect(f, "A12") == nil) // blank181 // explicit numeric points clamp outside values182 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "num", value: "2", color: red), ScalePoint(kind: "num", value: "8", color: green)]))])183 #expect(b.effect(f, "A1")?.scaleFill == red)184 #expect(b.effect(f, "A2")?.scaleFill == red)185 #expect(b.effect(f, "A5")?.scaleFill == RGBA(r: 128, g: 128, b: 0)) // 127.5 rounds away from zero186 #expect(b.effect(f, "A8")?.scaleFill == green)187 #expect(b.effect(f, "A10")?.scaleFill == green)188 // alpha is interpolated too189 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "min", color: RGBA(r: 0, g: 0, b: 0, a: 0)), ScalePoint(kind: "max", color: RGBA(r: 90, g: 180, b: 9, a: 180))]))])190 #expect(b.effect(f, "A6")?.scaleFill == RGBA(r: 50, g: 100, b: 5, a: 100)) // 5/9191 }192193 @Test func threePointColorScaleAndPointKinds() {194 let b = oneToTen()195 b.set("D1", "4")196 let f = ConditionalFormatter(engine: b.engine)197 // midpoint at the 50th percentile: PERCENTILE.INC(1…10, 0.5) = 5.5198 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "min", color: red), ScalePoint(kind: "percentile", value: "50", color: yellow),199 ScalePoint(kind: "max", color: green)]))])200 #expect(b.effect(f, "A1")?.scaleFill == red)201 #expect(b.effect(f, "A3")?.scaleFill == RGBA(r: 255, g: 113, b: 0)) // (3−1)/4.5 → 113.33202 #expect(b.effect(f, "A8")?.scaleFill == RGBA(r: 113, g: 255, b: 0)) // (8−5.5)/4.5203 #expect(b.effect(f, "A10")?.scaleFill == green)204 // percent points: min + p·(max − min) → 1.9 and 9.1205 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "percent", value: "10", color: RGBA(r: 0, g: 0, b: 0)),206 ScalePoint(kind: "percent", value: "90", color: RGBA(r: 72, g: 144, b: 216))]))])207 #expect(b.effect(f, "A1")?.scaleFill == RGBA(r: 0, g: 0, b: 0))208 #expect(b.effect(f, "A10")?.scaleFill == RGBA(r: 72, g: 144, b: 216))209 #expect(b.effect(f, "A2")?.scaleFill == RGBA(r: 1, g: 2, b: 3)) // (2−1.9)/7.2 = 1/72210 // formula point reading a cell: $D$1 = 4211 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "formula", value: "=$D$1", color: red), ScalePoint(kind: "max", color: green)]))])212 #expect(b.effect(f, "A4")?.scaleFill == red)213 #expect(b.effect(f, "A7")?.scaleFill == RGBA(r: 128, g: 128, b: 0))214 // a point without colour, an unknown kind or a single point disables the rule215 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "min"), ScalePoint(kind: "max", color: green)]))])216 #expect(b.hits(f, "A1:A10").isEmpty)217 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "bogus", color: red), ScalePoint(kind: "max", color: green)]))])218 #expect(b.hits(f, "A1:A10").isEmpty)219 b.rules([b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "min", color: red)]))])220 #expect(b.hits(f, "A1:A10").isEmpty)221 }222223 @Test func percentilePointsUseTheInclusiveDefinition() {224 let b = RulesTestBook()225 b.column("A1", ["10", "20", "30", "40", "50"])226 let f = ConditionalFormatter(engine: b.engine)227 // PERCENTILE.INC gives 20 and 40 (PERCENTILE.EXC would give 15 and 45)228 let dark = RGBA(r: 0, g: 0, b: 0), light = RGBA(r: 200, g: 100, b: 40)229 b.rules([b.rule("A1:A5", .colorScale(points: [ScalePoint(kind: "percentile", value: "25", color: dark), ScalePoint(kind: "percentile", value: "75", color: light)]))])230 #expect(b.effect(f, "A2")?.scaleFill == dark) // exactly at the lower point (EXC would give (33,17,7))231 #expect(b.effect(f, "A3")?.scaleFill == RGBA(r: 100, g: 50, b: 20))232 #expect(b.effect(f, "A4")?.scaleFill == light)233 #expect(AuditRuleSupport.percentileInc([10, 20, 30, 40], 0.25) == 17.5)234 #expect(AuditRuleSupport.percentileInc([10, 20, 30, 40], 0) == 10)235 #expect(AuditRuleSupport.percentileInc([10, 20, 30, 40], 1) == 40)236 #expect(AuditRuleSupport.percentileInc([7], 0.3) == 7)237 #expect(AuditRuleSupport.percentileInc([], 0.3) == nil)238 #expect(AuditRuleSupport.percentileInc([1, 2], 1.5) == nil)239 }240241 @Test func colorScaleOnConstantData() {242 let b = RulesTestBook()243 b.column("A1", ["4", "4", "4"])244 let f = ConditionalFormatter(engine: b.engine)245 b.rules([b.rule("A1:A3", .colorScale(points: [ScalePoint(kind: "min", color: red), ScalePoint(kind: "max", color: green)]))])246 #expect(b.effect(f, "A2")?.scaleFill == red) // documented: no spread → the first colour247 }248249 @Test func dataBars() {250 let b = oneToTen()251 b.column("B1", ["-5", "0", "5", "10"])252 b.column("C1", ["2", "4", "8"])253 b.column("D1", ["3", "3"])254 b.column("E1", ["-8", "-2", "-4"])255 let f = ConditionalFormatter(engine: b.engine)256 let minP = ScalePoint(kind: "min"), maxP = ScalePoint(kind: "max")257 b.rules([b.rule("A1:A10", .dataBar(min: minP, max: maxP, color: blue)),258 b.rule("B1:B4", .dataBar(min: minP, max: maxP, color: blue)),259 b.rule("C1:C3", .dataBar(min: ScalePoint(kind: "autoMin"), max: ScalePoint(kind: "autoMax"), color: green)),260 b.rule("D1:D2", .dataBar(min: minP, max: maxP, color: blue)),261 b.rule("E1:E3", .dataBar(min: minP, max: ScalePoint(kind: "autoMax"), color: red))])262 // positive data: bars start at the left edge263 #expect(b.effect(f, "A1")?.dataBar == DataBarEffect(start: 0, end: 0, axis: 0, color: blue, isNegative: false))264 #expect(b.effect(f, "A10")?.dataBar?.fraction == 1)265 #expect(abs((b.effect(f, "A4")?.dataBar?.fraction ?? 0) - 1.0 / 3) < 1e-15)266 #expect(b.effect(f, "A4")?.dataBar?.color == blue)267 // data straddling zero: axis at −min/(max − min) = 1/3; negative bars extend leftwards from it268 let third = 5.0 / 15269 #expect(b.effect(f, "B1")?.dataBar == DataBarEffect(start: 0, end: third, axis: third, color: blue, isNegative: true))270 #expect(b.effect(f, "B2")?.dataBar == DataBarEffect(start: third, end: third, axis: third, color: blue, isNegative: false))271 #expect(b.effect(f, "B3")?.dataBar == DataBarEffect(start: third, end: 10.0 / 15, axis: third, color: blue, isNegative: false))272 #expect(b.effect(f, "B4")?.dataBar == DataBarEffect(start: third, end: 1, axis: third, color: blue, isNegative: false))273 // automatic bounds include zero: 2, 4, 8 → 25 %, 50 %, 100 %274 #expect(b.effect(f, "C1")?.dataBar?.fraction == 0.25)275 #expect(b.effect(f, "C2")?.dataBar?.fraction == 0.5)276 #expect(b.effect(f, "C3")?.dataBar?.fraction == 1)277 // no spread: full bars278 #expect(b.effect(f, "D1")?.dataBar == DataBarEffect(start: 0, end: 1, axis: 0, color: blue, isNegative: false))279 // all-negative data with max 0: axis on the right edge280 #expect(b.effect(f, "E1")?.dataBar == DataBarEffect(start: 0, end: 1, axis: 1, color: red, isNegative: true))281 #expect(b.effect(f, "E2")?.dataBar == DataBarEffect(start: 0.75, end: 1, axis: 1, color: red, isNegative: true))282 #expect(b.effect(f, "E3")?.dataBar?.fraction == 0.5)283 #expect(b.effect(f, "A1")?.style == nil && b.effect(f, "A1")?.scaleFill == nil)284 }285286 @Test func iconSets() {287 let b = oneToTen()288 let f = ConditionalFormatter(engine: b.engine)289 func icons(_ range: String = "A1:A10") -> [Int] {290 let e = f.effects(sheet: 0, visible: RangeRef(a1: range)!)291 return e.keys.sorted().map { e[$0]!.icon!.index }292 }293 // OOXML form: one point per icon, the first is the floor. Cuts: 1 + 0.33·9 = 3.97 and 1 + 0.67·9 = 7.03294 let three = [ScalePoint(kind: "percent", value: "0"), ScalePoint(kind: "percent", value: "33"), ScalePoint(kind: "percent", value: "67")]295 b.rules([b.rule("A1:A10", .iconSet(name: "3Arrows", thresholds: three, reversed: false))])296 #expect(icons() == [0, 0, 0, 1, 1, 1, 1, 2, 2, 2])297 #expect(b.effect(f, "A10")?.icon == IconEffect(set: "3Arrows", index: 2, count: 3))298 #expect(b.effect(f, "A10")?.icon?.id == "3Arrows#2")299 #expect(b.effect(f, "A1")?.icon?.id == "3Arrows#0")300 b.rules([b.rule("A1:A10", .iconSet(name: "3Arrows", thresholds: three, reversed: true))])301 #expect(icons() == [2, 2, 2, 1, 1, 1, 1, 0, 0, 0])302 // cut-point form (one fewer point than icons); a value equal to a cut belongs to the upper bucket303 b.rules([b.rule("A1:A10", .iconSet(name: "3TrafficLights1", thresholds: [ScalePoint(kind: "num", value: "4"), ScalePoint(kind: "num", value: "8")], reversed: false))])304 #expect(icons() == [0, 0, 0, 1, 1, 1, 1, 2, 2, 2])305 // five icons on quintiles of the range: cuts 2.8, 4.6, 6.4, 8.2306 let five = ["0", "20", "40", "60", "80"].map { ScalePoint(kind: "percent", value: $0) }307 b.rules([b.rule("A1:A10", .iconSet(name: "5Rating", thresholds: five, reversed: false))])308 #expect(icons() == [0, 0, 1, 1, 2, 2, 3, 3, 4, 4])309 #expect(b.effect(f, "A9")?.icon?.count == 5)310 // percentile thresholds (inclusive): 33rd = 3.97, 67th = 7.03 on 1…10311 let pct = [ScalePoint(kind: "percentile", value: "0"), ScalePoint(kind: "percentile", value: "33"), ScalePoint(kind: "percentile", value: "67")]312 b.rules([b.rule("A1:A10", .iconSet(name: "3Symbols", thresholds: pct, reversed: false))])313 #expect(icons() == [0, 0, 0, 1, 1, 1, 1, 2, 2, 2])314 // a set name without a leading digit: the number of points is the number of icons315 b.rules([b.rule("A1:A10", .iconSet(name: "Custom", thresholds: [ScalePoint(kind: "min"), ScalePoint(kind: "num", value: "6")], reversed: false))])316 #expect(icons() == [0, 0, 0, 0, 0, 1, 1, 1, 1, 1])317 // non-numeric cells get no icon318 b.set("A3", "n/a")319 #expect(b.effect(f, "A3") == nil)320 }321322 @Test func duplicatesAndUnique() {323 let b = RulesTestBook()324 b.column("A1", ["a", "A", "b", "1", "1", "2", "", "=NA()", "=NA()"])325 let f = ConditionalFormatter(engine: b.engine)326 b.rules([b.rule("A1:A9", .duplicates(unique: false))])327 #expect(b.hits(f, "A1:A9") == ["A1", "A2", "A4", "A5"]) // text is case-insensitive; blanks and errors are ignored328 b.rules([b.rule("A1:A9", .duplicates(unique: true))])329 #expect(b.hits(f, "A1:A9") == ["A3", "A6"])330 // duplicates are counted across all the ranges of the rule331 b.set("C1", "b")332 b.rules([b.rule("A1:A9,C1:C1", .duplicates(unique: false))])333 #expect(b.hits(f, "A1:C9") == ["A1", "C1", "A2", "A3", "A4", "A5"])334 }335336 @Test func topAndBottom() {337 let b = oneToTen()338 b.column("B1", ["5", "5", "5", "1"])339 let f = ConditionalFormatter(engine: b.engine)340 let cases: [(Int, Bool, Bool, [String])] = [341 (3, false, false, ["A8", "A9", "A10"]),342 (2, false, true, ["A1", "A2"]),343 (20, true, false, ["A9", "A10"]), // 20 % of 10 values344 (15, true, true, ["A1"]), // INT(1.5) = 1345 (5, true, false, ["A10"]), // INT(0.5) = 0 → at least one346 (50, false, false, (1...10).map { "A\($0)" }),347 (0, false, false, []),348 ]349 for (rank, percent, bottom, expected) in cases {350 b.rules([b.rule("A1:A10", .top(rank: rank, percent: percent, bottom: bottom))])351 #expect(b.hits(f, "A1:A10") == expected, "top \(rank) percent=\(percent) bottom=\(bottom)")352 }353 // ties at the cut-off are all included354 b.rules([b.rule("B1:B4", .top(rank: 1, percent: false, bottom: false))])355 #expect(b.hits(f, "B1:B4") == ["B1", "B2", "B3"])356 b.rules([b.rule("B1:B4", .top(rank: 1, percent: false, bottom: true))])357 #expect(b.hits(f, "B1:B4") == ["B4"])358 }359360 @Test func aboveAndBelowAverage() {361 let b = oneToTen()362 b.column("B1", ["1", "2", "3", "label"])363 let f = ConditionalFormatter(engine: b.engine)364 b.rules([b.rule("A1:A10", .aboveAverage(above: true, equal: false))])365 #expect(b.hits(f, "A1:A10") == ["A6", "A7", "A8", "A9", "A10"]) // mean 5.5366 b.rules([b.rule("A1:A10", .aboveAverage(above: false, equal: false))])367 #expect(b.hits(f, "A1:A10") == ["A1", "A2", "A3", "A4", "A5"])368 b.rules([b.rule("B1:B4", .aboveAverage(above: true, equal: false))])369 #expect(b.hits(f, "B1:B4") == ["B3"]) // mean 2, text ignored370 b.rules([b.rule("B1:B4", .aboveAverage(above: true, equal: true))])371 #expect(b.hits(f, "B1:B4") == ["B2", "B3"])372 b.rules([b.rule("B1:B4", .aboveAverage(above: false, equal: true))])373 #expect(b.hits(f, "B1:B4") == ["B1", "B2"])374 b.rules([b.rule("B1:B4", .aboveAverage(above: false, equal: false))])375 #expect(b.hits(f, "B1:B4") == ["B1"])376 }377378 @Test func containsTextBlanksAndErrors() {379 let b = RulesTestBook()380 b.column("A1", ["Banana", "apple", "ANT", "1234", "", "=1/0"])381 b.set("B1", .text(""))382 b.set("B2", .text(" "))383 b.set("B3", "x")384 b.set("B4", "0")385 let f = ConditionalFormatter(engine: b.engine)386 b.rules([b.rule("A1:A6", .containsText("an"))])387 #expect(b.hits(f, "A1:A6") == ["A1", "A3"])388 b.rules([b.rule("A1:A6", .containsText("23"))])389 #expect(b.hits(f, "A1:A6") == ["A4"]) // numbers are searched as displayed390 b.rules([b.rule("A1:A6", .containsText(""))])391 #expect(b.hits(f, "A1:A6").isEmpty)392 b.rules([b.rule("A1:A6", .errors(true))])393 #expect(b.hits(f, "A1:A6") == ["A6"])394 b.rules([b.rule("A1:A6", .errors(false))])395 #expect(b.hits(f, "A1:A6") == ["A1", "A2", "A3", "A4", "A5"])396 b.rules([b.rule("A1:B6", .blanks(true))])397 #expect(b.hits(f, "A1:B6") == ["B1", "B2", "A5", "B5", "B6"]) // empty and whitespace-only text count as blank398 b.rules([b.rule("B1:B5", .blanks(false))])399 #expect(b.hits(f, "B1:B5") == ["B3", "B4"])400 }401402 @Test func priorityMergingAndStopIfTrue() {403 let b = oneToTen()404 let f = ConditionalFormatter(engine: b.engine)405 let low = b.rule("A1:A10", .cellIs(op: "greaterThan", formulas: ["5"]), style: DiffStyle(bold: true, fill: red), priority: 2)406 var high = b.rule("A1:A10", .cellIs(op: "greaterThan", formulas: ["8"]), style: DiffStyle(italic: true, fill: blue), priority: 1)407 b.rules([low, high]) // stored out of priority order on purpose408 var e = b.effect(f, "A9")409 #expect(e?.style == DiffStyle(bold: true, italic: true, fill: blue)) // priority 1 wins the fill, bold is merged in410 #expect(e?.matchedRules == [1, 0])411 #expect(b.effect(f, "A7")?.style == DiffStyle(bold: true, fill: red))412 #expect(b.effect(f, "A7")?.matchedRules == [0])413 #expect(b.effect(f, "A2") == nil)414 // stopIfTrue on the higher-priority rule hides the lower one where it matches415 high.stopIfTrue = true416 b.rules([low, high])417 e = b.effect(f, "A9")418 #expect(e?.style == DiffStyle(italic: true, fill: blue))419 #expect(e?.matchedRules == [1])420 #expect(b.effect(f, "A7")?.style == DiffStyle(bold: true, fill: red)) // rule 1 did not match here, so nothing stops421 // stopIfTrue on the lower-priority rule changes nothing above it422 high.stopIfTrue = false423 var lowStop = low424 lowStop.stopIfTrue = true425 b.rules([lowStop, high])426 #expect(b.effect(f, "A9")?.style == DiffStyle(bold: true, italic: true, fill: blue))427 // equal priorities: the order in the list decides428 let first = b.rule("A1:A10", .cellIs(op: "equal", formulas: ["3"]), style: DiffStyle(fill: yellow), priority: 5)429 let second = b.rule("A1:A10", .cellIs(op: "equal", formulas: ["3"]), style: DiffStyle(textColor: red, fill: green), priority: 5)430 b.rules([first, second])431 #expect(b.effect(f, "A3")?.style == DiffStyle(textColor: red, fill: yellow))432 #expect(b.effect(f, "A3")?.matchedRules == [0, 1])433 }434435 @Test func scalesBarsIconsAndStylesCombine() {436 let b = oneToTen()437 let f = ConditionalFormatter(engine: b.engine)438 let scale = b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "min", color: red), ScalePoint(kind: "max", color: green)]), priority: 3)439 let scale2 = b.rule("A1:A10", .colorScale(points: [ScalePoint(kind: "min", color: blue), ScalePoint(kind: "max", color: blue)]), priority: 4)440 let bar = b.rule("A1:A10", .dataBar(min: ScalePoint(kind: "min"), max: ScalePoint(kind: "max"), color: blue), priority: 2)441 let icon = b.rule("A1:A10", .iconSet(name: "3Arrows", thresholds: [ScalePoint(kind: "num", value: "5"), ScalePoint(kind: "num", value: "9")], reversed: false), priority: 5)442 let style = b.rule("A1:A10", .cellIs(op: "equal", formulas: ["10"]), style: DiffStyle(fill: yellow), priority: 6)443 b.rules([scale, scale2, bar, icon, style])444 let e = b.effect(f, "A10")445 #expect(e?.scaleFill == green) // the first colour scale by priority446 #expect(e?.dataBar?.fraction == 1)447 #expect(e?.icon?.index == 2)448 #expect(e?.style == DiffStyle(fill: yellow))449 #expect(e?.effectiveFill == yellow) // an explicit rule fill is painted over the scale450 #expect(e?.matchedRules == [2, 0, 1, 3, 4])451 #expect(b.effect(f, "A1")?.effectiveFill == red)452 // a colour scale with stopIfTrue stops later rules on numeric cells453 var stopping = scale454 stopping.priority = 1455 stopping.stopIfTrue = true456 b.rules([stopping, bar, icon])457 #expect(b.effect(f, "A10")?.dataBar == nil)458 #expect(b.effect(f, "A10")?.icon == nil)459 #expect(b.effect(f, "A10")?.scaleFill == green)460 }461462 @Test func onlyTheVisibleWindowIsReturned() {463 let b = oneToTen()464 let f = ConditionalFormatter(engine: b.engine)465 b.rules([b.rule("A1:A10", .cellIs(op: "greaterThan", formulas: ["0"])), b.rule("A:A", .cellIs(op: "greaterThan", formulas: ["9"]), style: DiffStyle(italic: true), priority: 2)])466 #expect(b.hits(f, "A3:B5") == ["A3", "A4", "A5"])467 #expect(b.hits(f, "B1:D20").isEmpty)468 #expect(b.hits(f, "A9:A12") == ["A9", "A10"])469 #expect(b.effect(f, "A10")?.style == DiffStyle(bold: true, italic: true)) // whole-column rule range470 #expect(f.effects(sheet: 5, visible: RangeRef(a1: "A1")!).isEmpty) // no such sheet471 b.rules([])472 #expect(b.hits(f, "A1:A10").isEmpty)473 }474475 @Test func statisticsAreComputedOncePerRuleAndRefreshedOnEdit() {476 // 100 000 numeric rows written straight into the model, then a colour scale over the whole column477 let wb = Workbook(sheetNames: ["Big"])478 let n = 100_000479 for r in 0..<n { wb.sheets[0].cells.set(row: r, col: 0, Cell(value: .number(Double(r)))) }480 let engine = CalcEngine(workbook: wb)481 let scale = ConditionalFormat(ranges: [RangeRef(a1: "A:A")!], kind: .colorScale(points: [482 ScalePoint(kind: "min", color: RGBA(r: 0, g: 0, b: 0)), ScalePoint(kind: "percentile", value: "50", color: RGBA(r: 100, g: 100, b: 100)),483 ScalePoint(kind: "max", color: RGBA(r: 200, g: 200, b: 200))]))484 let top = ConditionalFormat(ranges: [RangeRef(a1: "A:A")!], kind: .top(rank: 10, percent: true, bottom: false), style: DiffStyle(bold: true), priority: 2)485 engine.editMeta(sheet: 0) { $0.conditionalFormats = [scale, top] }486 let f = ConditionalFormatter(engine: engine)487 #expect(f.statisticsComputations == 0)488 // scroll through 40 windows of 50 rows489 for k in 0..<40 {490 let r0 = k * 2500491 let e = f.effects(sheet: 0, visible: RangeRef(r0: r0, c0: 0, r1: r0 + 49, c1: 3))492 #expect(e.count == 50)493 }494 #expect(f.statisticsComputations == 2) // one scan per rule, not per window or per cell495 #expect(f.effect(sheet: 0, row: 0, col: 0)?.scaleFill == RGBA(r: 0, g: 0, b: 0))496 #expect(f.effect(sheet: 0, row: n - 1, col: 0)?.scaleFill == RGBA(r: 200, g: 200, b: 200))497 #expect(f.effect(sheet: 0, row: 25_000, col: 0)?.scaleFill == RGBA(r: 50, g: 50, b: 50)) // 25000 / 49999.5 → 50.0005498 #expect(f.effect(sheet: 0, row: 89_999, col: 0)?.style == nil)499 #expect(f.effect(sheet: 0, row: 90_000, col: 0)?.style == DiffStyle(bold: true)) // top 10 % = rows 90000…99999500 #expect(f.statisticsComputations == 2)501 // an edit invalidates the cache: the new maximum rescales everything502 engine.set(sheet: 0, row: n - 1, col: 0, .value(.number(199_999)))503 #expect(f.effect(sheet: 0, row: n - 1, col: 0)?.scaleFill == RGBA(r: 200, g: 200, b: 200))504 #expect(f.statisticsComputations == 4) // both rules cover that cell: one rescan each505 #expect(f.effect(sheet: 0, row: 25_000, col: 0)?.scaleFill == RGBA(r: 50, g: 50, b: 50)) // median unchanged506 _ = f.effect(sheet: 0, row: 90_000, col: 0)507 #expect(f.statisticsComputations == 4)508 // undo is an edit too509 engine.undo()510 _ = f.effects(sheet: 0, visible: RangeRef(r0: 0, c0: 0, r1: 49, c1: 0))511 #expect(f.statisticsComputations == 6)512 // explicit invalidation513 f.invalidate()514 _ = f.effects(sheet: 0, visible: RangeRef(r0: 0, c0: 0, r1: 49, c1: 0))515 #expect(f.statisticsComputations == 8)516 }517518 @Test func effectsFollowEditsAndFormulaResults() {519 let b = RulesTestBook()520 b.column("A1", ["1", "2", "=A1+A2"])521 let f = ConditionalFormatter(engine: b.engine)522 b.rules([b.rule("A1:A3", .cellIs(op: "greaterThan", formulas: ["2"])), b.rule("A1:A3", .aboveAverage(above: true, equal: false), style: DiffStyle(italic: true), priority: 2)])523 #expect(b.hits(f, "A1:A3") == ["A3"])524 b.engine.set(sheet: 0, "A1", .value(.number(10))) // A3 becomes 12; the pending recalculation is done by the formatter525 #expect(b.engine.pendingCount > 0)526 #expect(b.hits(f, "A1:A3") == ["A1", "A3"])527 #expect(b.effect(f, "A1")?.style == DiffStyle(bold: true, italic: true)) // 10 > mean 8528 #expect(b.effect(f, "A3")?.style == DiffStyle(bold: true, italic: true))529 #expect(b.effect(f, "A2") == nil)530 // evaluating rules never touches the undo history531 let label = b.engine.undoLabel, count = b.engine.changeCount532 _ = b.hits(f, "A1:C10")533 #expect(b.engine.undoLabel == label && b.engine.changeCount == count)534 }535}536