SPB Git

Repository Public

TablesOpsTests.swift 37.4 KB 559 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAnalytics/Tests/AuditTests/TablesOpsTests.swift10// Module           : AuditTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import Audit9192/// Sales table in A1:F9 — header row, eight data rows (sheet rows 1…8), Total = Units × Price.93///94///     row  Region  Product  Units  Price  Date        Total95///      1   East    Widget   10     2.5    2026-01-05  2596///      2   West    Gadget   5      10     2026-01-20  5097///      3   East    Gadget   8      10     2026-02-03  8098///      4   North   Widget   12     2.5    2026-02-14  3099///      5   West    Widget   5      2.5    2026-03-01  12.5100///      6   east    Gizmo    20     4      2026-03-15  80101///      7   South   Gadget   (empty) 10    2027-01-09  0102///      8   West    Gizmo    7      4      2026-01-31  28103func makeSalesBook() -> RulesTestBook {104    let b = RulesTestBook()105    b.column("A1", ["Region", "East", "West", "East", "North", "West", "east", "South", "West"])106    b.column("B1", ["Product", "Widget", "Gadget", "Gadget", "Widget", "Widget", "Gizmo", "Gadget", "Gizmo"])107    b.column("C1", ["Units", "10", "5", "8", "12", "5", "20", "", "7"])108    b.column("D1", ["Price", "2.5", "10", "10", "2.5", "2.5", "4", "10", "4"])109    b.set("E1", "Date")110    let dates = [(2026, 1, 5), (2026, 1, 20), (2026, 2, 3), (2026, 2, 14), (2026, 3, 1), (2026, 3, 15), (2027, 1, 9), (2026, 1, 31)]111    for (i, d) in dates.enumerated() {112        b.engine.set(sheet: 0, row: i + 1, col: 4, .value(.number(DateSerial.serial(year: d.0, month: d.1, day: d.2))))113    }114    b.engine.setStyle(sheet: 0, range: RangeRef(a1: "E2:E9")!) { $0.numberFormat = "yyyy-mm-dd" }115    b.set("F1", "Total")116    for r in 2...9 { b.set("F\(r)", "=C\(r)*D\(r)") }117    b.engine.recalculate()118    return b119}120121@Suite struct TableCreationTests {122    let all = RangeRef(a1: "A1:F9")!123124    @Test func headerDetection() {125        let b = makeSalesBook()126        #expect(TableOps.detectHeader(workbook: b.wb, sheet: 0, range: all))127        #expect(TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "A1:F1")!))      // single row of text128        #expect(!TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "A2:F9")!))     // first row holds numbers129        #expect(!TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "C2:D9")!))130        // text-only columns: header when its texts do not reappear below…131        #expect(TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "A1:B9")!))132        // …but not when the first row is just another data row ("East" reappears below it)133        #expect(!TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "A2:B9")!))134        // …unless it is formatted differently (bold over non-bold)135        b.engine.setStyle(sheet: 0, range: RangeRef(a1: "A2:B2")!) { $0.bold = true }136        #expect(TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "A2:B9")!))137        // duplicate or blank header cells disqualify the row138        b.set("H1", "Name"); b.set("I1", "name"); b.set("H2", "1"); b.set("I2", "2")139        #expect(!TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H1:I2")!))140        b.set("K1", "Name"); b.set("K2", "1"); b.set("L2", "2")141        #expect(!TableOps.detectHeader(workbook: b.wb, sheet: 0, range: RangeRef(a1: "K1:L2")!))142    }143144    @Test func columnNames() {145        let b = RulesTestBook()146        b.set("A1", "Amount"); b.set("B1", "amount"); b.set("D1", "Amount"); b.set("E1", "2026"); b.set("F1", "Column3")147        #expect(TableOps.columnNames(sheet: b.sheet, range: RangeRef(a1: "A1:F3")!, hasHeader: true)148                == ["Amount", "amount2", "Column3", "Amount3", "2026", "Column32"])149        #expect(TableOps.columnNames(sheet: b.sheet, range: RangeRef(a1: "A1:C3")!, hasHeader: false) == ["Column1", "Column2", "Column3"])150    }151152    @Test func makeTableBuildsADefinition() throws {153        let b = makeSalesBook()154        let t = try TableOps.makeTable(workbook: b.wb, sheet: 0, range: all)155        #expect(t.name == "Table1" && t.id == 1)156        #expect(t.hasHeader && !t.hasTotals)157        #expect(t.columns.map(\.name) == ["Region", "Product", "Units", "Price", "Date", "Total"])158        #expect(t.dataRange == RangeRef(a1: "A2:F9"))159        #expect(b.sheet.meta.tables.isEmpty)               // makeTable does not store anything160        let noHeader = try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "C2:D9")!, name: "Numbers")161        #expect(!noHeader.hasHeader && noHeader.columns.map(\.name) == ["Column1", "Column2"])162        #expect(noHeader.dataRange == RangeRef(a1: "C2:D9"))163        let forced = try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "C2:D9")!, hasHeader: true)164        #expect(forced.columns.map(\.name) == ["10", "2.5"])165    }166167    @Test func addTableIsUndoableAndGuardsNamesAndOverlaps() throws {168        let b = makeSalesBook()169        let t = try TableOps.addTable(engine: b.engine, sheet: 0, range: all, name: "Sales")170        #expect(b.sheet.meta.tables == [t])171        #expect(b.wb.table(named: "sales")?.table.id == t.id)172        #expect(throws: TableError.overlapsTable("Sales")) { try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "F9:H12")!) }173        #expect(throws: TableError.duplicateName("SALES")) { try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H1:I3")!, name: "SALES") }174        #expect(throws: TableError.invalidName("My Table")) { try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H1:I3")!, name: "My Table") }175        #expect(throws: TableError.invalidName("A1")) { try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H1:I3")!, name: "A1") }176        #expect(throws: TableError.invalidRange) { try TableOps.makeTable(workbook: b.wb, sheet: 4, range: all) }177        b.engine.setDefinedName(DefinedName(name: "Rates", formula: "Sheet1!$K$1"))178        #expect(throws: TableError.duplicateName("Rates")) { try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H1:I3")!, name: "Rates") }179        b.engine.merge(sheet: 0, range: RangeRef(a1: "H5:I5")!)180        #expect(throws: TableError.overlapsMergedCells) { try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H1:I6")!) }181        // the next automatic name and id skip what exists182        b.set("K1", "k"); b.set("K2", "1")183        let second = try TableOps.addTable(engine: b.engine, sheet: 0, range: RangeRef(a1: "K1:K2")!)184        #expect(second.name == "Table1" && second.id == 2)185        let third = try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "M1:M2")!)186        #expect(third.name == "Table2" && third.id == 3)187        b.engine.undo()188        #expect(b.sheet.meta.tables.map(\.name) == ["Sales"])189        #expect(TableOps.isValidTableName("_x.1") && TableOps.isValidTableName("Ventes_été"))190        #expect(!TableOps.isValidTableName("") && !TableOps.isValidTableName("1abc") && !TableOps.isValidTableName("a-b") && !TableOps.isValidTableName("XFD1"))191        #expect(TableError.overlapsTable("Sales").description == "The range overlaps the table Sales.")192    }193194    @Test func currentRegion() {195        let b = makeSalesBook()196        b.set("H3", "note")                                // separated from the table by the empty column G197        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "C5")!) == all)198        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "A1")!) == all)199        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "C8")!) == all)        // an empty cell inside the block200        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "H3")!) == RangeRef(a1: "H3"))201        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "A10")!) == all)       // empty cell just below the block202        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "G10")!) == RangeRef(a1: "A1:H9"))   // touches F9 and, through column G, the note203        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "M20")!) == nil)204        b.set("G4", "bridge")                              // now the note is connected205        #expect(TableOps.currentRegion(sheet: b.sheet, around: CellRef(a1: "B2")!) == RangeRef(a1: "A1:H9"))206    }207208    @Test func autoExpansion() throws {209        let b = makeSalesBook()210        var t = try TableOps.addTable(engine: b.engine, sheet: 0, range: all, name: "Sales")211        // typing under the last row, inside the columns → new data row212        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "B10")!, text: "Widget", sheet: b.sheet) == .row(newRange: RangeRef(a1: "A1:F10")!))213        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "F10")!, text: "1", sheet: b.sheet) == .row(newRange: RangeRef(a1: "A1:F10")!))214        // not adjacent, outside the columns, or empty text → nothing215        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "B11")!, text: "x", sheet: b.sheet) == nil)216        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "G10")!, text: "x", sheet: b.sheet) == nil)217        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "B10")!, text: "  ", sheet: b.sheet) == nil)218        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "C4")!, text: "x", sheet: b.sheet) == nil)219        // typing right of the last column → new column, named from the header cell or "ColumnN"220        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "G1")!, text: "Margin", sheet: b.sheet) == .column(newRange: RangeRef(a1: "A1:G9")!, name: "Margin"))221        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "G1")!, text: "total", sheet: b.sheet) == .column(newRange: RangeRef(a1: "A1:G9")!, name: "total2"))222        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "G5")!, text: "0.4", sheet: b.sheet) == .column(newRange: RangeRef(a1: "A1:G9")!, name: "Column7"))223        // other content in the strip blocks the expansion; content in the typed cell itself does not224        b.set("D10", "note")225        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "B10")!, text: "x", sheet: b.sheet) == nil)226        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "D10")!, text: "x", sheet: b.sheet) == .row(newRange: RangeRef(a1: "A1:F10")!))227        b.engine.set(sheet: 0, "D10", .blank)228        // a totals row stops row expansion but not column expansion229        var withTotals = t230        withTotals.hasTotals = true231        #expect(TableOps.expansion(of: withTotals, typingAt: CellRef(a1: "B10")!, text: "x", sheet: b.sheet) == nil)232        #expect(TableOps.expansion(of: withTotals, typingAt: CellRef(a1: "G3")!, text: "x", sheet: b.sheet) != nil)233        // another table or a merged cell in the way234        b.set("G12", "h")235        _ = try TableOps.addTable(engine: b.engine, sheet: 0, range: RangeRef(a1: "G9:G12")!, name: "Other", hasHeader: false)236        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "G2")!, text: "x", sheet: b.sheet) == nil)237        b.engine.merge(sheet: 0, range: RangeRef(a1: "A10:B10")!)238        #expect(TableOps.expansion(of: t, typingAt: CellRef(a1: "C10")!, text: "x", sheet: b.sheet) == nil)239        // applying240        t = TableOps.applying(.row(newRange: RangeRef(a1: "A1:F10")!), to: t)241        #expect(t.range == RangeRef(a1: "A1:F10") && t.columns.count == 6)242        t = TableOps.applying(.column(newRange: RangeRef(a1: "A1:G10")!, name: "Margin"), to: t)243        #expect(t.range == RangeRef(a1: "A1:G10") && t.columns.last?.name == "Margin")244    }245246    @Test func totalsRowFormulas() throws {247        let b = makeSalesBook()248        var t = try TableOps.makeTable(workbook: b.wb, sheet: 0, range: all, name: "Sales")249        let expected: [(TotalsFunction, Int?)] = [(.sum, 109), (.average, 101), (.count, 103), (.countNumbers, 102), (.max, 104), (.min, 105), (.stdDev, 107), (.variance, 110), (.none, nil)]250        for (fn, code) in expected {251            #expect(fn.subtotalCode == code)252            let text = TableOps.totalsFormula(table: t, column: 2, function: fn)253            #expect(text == code.map { "=SUBTOTAL(\($0),Sales[Units])" })254        }255        #expect(TableOps.totalsFormula(table: t, column: 9, function: .sum) == nil)256        // the emitted text parses as a call on a structured reference and prints back unchanged257        let parsed = try FormulaParser.parse("=SUBTOTAL(109,Sales[Total])")258        #expect(parsed.formulaText() == "SUBTOTAL(109,Sales[Total])")259        guard case .call("SUBTOTAL", let args) = parsed, args.count == 2, case .structured(let sr) = args[1] else { Issue.record("unexpected AST"); return }260        #expect(sr.table == "Sales" && sr.colStart == "Total")261        // special characters in column names are escaped as the parser expects262        t.columns[3].name = "Price [CAD]"263        let escaped = try #require(TableOps.totalsFormula(table: t, column: 3, function: .average))264        #expect(escaped == "=SUBTOTAL(101,Sales[Price '[CAD']])")265        if case .call(_, let a) = try FormulaParser.parse(escaped), case .structured(let s2) = a[1] { #expect(s2.colStart == "Price [CAD]") } else { Issue.record("escaped name did not parse") }266        // from the columns' stored totals functions (OOXML spellings accepted)267        t.columns[2].totalsFunction = "sum"268        t.columns[3].totalsFunction = "countNums"269        t.columns[5].totalsFunction = "var"270        t.columns[0].totalsFunction = "none"271        t.columns[1].totalsFunction = "custom"272        #expect(TableOps.totalsRowFormulas(table: t) == [2: "=SUBTOTAL(109,Sales[Units])", 3: "=SUBTOTAL(102,Sales[Price '[CAD']])", 5: "=SUBTOTAL(110,Sales[Total])"])273        #expect(TableOps.totalsRow(of: t) == nil)274        t.hasTotals = true275        #expect(TableOps.totalsRow(of: t) == 8)276        #expect(TotalsFunction(name: "StdDev") == .stdDev && TotalsFunction(name: "median") == nil)277    }278}279280@Suite struct TableFilterSortTests {281    let data = RangeRef(a1: "A2:F9")!282    func hidden(_ b: RulesTestBook, _ filters: [ColumnFilter]) -> [Int] {283        Array(TableOps.filteredRows(sheet: b.sheet, dataRange: data, filters: filters))284    }285286    @Test func valueSetAndBlankFilters() {287        let b = makeSalesBook()288        #expect(hidden(b, [ColumnFilter(column: 0, .values(["east"], includeBlanks: false))]) == [2, 4, 5, 7, 8])289        #expect(hidden(b, [ColumnFilter(column: 0, .values(["North", "SOUTH"], includeBlanks: false))]) == [1, 2, 3, 5, 6, 8])290        #expect(hidden(b, [ColumnFilter(column: 2, .values(["5", "7"], includeBlanks: false))]) == [1, 3, 4, 6, 7])291        #expect(hidden(b, [ColumnFilter(column: 2, .values(["5", "7"], includeBlanks: true))]) == [1, 3, 4, 6])292        #expect(hidden(b, [ColumnFilter(column: 2, .blanks)]) == [1, 2, 3, 4, 5, 6, 8])293        #expect(hidden(b, [ColumnFilter(column: 2, .nonBlanks)]) == [7])294        #expect(hidden(b, []) == [])295        #expect(hidden(b, [ColumnFilter(column: 42, .blanks)]) == [])            // filter on a column outside the range is ignored296    }297298    @Test func numericConditions() {299        let b = makeSalesBook()300        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.greaterThan, 7)))]) == [2, 5, 7, 8])301        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.greaterThanOrEqual, 7)))]) == [2, 5, 7])302        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.lessThan, 7)))]) == [1, 3, 4, 6, 7, 8])303        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.lessThanOrEqual, 7)))]) == [1, 3, 4, 6, 7])304        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.equal, 5)))]) == [1, 3, 4, 6, 7, 8])305        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.notEqual, 5)))]) == [2, 5])       // the blank row is "not 5"306        #expect(hidden(b, [ColumnFilter(column: 2, .conditions(FilterCondition(.greaterThanOrEqual, 5), FilterCondition(.lessThanOrEqual, 8), matchAll: true))]) == [1, 4, 6, 7])307        #expect(hidden(b, [ColumnFilter(column: 2, .conditions(FilterCondition(.lessThan, 6), FilterCondition(.greaterThan, 15), matchAll: false))]) == [1, 3, 4, 7, 8])308        // formula results are filtered like values: Total > 30309        #expect(hidden(b, [ColumnFilter(column: 5, .condition(FilterCondition(.greaterThan, 30)))]) == [1, 4, 5, 7, 8])310        // a numeric operand never matches text cells with an ordering operator311        #expect(hidden(b, [ColumnFilter(column: 0, .condition(FilterCondition(.greaterThan, 0)))]) == [1, 2, 3, 4, 5, 6, 7, 8])312        // text operators with a number operand work on the displayed text: Price contains "2." → 2.5313        #expect(hidden(b, [ColumnFilter(column: 3, .condition(FilterCondition(.contains, 2.5)))]) == [2, 3, 6, 7, 8])314    }315316    @Test func textConditionsAndSearch() {317        let b = makeSalesBook()318        #expect(hidden(b, [ColumnFilter(column: 1, .condition(FilterCondition(.beginsWith, "g")))]) == [1, 4, 5])319        #expect(hidden(b, [ColumnFilter(column: 1, .condition(FilterCondition(.endsWith, "ET")))]) == [6, 8])320        #expect(hidden(b, [ColumnFilter(column: 1, .condition(FilterCondition(.contains, "dg")))]) == [6, 8])321        #expect(hidden(b, [ColumnFilter(column: 1, .condition(FilterCondition(.notContains, "dg")))]) == [1, 2, 3, 4, 5, 7])322        #expect(hidden(b, [ColumnFilter(column: 0, .condition(FilterCondition(.equal, "EAST")))]) == [2, 4, 5, 7, 8])323        #expect(hidden(b, [ColumnFilter(column: 0, .condition(FilterCondition(.notEqual, "west")))]) == [2, 5, 8])324        #expect(hidden(b, [ColumnFilter(column: 0, .condition(FilterCondition(.greaterThan, "north")))]) == [1, 3, 4, 6])     // South, West325        #expect(hidden(b, [ColumnFilter(column: 0, .condition(FilterCondition(.lessThanOrEqual, "north")))]) == [2, 5, 7, 8])326        #expect(hidden(b, [ColumnFilter(column: 1, .search("idg"))]) == [2, 3, 6, 7, 8])327        #expect(hidden(b, [ColumnFilter(column: 1, .search(""))]) == [])328        #expect(hidden(b, [ColumnFilter(column: 2, .search("2"))]) == [1, 2, 3, 5, 7, 8])       // 12 and 20329        #expect(hidden(b, [ColumnFilter(column: 2, .condition(FilterCondition(.equal, "")))]) == [1, 2, 3, 4, 5, 6, 8])    // blank = empty text330        // error cells only pass "not equal" / "does not contain"331        b.set("B3", "=1/0")332        #expect(hidden(b, [ColumnFilter(column: 1, .condition(FilterCondition(.contains, "g")))]) == [2])333        #expect(hidden(b, [ColumnFilter(column: 1, .condition(FilterCondition(.notContains, "widget")))]) == [1, 4, 5])334    }335336    @Test func topNDatesAndCombinedFilters() {337        let b = makeSalesBook()338        #expect(hidden(b, [ColumnFilter(column: 5, .top(count: 2, percent: false, bottom: false))]) == [1, 2, 4, 5, 7, 8])    // 80, 80339        #expect(hidden(b, [ColumnFilter(column: 5, .top(count: 1, percent: false, bottom: false))]) == [1, 2, 4, 5, 7, 8])    // tie kept340        #expect(hidden(b, [ColumnFilter(column: 5, .top(count: 3, percent: false, bottom: false))]) == [1, 4, 5, 7, 8])       // + 50341        #expect(hidden(b, [ColumnFilter(column: 5, .top(count: 25, percent: true, bottom: true))]) == [1, 2, 3, 4, 6, 8])     // 2 smallest: 0, 12.5342        #expect(hidden(b, [ColumnFilter(column: 5, .top(count: 1, percent: true, bottom: false))]) == [1, 2, 4, 5, 7, 8])     // at least one343        #expect(hidden(b, [ColumnFilter(column: 5, .top(count: 0, percent: false, bottom: false))]) == [1, 2, 3, 4, 5, 6, 7, 8])344        #expect(hidden(b, [ColumnFilter(column: 2, .top(count: 2, percent: false, bottom: true))]) == [1, 3, 4, 6, 7, 8])     // blanks never rank345        #expect(hidden(b, [ColumnFilter(column: 4, .dates([.month(year: 2026, month: 1)]))]) == [3, 4, 5, 6, 7])346        #expect(hidden(b, [ColumnFilter(column: 4, .dates([.year(2027)]))]) == [1, 2, 3, 4, 5, 6, 8])347        #expect(hidden(b, [ColumnFilter(column: 4, .dates([.day(year: 2026, month: 2, day: 14), .month(year: 2026, month: 3)]))]) == [1, 2, 3, 7, 8])348        #expect(hidden(b, [ColumnFilter(column: 0, .dates([.year(2026)]))]) == [1, 2, 3, 4, 5, 6, 7, 8])                      // text is not a date349        // date bounds are numeric conditions on serials: on or after 1 March 2026350        let march = DateSerial.serial(year: 2026, month: 3, day: 1)351        #expect(hidden(b, [ColumnFilter(column: 4, .condition(FilterCondition(.greaterThanOrEqual, march)))]) == [1, 2, 3, 4, 8])352        // filters on several columns combine with AND; top-N is ranked on the whole column353        #expect(hidden(b, [ColumnFilter(column: 0, .values(["West"], includeBlanks: false)), ColumnFilter(column: 2, .condition(FilterCondition(.greaterThan, 5)))]) == [1, 2, 3, 4, 5, 6, 7])354        #expect(hidden(b, [ColumnFilter(column: 0, .values(["West"], includeBlanks: false)), ColumnFilter(column: 5, .top(count: 3, percent: false, bottom: false))]) == [1, 3, 4, 5, 6, 7, 8])355    }356357    @Test func filteringATableAndStoringTheResult() throws {358        let b = makeSalesBook()359        let t = try TableOps.addTable(engine: b.engine, sheet: 0, range: RangeRef(a1: "A1:F9")!, name: "Sales")360        let rows = TableOps.filteredRows(sheet: b.sheet, table: t, filters: [ColumnFilter(column: 1, .values(["gizmo"], includeBlanks: false))])361        #expect(Array(rows) == [1, 2, 3, 4, 5, 7])362        b.engine.editMeta(sheet: 0) { $0.filteredRows = rows }363        #expect(b.sheet.rowHeight(1) == 0 && b.sheet.rowHeight(6) > 0 && b.sheet.rowHeight(0) > 0)    // header never hidden364        var totals = t365        totals.hasTotals = true          // the last row is then a totals row and is not filtered366        #expect(Array(TableOps.filteredRows(sheet: b.sheet, table: totals, filters: [ColumnFilter(column: 1, .values(["none"], includeBlanks: false))])) == [1, 2, 3, 4, 5, 6, 7])367        var headerOnly = t368        headerOnly.range = RangeRef(a1: "A1:F1")!369        #expect(TableOps.filteredRows(sheet: b.sheet, table: headerOnly, filters: [ColumnFilter(column: 0, .blanks)]).isEmpty)370    }371372    @Test func distinctValuesForTheDropDown() {373        let b = makeSalesBook()374        let regions = TableOps.distinctValues(sheet: b.sheet, dataRange: data, column: 0)375        #expect(regions.values.map(\.text) == ["East", "North", "South", "West"])376        #expect(regions.values.map(\.count) == [3, 1, 1, 3])377        #expect(regions.blanks == 0)378        let units = TableOps.distinctValues(sheet: b.sheet, dataRange: data, column: 2)379        #expect(units.values.map(\.text) == ["5", "7", "8", "10", "12", "20"])      // numeric order, not text order380        #expect(units.values.map(\.count) == [2, 1, 1, 1, 1, 1])381        #expect(units.blanks == 1)382        #expect(TableOps.distinctValues(sheet: b.sheet, dataRange: data, column: 77).values.isEmpty)383    }384385    @Test func sorting() {386        let b = makeSalesBook()387        func order(_ keys: [SortColumn]) -> [Int] { TableOps.sortedRowOrder(sheet: b.sheet, range: data, spec: SortSpec(keys)) }388        #expect(order([SortColumn(column: 0)]) == [1, 3, 6, 4, 7, 2, 5, 8])                          // stable inside East/West389        #expect(order([SortColumn(column: 0, ascending: false)]) == [2, 5, 8, 7, 4, 1, 3, 6])390        #expect(order([SortColumn(column: 0), SortColumn(column: 2, ascending: false)]) == [6, 1, 3, 4, 7, 8, 2, 5])391        #expect(order([SortColumn(column: 2)]) == [2, 5, 8, 3, 1, 4, 6, 7])                          // blank last392        #expect(order([SortColumn(column: 2, ascending: false)]) == [6, 4, 1, 3, 8, 2, 5, 7])        // blank still last393        #expect(order([SortColumn(column: 0, caseSensitive: true)]) == [6, 1, 3, 4, 7, 2, 5, 8])     // "east" before "East"394        #expect(order([SortColumn(column: 1), SortColumn(column: 5, ascending: false)]) == [3, 2, 7, 6, 8, 4, 1, 5])395        #expect(order([SortColumn(column: 4, ascending: false)]).first == 7)                          // latest date396        #expect(order([]) == [1, 2, 3, 4, 5, 6, 7, 8])397        #expect(order([SortColumn(column: 99)]) == [1, 2, 3, 4, 5, 6, 7, 8])398        #expect(!TableOps.isSorted(sheet: b.sheet, range: data, spec: SortSpec([SortColumn(column: 0)])))399        #expect(TableOps.isSorted(sheet: b.sheet, range: RangeRef(a1: "A2:F4")!, spec: SortSpec([SortColumn(column: 3)])))400        let keys = SortSpec([SortColumn(column: 0), SortColumn(column: 2, ascending: false)]).engineKeys(range: RangeRef(a1: "C5:F9")!)401        #expect(keys.map(\.column) == [2, 4] && keys.map(\.ascending) == [true, false])402    }403404    @Test func sortingMixedTypes() {405        let b = RulesTestBook()406        b.column("A1", ["pear", "3", "TRUE", "", "=1/0", "Apple", "-2", "FALSE", "=NA()"])407        let rg = RangeRef(a1: "A1:A9")!408        // ascending: numbers, text, FALSE < TRUE, errors (#DIV/0! before #N/A), blank409        #expect(TableOps.sortedRowOrder(sheet: b.sheet, range: rg, spec: SortSpec([SortColumn(column: 0)])) == [6, 1, 5, 0, 7, 2, 4, 8, 3])410        // descending reverses everything except the blank, which stays last411        #expect(TableOps.sortedRowOrder(sheet: b.sheet, range: rg, spec: SortSpec([SortColumn(column: 0, ascending: false)])) == [8, 4, 2, 7, 0, 5, 1, 6, 3])412    }413414    @Test func sortOrderAgreesWithTheEngineSort() {415        let b = makeSalesBook()416        let spec = SortSpec([SortColumn(column: 1), SortColumn(column: 2, ascending: false)])417        let order = TableOps.sortedRowOrder(sheet: b.sheet, range: data, spec: spec)418        let expectedRegions = order.map { b.sheet.value($0, 0) }419        let expectedUnits = order.map { b.sheet.value($0, 2) }420        b.engine.sort(sheet: 0, range: data, keys: spec.engineKeys(range: data))421        b.engine.recalculate()422        #expect((1...8).map { b.sheet.value($0, 0) } == expectedRegions)423        #expect((1...8).map { b.sheet.value($0, 2) } == expectedUnits)424    }425426    @Test func removeDuplicates() {427        let b = makeSalesBook()428        #expect(TableOps.duplicateRows(sheet: b.sheet, range: data, columns: [1]) == [3, 4, 5, 7, 8])429        #expect(TableOps.duplicateRows(sheet: b.sheet, range: data, columns: [0]) == [3, 5, 6, 8])       // "east" = "East"430        #expect(TableOps.duplicateRows(sheet: b.sheet, range: data, columns: [0, 1]) == [])431        #expect(TableOps.duplicateRows(sheet: b.sheet, range: data, columns: [1, 3]) == [3, 4, 5, 7, 8])432        #expect(TableOps.duplicateRows(sheet: b.sheet, range: data) == [])433        #expect(TableOps.duplicateRows(sheet: b.sheet, range: data, columns: []) == [])434        // whole-row duplicates, blanks equal to blanks, number ≠ its text form435        let c = RulesTestBook()436        c.column("A1", ["a", "A", "b", "", "", "1", "1"])437        c.column("B1", ["1", "1", "1", "", "", "x", "x"])438        c.set("A8", .text("1")); c.set("B8", "x")439        #expect(TableOps.duplicateRows(sheet: c.sheet, range: RangeRef(a1: "A1:B8")!) == [1, 4, 6])440    }441}442443@Suite struct TableProfileTests {444    let data = RangeRef(a1: "A2:F9")!445446    @Test func dateFormatDetection() {447        for f in ["yyyy-mm-dd", "m/d/yy", "d-mmm", "h:mm:ss", "[h]:mm", "mmm yyyy", "[$-409]d-mmm-yy;@", "dd/mm/yyyy hh:mm", "mm:ss.0"] {448            #expect(TableOps.isDateFormat(f), "\(f)")449        }450        for f in ["General", "0.00", "#,##0", "0%", "0.00E+00", "@", "$#,##0.00_);[Red]($#,##0.00)", "0.0 \"days\"", "#,##0 \\d", ""] {451            #expect(!TableOps.isDateFormat(f), "\(f)")452        }453    }454455    @Test func typeInference() {456        let b = makeSalesBook()457        let types = (0..<6).map { TableOps.inferType(workbook: b.wb, sheet: 0, range: data, column: $0) }458        #expect(types.map(\.type) == [.text, .text, .integer, .number, .date, .number])459        #expect(types[2].numbers == 7 && types[2].integers == 7 && types[2].blanks == 1 && types[2].dominantShare == 1)460        #expect(types[3].integers == 5 && types[3].numbers == 8)461        #expect(types[4].dates == 8)462        #expect(types[0].texts == 8 && types[0].numericTexts == 0)463        // mixed column: 3 numbers, 2 texts (one numeric-looking), 1 boolean, 1 error, 2 blanks464        let m = RulesTestBook()465        m.column("A1", ["1", "2.5", "3", "abc", "", "TRUE", "=1/0"])466        m.set("A8", .text("42"))467        m.set("A9", .text("  "))468        let p = TableOps.inferType(workbook: m.wb, sheet: 0, range: RangeRef(a1: "A1:A9")!, column: 0)469        #expect(p.type == .mixed && p.dominantType == .number)470        #expect(p.numbers == 3 && p.integers == 2 && p.texts == 2 && p.numericTexts == 1 && p.booleans == 1 && p.errors == 1 && p.blanks == 2)471        #expect(p.dominantShare == 0.5)472        #expect(TableOps.inferType(workbook: m.wb, sheet: 0, range: RangeRef(a1: "C1:C5")!, column: 0).type == .empty)473        #expect(TableOps.inferType(workbook: m.wb, sheet: 0, range: RangeRef(a1: "A6:A6")!, column: 0).type == .boolean)474        #expect(TableOps.inferType(workbook: m.wb, sheet: 0, range: RangeRef(a1: "A1:A3")!, column: 0).type == .number)475        #expect(TableOps.inferType(workbook: m.wb, sheet: 0, range: RangeRef(a1: "A1:A3")!, column: 5).type == .empty)476        // a date column with one unformatted number is a plain number column477        b.engine.setStyle(sheet: 0, range: RangeRef(a1: "E4")!) { $0.numberFormat = "General" }478        let e = TableOps.inferType(workbook: b.wb, sheet: 0, range: data, column: 4)479        #expect(e.type == .integer && e.dates == 7)480    }481482    @Test func missingValues() {483        let b = makeSalesBook()484        let clean = TableOps.missingValues(sheet: b.sheet, range: data)485        #expect(clean.count == 6)486        #expect(clean.map(\.total) == [0, 0, 1, 0, 0, 0])487        #expect(clean[2].blankRows == [7] && clean[2].column == 2)488        let m = RulesTestBook()489        m.column("A1", ["1", "", "N/A", "=1/0", "", " - ", "x", "null"])490        m.set("A2", .text(""))491        m.set("B3", "?")492        let r = TableOps.missingValues(sheet: m.sheet, range: RangeRef(a1: "A1:B8")!)493        #expect(r[0].blankRows == [1, 4])494        #expect(r[0].placeholderRows == [2, 5, 7])495        #expect(r[0].errorRows == [3])496        #expect(r[0].total == 6)497        #expect(r[1].blankRows == [0, 1, 3, 4, 5, 6, 7] && r[1].placeholderRows == [2])498        let custom = TableOps.missingValues(sheet: m.sheet, range: RangeRef(a1: "A1:A8")!, placeholders: ["x"])499        #expect(custom[0].placeholderRows == [6])500    }501502    @Test func columnSummaries() throws {503        let b = makeSalesBook()504        let t = try TableOps.makeTable(workbook: b.wb, sheet: 0, range: RangeRef(a1: "A1:F9")!, name: "Sales")505        let s = TableOps.summaries(workbook: b.wb, sheet: 0, table: t)506        #expect(s.map(\.name) == ["Region", "Product", "Units", "Price", "Date", "Total"])507        // Units: 10, 5, 8, 12, 5, 20, 7508        let units = s[2]509        #expect(units.type == .integer && units.count == 7 && units.blanks == 1 && units.errors == 0 && units.distinct == 6 && units.numericCount == 7)510        #expect(units.sum == 67 && units.min == 5 && units.max == 20 && units.median == 8)511        #expect(abs(units.mean! - 67.0 / 7) < 1e-14)512        #expect(abs(units.standardDeviation! - ((807.0 - 67.0 * 67.0 / 7) / 6).squareRoot()) < 1e-13)     // Σx² = 807513        #expect(units.mostFrequent == "5" && units.mostFrequentCount == 2)514        #expect(units.minLength == nil && units.maxLength == nil)515        // Price: 2.5 ×3, 10 ×3, 4 ×2 → even count: median = (4 + 4) / 2516        #expect(s[3].median == 4 && s[3].distinct == 3 && s[3].sum == 45.5 && s[3].mostFrequent == "2.5")517        // Region (text): East ×3 (case-insensitive) ties with West ×3 → the one met first518        let region = s[0]519        #expect(region.type == .text && region.count == 8 && region.distinct == 4 && region.numericCount == 0)520        #expect(region.sum == nil && region.mean == nil && region.median == nil && region.standardDeviation == nil)521        #expect(region.minLength == 4 && region.maxLength == 5)522        #expect(region.mostFrequent == "East" && region.mostFrequentCount == 3)523        // Total (formulas): 25, 50, 80, 30, 12.5, 80, 0, 28524        #expect(s[5].sum == 305.5 && s[5].max == 80 && s[5].min == 0 && s[5].median == 29 && s[5].mostFrequent == "80")525        #expect(s[4].type == .date)526        // report grid: a header row plus 16 statistics, one column per table column527        let grid = TableOps.summaryTable(s)528        #expect(grid.count == 17 && grid.allSatisfy { $0.count == 7 })529        #expect(grid[0] == [.text("Statistic"), .text("Region"), .text("Product"), .text("Units"), .text("Price"), .text("Date"), .text("Total")])530        #expect(grid[1][3] == .text("integer"))531        #expect(grid[7][0] == .text("Sum") && grid[7][3] == .number(67) && grid[7][1] == .blank)532        #expect(grid[15][1] == .text("East"))533        // a single numeric value has no standard deviation; an errors-only column has none of the numeric fields534        let one = TableOps.summary(workbook: b.wb, sheet: 0, range: RangeRef(a1: "C2:C2")!, column: 0, name: "x")535        #expect(one.standardDeviation == nil && one.mean == 10 && one.median == 10)536        b.set("H2", "=1/0")537        let err = TableOps.summary(workbook: b.wb, sheet: 0, range: RangeRef(a1: "H2:H3")!, column: 0, name: "e")538        #expect(err.errors == 1 && err.count == 1 && err.blanks == 1 && err.sum == nil && err.mostFrequent == "#DIV/0!")539        var headerOnly = t540        headerOnly.range = RangeRef(a1: "A1:F1")!541        #expect(TableOps.summaries(workbook: b.wb, sheet: 0, table: headerOnly).isEmpty)542    }543544    @Test func criteriaAndSpecsAreCodable() throws {545        let filters = [ColumnFilter(column: 0, .values(["a", "b"], includeBlanks: true)),546                       ColumnFilter(column: 1, .conditions(FilterCondition(.greaterThan, 3), FilterCondition(.beginsWith, "x"), matchAll: false)),547                       ColumnFilter(column: 2, .top(count: 5, percent: true, bottom: false)),548                       ColumnFilter(column: 3, .dates([.year(2026), .month(year: 2026, month: 2), .day(year: 2026, month: 2, day: 3)])),549                       ColumnFilter(column: 4, .search("q")), ColumnFilter(column: 5, .blanks), ColumnFilter(column: 6, .nonBlanks)]550        let decoded = try JSONDecoder().decode([ColumnFilter].self, from: JSONEncoder().encode(filters))551        #expect(decoded == filters)552        let spec = SortSpec([SortColumn(column: 2, ascending: false, caseSensitive: true)])553        #expect(try JSONDecoder().decode(SortSpec.self, from: JSONEncoder().encode(spec)) == spec)554        let b = makeSalesBook()555        let summary = TableOps.summary(workbook: b.wb, sheet: 0, range: RangeRef(a1: "C2:C9")!, column: 0, name: "Units")556        #expect(try JSONDecoder().decode(ColumnSummary.self, from: JSONEncoder().encode(summary)) == summary)557    }558}559
Files main
Menu

Theme