SPB Git

Repository Public

LibreOfficeTests.swift 10.5 KB 196 lines swift

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
Files main
Menu

Theme