SPB Git

Repository Public

StepTests.swift 23.2 KB 388 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetAutomation/Tests/SheetAutomationTests/StepTests.swift10// Module           : SheetAutomationTests11// 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 SheetCore90import SheetIO91import SheetData92import SheetCharts93@testable import SheetAutomation9495@Suite struct StepTests {96    @Test func openWorkbookAndSetCell() async throws {97        let dir = TestEnv.tempDir()98        TestEnv.modelXLSX(dir)99        let (run, wb) = try await TestEnv.run([100            .openWorkbook(OpenWorkbookSettings(path: "model.xlsx")),101            .setCell(SetCellSettings(sheet: "Data", cell: "B2", input: "2000")),102            .setCell(SetCellSettings(sheet: "Summary", cell: "A3", input: "=B1*2")),103            .setCell(SetCellSettings(sheet: "Summary", cell: "A4", input: "2026-03-01")),104        ], in: dir)105        expectSucceeded(run)106        let w = try #require(wb)107        #expect(TestEnv.value(w, "Summary", "B1") == .number(2000 + 450.25 + 89.9))108        #expect(TestEnv.value(w, "Summary", "A3").numberValue == 2 * (2000 + 450.25 + 89.9))109        #expect(TestEnv.value(w, "Summary", "A4").isNumber)          // a date serial110        #expect(run.stepResults.count == 4 && run.stepResults.allSatisfy { $0.status == .succeeded })111        #expect(run.parameterValues.isEmpty)112    }113114    @Test func newWorkbookAndSheetSteps() async throws {115        let dir = TestEnv.tempDir()116        let (run, wb) = try await TestEnv.run([117            .newWorkbook(NewWorkbookSettings(sheetName: "Input")),118            .addSheet(SheetNameSettings(name: "Scratch")),119            .addSheet(SheetNameSettings(name: "Report")),120            .setCell(SetCellSettings(sheet: "Input", cell: "A1", input: "7")),121            .setCell(SetCellSettings(sheet: "Report", cell: "A1", input: "=Input!A1*6")),122            .renameSheet(RenameSheetSettings(from: "Input", to: "Source")),123            .deleteSheet(SheetNameSettings(name: "Scratch")),124        ], in: dir)125        expectSucceeded(run)126        let w = try #require(wb)127        #expect(w.sheets.map(\.name) == ["Source", "Report"])128        #expect(TestEnv.value(w, "Report", "A1") == .number(42))      // the formula followed the rename129    }130131    @Test func sheetStepFailures() async throws {132        let dir = TestEnv.tempDir()133        let (r1, _) = try await TestEnv.run([.newWorkbook(NewWorkbookSettings()), .deleteSheet(SheetNameSettings(name: "Sheet1"))], in: dir)134        #expect(r1.status == .failed && r1.message.contains("only sheet"))135        let (r2, _) = try await TestEnv.run([.newWorkbook(NewWorkbookSettings()), .renameSheet(RenameSheetSettings(from: "Nope", to: "X"))], in: dir)136        #expect(r2.status == .failed && r2.failedStep?.message.contains("no sheet named") == true)137        let (r3, _) = try await TestEnv.run([.newWorkbook(NewWorkbookSettings()), .addSheet(SheetNameSettings(name: "Sheet1"))], in: dir)138        #expect(r3.status == .failed)139    }140141    @Test func importFileIntoNewAndExistingSheets() async throws {142        let dir = TestEnv.tempDir()143        TestEnv.salesCSV(dir)144        TestEnv.modelXLSX(dir)145        let (run, wb) = try await TestEnv.run([146            .importFile(ImportFileSettings(path: "sales.csv")),                               // creates the workbook, sheet "sales"147            .importFile(ImportFileSettings(path: "model.xlsx", sourceSheet: "Summary", sheet: "FromExcel", cell: "C3")),148        ], in: dir)149        expectSucceeded(run)150        let w = try #require(wb)151        #expect(w.sheets.map(\.name) == ["sales", "FromExcel"])152        #expect(TestEnv.value(w, "sales", "A1") == .text("date"))153        #expect(TestEnv.value(w, "sales", "C2") == .number(100.5))154        #expect(TestEnv.value(w, "sales", "A2").isNumber)                                    // dates are inferred155        #expect(w.styles[w.sheet(named: "sales")!.styleID(1, 0)].numberFormat != "General")156        #expect(TestEnv.value(w, "FromExcel", "C3") == .text("Total"))157        #expect(TestEnv.value(w, "FromExcel", "D3") == .number(1200 + 450.25 + 89.9))        // values, not formulas158        #expect(w.sheet(named: "FromExcel")!.cell(2, 3)?.formula == nil)159        #expect(run.stepResults[0].outputs.contains(StepOutput(.range, "Range", "sales!A1:C27")))160    }161162    @Test func importFileReplacesExistingDataAndHonoursCSVOptions() async throws {163        let dir = TestEnv.tempDir()164        try Data("a;b\n1,5;x\n2,5;y\n3,5;z\n".utf8).write(to: dir.appendingPathComponent("long.csv"))165        try Data("a;b\n9,25;q\n".utf8).write(to: dir.appendingPathComponent("short.csv"))166        let (run, wb) = try await TestEnv.run([167            .newWorkbook(NewWorkbookSettings(sheetName: "Data")),168            .importFile(ImportFileSettings(path: "long.csv", sheet: "Data", delimiter: ";", decimalComma: true)),169            .setCell(SetCellSettings(sheet: "Data", cell: "D1", input: "keep")),170            .importFile(ImportFileSettings(path: "short.csv", sheet: "Data", delimiter: ";", decimalComma: true)),171        ], in: dir)172        expectSucceeded(run)173        let w = try #require(wb)174        #expect(TestEnv.value(w, "Data", "A2") == .number(9.25))175        #expect(TestEnv.value(w, "Data", "A3").isBlank && TestEnv.value(w, "Data", "B4").isBlank)   // stale rows cleared176        #expect(TestEnv.value(w, "Data", "D1") == .text("keep"))                                    // other columns untouched177        let (run2, wb2) = try await TestEnv.run([.importFile(ImportFileSettings(path: "long.csv", delimiter: ";", inferTypes: false))], in: dir)178        expectSucceeded(run2)179        #expect(TestEnv.value(try #require(wb2), "long", "A2") == .text("1,5"))180    }181182    @Test func cleanRange() async throws {183        let dir = TestEnv.tempDir()184        TestEnv.salesCSV(dir)185        let (run, wb) = try await TestEnv.run([186            .importFile(ImportFileSettings(path: "sales.csv", sheet: "Data", inferTypes: false)),187            .cleanRange(CleanRangeSettings(range: "Data!*", changeCase: .upper, parseNumbers: true, parseDates: true, removeDuplicates: true)),188        ], in: dir)189        expectSucceeded(run)190        let w = try #require(wb)191        let sheet = w.sheet(named: "Data")!192        #expect(sheet.cells.usedRange() == RangeRef(a1: "A1:C25"))                // 24 rows + header: empty row and duplicate removed193        #expect(TestEnv.value(w, "Data", "B2") == .text("HOUSING"))               // trimmed and upper-cased194        #expect(TestEnv.value(w, "Data", "A1") == .text("date"))                  // header untouched195        #expect(TestEnv.value(w, "Data", "C2") == .number(100.5))196        #expect(TestEnv.value(w, "Data", "A2").isNumber)                          // parsed date197        var total = 0.0198        for r in 1...24 { total += sheet.value(r, 2).numberValue ?? .nan }199        #expect(abs(total - TestEnv.salesTotal) < 1e-9)200        #expect(run.stepResults[1].message.contains("1 empty row removed") && run.stepResults[1].message.contains("1 duplicate removed"))201    }202203    @Test func cleanRangeFillAndDuplicateColumns() async throws {204        let dir = TestEnv.tempDir()205        try Data("region,city,v\nEast,A,1\n,B,2\n,A,3\nWest,C,\n".utf8).write(to: dir.appendingPathComponent("gaps.csv"))206        let (run, wb) = try await TestEnv.run([207            .importFile(ImportFileSettings(path: "gaps.csv", sheet: "D")),208            .cleanRange(CleanRangeSettings(range: "D!A:A", fillMissing: .previous, removeEmptyRows: false)),209            .cleanRange(CleanRangeSettings(range: "D!C:C", fillMissing: .constant, fillValue: "0", removeEmptyRows: false)),210            .cleanRange(CleanRangeSettings(range: "D!A1:C5", removeDuplicates: true, duplicateColumns: ["region", "city"])),211        ], in: dir)212        expectSucceeded(run)213        let w = try #require(wb)214        #expect(TestEnv.value(w, "D", "A3") == .text("East"))215        #expect(TestEnv.value(w, "D", "C4") == .number(0))            // West row moved up after the duplicate East/A was removed216        #expect(TestEnv.value(w, "D", "A4") == .text("West"))217        #expect(w.sheet(named: "D")!.cells.usedRange() == RangeRef(a1: "A1:C4"))218    }219220    @Test func cleanRangeRefusesToRemoveRowsOverFormulas() async throws {221        let dir = TestEnv.tempDir()222        let (run, _) = try await TestEnv.run([223            .newWorkbook(NewWorkbookSettings(sheetName: "D")),224            .setCell(SetCellSettings(sheet: "D", cell: "A1", input: "h")),225            .setCell(SetCellSettings(sheet: "D", cell: "A2", input: "=1+1")),226            .cleanRange(CleanRangeSettings(range: "D!A1:A3")),227        ], in: dir)228        #expect(run.status == .failed && run.message.contains("contains formulas"))229    }230231    @Test func fillFormulaSortAndFormat() async throws {232        let dir = TestEnv.tempDir()233        try Data("item,qty,price\nb,2,10\na,5,3\nc,1,7.5\n".utf8).write(to: dir.appendingPathComponent("items.csv"))234        let (run, wb) = try await TestEnv.run([235            .importFile(ImportFileSettings(path: "items.csv", sheet: "Items")),236            .setCell(SetCellSettings(sheet: "Items", cell: "D1", input: "total")),237            .setFormulaRange(SetFormulaRangeSettings(range: "Items!D2:D4", formula: "=B2*C2")),238            .sort(SortSettings(range: "Items!A1:D4", keys: ["total desc"])),239            .formatRange(FormatRangeSettings(range: "Items!A1:D1", bold: .on, fill: "0891B2", textColor: "FFFFFF", alignment: .center)),240            .formatRange(FormatRangeSettings(range: "Items!D:D", numberFormat: "#,##0.00", columnWidth: 120)),241            .setFormulaRange(SetFormulaRangeSettings(range: "Items!F1:G2", formula: "=ROW()*10+COLUMN()")),242        ], in: dir)243        expectSucceeded(run)244        let w = try #require(wb)245        #expect((2...4).map { TestEnv.value(w, "Items", "A\($0)") } == [.text("b"), .text("a"), .text("c")])246        #expect((2...4).map { TestEnv.value(w, "Items", "D\($0)") } == [.number(20), .number(15), .number(7.5)])247        let sheet = w.sheet(named: "Items")!248        #expect(sheet.cell(2, 3)?.formula?.expr.formulaText() == "B3*C3")        // formulas moved with their rows249        let header = w.styles[sheet.styleID(0, 0)]250        #expect(header.bold && header.fill == RGBA(hex: 0x0891B2) && header.textColor == .white && header.hAlign == .center)251        #expect(w.styles[sheet.styleID(2, 3)].numberFormat == "#,##0.00")252        #expect(sheet.meta.colWidths[3] == 120)253        #expect(TestEnv.value(w, "Items", "G2") == .number(27) && TestEnv.value(w, "Items", "F1") == .number(16))254        // sort by column letter and by position255        let (run2, wb2) = try await TestEnv.run([.sort(SortSettings(range: "Items!A1:D4", keys: ["A"])), .sort(SortSettings(range: "Items!A2:D4", keys: ["2 desc"], hasHeader: false))],256                                               workbook: w.clone(), in: dir)257        expectSucceeded(run2)258        #expect((2...4).map { TestEnv.value(try! #require(wb2), "Items", "B\($0)") } == [.number(5), .number(2), .number(1)])259    }260261    @Test func recalculateCountsAndFailsOnErrors() async throws {262        let dir = TestEnv.tempDir()263        let wb = TestEnv.modelWorkbook()264        let (run, out) = try await TestEnv.run([.setCell(SetCellSettings(sheet: "Data", cell: "B2", input: "10")), .recalculate(RecalculateSettings())], workbook: wb.clone(), in: dir)265        expectSucceeded(run)266        #expect(run.stepResults[1].outputs.contains(StepOutput(.count, "Formulas", "2")) && run.stepResults[1].outputs.contains(StepOutput(.count, "Error values", "0")))267        #expect(TestEnv.value(try #require(out), "Summary", "B1") == .number(10 + 450.25 + 89.9))268        let (bad, none) = try await TestEnv.run([.setCell(SetCellSettings(sheet: "Summary", cell: "C1", input: "=1/0")), .recalculate(RecalculateSettings(failOnErrors: true))],269                                                workbook: wb.clone(), in: dir)270        #expect(bad.status == .failed && none == nil)271        #expect(bad.failedStep?.issues == ["Summary!C1 holds #DIV/0!"])272    }273274    @Test func datasetsSQLAndTransform() async throws {275        let dir = TestEnv.tempDir()276        TestEnv.salesCSV(dir)277        let pipeline = TransformPipeline(name: "Top", source: .dataset("by_cat"), steps: [.sort([SortSpec("total", ascending: false)]), .limit(2)])278        let (run, wb) = try await TestEnv.run([279            .newWorkbook(NewWorkbookSettings(sheetName: "Out")),280            .importDataset(ImportDatasetSettings(path: "sales.csv", name: "sales")),281            .importDataset(ImportDatasetSettings(path: "sales.csv", name: "lazy_sales", lazy: true)),282            .runSQL(RunSQLSettings(sql: "SELECT trim(category) AS category, sum(amount) AS total, count(*) AS n FROM sales WHERE category IS NOT NULL GROUP BY 1", output: .dataset("by_cat"))),283            .runSQL(RunSQLSettings(sql: "SELECT * FROM by_cat ORDER BY category", output: .sheet("Out", cell: "B2"))),284            .runTransform(RunTransformSettings(pipeline: pipeline, output: .sheet("Top2"))),285            .runSQL(RunSQLSettings(sql: "SELECT count(*) AS n FROM lazy_sales", output: .sheet("Out", cell: "H1"))),286        ], in: dir)287        expectSucceeded(run)288        let w = try #require(wb)289        #expect(TestEnv.value(w, "Out", "B2") == .text("category") && TestEnv.value(w, "Out", "B3") == .text("Food"))290        // Food rows are i = 1, 5, 9, 13, 17, 21 (the duplicated line is a Housing row)291        let food = [1, 5, 9, 13, 17, 21].reduce(0.0) { $0 + 100 + Double($1) * 10 + 0.5 }292        #expect(TestEnv.value(w, "Out", "C3") == .number(food) && TestEnv.value(w, "Out", "D3") == .number(6))293        #expect(TestEnv.value(w, "Out", "D4") == .number(7))                               // Housing: 6 + the duplicate294        #expect(w.sheet(named: "Top2")!.cells.usedRange() == RangeRef(a1: "A1:C3"))295        #expect(TestEnv.value(w, "Top2", "A2") == .text("Leisure"))296        #expect(TestEnv.value(w, "Out", "H2") == .number(26))                              // 24 + empty + duplicate297        #expect(run.stepResults[1].outputs.contains(StepOutput(.dataset, "Dataset", "sales")))298    }299300    @Test func rangeToDatasetAndStoredTransform() async throws {301        let dir = TestEnv.tempDir()302        let wb = TestEnv.modelWorkbook()303        let stored = TransformPipeline(name: "Big ones", source: .dataset("d"), steps: [.filter(FilterGroup(FilterCondition("amount", .greater, .double(100))))])304        try TransformStore.save(stored, in: wb)305        let (run, out) = try await TestEnv.run([306            .rangeToDataset(RangeToDatasetSettings(range: "Data!*", name: "d")),307            .runTransform(RunTransformSettings(pipelineID: "big ones", output: .sheet("Filtered"))),308            .runTransform(RunTransformSettings(pipelineID: stored.id.uuidString, output: .dataset("filtered"))),309            .runSQL(RunSQLSettings(sql: "SELECT sum(amount) AS s FROM filtered", output: .sheet("Filtered", cell: "E1"))),310        ], workbook: wb, in: dir)311        expectSucceeded(run)312        let w = try #require(out)313        #expect(w.sheet(named: "Filtered")!.cells.usedRange()?.r1 == 2)314        #expect(TestEnv.value(w, "Filtered", "E2") == .number(1650.25))315        let (bad, _) = try await TestEnv.run([.runTransform(RunTransformSettings(pipelineID: "missing"))], workbook: TestEnv.modelWorkbook(), in: dir)316        #expect(bad.status == .failed && bad.message.contains("no transform named"))317    }318319    @Test func sqlErrorsAreReportedOnTheStep() async throws {320        let dir = TestEnv.tempDir()321        let (run, _) = try await TestEnv.run([.newWorkbook(NewWorkbookSettings()), .runSQL(RunSQLSettings(sql: "SELECT * FROM nowhere"))], in: dir)322        #expect(run.status == .failed)323        #expect(run.failedStep?.message.lowercased().contains("nowhere") == true)324    }325326    @Test func pivotFromRangeAndDataset() async throws {327        let dir = TestEnv.tempDir()328        TestEnv.salesCSV(dir)329        let (run, wb) = try await TestEnv.run([330            .importFile(ImportFileSettings(path: "sales.csv", sheet: "Data")),331            .cleanRange(CleanRangeSettings(range: "Data!*", removeDuplicates: true)),332            .createPivot(CreatePivotSettings(range: "Data!*", rows: ["category"], values: ["sum(amount)", "count(amount)", "average(amount)"], sheet: "Summary")),333            .importDataset(ImportDatasetSettings(path: "sales.csv", name: "raw")),334            .createPivot(CreatePivotSettings(source: .dataset, dataset: "raw", rows: ["category"], values: ["max(amount)"], sheet: "Summary", cell: "G1", grandTotals: false, name: "Max")),335        ], in: dir)336        expectSucceeded(run)337        let w = try #require(wb)338        #expect(TestEnv.value(w, "Summary", "A1") == .text("category"))339        #expect(TestEnv.value(w, "Summary", "B1") == .text("Sum of amount"))340        #expect((2...5).map { TestEnv.value(w, "Summary", "A\($0)") } == ["Food", "Housing", "Leisure", "Transport"].map { .text($0) })341        let housing = [0, 4, 8, 12, 16, 20].reduce(0.0) { $0 + 100 + Double($1) * 10 + 0.5 }342        #expect(TestEnv.value(w, "Summary", "B3") == .number(housing))343        #expect(TestEnv.value(w, "Summary", "C3") == .number(6))344        #expect(TestEnv.value(w, "Summary", "D3") == .number(housing / 6))345        #expect(TestEnv.value(w, "Summary", "B6").numberValue.map { abs($0 - TestEnv.salesTotal) < 1e-9 } == true)        // grand total row346        #expect(w.styles[w.sheet(named: "Summary")!.styleID(0, 0)].bold)347        #expect(TestEnv.value(w, "Summary", "G1") == .text("category") && TestEnv.value(w, "Summary", "H5").isNumber && TestEnv.value(w, "Summary", "H6").isBlank)348        #expect(run.stepResults[2].outputs == [StepOutput(.range, "Range", "Summary!A1:D6")])349    }350351    @Test func pivotValueSyntax() throws {352        #expect(try WorkflowEngine.pivotValue("amount") == PivotValue("amount", .sum))353        #expect(try WorkflowEngine.pivotValue(" AVG( unit price ) ") == PivotValue("unit price", .average))354        #expect(try WorkflowEngine.pivotValue("distinct(id)") == PivotValue("id", .distinctCount))355        #expect(throws: WorkflowError.self) { try WorkflowEngine.pivotValue("mode(x)") }356        #expect(throws: WorkflowError.self) { try WorkflowEngine.pivotValue("sum()") }357    }358359    @Test func createChartAutoAndExplicit() async throws {360        let dir = TestEnv.tempDir()361        let wb = TestEnv.modelWorkbook()362        let (run, out) = try await TestEnv.run([363            .createChart(CreateChartSettings(range: "Data!A1:B4", title: "Spend", name: "Auto")),364            .createChart(CreateChartSettings(range: "Data!*", kind: "pie", name: "Pie", sheet: "Summary", anchor: "D3", width: 300, height: 200)),365            .createChart(CreateChartSettings(range: "{{last.range}}", kind: "line", name: "Line")),366        ], workbook: wb, in: dir)367        // {{last.range}} is not defined: no earlier step published a range368        #expect(run.status == .failed && run.failedStep?.message.contains("no earlier step produced it") == true)369        #expect(out == nil)370        let (ok, result) = try await TestEnv.run([371            .createChart(CreateChartSettings(range: "Data!A1:B4", title: "Spend", name: "Auto")),372            .createChart(CreateChartSettings(range: "Data!*", kind: "pie", name: "Pie", sheet: "Summary", anchor: "D3", width: 300, height: 200)),373        ], workbook: TestEnv.modelWorkbook(), in: dir)374        expectSucceeded(ok)375        let w = try #require(result)376        let auto = try #require(w.sheets[0].meta.objects.first)377        let spec = try #require(ChartSpec(sheetObject: auto))378        #expect(auto.name == "Auto" && spec.title == "Spend" && !spec.series.isEmpty)379        let pie = try #require(w.sheets[1].meta.objects.first)380        #expect(ChartSpec(sheetObject: pie)?.kind == .pie && pie.width == 300 && pie.height == 200)381        #expect(pie.x == 240 && pie.y == 40)                                                 // three default columns, two default rows382        let (dup, _) = try await TestEnv.run([.createChart(CreateChartSettings(range: "Data!*", name: "Auto"))], workbook: w.clone(), in: dir)383        #expect(dup.status == .failed && dup.message.contains("already exists"))384        let (empty, _) = try await TestEnv.run([.createChart(CreateChartSettings(range: "Data!A1:A4", name: "Text"))], workbook: TestEnv.modelWorkbook(), in: dir)385        #expect(empty.status == .failed)386    }387}388
Files main
Menu

Theme