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