SPB Git

Repository Public

OutputTests.swift 15.1 KB 272 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/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
Files main
Menu

Theme