SPB Git

Repository Public

RulesConditionalTests.swift 31.5 KB 536 lines swift

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

Theme