SPB Git

Repository Public

ConditionalFormatter.swift 14.5 KB 313 lines swift

1import Foundation2import SheetCore34/// Evaluates the conditional-format rules of a sheet for the cells the grid is about to draw.5///6/// Conventions7/// - **Priority**: rules are applied by ascending `priority` (1 first); ties keep their order in8///   `SheetMeta.conditionalFormats`. A matching rule with `stopIfTrue` ends the evaluation of the cell.9/// - **Anchor**: formulas (`formula` rules, `cellIs` operands, formula scale points) are written for10///   the top-left cell of the rule's *first* range; for another cell the relative references shift by11///   the cell's offset from that anchor (`$` parts stay fixed).12/// - **cellIs** compares with the spreadsheet ordering (numbers < text < booleans, text13///   case-insensitive). A blank cell compares as 0 / empty text, exactly as `=A1<5` would. Error cells never match.14/// - **Statistics** (min, max, percentiles, average, top-N cut-off, duplicate counts) are taken over the15///   numeric cells of *all* the ranges of the rule. Percent points are `min + p·(max − min)`; percentile16///   points use the inclusive definition (Excel `PERCENTILE.INC`, which is what Excel uses for17///   colour scales and icon sets). Only numbers count: text, booleans, blanks and errors get no scale, bar or icon.18/// - **Caching**: per-rule statistics are computed once and reused until `engine.changeCount` changes19///   (any edit, undo or redo) or `invalidate()` is called. Values that change without an edit20///   (volatile functions) need an explicit `invalidate()`.21///22/// Not thread-safe: use it on the thread that owns the engine.23public final class ConditionalFormatter {24    public let engine: CalcEngine25    /// How many times range statistics have been (re)computed — for diagnostics and tests.26    public private(set) var statisticsComputations = 02728    private var caches: [CacheKey: RuleCache] = [:]29    private var cachedChangeCount = Int.min3031    public init(engine: CalcEngine) { self.engine = engine }3233    /// Drops every cached statistic and parsed formula.34    public func invalidate() {35        caches.removeAll()36        cachedChangeCount = Int.min37    }3839    /// Resolved effects for the cells of `visible` that at least one rule affects.40    public func effects(sheet si: Int, visible: RangeRef) -> [CellRef: ConditionalEffect] {41        guard si >= 0, si < engine.workbook.sheets.count else { return [:] }42        let sheet = engine.workbook.sheets[si]43        let rules = sheet.meta.conditionalFormats44        guard !rules.isEmpty else { return [:] }45        if engine.pendingCount > 0 { engine.recalculate() }46        if engine.changeCount != cachedChangeCount {47            caches.removeAll(keepingCapacity: true)48            cachedChangeCount = engine.changeCount49        }50        let order = rules.indices.sorted { (rules[$0].priority, $0) < (rules[$1].priority, $1) }51        var out: [CellRef: Entry] = [:]52        // Formula evaluation is recursive: run the whole pass on one deep-stack thread instead of one per formula.53        CalcEngine.runDeep {54            for idx in order {55                let rule = rules[idx]56                guard !rule.ranges.isEmpty else { continue }57                for range in rule.ranges {58                    guard let window = range.intersection(visible) else { continue }59                    let cache = self.cache(for: rule, index: idx, sheet: sheet)60                    for r in window.r0...window.r1 {61                        for c in window.c0...window.c1 {62                            let ref = CellRef(r, c)63                            if out[ref]?.stopped == true { continue }64                            if out[ref]?.effect.matchedRules.contains(idx) == true { continue }   // overlapping ranges of one rule65                            guard let m = self.match(rule, cache, si, sheet, r, c) else { continue }66                            var entry = out[ref] ?? Entry()67                            entry.effect.matchedRules.append(idx)68                            switch m {69                            case .style: entry.effect.merge(rule.style)70                            case .fill(let color): if entry.effect.scaleFill == nil { entry.effect.scaleFill = color }71                            case .bar(let bar): if entry.effect.dataBar == nil { entry.effect.dataBar = bar }72                            case .icon(let icon): if entry.effect.icon == nil { entry.effect.icon = icon }73                            }74                            if rule.stopIfTrue { entry.stopped = true }75                            out[ref] = entry76                        }77                    }78                }79            }80        }81        return out.mapValues(\.effect)82    }8384    /// Resolved effect of a single cell, or nil when no rule applies.85    public func effect(sheet si: Int, row: Int, col: Int) -> ConditionalEffect? {86        effects(sheet: si, visible: RangeRef(r0: row, c0: col, r1: row, c1: col))[CellRef(row, col)]87    }8889    // MARK: Internals9091    private struct Entry {92        var effect = ConditionalEffect()93        var stopped = false94    }9596    private struct CacheKey: Hashable {97        var sheetID: Int98        var index: Int99    }100101    private enum Match {102        case style103        case fill(RGBA)104        case bar(DataBarEffect)105        case icon(IconEffect)106    }107108    /// Lazily computed state of one rule.109    private final class RuleCache {110        let rule: ConditionalFormat111        var exprs: [Expr?] = []112        var parsed = false113        /// Values of operand formulas that do not depend on the cell.114        var fixedOperands: [Value?] = []115        var sorted: [Double]?116        var mean: Double?117        var keyCounts: [String: Int]?118        var thresholds: [Double?]?119        var topCut: Double??120        init(rule: ConditionalFormat) { self.rule = rule }121    }122123    private func cache(for rule: ConditionalFormat, index: Int, sheet: Sheet) -> RuleCache {124        let key = CacheKey(sheetID: sheet.id, index: index)125        if let c = caches[key], c.rule == rule { return c }126        let c = RuleCache(rule: rule)127        caches[key] = c128        return c129    }130131    private func numbers(_ cache: RuleCache, _ sheet: Sheet) -> [Double] {132        if let s = cache.sorted { return s }133        statisticsComputations += 1134        var values = [Double]()135        var sum = 0.0136        for range in cache.rule.ranges {137            sheet.cells.forEach(in: range) { _, _, cell in138                if case .number(let n) = cell.computed { values.append(n); sum += n }139            }140        }141        values.sort()142        cache.sorted = values143        cache.mean = values.isEmpty ? nil : sum / Double(values.count)144        return values145    }146147    private func keyCounts(_ cache: RuleCache, _ sheet: Sheet) -> [String: Int] {148        if let k = cache.keyCounts { return k }149        statisticsComputations += 1150        var counts: [String: Int] = [:]151        for range in cache.rule.ranges {152            sheet.cells.forEach(in: range) { _, _, cell in153                if let k = AuditRuleSupport.key(cell.computed) { counts[k, default: 0] += 1 }154            }155        }156        cache.keyCounts = counts157        return counts158    }159160    private func parse(_ cache: RuleCache, _ texts: [String]) {161        guard !cache.parsed else { return }162        cache.parsed = true163        cache.exprs = texts.map { AuditRuleSupport.parse($0) }164        cache.fixedOperands = Array(repeating: nil, count: texts.count)165    }166167    /// Value of the k-th formula of the rule for the given cell.168    private func operand(_ cache: RuleCache, _ k: Int, _ si: Int, _ sheet: Sheet, _ row: Int, _ col: Int) -> Value? {169        guard k < cache.exprs.count, let expr = cache.exprs[k] else { return nil }170        if let fixed = cache.fixedOperands[k] { return fixed }171        let anchor = cache.rule.ranges[0].topLeft172        if AuditRuleSupport.isPositionIndependent(expr) {173            let v = engine.evaluate(expr, sheet: si, at: anchor).scalar174            cache.fixedOperands[k] = v175            return v176        }177        let e = AuditRuleSupport.shifted(expr, dRow: row - anchor.row, dCol: col - anchor.col, sheetName: sheet.name)178        return engine.evaluate(e, sheet: si, at: CellRef(row, col)).scalar179    }180181    private func pointNumber(_ text: String?, _ si: Int, _ anchor: CellRef) -> Double? {182        guard let text else { return nil }183        if let d = Double(text.trimmingCharacters(in: .whitespaces)) { return d }184        return engine.evaluate(text, sheet: si, at: anchor).scalar.numberValue185    }186187    /// Numeric position of a scale point. Kinds (case-insensitive): min, max, num, percent, percentile,188    /// formula, plus autoMin (= min(0, minimum)) and autoMax (= max(0, maximum)).189    private func threshold(_ p: ScalePoint, _ sorted: [Double], _ si: Int, _ anchor: CellRef) -> Double? {190        guard let lo = sorted.first, let hi = sorted.last else { return nil }191        switch p.kind.lowercased() {192        case "min": return lo193        case "max": return hi194        case "automin": return min(0, lo)195        case "automax": return max(0, hi)196        case "num", "formula": return pointNumber(p.value, si, anchor)197        case "percent":198            guard let v = pointNumber(p.value, si, anchor) else { return nil }199            return lo + v / 100 * (hi - lo)200        case "percentile":201            guard let v = pointNumber(p.value, si, anchor) else { return nil }202            return AuditRuleSupport.percentileInc(sorted, v / 100)203        default: return nil204        }205    }206207    private func thresholds(_ cache: RuleCache, _ points: [ScalePoint], _ si: Int, _ sheet: Sheet) -> [Double?] {208        if let t = cache.thresholds { return t }209        let sorted = numbers(cache, sheet)210        let anchor = cache.rule.ranges[0].topLeft211        let t = points.map { threshold($0, sorted, si, anchor) }212        cache.thresholds = t213        return t214    }215216    private func match(_ rule: ConditionalFormat, _ cache: RuleCache, _ si: Int, _ sheet: Sheet, _ row: Int, _ col: Int) -> Match? {217        let v = sheet.value(row, col)218        switch rule.kind {219        case .cellIs(let op, let formulas):220            if v.isError { return nil }221            parse(cache, formulas)222            let a = operand(cache, 0, si, sheet, row, col)223            let b = operand(cache, 1, si, sheet, row, col)224            if a?.isError == true || b?.isError == true { return nil }225            return AuditRuleSupport.compare(op, v, a, b) == true ? .style : nil226227        case .formula(let text):228            parse(cache, [text])229            guard let result = operand(cache, 0, si, sheet, row, col) else { return nil }230            return AuditRuleSupport.truthy(result) ? .style : nil231232        case .colorScale(let points):233            guard case .number(let x) = v, points.count >= 2 else { return nil }234            let t = thresholds(cache, points, si, sheet)235            var stops = [(Double, RGBA)]()236            for (p, th) in zip(points, t) {237                guard let th, let color = p.color else { return nil }238                stops.append((th, color))239            }240            if x <= stops[0].0 { return .fill(stops[0].1) }241            for i in 0..<(stops.count - 1) where x <= stops[i + 1].0 {242                let (t0, c0) = stops[i], (t1, c1) = stops[i + 1]243                return .fill(t1 > t0 ? AuditRuleSupport.mix(c0, c1, (x - t0) / (t1 - t0)) : c1)244            }245            return .fill(stops[stops.count - 1].1)246247        case .dataBar(let minPoint, let maxPoint, let color):248            guard case .number(let x) = v else { return nil }249            let t = thresholds(cache, [minPoint, maxPoint], si, sheet)250            guard let lo = t[0], let hi = t[1] else { return nil }251            if !(hi > lo) { return .bar(DataBarEffect(start: 0, end: 1, axis: 0, color: color, isNegative: x < 0)) }252            let axis = lo >= 0 ? 0 : (hi <= 0 ? 1 : -lo / (hi - lo))253            let pos = max(0, min(1, (x - lo) / (hi - lo)))254            return .bar(DataBarEffect(start: min(axis, pos), end: max(axis, pos), axis: axis, color: color, isNegative: x < 0))255256        case .iconSet(let name, let points, let reversed):257            guard case .number(let x) = v, !points.isEmpty else { return nil }258            let t = thresholds(cache, points, si, sheet)259            let declared = name.first.flatMap { $0.wholeNumberValue }260            // OOXML lists one point per icon, the first being the floor of the lowest bucket;261            // a list one shorter than the icon count is read as the cut points themselves.262            let count = declared ?? points.count263            let cuts = t.count >= count ? Array(t.dropFirst(t.count - count + 1)) : t264            var bucket = 0265            for cut in cuts {266                guard let cut else { return nil }267                if x >= cut { bucket += 1 }268            }269            bucket = min(bucket, count - 1)270            return .icon(IconEffect(set: name, index: reversed ? count - 1 - bucket : bucket, count: count))271272        case .duplicates(let unique):273            guard let k = AuditRuleSupport.key(v) else { return nil }274            let n = keyCounts(cache, sheet)[k] ?? 0275            return (unique ? n == 1 : n > 1) ? .style : nil276277        case .top(let rank, let percent, let bottom):278            guard case .number(let x) = v else { return nil }279            if cache.topCut == nil {280                let sorted = numbers(cache, sheet)281                var n = percent ? max(1, Int(Double(sorted.count) * Double(rank) / 100)) : rank282                n = min(n, sorted.count)283                cache.topCut = .some(n >= 1 && rank >= 1 ? (bottom ? sorted[n - 1] : sorted[sorted.count - n]) : nil)284            }285            guard let cut = cache.topCut ?? nil else { return nil }286            return (bottom ? x <= cut : x >= cut) ? .style : nil287288        case .aboveAverage(let above, let equal):289            guard case .number(let x) = v else { return nil }290            _ = numbers(cache, sheet)291            guard let mean = cache.mean else { return nil }292            let hit = above ? (equal ? x >= mean : x > mean) : (equal ? x <= mean : x < mean)293            return hit ? .style : nil294295        case .containsText(let needle):296            guard !needle.isEmpty, !v.isError, !v.isBlank else { return nil }297            return AuditRuleSupport.display(v).range(of: needle, options: .caseInsensitive) != nil ? .style : nil298299        case .blanks(let wantBlank):300            let blank: Bool301            switch v {302            case .blank: blank = true303            case .text(let s): blank = s.trimmingCharacters(in: .whitespaces).isEmpty304            default: blank = false305            }306            return blank == wantBlank ? .style : nil307308        case .errors(let wantError):309            return v.isError == wantError ? .style : nil310        }311    }312}313
Files main
Menu

Theme