Repository Public
1import Foundation2import SheetCore34public enum TableError: Error, Sendable, Hashable, CustomStringConvertible {5 case invalidRange6 case overlapsTable(String)7 case overlapsMergedCells8 case invalidName(String)9 case duplicateName(String)1011 public var description: String {12 switch self {13 case .invalidRange: "The range is empty or outside the sheet."14 case .overlapsTable(let n): "The range overlaps the table \(n)."15 case .overlapsMergedCells: "A table cannot contain merged cells."16 case .invalidName(let n): "\"\(n)\" is not a valid table name."17 case .duplicateName(let n): "The name \"\(n)\" is already in use."18 }19 }20}2122/// Aggregations offered by a table's totals row, with their SUBTOTAL function numbers23/// (the 101–111 series, which ignores rows hidden by a filter or manually).24public enum TotalsFunction: String, Sendable, Codable, CaseIterable {25 case none, sum, average, count, countNumbers, max, min, stdDev, variance2627 /// First argument of SUBTOTAL, nil for `none`.28 public var subtotalCode: Int? {29 switch self {30 case .none: nil31 case .average: 10132 case .countNumbers: 10233 case .count: 10334 case .max: 10435 case .min: 10536 case .stdDev: 10737 case .sum: 10938 case .variance: 11039 }40 }4142 /// Reads the names used in `TableColumn.totalsFunction` (OOXML spellings included: "countNums", "var").43 public init?(name: String) {44 switch name.lowercased() {45 case "none", "": self = .none46 case "sum": self = .sum47 case "average": self = .average48 case "count": self = .count49 case "countnums", "countnumbers": self = .countNumbers50 case "max": self = .max51 case "min": self = .min52 case "stddev": self = .stdDev53 case "var", "variance": self = .variance54 default: return nil55 }56 }57}5859/// What typing next to a table should do to it.60public enum TableExpansion: Sendable, Hashable {61 /// A data row is appended; the table then covers `newRange`.62 case row(newRange: RangeRef)63 /// A column named `name` is appended; the table then covers `newRange`.64 case column(newRange: RangeRef, name: String)65}6667/// Structured-table helpers: creation, auto-expansion, totals, filtering, sorting, duplicates and profiling.68/// Functions are pure unless their name says otherwise (`addTable` edits the sheet through the engine).69/// They read the computed values stored in the sheet: recalculate pending formulas70/// (`CalcEngine.recalculate()`) before calling them.71public enum TableOps {72 // MARK: Creation7374 /// The block of contiguous non-empty cells around `cell` (Excel's "current region": grown while a75 /// bordering row or column touches content, diagonals included). Nil when the cell and its neighbours are empty.76 public static func currentRegion(sheet: Sheet, around cell: CellRef) -> RangeRef? {77 func hasContent(_ r: RangeRef) -> Bool {78 var found = false79 sheet.cells.forEach(in: r) { _, _, c in if c.hasContent { found = true } }80 return found81 }82 var region = RangeRef(cell)83 var grew = true84 while grew {85 grew = false86 let c0 = max(region.c0 - 1, 0), c1 = min(region.c1 + 1, Limits.maxCols - 1)87 let r0 = max(region.r0 - 1, 0), r1 = min(region.r1 + 1, Limits.maxRows - 1)88 if region.r0 > 0, hasContent(RangeRef(r0: region.r0 - 1, c0: c0, r1: region.r0 - 1, c1: c1)) { region.r0 -= 1; grew = true }89 if region.r1 < Limits.maxRows - 1, hasContent(RangeRef(r0: region.r1 + 1, c0: c0, r1: region.r1 + 1, c1: c1)) { region.r1 += 1; grew = true }90 if region.c0 > 0, hasContent(RangeRef(r0: r0, c0: region.c0 - 1, r1: r1, c1: region.c0 - 1)) { region.c0 -= 1; grew = true }91 if region.c1 < Limits.maxCols - 1, hasContent(RangeRef(r0: r0, c0: region.c1 + 1, r1: r1, c1: region.c1 + 1)) { region.c1 += 1; grew = true }92 }93 if region.isSingleCell && !hasContent(region) { return nil }94 // trim empty outer rows/columns (the start cell itself may be empty)95 var used: RangeRef?96 sheet.cells.forEach(in: region) { r, c, cellValue in97 if cellValue.hasContent { used = used?.union(RangeRef(CellRef(r, c))) ?? RangeRef(CellRef(r, c)) }98 }99 return used100 }101102 /// Decides whether the first row of `range` is a header row.103 ///104 /// Rule: the first row must consist of non-blank, pairwise distinct (case-insensitive) text. Then105 /// it is a header when (a) the range has a single row, or (b) some column holds a non-text value106 /// below it (within the next 50 rows), or (c) the first row is bold where the second is not, or107 /// (d) every column is text and no header text reappears below in its own column.108 public static func detectHeader(workbook: Workbook, sheet si: Int, range: RangeRef) -> Bool {109 let sheet = workbook.sheets[si]110 var seen = Set<String>()111 for c in range.c0...range.c1 {112 guard case .text(let s) = sheet.value(range.r0, c), !s.trimmingCharacters(in: .whitespaces).isEmpty,113 seen.insert(s.lowercased()).inserted else { return false }114 }115 if range.rows == 1 { return true }116 let last = min(range.r1, range.r0 + 50)117 var repeated = false118 for c in range.c0...range.c1 {119 let header = sheet.value(range.r0, c).textValue?.lowercased()120 for r in (range.r0 + 1)...last {121 switch sheet.value(r, c) {122 case .number, .bool: return true123 case .text(let s): if s.lowercased() == header { repeated = true }124 default: break125 }126 }127 }128 for c in range.c0...range.c1 {129 let top = workbook.styles[sheet.styleID(range.r0, c)].bold130 let below = workbook.styles[sheet.styleID(range.r0 + 1, c)].bold131 if top && !below { return true }132 }133 return !repeated134 }135136 /// Column names for a table over `range`: header texts (blank → "ColumnN"), made unique by137 /// appending 2, 3… to repeats; "Column1…N" without a header.138 public static func columnNames(sheet: Sheet, range: RangeRef, hasHeader: Bool) -> [String] {139 var used = Set<String>()140 var out = [String]()141 for (k, c) in (range.c0...range.c1).enumerated() {142 var base = hasHeader ? AuditRuleSupport.display(sheet.value(range.r0, c)).trimmingCharacters(in: .whitespaces) : ""143 if base.isEmpty { base = "Column\(k + 1)" }144 out.append(unique(base, &used))145 }146 return out147 }148149 private static func unique(_ base: String, _ used: inout Set<String>) -> String {150 var name = base151 var n = 2152 while !used.insert(name.lowercased()).inserted { name = "\(base)\(n)"; n += 1 }153 return name154 }155156 /// Table names start with a letter or underscore, contain letters, digits, "_" or ".", and must not read as a cell reference.157 public static func isValidTableName(_ name: String) -> Bool {158 guard let first = name.unicodeScalars.first, name.count <= 255 else { return false }159 guard CharacterSet.letters.contains(first) || first == "_" else { return false }160 let allowed = CharacterSet.alphanumerics.union(CharacterSet(charactersIn: "_."))161 guard name.unicodeScalars.allSatisfy({ allowed.contains($0) }) else { return false }162 return CellRef(a1: name) == nil163 }164165 /// Builds (without storing) a table definition for `range`. `hasHeader` nil = detect; `name` nil = next free "TableN".166 /// Without a header the definition has `hasHeader = false` and the whole range is data.167 public static func makeTable(workbook: Workbook, sheet si: Int, range: RangeRef, name: String? = nil, hasHeader: Bool? = nil) throws -> TableDef {168 guard si >= 0, si < workbook.sheets.count, range.r0 >= 0, range.c0 >= 0,169 range.r1 < Limits.maxRows, range.c1 < Limits.maxCols else { throw TableError.invalidRange }170 let sheet = workbook.sheets[si]171 if let other = sheet.meta.tables.first(where: { $0.range.intersects(range) }) { throw TableError.overlapsTable(other.name) }172 if sheet.meta.merges.contains(where: { $0.intersects(range) }) { throw TableError.overlapsMergedCells }173 func taken(_ n: String) -> Bool { workbook.table(named: n) != nil || workbook.definedName(n) != nil }174 let tableName: String175 if let name {176 guard isValidTableName(name) else { throw TableError.invalidName(name) }177 guard !taken(name) else { throw TableError.duplicateName(name) }178 tableName = name179 } else {180 var n = 1181 while taken("Table\(n)") { n += 1 }182 tableName = "Table\(n)"183 }184 let header = hasHeader ?? detectHeader(workbook: workbook, sheet: si, range: range)185 var def = TableDef(id: workbook.nextTableID(), name: tableName, range: range,186 columns: columnNames(sheet: sheet, range: range, hasHeader: header).map { TableColumn(name: $0) })187 def.hasHeader = header188 return def189 }190191 /// Creates the table and stores it in the sheet as one undoable step.192 @discardableResult193 public static func addTable(engine: CalcEngine, sheet si: Int, range: RangeRef, name: String? = nil, hasHeader: Bool? = nil) throws -> TableDef {194 let def = try makeTable(workbook: engine.workbook, sheet: si, range: range, name: name, hasHeader: hasHeader)195 engine.editMeta(sheet: si) { $0.tables.append(def) }196 return def197 }198199 // MARK: Auto-expansion200201 /// Decides whether typing `text` into `cell` should grow `table`.202 ///203 /// - Row: the cell is directly below the last row, inside the table's columns, the table has no204 /// totals row, and the rest of that row under the table is empty.205 /// - Column: the cell is directly right of the last column, inside the table's rows, and the rest206 /// of that column beside the table is empty. Typed into the header row, the text becomes the207 /// column name; otherwise the column is named "ColumnN".208 /// - Never when the grown range would touch another table or a merged cell, or when the text is empty.209 public static func expansion(of table: TableDef, typingAt cell: CellRef, text: String, sheet: Sheet) -> TableExpansion? {210 guard !text.trimmingCharacters(in: .whitespaces).isEmpty else { return nil }211 let rg = table.range212 func free(_ strip: RangeRef) -> Bool {213 var ok = true214 sheet.cells.forEach(in: strip) { r, c, v in if v.hasContent && !(r == cell.row && c == cell.col) { ok = false } }215 if sheet.meta.merges.contains(where: { $0.intersects(strip) }) { ok = false }216 if sheet.meta.tables.contains(where: { $0.id != table.id && $0.range.intersects(strip) }) { ok = false }217 return ok218 }219 if cell.row == rg.r1 + 1, cell.col >= rg.c0, cell.col <= rg.c1, !table.hasTotals, cell.row < Limits.maxRows {220 let strip = RangeRef(r0: cell.row, c0: rg.c0, r1: cell.row, c1: rg.c1)221 return free(strip) ? .row(newRange: rg.union(strip)) : nil222 }223 if cell.col == rg.c1 + 1, cell.row >= rg.r0, cell.row <= rg.r1, cell.col < Limits.maxCols {224 let strip = RangeRef(r0: rg.r0, c0: cell.col, r1: rg.r1, c1: cell.col)225 guard free(strip) else { return nil }226 var used = Set(table.columns.map { $0.name.lowercased() })227 let typedHeader = table.hasHeader && cell.row == rg.r0228 let base = typedHeader ? text.trimmingCharacters(in: .whitespaces) : "Column\(table.columns.count + 1)"229 return .column(newRange: rg.union(strip), name: unique(base, &used))230 }231 return nil232 }233234 /// The table after an expansion.235 public static func applying(_ expansion: TableExpansion, to table: TableDef) -> TableDef {236 var t = table237 switch expansion {238 case .row(let newRange): t.range = newRange239 case .column(let newRange, let name):240 t.range = newRange241 t.columns.append(TableColumn(name: name))242 }243 return t244 }245246 // MARK: Totals row247248 private static func bracketed(_ column: String) -> String {249 var o = ""250 for ch in column { if "[]#'@".contains(ch) { o.append("'") }; o.append(ch) }251 return "[" + o + "]"252 }253254 /// Formula text for the totals cell of a column, e.g. `=SUBTOTAL(109,Sales[Amount])`255 /// (nil for `.none` or an invalid column index). The 101–111 function numbers skip filtered rows.256 public static func totalsFormula(table: TableDef, column: Int, function: TotalsFunction) -> String? {257 guard column >= 0, column < table.columns.count, let code = function.subtotalCode else { return nil }258 return "=SUBTOTAL(\(code),\(table.name)\(bracketed(table.columns[column].name)))"259 }260261 /// Totals formulas for every column whose `totalsFunction` names an aggregation (key = column index in the table).262 public static func totalsRowFormulas(table: TableDef) -> [Int: String] {263 var out: [Int: String] = [:]264 for (i, col) in table.columns.enumerated() {265 guard let name = col.totalsFunction, let fn = TotalsFunction(name: name) else { continue }266 if let f = totalsFormula(table: table, column: i, function: fn) { out[i] = f }267 }268 return out269 }270271 /// Sheet row of the totals row, when the table shows one.272 public static func totalsRow(of table: TableDef) -> Int? { table.hasTotals ? table.range.r1 : nil }273}274