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