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/OutputTests.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 PDFKit90import SheetCore91import SheetIO92import SheetData93import SheetPython94@testable import SheetAutomation9596@Suite struct OutputTests {97 @Test func validateRules() async throws {98 let dir = TestEnv.tempDir()99 let ok = ValidateSettings(noErrorValues: true, auditFailOn: .error, requiredCells: ["Summary!A1:B2", "Data!A:B"], mustBeTrue: ["=Summary!B1>1000", "=Summary!B2=3"])100 let (run, _) = try await TestEnv.run([.validate(ok)], workbook: TestEnv.modelWorkbook(), in: dir)101 expectSucceeded(run)102 #expect(run.stepResults[0].message == "6 checks passed.")103104 let (bad, wb) = try await TestEnv.run([105 .setCell(SetCellSettings(sheet: "Summary", cell: "C1", input: "=1/0")),106 .setCell(SetCellSettings(sheet: "Data", cell: "B3", input: "")),107 .validate(ValidateSettings(noErrorValues: true, requiredCells: ["Data!A1:B4", "Summary!D9"], mustBeTrue: ["=Summary!B2=99", "=Summary!A1"])),108 .setCell(SetCellSettings(sheet: "Summary", cell: "E1", input: "never")),109 ], workbook: TestEnv.modelWorkbook(), in: dir)110 #expect(bad.status == .failed && wb == nil)111 let issues = try #require(bad.failedStep?.issues)112 #expect(issues.contains("Summary!C1 holds #DIV/0!"))113 #expect(issues.contains { $0.contains("Data!A1:B4") && $0.contains("B3") })114 #expect(issues.contains { $0.contains("Summary!D9") })115 #expect(issues.contains("=Summary!B2=99 is FALSE, expected TRUE"))116 #expect(issues.contains("=Summary!A1 is Total, expected TRUE"))117 #expect(bad.stepResults.count == 3) // the step after the failure never ran118 #expect(bad.message.contains("Validation failed: 5 problems"))119120 let scoped = ValidateSettings(noErrorValues: true, errorRange: "Data!*")121 let (r3, _) = try await TestEnv.run([.setCell(SetCellSettings(sheet: "Summary", cell: "C1", input: "=1/0")), .validate(scoped)], workbook: TestEnv.modelWorkbook(), in: dir)122 expectSucceeded(r3)123 }124125 @Test func validateWithAuditor() async throws {126 let dir = TestEnv.tempDir()127 let (run, _) = try await TestEnv.run([128 .setCell(SetCellSettings(sheet: "Summary", cell: "C5", input: "=C6")),129 .setCell(SetCellSettings(sheet: "Summary", cell: "C6", input: "=C5")),130 .validate(ValidateSettings(noErrorValues: false, auditFailOn: .error)),131 ], workbook: TestEnv.modelWorkbook(), in: dir)132 #expect(run.status == .failed)133 #expect(run.failedStep?.issues.contains { $0.lowercased().contains("circular") } == true)134 }135136 @Test func exportEveryWritableFormat() async throws {137 let dir = TestEnv.tempDir()138 let formats = ["xlsx", "xlsm", "ods", "csv", "tsv", "json", "jsonl", "orixsheet"]139 let steps = formats.map { WorkflowStep.exportFile(ExportFileSettings(path: "out/model.\($0)", sheet: "Data")) }140 let (run, _) = try await TestEnv.run(steps, workbook: TestEnv.modelWorkbook(), in: dir)141 expectSucceeded(run)142 #expect(run.outputFiles.count == formats.count)143 for f in formats {144 let url = dir.appendingPathComponent("out/model.\(f)")145 let wb = try TestEnv.load(url)146 let sheet = try #require(wb.sheets.first { $0.cells.usedRange() != nil })147 #expect(sheet.value(1, 0) == .text("Housing"), "\(f)")148 #expect(sheet.value(2, 1) == .number(450.25), "\(f)")149 }150 // formulas survive the workbook formats and are flattened by the text formats151 let xlsx = try TestEnv.load(dir.appendingPathComponent("out/model.xlsx"))152 #expect(xlsx.sheet(named: "Summary")?.cell(0, 1)?.formula?.expr.formulaText() == "SUM(Data!B:B)")153 #expect(run.stepResults[3].issues.contains { $0.contains("[loss] Sheets") })154 #expect(try String(contentsOf: dir.appendingPathComponent("out/model.csv"), encoding: .utf8) == "category,amount\nHousing,1200\nFood,450.25\nTransport,89.9\n")155 if let seen = TestEnv.openpyxl("wb = openpyxl.load_workbook(r'\(dir.path)/out/model.xlsx')\nprint(wb.sheetnames, wb['Data']['B3'].value, wb['Summary']['B1'].value)") {156 #expect(seen == "['Data', 'Summary'] 450.25 =SUM(Data!B:B)")157 }158 }159160 @Test func exportFailOnLossWritesNothing() async throws {161 let dir = TestEnv.tempDir()162 let (run, wb) = try await TestEnv.run([163 .exportFile(ExportFileSettings(path: "a.xlsx", failOnLoss: true)),164 .exportFile(ExportFileSettings(path: "b.csv", sheet: "Summary", failOnLoss: true)),165 ], workbook: TestEnv.modelWorkbook(), in: dir)166 #expect(run.status == .failed && wb == nil)167 #expect(run.failedStep?.path == [1])168 #expect(run.failedStep?.message.contains("would lose content") == true)169 #expect(run.failedStep?.issues.contains { $0.contains("Formulas") } == true)170 #expect(run.outputFiles.isEmpty)171 #expect(!TestEnv.exists(dir.appendingPathComponent("a.xlsx")) && !TestEnv.exists(dir.appendingPathComponent("b.csv"))) // even the first, successful export172 let (bad, _) = try await TestEnv.run([.exportFile(ExportFileSettings(path: "a.bin", format: "xlsx"))], workbook: TestEnv.modelWorkbook(), in: dir)173 expectSucceeded(bad) // an explicit format wins over the extension174 #expect(try TestEnv.load(dir.appendingPathComponent("a.bin")).sheets.count == 2)175 }176177 @Test func exportPDFHasPagesAndVectorText() async throws {178 let dir = TestEnv.tempDir()179 let (run, _) = try await TestEnv.run([180 .createChart(CreateChartSettings(range: "Data!A1:B4", title: "Spend by category", name: "C", anchor: "D2")),181 .exportPDF(ExportPDFSettings(path: "all.pdf")),182 .exportPDF(ExportPDFSettings(path: "one.pdf", sheets: ["Summary"], orientation: .landscape)),183 ], workbook: TestEnv.modelWorkbook(), in: dir)184 expectSucceeded(run)185 let all = try #require(PDFDocument(url: dir.appendingPathComponent("all.pdf")))186 #expect(all.pageCount == 2)187 let text = all.string ?? ""188 #expect(text.contains("Housing") && text.contains("Transport") && text.contains("Total"))189 #expect(text.contains("Spend by category")) // the chart is drawn as vector text too190 let one = try #require(PDFDocument(url: dir.appendingPathComponent("one.pdf")))191 #expect(one.pageCount == 1 && (one.string ?? "").contains("Rows") && !(one.string ?? "").contains("Housing"))192 let box = try #require(one.page(at: 0)?.bounds(for: .mediaBox))193 #expect(box.width > box.height)194 #expect(run.stepResults[1].outputs.contains(StepOutput(.count, "Pages", "2")))195 // no renderer in the host → a clear error196 let bare = StandardWorkflowHost(baseDirectory: dir, readable: [dir], writable: [dir])197 let (noPDF, _) = try await WorkflowEngine(host: bare).run(Workflow(name: "x", [.exportPDF(ExportPDFSettings(path: "z.pdf"))]), workbook: TestEnv.modelWorkbook())198 #expect(noPDF.status == .failed && noPDF.message.contains("not available"))199 }200201 @Test func exportChartFormats() async throws {202 let dir = TestEnv.tempDir()203 let (run, _) = try await TestEnv.run([204 .createChart(CreateChartSettings(range: "Data!A1:B4", kind: "column", title: "Spend", name: "Spend chart", width: 400, height: 250)),205 .exportChart(ExportChartSettings(chart: "spend chart", path: "c.png", format: .png, scale: 2)),206 .exportChart(ExportChartSettings(chart: "Spend chart", path: "c.pdf", format: .pdf)),207 .exportChart(ExportChartSettings(chart: "Spend chart", path: "c.svg", format: .svg)),208 ], workbook: TestEnv.modelWorkbook(), in: dir)209 expectSucceeded(run)210 let png = try Data(contentsOf: dir.appendingPathComponent("c.png"))211 #expect(png.prefix(4) == Data([0x89, 0x50, 0x4E, 0x47]))212 let rep = try #require(NSBitmapImageRep(data: png))213 #expect(rep.pixelsWide == 800 && rep.pixelsHigh == 500)214 let pdf = try #require(PDFDocument(url: dir.appendingPathComponent("c.pdf")))215 #expect(pdf.pageCount == 1 && (pdf.string ?? "").contains("Spend") && (pdf.string ?? "").contains("Housing"))216 let svg = try String(contentsOf: dir.appendingPathComponent("c.svg"), encoding: .utf8)217 #expect(svg.hasPrefix("<?xml") || svg.hasPrefix("<svg"))218 #expect(svg.contains("<text") && svg.contains("Transport"))219 let (missing, _) = try await TestEnv.run([.exportChart(ExportChartSettings(chart: "Nope", path: "x.png"))], workbook: TestEnv.modelWorkbook(), in: dir)220 #expect(missing.status == .failed && missing.message.contains("no chart"))221 }222223 @Test func saveWorkbookNativeAndReopen() async throws {224 let dir = TestEnv.tempDir()225 let (run, _) = try await TestEnv.run([226 .setCell(SetCellSettings(sheet: "Summary", cell: "D1", input: "saved")),227 .saveWorkbook(SaveWorkbookSettings(path: "doc.orixsheet")),228 ], workbook: TestEnv.modelWorkbook(), in: dir)229 expectSucceeded(run)230 // reopen it in a second run, change it, and save in place (no path)231 let (again, _) = try await TestEnv.run([232 .openWorkbook(OpenWorkbookSettings(path: "doc.orixsheet")),233 .setCell(SetCellSettings(sheet: "Data", cell: "B2", input: "1")),234 .saveWorkbook(SaveWorkbookSettings()),235 ], in: dir)236 expectSucceeded(again)237 let wb = try TestEnv.load(dir.appendingPathComponent("doc.orixsheet"))238 #expect(TestEnv.value(wb, "Summary", "D1") == .text("saved"))239 #expect(TestEnv.value(wb, "Summary", "B1") == .number(1 + 450.25 + 89.9))240 #expect(try NativeFormat.verify(url: dir.appendingPathComponent("doc.orixsheet")).isEmpty)241 let (noPath, _) = try await TestEnv.run([.saveWorkbook(SaveWorkbookSettings())], workbook: TestEnv.modelWorkbook(), in: dir)242 #expect(noPath.status == .failed && noPath.message.contains("not been saved yet"))243 }244245 @Test func laterStepsReadFilesWrittenEarlierInTheRun() async throws {246 let dir = TestEnv.tempDir()247 let (run, wb) = try await TestEnv.run([248 .exportFile(ExportFileSettings(path: "stage/data.csv", sheet: "Data")),249 .importDataset(ImportDatasetSettings(path: "stage/data.csv", name: "d")),250 .runSQL(RunSQLSettings(sql: "SELECT max(amount) AS top FROM d", output: .sheet("Top"))),251 .importFile(ImportFileSettings(path: "{{last.path}}", sheet: "Again")),252 ], workbook: TestEnv.modelWorkbook(), in: dir)253 expectSucceeded(run)254 let w = try #require(wb)255 #expect(TestEnv.value(w, "Top", "A2") == .number(1200))256 #expect(TestEnv.value(w, "Again", "B4") == .number(89.9))257 #expect(TestEnv.exists(dir.appendingPathComponent("stage/data.csv")))258 }259260 @Test func pythonStepRefusesUnapprovedScripts() async throws {261 let dir = TestEnv.tempDir()262 PythonPaths.supportDirectory = TestEnv.support263 let wb = TestEnv.modelWorkbook()264 PythonScripts.save(PythonScript(name: "Forecast", code: "print('hello')\n"), to: wb)265 let (run, out) = try await TestEnv.run([.runPython(RunPythonSettings(script: "Forecast"))], workbook: wb.clone(), in: dir)266 #expect(run.status == .failed && out == nil)267 #expect(run.message.contains("Script not approved"))268 let (missing, _) = try await TestEnv.run([.runPython(RunPythonSettings(script: "Other"))], workbook: wb.clone(), in: dir)269 #expect(missing.status == .failed && missing.message.contains("no Python script named"))270 }271}272