SPB Git

Repository Public

RulesData.swift 15.0 KB 241 lines swift

1import Foundation2import SheetCore34extension ModelAuditor.Scan {5    private struct Where: Hashable { var sheet: Int; var cell: CellRef }67    private func isNumericText(_ v: Value) -> Bool {8        guard case .text(let s) = v else { return false }9        let t = s.trimmingCharacters(in: .whitespaces)10        // identifiers with leading zeros (postal codes, part numbers) are meant to be text11        if t.count > 1, t.hasPrefix("0"), let second = t.dropFirst().first, second.isNumber { return false }12        return Value.parseNumber(s) != nil13    }1415    // MARK: Numeric text in calculations, numbers stored as text1617    mutating func ruleTextNumbers() {18        // text cells that look like numbers, and how formulas use them19        var uses = [Where: [(String, String)]]()          // text cell → (formula address + text, "coerced" | "ignored by SUM")20        var order = [Where]()21        for si in wb.sheets.indices {22            for f in formulas[si] {23                let user = FormulaIntel.address(sheetName(si), f.ref) + " (" + text(f) + ")"24                func operand(_ e: Expr) {25                    var inner = e26                    while case .paren(let x) = inner { inner = x }27                    guard case .cell = inner, let r = FormulaIntel.directRefs(inner, workbook: wb, sheet: si).first else { return }28                    if isNumericText(wb.sheets[r.sheet].value(r.range.r0, r.range.c0)) {29                        let w = Where(sheet: r.sheet, cell: r.range.topLeft)30                        if uses[w] == nil { uses[w] = [] ; order.append(w) }31                        if !(uses[w]!.contains { $0.0 == user }) { uses[w]!.append((user, "converted to a number by the arithmetic")) }32                    }33                }34                f.expr.walk { node in35                    switch node {36                    case .binary(let op, let a, let b) where [.add, .sub, .mul, .div, .pow].contains(op): operand(a); operand(b)37                    case .unary(_, let x), .percent(let x): operand(x)38                    case .call(let name, let args) where options.aggregateFunctions.contains(name):39                        for a in args {40                            for r in FormulaIntel.directRefs(a, workbook: wb, sheet: si) {41                                switch a { case .cell, .range: break; default: continue }42                                wb.sheets[r.sheet].cells.forEach(in: r.range) { row, col, c in43                                    guard isNumericText(c.computed) else { return }44                                    let w = Where(sheet: r.sheet, cell: CellRef(row, col))45                                    if uses[w] == nil { uses[w] = []; order.append(w) }46                                    if !(uses[w]!.contains { $0.0 == user }) { uses[w]!.append((user, "silently ignored by \(name)")) }47                                }48                            }49                        }50                    default: break51                    }52                }53            }54        }55        if options.rules.contains(.textNumberInArithmetic) {56            for w in order.sorted(by: { ($0.sheet, $0.cell) < ($1.sheet, $1.cell) }) {57                let list = uses[w]!58                let v = wb.sheets[w.sheet].value(w.cell.row, w.cell.col)59                let ignored = list.contains { $0.1.hasPrefix("silently") }60                add(.textNumberInArithmetic, .warning, sheet: w.sheet, cells: [w.cell],61                    evidence: "\(w.cell.a1) holds the text \(FormulaIntel.preview(v)), not a number; it is " + list.prefix(4).map { $0.1 + " in " + $0.0 }.joined(separator: "; ")62                        + (list.count > 4 ? "; … (\(list.count) formulas)" : "") + ".",63                    explanation: ignored ? "Aggregates such as SUM skip text, so this value is left out of the total without any warning, while arithmetic operators would convert it."64                                         : "The value only takes part in the calculation because the operator converts text to a number; aggregates such as SUM would skip it.",65                    action: "Convert the cell to a real number (re-enter it, or use VALUE / Text to Columns).")66            }67        }68        guard options.rules.contains(.numberStoredAsText) else { return }69        for (si, sheet) in wb.sheets.enumerated() {70            var cells = [CellRef]()71            sheet.cells.forEachAll { r, c, cell in72                guard cell.formula == nil, !cell.isSpill, isNumericText(cell.value) else { return }73                if options.rules.contains(.textNumberInArithmetic), uses[Where(sheet: si, cell: CellRef(r, c))] != nil { return }74                if let t = sheet.table(containing: r, c), t.hasHeader, r == t.range.r0 { return }75                cells.append(CellRef(r, c))76            }77            // one finding per column78            for (col, group) in Dictionary(grouping: cells, by: \.col).sorted(by: { $0.key < $1.key }) {79                let sorted = group.sorted()80                let sample = sheet.value(sorted[0].row, sorted[0].col)81                add(.numberStoredAsText, .info, sheet: si, cells: sorted,82                    evidence: "\(sorted.count) cell\(sorted.count == 1 ? "" : "s") in column \(A1.columnName(col)) (\(rangesText(sorted))) contain\(sorted.count == 1 ? "s" : "") numbers stored as text, e.g. \(sorted[0].a1) = \(FormulaIntel.preview(sample)).",83                    explanation: "Numbers stored as text sort and filter differently and are skipped by SUM, AVERAGE and similar functions.",84                    action: "Convert them to numbers if they are quantities; leave them if they are identifiers.")85            }86        }87    }8889    // MARK: Table columns9091    private func kind(_ v: Value) -> String? {92        switch v {93        case .number: "number"94        case .text(let s): s.isEmpty ? nil : "text"95        case .bool: "boolean"96        default: nil97        }98    }99100    mutating func ruleMixedTypes() {101        for (si, sheet) in wb.sheets.enumerated() {102            for table in sheet.meta.tables {103                guard let data = table.dataRange else { continue }104                for c in data.c0...data.c1 {105                    var byKind = [String: [CellRef]]()106                    sheet.cells.forEach(in: RangeRef(r0: data.r0, c0: c, r1: data.r1, c1: c)) { r, col, cell in107                        guard cell.formula == nil, !cell.isSpill, let k = kind(cell.value) else { return }108                        byKind[k, default: []].append(CellRef(r, col))109                    }110                    guard byKind.count > 1 else { continue }111                    let ranked = byKind.sorted { ($0.value.count, $1.key) > ($1.value.count, $0.key) }112                    let minority = ranked.dropFirst().flatMap(\.value).sorted()113                    let colName = c - table.range.c0 < table.columns.count ? table.columns[c - table.range.c0].name : A1.columnName(c)114                    let counts = ranked.map { "\($0.value.count) \($0.key)" }.joined(separator: ", ")115                    add(.mixedTypesInTableColumn, .warning, sheet: si, cells: minority,116                        evidence: "Column \"\(colName)\" of table \(table.name) mixes types: \(counts). The odd ones out: \(list(minority)).",117                        explanation: "A data column should hold one type. Stray text among numbers (or the reverse) is skipped by aggregates and breaks sorting, filtering and lookups.",118                        action: "Correct the odd cells so that the whole column has the same type.")119                }120            }121        }122    }123124    mutating func ruleTextResults() {125        for (si, sheet) in wb.sheets.enumerated() {126            func report(_ column: [CellRef], _ what: String) {127                var numbers = 0, filled = 0128                var textFormulas = [CellRef]()129                for ref in column {130                    guard let cell = sheet.cell(ref.row, ref.col) else { continue }131                    let v = cell.computed132                    if case .text(let s) = v {133                        if s.isEmpty && !options.flagEmptyTextResults { continue }134                        filled += 1135                        if cell.formula != nil { textFormulas.append(ref) }136                    } else if v.isNumber { numbers += 1; filled += 1 } else if !v.isBlank, !v.isError { filled += 1 }    // errors are another rule's business137                }138                guard !textFormulas.isEmpty, filled > 0, Double(numbers) / Double(filled) >= options.numericColumnShare else { return }139                let first = textFormulas[0]140                let f = formula(si, first)141                add(.textResultInNumericColumn, .warning, sheet: si, cells: textFormulas,142                    evidence: "\(what): \(numbers) of \(filled) values are numbers, but \(list(textFormulas)) return\(textFormulas.count == 1 ? "s" : "") text, e.g. \(first.a1): \(f.map(text) ?? "") → \(FormulaIntel.preview(sheet.value(first.row, first.col))).",143                    explanation: "Text in a numeric column is ignored by SUM and AVERAGE and turns arithmetic on it into #VALUE!.",144                    action: "Return a number (or a real blank / NA()) instead of text, or keep the text in a separate column.")145            }146            var inTables = [RangeRef]()147            for table in sheet.meta.tables {148                guard let data = table.dataRange else { continue }149                inTables.append(table.range)150                for c in data.c0...data.c1 {151                    let name = c - table.range.c0 < table.columns.count ? table.columns[c - table.range.c0].name : A1.columnName(c)152                    report((data.r0...data.r1).map { CellRef($0, c) }, "Column \"\(name)\" of table \(table.name)")153                }154            }155            // outside tables: vertical runs of formulas156            var byCol = [Int: [Int]]()157            for f in formulas[si] where !inTables.contains(where: { $0.contains(f.ref) }) { byCol[f.col, default: []].append(f.row) }158            for col in byCol.keys.sorted() {159                let rows = byCol[col]!.sorted()160                var start = 0161                for i in 1...rows.count {162                    if i == rows.count || rows[i] != rows[i - 1] + 1 {163                        if i - start >= max(4, options.runLength + 1) {164                            let cells = rows[start..<i].map { CellRef($0, col) }165                            report(cells, "Formula column \(RangeRef(cells.first!, cells.last!).a1)")166                        }167                        start = i168                    }169                }170            }171        }172    }173174    // MARK: Merged cells inside data175176    mutating func ruleMerged() {177        for (si, sheet) in wb.sheets.enumerated() where !sheet.meta.merges.isEmpty {178            var referenced = Set<RangeRef>()179            for (sj, list) in formulas.enumerated() {180                for f in list {181                    for a in engine.precedents(sheet: sj, row: f.row, col: f.col) where a.sheet == si && !a.range.isSingleCell { referenced.insert(a.range) }182                }183            }184            for m in sheet.meta.merges.sorted(by: { ($0.r0, $0.c0) < ($1.r0, $1.c0) }) where !m.isSingleCell {185                var context: String?186                if let t = sheet.meta.tables.first(where: { $0.range.intersects(m) }) { context = "table \(t.name) (\(t.range.a1))" }187                else if let af = sheet.meta.autoFilter, af.intersects(m) { context = "the filtered range \(af.a1)" }188                else if let r = referenced.filter({ $0.intersects(m) }).min(by: { ($0.r0, $0.c0, $0.r1, $0.c1) < ($1.r0, $1.c0, $1.r1, $1.c1) }) { context = "the range \(r.a1) used by formulas" }189                guard let context else { continue }190                add(.mergedCellsInData, .warning, sheet: si, cells: [], ranges: [m], key: m.a1,191                    evidence: "The merged cells \(m.a1) lie inside \(context).",192                    explanation: "Only the top-left cell of a merge holds a value; the others are empty, which breaks sorting, filtering, fill-down and any formula reading the range.",193                    action: "Unmerge the cells and repeat the value on each row (use Center Across Selection for the look).")194            }195        }196    }197198    // MARK: Hidden content199200    mutating func ruleHidden() {201        for (si, sheet) in wb.sheets.enumerated() {202            if sheet.meta.isHidden {203                var constants = 0, formulaCount = 0204                sheet.cells.forEachAll { _, _, c in if c.formula != nil { formulaCount += 1 } else if c.hasContent && !c.isSpill { constants += 1 } }205                if constants + formulaCount > 0 {206                    add(.hiddenContent, .info, sheet: si, cells: [], ranges: sheet.cells.usedRange().map { [$0] } ?? [], key: "sheet",207                        evidence: "The hidden sheet \"\(sheet.name)\" contains \(constants) value\(constants == 1 ? "" : "s") and \(formulaCount) formula\(formulaCount == 1 ? "" : "s").",208                        explanation: "Hidden sheets are part of the calculation but are easily overlooked by reviewers.",209                        action: "Unhide the sheet for review, or document what it contains.")210                }211                continue212            }213            func scan(_ hidden: IndexSet, rows: Bool) {214                guard !hidden.isEmpty else { return }215                var stats = [Int: (Int, Int)]()216                sheet.cells.forEachAll { r, c, cell in217                    let k = rows ? r : c218                    guard hidden.contains(k), cell.hasContent else { return }219                    var s = stats[k] ?? (0, 0)220                    if cell.formula != nil { s.1 += 1 } else { s.0 += 1 }221                    stats[k] = s222                }223                for range in hidden.rangeView {224                    let keys = range.filter { stats[$0] != nil }225                    guard let lo = keys.first, let hi = keys.last else { continue }226                    let constants = keys.reduce(0) { $0 + stats[$1]!.0 }, formulaCount = keys.reduce(0) { $0 + stats[$1]!.1 }227                    let label = rows ? (lo == hi ? "row \(lo + 1)" : "rows \(lo + 1)–\(hi + 1)") : (lo == hi ? "column \(A1.columnName(lo))" : "columns \(A1.columnName(lo))–\(A1.columnName(hi))")228                    let used = sheet.cells.usedRange() ?? RangeRef(r0: 0, c0: 0, r1: 0, c1: 0)229                    let area = rows ? RangeRef(r0: lo, c0: used.c0, r1: hi, c1: used.c1) : RangeRef(r0: used.r0, c0: lo, r1: used.r1, c1: hi)230                    add(.hiddenContent, .info, sheet: si, cells: [], ranges: [area], key: rows ? "rows\(lo + 1)" : "cols" + A1.columnName(lo),231                        evidence: "Hidden \(label) contain\(lo == hi ? "s" : "") \(constants) value\(constants == 1 ? "" : "s") and \(formulaCount) formula\(formulaCount == 1 ? "" : "s").",232                        explanation: "Hidden rows and columns still take part in calculations (and in SUM over the range) but are invisible to the reader.",233                        action: "Unhide them for review, or group them with an outline so that their presence is visible.")234                }235            }236            scan(sheet.meta.hiddenRows, rows: true)237            scan(sheet.meta.hiddenCols, rows: false)238        }239    }240}241
Files main
Menu

Theme