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