Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetTemplates/Tests/SheetTemplatesTests/StructureTests.swift10// Module : SheetTemplatesTests11// 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 SheetEngine91import SheetCharts92import SheetIO93@testable import SheetTemplates9495@Suite struct Library {96 @Test func fifteenTemplatesWithUniqueIdentifiers() {97 #expect(TemplateLibrary.all.count == 15)98 #expect(Set(TemplateLibrary.all.map(\.id)).count == 15)99 for c in TemplateCategory.allCases { #expect(!TemplateLibrary.templates(in: c).isEmpty, "no template in \(c)") }100 for t in TemplateLibrary.all {101 #expect(TemplateLibrary.template(id: t.id)?.title == t.title)102 #expect(!t.summary.isEmpty && !t.systemImage.isEmpty)103 #expect(t.title(locale: french) != "" && t.summary(locale: french) != t.summary)104 }105 #expect(TemplateLibrary.template(id: "nope") == nil)106 #expect(TemplateLibrary.blank(locale: english).sheets.map(\.name) == ["Sheet1"])107 #expect(TemplateLibrary.blank(locale: french).sheets.map(\.name) == ["Feuille1"])108 }109}110111@Suite struct Structure {112 @Test(arguments: Case.all) func buildsWithoutProblemOrErrorValue(_ c: Case) {113 let (wb, problems) = BuildCache.shared.get(c)114 #expect(problems.isEmpty, "\(c.tag): \(problems)")115 let errors = errorCells(wb)116 #expect(errors.isEmpty, "\(c.tag): \(errors.prefix(12))")117 // nothing left to calculate, and no formula evaluates to a lambda or a blocked spill118 let engine = CalcEngine(workbook: wb, trustCachedValues: true)119 #expect(!engine.hasPendingWork || engine.pendingCount == 0)120 #expect(wb.sheets.allSatisfy { Workbook.isValidSheetName($0.name) })121 }122123 @Test(arguments: Case.all) func everyFormulaUsesRegisteredFunctions(_ c: Case) {124 let wb = BuildCache.shared.get(c).workbook125 var names = Set<String>(), formulas = 0126 for s in wb.sheets { s.cells.forEachAll { _, _, cell in if let f = cell.formula { formulas += 1; names.formUnion(functionNames(f.expr)) } } }127 #expect(formulas > 20, "\(c.tag) has only \(formulas) formulas")128 let unknown = names.filter { FunctionRegistry.standard.lookup($0) == nil && !ORIXEngine.specialForms.contains($0) }129 #expect(unknown.isEmpty, "\(c.tag): unregistered \(unknown.sorted())")130 // conditional-format formulas, validation formulas and defined names parse and use known functions too131 for s in wb.sheets {132 for cf in s.meta.conditionalFormats {133 var texts: [String] = []134 switch cf.kind {135 case .formula(let f): texts = [f]136 case .cellIs(_, let fs): texts = fs137 default: break138 }139 for t in texts {140 let e = try? FormulaParser.parse(t)141 #expect(e != nil, "\(c.tag) \(s.name): conditional formula \(t)")142 if let e { #expect(functionNames(e).allSatisfy { FunctionRegistry.standard.lookup($0) != nil || ORIXEngine.specialForms.contains($0) }) }143 }144 #expect(cf.style != DiffStyle() || { if case .dataBar = cf.kind { true } else { false } }(), "\(c.tag) \(s.name): rule without a format")145 }146 for v in s.meta.validations {147 for t in [v.formula1, v.formula2].compactMap({ $0 }) { #expect((try? FormulaParser.parse(t)) != nil, "\(c.tag) \(s.name): validation formula \(t)") }148 #expect(v.errorMessage?.isEmpty == false)149 }150 }151 for dn in wb.names.values {152 let v = wb.eval(dn.name)153 #expect(!v.scalar.isError, "\(c.tag): name \(dn.name) = \(v.scalar)")154 }155 }156157 @Test(arguments: Case.all) func sheetsAreDocumentedAndReadyToPrint(_ c: Case) {158 let wb = BuildCache.shared.get(c).workbook159 #expect(wb.properties.title == c.template.title(locale: c.locale))160 #expect(wb.activeSheet == 0)161 var notes = 0, validations = 0162 for s in wb.sheets {163 // title block: title, subtitle, "how to use" note stating that the figures are examples164 let how = s.value(2, 0).textValue ?? ""165 #expect(s.value(0, 0).isText && s.value(1, 0).isText, "\(c.tag) \(s.name): title block")166 #expect(how.hasPrefix(c.locale == french ? "Mode d’emploi" : "How to use"), "\(c.tag) \(s.name): how-to note")167 #expect(how.contains("exemple") || how.contains("example") || how.contains("formul") || how.contains("résultats") || how.contains("results") || how.contains("Rien") || how.contains("othing"),168 "\(c.tag) \(s.name): the note must say the figures are examples or that the sheet is calculated: \(how)")169 #expect(s.meta.tabColor != nil)170 #expect(s.meta.print.fitToWidth == 1 && s.meta.print.header.contains("&A") && s.meta.print.footer.contains("&P"))171 #expect(s.meta.frozenRows > 0, "\(c.tag) \(s.name): frozen header rows")172 #expect(!s.meta.colWidths.isEmpty)173 notes += s.meta.comments.count; validations += s.meta.validations.count174 // merges never overlap175 for (i, a) in s.meta.merges.enumerated() { for b in s.meta.merges.dropFirst(i + 1) { #expect(!a.intersects(b), "\(c.tag) \(s.name): overlapping merges") } }176 }177 #expect(notes >= 2, "\(c.tag): cell notes explaining inputs")178 #expect(validations >= 1, "\(c.tag): data validation on input cells")179 #expect(!wb.names.isEmpty, "\(c.tag): named ranges for key inputs")180 }181182 @Test(arguments: Case.all) func chartsReadNumericData(_ c: Case) throws {183 let wb = BuildCache.shared.get(c).workbook184 let source = WorkbookDataSource(workbook: wb)185 for s in wb.sheets {186 for o in s.meta.objects {187 let spec = try #require(ChartSpec(sheetObject: o), "\(c.tag) \(s.name): chart payload")188 #expect(!spec.title.isEmpty && !spec.series.isEmpty)189 for series in spec.series {190 let ref = try #require(series.values)191 if case .range(let sheet, _) = ref { #expect(wb.sheet(named: sheet) != nil, "\(c.tag): chart sheet \(sheet)") }192 let numbers = source.values(ref).compactMap(\.numberValue)193 #expect(numbers.count >= 2, "\(c.tag) \(spec.title): series \(series.name) has \(numbers.count) numeric points")194 }195 #expect(o.width > 100 && o.height > 100 && o.x >= 0 && o.y >= 0)196 }197 }198 }199200 /// English and French builds differ only by their labels: every sheet holds the same numbers (cell by cell, except201 /// where a sorted dynamic array orders translated labels differently — there the same numbers appear in another order).202 @Test(arguments: TemplateLibrary.all.map(\.id)) func localesShareTheSameNumbers(_ id: String) {203 let en = BuildCache.shared.get(Case(id: id, locale: english)).workbook, fr = BuildCache.shared.get(Case(id: id, locale: french)).workbook204 #expect(en.sheets.count == fr.sheets.count)205 #expect(en.sheets.map(\.name) != fr.sheets.map(\.name), "\(id): sheet names are localised")206 var compared = 0, textDiffers = 0, moved = 0207 for (a, b) in zip(en.sheets, fr.sheets) {208 #expect(a.cells.count == b.cells.count, "\(id) \(a.name): cell count")209 var na: [Double] = [], nb: [Double] = []210 a.cells.forEachAll { r, col, cell in211 switch cell.computed {212 case .number(let x):213 compared += 1; na.append(x)214 if b.value(r, col) != .number(x) { moved += 1 }215 case .text(let t): if b.value(r, col) != .text(t) { textDiffers += 1 }216 default: break217 }218 }219 b.cells.forEachAll { _, _, cell in if case .number(let y) = cell.computed { nb.append(y) } }220 #expect(na.sorted() == nb.sorted(), "\(id) \(a.name): the two locales hold different numbers")221 #expect(a.meta.colWidths == b.meta.colWidths && a.meta.merges == b.meta.merges)222 }223 #expect(compared > 50 && textDiffers > 10, "\(id): \(compared) numbers, \(textDiffers) translated labels")224 #expect(moved == 0 || id == "sales-tracking", "\(id): \(moved) numbers sit in different cells")225 }226}227