SPB Git

Repository Public

TableOps.swift 13.5 KB 274 lines swift

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
Files main
Menu

Theme