SPB Git

Repository Public

TableTests.swift 18.0 KB 280 lines swift

1import Foundation2import Testing3import SheetCore4import SheetUI5import Audit6@testable import FeatureAudit78@MainActor @Suite struct TableActionsTests {9    @discardableResult10    func makeTable(_ s: WorkbookSession, totals: Bool = true, name: String = "Deals") -> TableDef {11        s.perform("Convert to Table") { e in12            try! TableActions.create(engine: e, sheet: 0, range: Fixture.rg("A1:D7"), options: .init(name: name, totals: totals))13        }14    }1516    @Test func createStylesHeaderAddsTotalsAndBanding() throws {17        let s = Fixture.sales()18        let t = makeTable(s)19        #expect(t.name == "Deals" && t.hasHeader && t.hasTotals && t.range == Fixture.rg("A1:D8"))20        #expect(t.columns.map(\.name) == ["Region", "Units", "Price", "Revenue"])21        #expect(t.columns.map(\.totalsFunction) == [nil, "sum", "sum", "sum"])22        let sh = s.sheet23        let header = s.workbook.styles[sh.styleID(0, 2)]24        #expect(header.bold && header.fill == TableStyling.headerFill && header.textColor == .white)25        // totals row: label + SUBTOTAL formulas that evaluate26        #expect(Fixture.value(s, "A8") == .text("Total"))27        #expect(s.engine.inputText(sheet: 0, row: 7, col: 1) == "=SUBTOTAL(109,Deals[Units])")28        #expect(Fixture.value(s, "B8") == .number(95))29        #expect(Fixture.value(s, "D8") == .number(25 + 60 + 20 + 0 + 60 + 80))30        #expect(s.workbook.styles[sh.styleID(7, 1)].bold)31        // banding rule over the data rows only32        let band = try #require(sh.meta.conditionalFormats.first { TableStyling.isBandRule($0, of: t) })33        #expect(band.ranges == [Fixture.rg("A2:D7")] && band.kind == .formula("MOD(ROW(),2)=1"))34        let fx = ConditionalFormatter(engine: s.engine)35        #expect(fx.effect(sheet: 0, row: 1, col: 0) == nil)36        #expect(fx.effect(sheet: 0, row: 2, col: 0)?.style?.fill == TableStyling.bandFill)37        #expect(fx.effect(sheet: 0, row: 7, col: 0) == nil)38        // one undo step removes everything39        s.undo()40        #expect(s.sheet.meta.tables.isEmpty && s.sheet.meta.conditionalFormats.isEmpty && Fixture.value(s, "A8") == .blank)41        #expect(!s.workbook.styles[s.sheet.styleID(0, 2)].bold)42    }4344    @Test func totalsRowIgnoresFilteredRowsAndCanChangeFunction() throws {45        let s = Fixture.sales()46        let t = makeTable(s)47        let target = DataTarget(table: t)48        DataActions.setFilter(.values(["North"], includeBlanks: false), column: 0, target: target, in: s)49        #expect(Fixture.value(s, "B8") == .number(50))                  // SUBTOTAL 109 skips hidden rows50        DataActions.clearFilters(in: s)51        #expect(Fixture.value(s, "B8") == .number(95))52        s.perform("fn") { e in try! TableActions.setTotalsFunction(engine: e, sheet: 0, tableID: t.id, column: 1, function: .max) }53        #expect(Fixture.value(s, "B8") == .number(40))54        #expect(s.engine.inputText(sheet: 0, row: 7, col: 1) == "=SUBTOTAL(104,Deals[Units])")55        s.perform("fn") { e in try! TableActions.setTotalsFunction(engine: e, sheet: 0, tableID: t.id, column: 1, function: .none) }56        #expect(Fixture.value(s, "B8") == .blank)57        s.perform("off") { e in try! TableActions.setTotals(engine: e, sheet: 0, tableID: t.id, on: false) }58        #expect(s.sheet.meta.tables[0].range == Fixture.rg("A1:D7") && Fixture.value(s, "A8") == .blank && !s.sheet.meta.tables[0].hasTotals)59        // an occupied row below blocks the totals row60        Fixture.fill(s, at: "C8", [["note"]])61        #expect(throws: TableActionError.totalsRowOccupied) {62            try s.perform("on") { e in try TableActions.setTotals(engine: e, sheet: 0, tableID: t.id, on: true) }63        }64        #expect(!s.sheet.meta.tables[0].hasTotals)65    }6667    @Test func renameRewritesStructuredReferences() throws {68        let s = Fixture.sales()69        let t = makeTable(s, totals: false)70        Fixture.fill(s, at: "F1", [["=SUM(Deals[Units])"], ["=COUNTA(Deals[Region])"]])71        Fixture.fill(s, sheet: 1, [["=MAX(Deals[Price])"]])72        s.perform("rename") { e in try! TableActions.rename(engine: e, sheet: 0, tableID: t.id, to: "Pipeline") }73        #expect(s.sheet.meta.tables[0].name == "Pipeline")74        #expect(s.engine.inputText(sheet: 0, row: 0, col: 5) == "=SUM(Pipeline[Units])")75        #expect(s.engine.inputText(sheet: 1, row: 0, col: 0) == "=MAX(Pipeline[Price])")76        #expect(Fixture.value(s, "F1") == .number(95) && Fixture.value(s, "F2") == .number(6) && Fixture.value(s, "A1", sheet: 1) == .number(4))77        s.undo()78        #expect(s.sheet.meta.tables[0].name == "Deals" && Fixture.value(s, "F1") == .number(95))79        #expect(s.engine.inputText(sheet: 0, row: 0, col: 5) == "=SUM(Deals[Units])")80        for bad in ["A1", "has space", "9x", ""] {81            #expect(throws: TableActionError.invalidName(bad)) { try TableActions.rename(engine: s.engine, sheet: 0, tableID: t.id, to: bad) }82        }83        s.perform("n") { $0.setDefinedName(DefinedName(name: "Taken", formula: "Sales!$A$1")) }84        #expect(throws: TableActionError.duplicateName("Taken")) { try TableActions.rename(engine: s.engine, sheet: 0, tableID: t.id, to: "Taken") }85        #expect(throws: TableActionError.noSuchTable) { try TableActions.rename(engine: s.engine, sheet: 0, tableID: 999, to: "X") }86    }8788    @Test func resizeKeepsTotalsAndBanding() throws {89        let s = Fixture.sales()90        let t = makeTable(s)91        Fixture.fill(s, at: "E1", [["Margin"], ["0.1"], ["0.2"]])92        s.perform("resize") { e in try! TableActions.resize(engine: e, sheet: 0, tableID: t.id, to: Fixture.rg("A1:E7")) }93        var now = s.sheet.meta.tables[0]94        #expect(now.range == Fixture.rg("A1:E8") && now.hasTotals)95        #expect(now.columns.map(\.name) == ["Region", "Units", "Price", "Revenue", "Margin"])96        #expect(now.columns[1].totalsFunction == "sum" && now.columns[4].totalsFunction == nil)97        #expect(Fixture.value(s, "A8") == .text("Total") && Fixture.value(s, "B8") == .number(95))98        #expect(s.workbook.styles[s.sheet.styleID(0, 4)].fill == TableStyling.headerFill)99        #expect(s.sheet.meta.conditionalFormats.filter { TableStyling.isBandRule($0, of: now) }.map(\.ranges) == [[Fixture.rg("A2:E7")]])100        #expect(s.sheet.meta.conditionalFormats.count == 1)101        s.undo()102        now = s.sheet.meta.tables[0]103        #expect(now.range == Fixture.rg("A1:D8") && Fixture.value(s, "B8") == .number(95) && now.columns.count == 4)104        // shrinking over data rows: the totals row cannot land on the rows left behind, and nothing changes105        let before = s.engine.changeCount106        #expect(throws: TableActionError.totalsRowOccupied) {107            try s.perform("shrink") { e in try TableActions.resize(engine: e, sheet: 0, tableID: t.id, to: Fixture.rg("A1:D5")) }108        }109        if s.engine.changeCount != before { s.undo() }110        #expect(s.sheet.meta.tables[0].range == Fixture.rg("A1:D8") && Fixture.value(s, "B8") == .number(95))111        // without a totals row the table can shrink112        s.perform("off") { e in try! TableActions.setTotals(engine: e, sheet: 0, tableID: t.id, on: false) }113        s.perform("shrink") { e in try! TableActions.resize(engine: e, sheet: 0, tableID: t.id, to: Fixture.rg("A1:D5")) }114        #expect(s.sheet.meta.tables[0].dataRange == Fixture.rg("A2:D5") && Fixture.value(s, "A7") == .text("North"))115        #expect(throws: TableActionError.invalidResize) { try TableActions.resize(engine: s.engine, sheet: 0, tableID: t.id, to: Fixture.rg("A2:D7")) }116        #expect(throws: TableActionError.invalidResize) { try TableActions.resize(engine: s.engine, sheet: 0, tableID: t.id, to: Fixture.rg("A1:D1")) }117    }118119    @Test func bandingToggleAndDeleteKeepData() throws {120        let s = Fixture.sales()121        let t = makeTable(s, totals: false)122        s.perform("b") { e in try! TableActions.setBanding(engine: e, sheet: 0, tableID: t.id, on: false) }123        #expect(s.sheet.meta.conditionalFormats.isEmpty && !s.sheet.meta.tables[0].showRowStripes)124        s.perform("b") { e in try! TableActions.setBanding(engine: e, sheet: 0, tableID: t.id, on: true) }125        #expect(s.sheet.meta.conditionalFormats.count == 1)126        s.perform("d") { e in try! TableActions.delete(engine: e, sheet: 0, tableID: t.id) }127        #expect(s.sheet.meta.tables.isEmpty && s.sheet.meta.conditionalFormats.isEmpty)128        #expect(Fixture.value(s, "A2") == .text("North") && Fixture.value(s, "D7") == .number(80))129        #expect(s.workbook.styles[s.sheet.styleID(0, 0)].bold)              // formatting stays130    }131132    @Test func creationErrorsAreReported() {133        let s = Fixture.sales()134        makeTable(s, totals: false)135        #expect(throws: TableError.overlapsTable("Deals")) { try TableActions.create(engine: s.engine, sheet: 0, range: Fixture.rg("C3:F9")) }136        #expect(throws: TableError.duplicateName("Deals")) { try TableActions.create(engine: s.engine, sheet: 0, range: Fixture.rg("H1:I3"), options: .init(name: "Deals")) }137        #expect(TableMessages.text(for: TableError.overlapsTable("Deals")).contains("Deals"))138        #expect(!TableMessages.text(for: TableActionError.totalsRowOccupied).isEmpty)139    }140}141142@MainActor @Suite struct DuplicateTests {143    @Test func removesDuplicateRowsInsideTheRangeOnly() {144        let s = Fixture.sales()145        Fixture.fill(s, at: "F1", (1...8).map { ["side \($0)"] })               // beside the range: must not move146        Fixture.fill(s, at: "A9", [["below"]])147        let target = DataActions.target(s)148        #expect(target?.range == Fixture.rg("A1:D7") && target?.hasHeader == true)149        let removed = DataActions.removeDuplicates(DataTarget(range: Fixture.rg("A1:D7"), hasHeader: true, columnNames: []), columns: nil, in: s)150        #expect(removed == 1)151        #expect((2...7).map { Fixture.value(s, "A\($0)") } == [.text("North"), .text("South"), .text("East"), .text("West"), .text("North"), .blank])152        #expect(Fixture.value(s, "B6") == .number(40) && Fixture.value(s, "D6") == .number(80))153        #expect(s.engine.inputText(sheet: 0, row: 5, col: 3) == "=B6*C6")       // the moved formula follows its row154        #expect((1...8).map { Fixture.value(s, "F\($0)") } == (1...8).map { Value.text("side \($0)") })155        #expect(Fixture.value(s, "A9") == .text("below"))156        #expect(s.statusMessage?.contains("1") == true)157        s.undo()158        #expect(Fixture.value(s, "A6") == .text("South") && Fixture.value(s, "A7") == .text("North") && Fixture.value(s, "D7") == .number(80))159    }160161    @Test func columnChoiceDecidesWhatIsADuplicate() {162        let s = Fixture.sales()163        // on Region alone: South (row 6) and North (row 7) repeat164        let n = TableActions.removeDuplicates(engine: s.engine, sheet: 0, range: Fixture.rg("A1:D7"), hasHeader: true, columns: [0])165        #expect(n == 2)166        #expect((2...7).map { Fixture.value(s, "A\($0)") } == [.text("North"), .text("South"), .text("East"), .text("West"), .blank, .blank])167        #expect(TableActions.removeDuplicates(engine: s.engine, sheet: 0, range: Fixture.rg("A1:D7"), hasHeader: true, columns: [0]) == 0)168    }169170    @Test func tableShrinksAndKeepsItsTotals() throws {171        let s = Fixture.sales()172        let t = s.perform("t") { e in try! TableActions.create(engine: e, sheet: 0, range: Fixture.rg("A1:D7"), options: .init(name: "Deals", totals: true)) }173        let removed = DataActions.removeDuplicates(DataTarget(table: t), columns: nil, in: s)174        #expect(removed == 1)175        let now = s.sheet.meta.tables[0]176        #expect(now.range == Fixture.rg("A1:D7") && now.hasTotals && now.dataRange == Fixture.rg("A2:D6"))177        #expect(Fixture.value(s, "A7") == .text("Total") && Fixture.value(s, "B7") == .number(75) && Fixture.value(s, "A8") == .blank)178        #expect(s.sheet.meta.conditionalFormats.map(\.ranges) == [[Fixture.rg("A2:D6")]])179        s.undo()180        #expect(s.sheet.meta.tables[0].range == Fixture.rg("A1:D8") && Fixture.value(s, "B8") == .number(95))181    }182}183184@MainActor @Suite struct FilterTests {185    @Test func columnModelBuildsCriteria() {186        let all = ["East", "North", "South", "West"]187        var m = ColumnFilterModel(criterion: nil, allValues: all)188        #expect(m.criterion(allValues: all, hasBlanks: false) == nil)                    // everything ticked = no filter189        #expect(m.criterion(allValues: all, hasBlanks: true) == nil)190        m.checked.remove("West")191        #expect(m.criterion(allValues: all, hasBlanks: true) == .values(["East", "North", "South"], includeBlanks: true))192        m.includeBlanks = false193        #expect(m.criterion(allValues: all, hasBlanks: true) == .values(["East", "North", "South"], includeBlanks: false))194        m.search = "th"195        #expect(m.visibleValues(all) == ["North", "South"])196197        m = ColumnFilterModel(criterion: .values(["north"], includeBlanks: false), allValues: all)198        #expect(m.mode == .values && m.checked == ["North"] && !m.includeBlanks)199200        m = ColumnFilterModel()201        m.mode = .condition202        #expect(m.criterion(allValues: all, hasBlanks: false) == nil)                    // no operand yet203        m.op1 = .greaterThan; m.text1 = " 12.5 "204        #expect(m.criterion(allValues: all, hasBlanks: false) == .condition(FilterCondition(.greaterThan, 12.5)))205        m.useSecond = true; m.op2 = .lessThan; m.text2 = "40"; m.matchAll = false206        #expect(m.criterion(allValues: all, hasBlanks: false) == .conditions(FilterCondition(.greaterThan, 12.5), FilterCondition(.lessThan, 40.0), matchAll: false))207        m.useSecond = false; m.op1 = .contains; m.text1 = "or"208        #expect(m.criterion(allValues: all, hasBlanks: false) == .condition(FilterCondition(.contains, "or")))209        m.op1 = .greaterThanOrEqual; m.text1 = "2026-02-01"210        #expect(m.criterion(allValues: all, hasBlanks: false, isDate: true) == .condition(FilterCondition(.greaterThanOrEqual, DateSerial.serial(year: 2026, month: 2, day: 1))))211212        m.mode = .top; m.topCount = 3; m.topPercent = true; m.topBottom = true213        #expect(m.criterion(allValues: all, hasBlanks: false) == .top(count: 3, percent: true, bottom: true))214        m.topCount = 0215        #expect(m.criterion(allValues: all, hasBlanks: false) == nil)216217        for c in [FilterCriterion.condition(FilterCondition(.beginsWith, "No")), .conditions(FilterCondition(.greaterThan, 1.0), FilterCondition(.lessThan, 9.0), matchAll: true), .top(count: 5, percent: false, bottom: false)] {218            #expect(ColumnFilterModel(criterion: c, allValues: all).criterion(allValues: all, hasBlanks: false) == c)219        }220    }221222    @Test func filteringHidesRowsReportsStatusAndUndoes() throws {223        let s = Fixture.sales()224        s.select(cell: CellRef(a1: "B3")!)225        let target = try #require(DataActions.target(s))226        #expect(DataActions.status(s, target: target) == (6, 6))227        DataActions.setFilter(.values(["South", "North"], includeBlanks: false), column: 0, target: target, in: s)228        #expect(s.sheet.meta.filteredRows == IndexSet([3, 4]))229        #expect(s.sheet.meta.autoFilter == Fixture.rg("A1:D7"))230        #expect(DataActions.status(s) == (4, 6))231        #expect(s.statusMessage == "4 of 6 rows shown")232        // a second column narrows it; the first criterion is kept233        DataActions.setFilter(.condition(FilterCondition(.greaterThan, 15.0)), column: 1, target: target, in: s)234        #expect(s.sheet.meta.filteredRows == IndexSet([1, 3, 4]))235        #expect(DataActions.activeFilter(s)?.filters.map(\.column) == [0, 1])236        // replacing and removing a column's criterion237        DataActions.setFilter(.top(count: 1, percent: false, bottom: false), column: 1, target: target, in: s)238        #expect(s.sheet.meta.filteredRows == IndexSet([1, 2, 3, 4, 5]))239        DataActions.setFilter(nil, column: 1, target: target, in: s)240        #expect(s.sheet.meta.filteredRows == IndexSet([3, 4]))241        // undo restores the rows; stored criteria that no longer match the sheet are ignored242        s.undo(); s.undo(); s.undo(); s.undo()243        #expect(s.sheet.meta.filteredRows.isEmpty && DataActions.activeFilter(s) == nil)244        s.redo()245        #expect(s.sheet.meta.filteredRows == IndexSet([3, 4]))246        DataActions.clearFilters(in: s)247        #expect(s.sheet.meta.filteredRows.isEmpty && s.workbook.attachments[FilterStore.attachmentKey] == nil)248        s.undo()249        #expect(s.sheet.meta.filteredRows == IndexSet([3, 4]))250    }251252    @Test func sortUsesSeveralKeysAndReappliesTheFilter() throws {253        let s = Fixture.sales()254        let target = try #require(DataActions.target(s))255        DataActions.sort(target, keys: [SortColumn(column: 0, ascending: true), SortColumn(column: 1, ascending: false)], in: s)256        #expect((2...7).map { Fixture.value(s, "A\($0)") } == ["East", "North", "North", "South", "South", "West"].map { Value.text($0) })257        #expect((2...7).map { Fixture.value(s, "B\($0)") } == [.number(5), .number(40), .number(10), .number(20), .number(20), .blank])258        #expect(Fixture.value(s, "A1") == .text("Region") && Fixture.value(s, "D3") == .number(80))259        DataActions.setFilter(.values(["North"], includeBlanks: false), column: 0, target: target, in: s)260        #expect(s.sheet.meta.filteredRows == IndexSet([1, 4, 5, 6]))261        DataActions.sort(target, keys: [SortColumn(column: 0, ascending: false)], in: s)262        #expect(Fixture.value(s, "A2") == .text("West"))263        #expect(s.sheet.meta.filteredRows == IndexSet([1, 2, 3, 6]))             // still only North visible264        #expect(DataActions.activeFilter(s) != nil)265    }266267    @Test func targetResolution() throws {268        let s = Fixture.sales()269        s.select(cell: CellRef(a1: "H20")!)270        #expect(DataActions.target(s) == nil)271        s.select(Fixture.rg("A1:B4"))272        let sel = try #require(DataActions.target(s))273        #expect(sel.range == Fixture.rg("A1:B4") && sel.hasHeader && sel.columnNames == ["Region", "Units"] && sel.rowCount == 3)274        let t = s.perform("t") { e in try! TableActions.create(engine: e, sheet: 0, range: Fixture.rg("A1:D7"), options: .init(name: "Deals", totals: true)) }275        s.select(cell: CellRef(a1: "C4")!)276        let onTable = try #require(DataActions.target(s))277        #expect(onTable.tableID == t.id && onTable.range == Fixture.rg("A1:D7") && onTable.dataRange == Fixture.rg("A2:D7") && onTable.tableName == "Deals")278    }279}280
Files main
Menu

Theme