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/LibreOfficeTests.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 SheetIO92@testable import SheetTemplates9394/// Independent reference check of the whole stack: every template is exported to XLSX with its cached formula95/// results deliberately replaced by a sentinel, LibreOffice (headless) opens the files, recalculates them and saves96/// them again, and the values LibreOffice computed are compared with those of ORIX Sheet — every formula cell and97/// every spilled cell, within 1e-6 relative.98///99/// Because the exported files carry the sentinel instead of real results, a value that matches can only come from100/// LibreOffice's own calculation. The detailed report is written to /tmp/orix-template-io/libreoffice-report.txt.101/// The test is skipped when soffice is not installed or when ORIX_TEMPLATES_SKIP_LO is set.102@Suite struct LibreOfficeCrossCheck {103 static let soffice = "/opt/homebrew/bin/soffice"104 static let profile = "/tmp/lo_profile_templates"105 static let sentinel = -987_654.321106107 /// Differences that are understood and accepted, per workbook tag: (sheet index, A1 range, reason). Keep it short.108 static let accepted: [String: [(sheet: Int, range: String, reason: String)]] = [:]109110 struct Outcome { var compared = 0; var mismatches: [String] = [] }111112 @Test func libreOfficeRecalculatesEveryTemplateToTheSameValues() throws {113 guard FileManager.default.isExecutableFile(atPath: Self.soffice), ProcessInfo.processInfo.environment["ORIX_TEMPLATES_SKIP_LO"] == nil else { return }114 let fm = FileManager.default115 let input = scratch.appendingPathComponent("lo-in"), output = scratch.appendingPathComponent("lo-out")116 for dir in [input, output] { try? fm.removeItem(at: dir); try fm.createDirectory(at: dir, withIntermediateDirectories: true) }117118 // 1. export with poisoned caches119 for c in Case.all {120 let poisoned = BuildCache.shared.get(c).workbook.clone()121 for s in poisoned.sheets {122 var spilled: [(Int, Int, Cell)] = []123 s.cells.forEachAll { r, col, cell in124 if let f = cell.formula { f.value = .number(Self.sentinel) } else if cell.isSpill { spilled.append((r, col, cell)) }125 }126 for (r, col, var cell) in spilled { cell.value = .number(Self.sentinel); s.cells.set(row: r, col: col, cell) }127 }128 try WorkbookWriter.save(poisoned, to: input.appendingPathComponent("\(c.tag).xlsx"), format: .xlsx)129 }130131 // 2. LibreOffice: recalculate on load (private profile), save again as XLSX132 let registry = URL(fileURLWithPath: Self.profile).appendingPathComponent("user/registrymodifications.xcu")133 if !fm.fileExists(atPath: registry.path) {134 try fm.createDirectory(at: registry.deletingLastPathComponent(), withIntermediateDirectories: true)135 try """136 <?xml version="1.0" encoding="UTF-8"?>137 <oor:items xmlns:oor="http://openoffice.org/2001/registry" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">138 <item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="OOXMLRecalcMode" oor:op="fuse"><value>0</value></prop></item>139 <item oor:path="/org.openoffice.Office.Calc/Formula/Load"><prop oor:name="ODFRecalcMode" oor:op="fuse"><value>0</value></prop></item>140 </oor:items>141 """.write(to: registry, atomically: true, encoding: .utf8)142 }143 let p = Process()144 p.executableURL = URL(fileURLWithPath: Self.soffice)145 p.arguments = ["--headless", "--norestore", "-env:UserInstallation=file://\(Self.profile)", "--convert-to", "xlsx:Calc MS Excel 2007 XML", "--outdir", output.path]146 + Case.all.map { input.appendingPathComponent("\($0.tag).xlsx").path }147 p.standardOutput = FileHandle.nullDevice; p.standardError = FileHandle.nullDevice148 try p.run()149 p.waitUntilExit()150 #expect(p.terminationStatus == 0, "soffice exited with \(p.terminationStatus)")151152 // 3. compare153 var report = "LibreOffice cross-check — every formula and spilled cell, relative tolerance 1e-6\n"154 var total = Outcome()155 for c in Case.all {156 let ours = BuildCache.shared.get(c).workbook157 let url = output.appendingPathComponent("\(c.tag).xlsx")158 guard fm.fileExists(atPath: url.path) else { Issue.record("\(c.tag): LibreOffice produced no file"); continue }159 let theirs = try WorkbookReader.load(url: url).workbook160 var o = Outcome(), skipped = 0161 #expect(ours.sheets.count == theirs.sheets.count, "\(c.tag): sheet count")162 for (si, (a, b)) in zip(ours.sheets, theirs.sheets).enumerated() {163 let waived = (Self.accepted[c.tag] ?? []).filter { $0.sheet == si }.compactMap { RangeRef(a1: $0.range) }164 a.cells.forEachAll { r, col, cell in165 guard cell.formula != nil || cell.isSpill else { return }166 if waived.contains(where: { $0.contains(row: r, col: col) }) { skipped += 1; return }167 o.compared += 1168 let x = cell.computed.scalar, y = b.value(r, col).scalar169 if !Self.same(x, y) { o.mismatches.append("\(a.name)!\(CellRef(r, col).a1): ORIX \(x) — LibreOffice \(y) [\(cell.formula.map { "=" + $0.expr.formulaText() } ?? "spill")]") }170 }171 }172 report += "\n\(c.tag): \(o.compared) cells compared, \(o.mismatches.count) mismatches" + (skipped > 0 ? ", \(skipped) waived" : "") + "\n"173 for w in Self.accepted[c.tag] ?? [] { report += " waived \(ours.sheets[w.sheet].name)!\(w.range): \(w.reason)\n" }174 for m in o.mismatches.sorted().prefix(25) { report += " \(m)\n" }175 total.compared += o.compared; total.mismatches += o.mismatches.map { "\(c.tag) \($0)" }176 #expect(o.mismatches.isEmpty, "\(c.tag): \(o.mismatches.count) cells differ from LibreOffice, e.g. \(o.mismatches.sorted().prefix(3))")177 #expect(o.compared > 20, "\(c.tag): too few cells compared")178 }179 report += "\nTOTAL: \(total.compared) cells compared in \(Case.all.count) workbooks, \(total.mismatches.count) mismatches\n"180 try report.write(to: scratch.appendingPathComponent("libreoffice-report.txt"), atomically: true, encoding: .utf8)181 print(report.split(separator: "\n").last ?? "")182 }183184 /// Same value within 1e-6 relative; an empty string and a blank are the same thing after a file round trip.185 static func same(_ x: Value, _ y: Value) -> Bool {186 switch (x, y) {187 case (.number(let a), .number(let b)): return a == b || abs(a - b) <= 1e-6 * max(abs(a), abs(b)) || abs(a - b) < 1e-9188 case (.text(let a), .text(let b)): return a == b189 case (.text(""), .blank), (.blank, .text("")), (.blank, .blank): return true190 case (.bool(let a), .bool(let b)): return a == b191 case (.number(let a), .bool(let b)), (.bool(let b), .number(let a)): return a == (b ? 1 : 0)192 default: return false193 }194 }195}196