SPB Git

Repository Public

TestSupport.swift 21.3 KB 382 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetIO/Tests/SheetIOTests/TestSupport.swift10// Module           : SheetIOTests11// 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 SheetCore90@testable import SheetIO9192struct SplitMix64: RandomNumberGenerator {93    var state: UInt6494    init(seed: UInt64) { state = seed }95    mutating func next() -> UInt64 {96        state &+= 0x9E37_79B9_7F4A_7C1597        var z = state98        z = (z ^ (z >> 30)) &* 0xBF58_476D_1CE4_E5B999        z = (z ^ (z >> 27)) &* 0x94D0_49BB_1331_11EB100        return z ^ (z >> 31)101    }102}103104enum Scratch {105    /// Fresh temporary directory, removed by the caller or left to the system.106    static func dir(_ name: String = "t") -> URL {107        let u = FileManager.default.temporaryDirectory.appendingPathComponent("sheetio-\(name)-\(UUID().uuidString)", isDirectory: true)108        try! FileManager.default.createDirectory(at: u, withIntermediateDirectories: true)109        return u110    }111    static var fixtures: URL { Bundle.module.resourceURL!.appendingPathComponent("Fixtures") }112    static func fixture(_ name: String) -> URL { fixtures.appendingPathComponent(name) }113    static var generators: URL { URL(fileURLWithPath: #filePath).deletingLastPathComponent().appendingPathComponent("Generators") }114}115116enum Tools {117    static let python = "/opt/homebrew/bin/python3"118    static let soffice = "/opt/homebrew/bin/soffice"119    static var hasPython: Bool { FileManager.default.isExecutableFile(atPath: python) }120    static var hasLibreOffice: Bool { FileManager.default.isExecutableFile(atPath: soffice) }121122    /// Runs a tool with a hard timeout; both pipes are drained concurrently so a chatty child cannot block.123    @discardableResult124    static func run(_ exe: String, _ args: [String], cwd: URL? = nil, timeout: TimeInterval = 90) throws -> (status: Int32, out: String, err: String) {125        let p = Process()126        p.executableURL = URL(fileURLWithPath: exe)127        p.arguments = args128        if let cwd { p.currentDirectoryURL = cwd }129        let o = Pipe(), e = Pipe()130        p.standardOutput = o; p.standardError = e131        try p.run()132        let killer = DispatchWorkItem { if p.isRunning { p.terminate() } }133        DispatchQueue.global().asyncAfter(deadline: .now() + timeout, execute: killer)134        final class Box: @unchecked Sendable { var data = Data() }135        let errBox = Box()136        let group = DispatchGroup()137        group.enter()138        DispatchQueue.global().async { errBox.data = e.fileHandleForReading.readDataToEndOfFile(); group.leave() }139        let od = o.fileHandleForReading.readDataToEndOfFile()140        group.wait()141        p.waitUntilExit()142        killer.cancel()143        return (p.terminationStatus, String(decoding: od, as: UTF8.self), String(decoding: errBox.data, as: UTF8.self))144    }145146    /// What openpyxl sees in a workbook, as JSON (see Generators/dump_xlsx.py).147    static func openpyxlDump(_ url: URL) throws -> [String: Any] {148        let r = try run(python, ["-I", generators("dump_xlsx.py"), url.path])149        guard r.status == 0, let obj = try JSONSerialization.jsonObject(with: Data(r.out.utf8)) as? [String: Any] else {150            throw SheetIOError.internalError("openpyxl failed: \(r.err.suffix(600))")151        }152        return obj153    }154    static func generators(_ name: String) -> String { Scratch.generators.appendingPathComponent(name).path }155156    private static let officeLock = NSLock()157    /// LibreOffice headless conversion with a private profile. Returns the output files.158    static func libreOfficeConvert(_ inputs: [URL], to format: String, outDir: URL) throws -> [URL] {159        officeLock.lock(); defer { officeLock.unlock() }160        let r = try run(soffice, ["-env:UserInstallation=file:///tmp/lo_profile_io", "--headless", "--norestore", "--convert-to", format, "--outdir", outDir.path] + inputs.map(\.path))161        let ext = String(format.split(separator: ":").first ?? "")162        let outs = inputs.map { outDir.appendingPathComponent($0.deletingPathExtension().lastPathComponent + "." + ext) }163        for o in outs where !FileManager.default.fileExists(atPath: o.path) {164            throw SheetIOError.internalError("LibreOffice did not produce \(o.lastPathComponent): \(r.out.suffix(300)) \(r.err.suffix(300))")165        }166        return outs167    }168}169170func cellValue(_ wb: Workbook, _ sheet: Int, _ a1: String) -> Value {171    let r = CellRef(a1: a1)!172    return wb.sheets[sheet].value(r.row, r.col)173}174func cellFormula(_ wb: Workbook, _ sheet: Int, _ a1: String) -> String? {175    let r = CellRef(a1: a1)!176    return wb.sheets[sheet].cell(r.row, r.col)?.formula?.expr.formulaText()177}178func setCell(_ sheet: Sheet, _ a1: String, _ v: Value, style: Int32 = 0) {179    let r = CellRef(a1: a1)!180    sheet.cells.set(row: r.row, col: r.col, Cell(value: v, style: style))181}182func setFormula(_ sheet: Sheet, _ a1: String, _ text: String, cached: Value, style: Int32 = 0, spill: String? = nil) {183    let r = CellRef(a1: a1)!184    let f = Formula(expr: try! FormulaParser.parse(text))185    f.value = cached186    if let spill { f.spill = RangeRef(a1: spill) }187    sheet.cells.set(row: r.row, col: r.col, Cell(formula: f, style: style))188}189190/// Random workbooks exercising every field of the SheetCore model.191struct RandomWorkbook {192    var rng: SplitMix64193    init(seed: UInt64) { rng = SplitMix64(seed: seed) }194195    mutating func int(_ r: ClosedRange<Int>) -> Int { Int.random(in: r, using: &rng) }196    mutating func bool(_ p: Double = 0.5) -> Bool { Double.random(in: 0..<1, using: &rng) < p }197    mutating func pick<T>(_ a: [T]) -> T { a[int(0...(a.count - 1))] }198    mutating func double() -> Double {199        switch int(0...9) {200        case 0: return Double(int(-1000...1000))201        case 1: return Double.random(in: -1e-300...1e-300, using: &rng)202        case 2: return Double.random(in: -1e300...1e300, using: &rng)203        case 3: return pick([0.1, 0.2, 0.30000000000000004, 1.0 / 3.0, .pi, 1e15, 9007199254740992, -0.0, 5e-324, .greatestFiniteMagnitude])204        default: return Double.random(in: -1e6...1e6, using: &rng)205        }206    }207    mutating func string(max: Int = 24) -> String {208        let alphabet: [String] = ["a", "b", "Z", "é", "ü", "日", "本", "😀", "👨‍👩‍👧", " ", "\n", "\t", "\"", "'", "<", ">", "&", ",", ";", "=", "_x0041_", "\r", "\u{1}", "0", "9", "-", "%"]209        return (0..<int(0...max)).map { _ in pick(alphabet) }.joined()210    }211    mutating func color() -> RGBA { RGBA(r: UInt8(int(0...255)), g: UInt8(int(0...255)), b: UInt8(int(0...255)), a: bool(0.9) ? 255 : UInt8(int(0...255))) }212    mutating func range(maxRow: Int = 200, maxCol: Int = 40) -> RangeRef {213        let r0 = int(0...maxRow), c0 = int(0...maxCol)214        return RangeRef(r0: r0, c0: c0, r1: r0 + int(0...8), c1: c0 + int(0...5))215    }216    mutating func value(allowArray: Bool = true) -> Value {217        switch int(0...11) {218        case 0...4: return .number(double())219        case 5...7: return .text(string())220        case 8: return .bool(bool())221        case 9: return .error(pick(CellError.allCases))222        case 10: return .text(String(repeating: pick(["x", "é", "数"]), count: int(0...3000)))223        default:224            guard allowArray else { return .blank }225            let rows = int(1...3), cols = int(1...3)226            var items = [Value]()227            for _ in 0..<(rows * cols) { items.append(value(allowArray: false)) }228            return .array(ArrayValue(rows: rows, cols: cols, items: items))229        }230    }231    mutating func style() -> CellStyle {232        var s = CellStyle()233        if bool(0.3) { s.fontName = pick(["Arial", "Menlo", "Times New Roman", "Hiragino Sans"]) }234        if bool(0.3) { s.fontSize = Double(int(6...40)) + (bool(0.2) ? 0.5 : 0) }235        s.bold = bool(0.3); s.italic = bool(0.3); s.strikethrough = bool(0.1)236        s.underline = pick(UnderlineStyle.allCases)237        if bool(0.4) { s.textColor = color() }238        if bool(0.4) { s.fill = color() }239        if bool(0.3) { s.borders.left = BorderEdge(style: pick(BorderStyle.allCases), color: color()) }240        if bool(0.3) { s.borders.right = BorderEdge(style: pick(BorderStyle.allCases), color: color()) }241        if bool(0.3) { s.borders.top = BorderEdge(style: pick(BorderStyle.allCases), color: color()) }242        if bool(0.3) { s.borders.bottom = BorderEdge(style: pick(BorderStyle.allCases), color: color()) }243        s.hAlign = pick(HAlign.allCases); s.vAlign = pick(VAlign.allCases)244        s.wrap = bool(0.2); s.shrinkToFit = bool(0.1); s.rotation = bool(0.2) ? int(-90...90) : 0; s.indent = bool(0.2) ? int(0...10) : 0245        s.numberFormat = pick(["General", "0.00", "#,##0", "0%", "yyyy-mm-dd", "h:mm:ss", "[$€-fr-FR] #,##0.00;[Red]-#,##0.00", "@", "0.00E+00", "\"x\"0"])246        s.locked = bool(0.8); s.formulaHidden = bool(0.1)247        return s248    }249250    static let formulas = ["A1+B2", "SUM(A1:C10)*2", "IF($A$1>0,\"yes\",\"no\")", "'Other Sheet'!A1&\"x\"", "LET(x,5,x*2)", "SEQUENCE(3,2)", "A1#",251                           "Table1[Amount]", "{1,2;3,4}", "-A1^2%", "VLOOKUP(A1,$B$1:$D$20,3,FALSE)", "SUM(A:A,1:3)", "LAMBDA(a,a+1)(3)", "@A1:A10",252                           "#REF!+1", "\"é😀\"&A1", "1E-05+1.5E+20", "PMT(0.05/12,360,,1)", "MyName*2", "Sheet1!MyLocal"]253254    mutating func scalePoint() -> ScalePoint {255        ScalePoint(kind: pick(["min", "max", "num", "percent", "percentile", "formula"]), value: bool() ? String(int(0...100)) : nil, color: bool() ? color() : nil)256    }257258    mutating func meta(styleCount: Int) -> SheetMeta {259        var m = SheetMeta()260        for _ in 0..<int(0...12) { m.colWidths[int(0...60)] = Double.random(in: 1...400, using: &rng) }261        for _ in 0..<int(0...12) { m.rowHeights[int(0...500)] = Double.random(in: 1...300, using: &rng) }262        if bool() { m.defaultColWidth = Double.random(in: 20...200, using: &rng) }263        if bool() { m.defaultRowHeight = Double.random(in: 10...60, using: &rng) }264        for _ in 0..<int(0...5) { m.hiddenRows.insert(int(0...1000)); m.hiddenCols.insert(int(0...100)); m.filteredRows.insert(int(0...1000)) }265        for _ in 0..<int(0...5) { m.rowOutline[int(0...300)] = int(1...7); m.colOutline[int(0...50)] = int(1...7) }266        for _ in 0..<int(0...3) { m.collapsedRows.insert(int(0...300)); m.collapsedCols.insert(int(0...50)) }267        for _ in 0..<int(0...5) { m.colStyles[int(0...50)] = Int32(int(0...(styleCount - 1))); m.rowStyles[int(0...200)] = Int32(int(0...(styleCount - 1))) }268        for _ in 0..<int(0...5) { m.merges.append(range()) }269        m.frozenRows = bool(0.3) ? int(0...5) : 0; m.frozenCols = bool(0.3) ? int(0...3) : 0270        for i in 0..<int(0...2) {271            let r = range()272            var t = TableDef(id: i + 1, name: "Table\(i + 1)", range: r, columns: (0..<r.cols).map { TableColumn(name: "C\($0)" + string(max: 4), totalsFunction: bool(0.3) ? "sum" : nil, calculatedFormula: bool(0.2) ? "[@C0]*2" : nil) })273            t.hasHeader = bool(0.8); t.hasTotals = bool(0.3); t.styleName = bool(0.8) ? "TableStyleMedium\(int(1...20))" : nil274            t.showRowStripes = bool(); t.autoFilter = bool()275            m.tables.append(t)276        }277        for p in 0..<int(0...8) {278            let kind: ConditionalRuleKind279            switch int(0...10) {280            case 0: kind = .cellIs(op: pick(["greaterThan", "between", "equal"]), formulas: ["1", "A1*2"].prefix(int(1...2)).map { $0 })281            case 1: kind = .formula("MOD(ROW(),2)=0")282            case 2: kind = .colorScale(points: (0..<int(2...3)).map { _ in scalePoint() })283            case 3: kind = .dataBar(min: scalePoint(), max: scalePoint(), color: color())284            case 4: kind = .iconSet(name: pick(["3Arrows", "4Rating", "5Quarters"]), thresholds: (0..<int(0...5)).map { _ in scalePoint() }, reversed: bool())285            case 5: kind = .duplicates(unique: bool())286            case 6: kind = .top(rank: int(1...100), percent: bool(), bottom: bool())287            case 7: kind = .aboveAverage(above: bool(), equal: bool())288            case 8: kind = .containsText(string())289            case 9: kind = .blanks(bool())290            default: kind = .errors(bool())291            }292            var d = DiffStyle(bold: bool() ? bool() : nil, italic: bool() ? bool() : nil, textColor: bool() ? color() : nil, fill: bool() ? color() : nil)293            d.strikethrough = bool() ? bool() : nil294            d.numberFormat = bool(0.3) ? "0.00" : nil295            var cf = ConditionalFormat(ranges: (0..<int(1...3)).map { _ in range() }, kind: kind, style: d, priority: p + 1)296            cf.stopIfTrue = bool(0.2)297            m.conditionalFormats.append(cf)298        }299        for _ in 0..<int(0...5) {300            var v = DataValidation(ranges: (0..<int(1...2)).map { _ in range() }, kind: pick(DataValidation.Kind.allCases), op: pick(["between", "notEqual", "greaterThan"]),301                                   formula1: bool(0.8) ? pick(["1", "\"a,b,c\"", "$A$1:$A$5", "TODAY()"]) : nil, formula2: bool(0.3) ? "100" : nil)302            v.allowBlank = bool(); v.showDropdown = bool()303            v.errorTitle = bool() ? string() : nil; v.errorMessage = bool() ? string(max: 80) : nil304            v.promptTitle = bool() ? string() : nil; v.promptMessage = bool() ? string(max: 80) : nil305            v.errorStyle = pick(["stop", "warning", "information"])306            m.validations.append(v)307        }308        for _ in 0..<int(0...6) {309            m.comments[CellRef(int(0...300), int(0...40))] = CellComment(author: string(), text: string(max: 200), isNote: bool(),310                                                                       date: bool() ? Date(timeIntervalSinceReferenceDate: Double.random(in: 0...9e8, using: &rng)) : nil)311        }312        for _ in 0..<int(0...6) {313            m.hyperlinks[CellRef(int(0...300), int(0...40))] = Hyperlink(target: pick(["https://example.com/a?b=1&c=é", "#Sheet1!A1", "mailto:a@b.co", "file:///tmp/x y.txt#frag"]),314                                                                       tooltip: bool() ? string() : nil, display: bool() ? string() : nil)315        }316        if bool(0.4) { m.autoFilter = range() }317        for _ in 0..<int(0...3) {318            let payload = Data((0..<int(0...2000)).map { _ in UInt8(int(0...255)) })319            m.objects.append(SheetObject(kind: pick(["chart", "image", "pivot"]), name: string(), x: double(), y: Double(int(0...5000)), width: 320.5, height: 200, payload: payload))320        }321        var p = PrintSettings()322        if bool() {323            p.landscape = bool(); p.paperName = pick(["Letter", "A4", "Custom"]); p.paperWidthPt = Double.random(in: 200...1200, using: &rng); p.paperHeightPt = 841.89324            p.marginTop = Double.random(in: 0...100, using: &rng); p.marginLeft = 36; p.headerMargin = 0.3 * 72325            p.header = "&L" + string() + "&C&P / &N"; p.footer = string()326            p.scalePercent = int(10...400); p.fitToWidth = bool() ? int(0...3) : nil; p.fitToHeight = bool() ? int(0...3) : nil327            p.printArea = bool() ? range() : nil328            p.repeatRows = bool() ? 0...int(0...3) : nil; p.repeatCols = bool() ? 1...int(1...3) : nil329            p.rowBreaks = (0..<int(0...3)).map { _ in int(1...500) }; p.colBreaks = (0..<int(0...2)).map { _ in int(1...30) }330            p.showGridlines = bool(); p.showHeadings = bool(); p.centerHorizontally = bool(); p.centerVertically = bool()331        }332        m.print = p333        m.showGridlines = bool(0.8); m.showHeadings = bool(0.8); m.zoom = pick([1.0, 0.75, 1.5, 2.0, 0.333])334        m.tabColor = bool(0.3) ? color() : nil; m.isHidden = bool(0.1); m.isProtected = bool(0.1)335        return m336    }337338    mutating func workbook(cellsPerSheet: ClosedRange<Int> = 0...400) -> Workbook {339        let wb = Workbook()340        let names = ["Sheet1", "Other Sheet", "Données 2024", "It's", "数据", "A.B-C", "Q1", "x"]341        let count = int(1...4)342        for i in 0..<(count + 1) { wb.addSheet(named: names[i]) }343        // remove one sheet so that identifiers are not contiguous, and shuffle the order344        wb.sheets.remove(at: int(0...count))345        wb.sheets.shuffle(using: &rng)346        var styleIDs: [Int32] = [0]347        for _ in 0..<int(0...12) { styleIDs.append(wb.styles.intern(style())) }348        wb.date1904 = bool(0.2)349        wb.activeSheet = int(0...(wb.sheets.count - 1))350        if bool() { wb.defaultFontName = "Avenir Next"; wb.defaultFontSize = 11.5 }351        wb.properties.title = string(); wb.properties.author = string(); wb.properties.subject = string()352        wb.properties.keywords = string(); wb.properties.comments = string(max: 100)353        wb.properties.created = bool() ? Date(timeIntervalSinceReferenceDate: Double.random(in: 0...9e8, using: &rng)) : nil354        wb.properties.modified = bool() ? Date(timeIntervalSinceReferenceDate: 7.5e8 + 0.123456) : nil355        for _ in 0..<int(0...3) { wb.attachments["scripts/" + string(max: 8) + ".py"] = Data((0..<int(0...5000)).map { _ in UInt8(int(0...255)) }) }356        wb.setDefinedName(DefinedName(name: "MyName", formula: "Sheet1!$A$1:$B$5", comment: bool() ? string() : nil))357        var local = DefinedName(name: "MyLocal", formula: "1+1", scopeSheetID: wb.sheets[0].id)358        local.hidden = bool()359        wb.setDefinedName(local)360        for sheet in wb.sheets {361            sheet.meta = meta(styleCount: wb.styles.count)362            for _ in 0..<int(cellsPerSheet) {363                let r = bool(0.9) ? int(0...300) : int(0...(Limits.maxRows - 1)), c = bool(0.9) ? int(0...30) : int(0...(Limits.maxCols - 1))364                var cell = Cell(style: pick(styleIDs))365                switch int(0...9) {366                case 0...5: cell.value = value()367                case 6, 7:368                    let f = Formula(expr: try! FormulaParser.parse(pick(Self.formulas)))369                    f.value = value(allowArray: false)370                    if bool(0.2) { f.spill = RangeRef(r0: r, c0: c, r1: min(r + 2, Limits.maxRows - 1), c1: min(c + 1, Limits.maxCols - 1)) }371                    f.isVolatile = bool(0.1)372                    cell.formula = f373                case 8: cell.value = value(allowArray: false); cell.isSpill = true374                default: break       // style-only cell375                }376                sheet.cells.set(row: r, col: c, cell)377            }378        }379        return wb380    }381}382
Files main
Menu

Theme