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