Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetIO/Tests/SheetIOTests/NativeFormatTests.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 SheetIO9192@Suite struct NativeFormatTests {93 @Test func randomWorkbooksRoundTripLosslessly() throws {94 let dir = Scratch.dir("native")95 defer { try? FileManager.default.removeItem(at: dir) }96 for seed in 1...40 {97 var gen = RandomWorkbook(seed: UInt64(seed))98 let wb = gen.workbook()99 let url = dir.appendingPathComponent("w\(seed).orixsheet")100 let issues = try NativeFormat.write(wb, to: url)101 #expect(issues.isEmpty, "seed \(seed): \(issues)")102 let loaded = try NativeFormat.read(url: url)103 #expect(loaded.issues.isEmpty, "seed \(seed): \(loaded.issues)")104 let diff = WorkbookDiff.differences(wb, loaded.workbook, options: .exact)105 #expect(diff.isEmpty, "seed \(seed): \(diff.prefix(5))")106 #expect(try NativeFormat.verify(url: url).isEmpty)107 // a second generation must be byte-identical for every content part (stable encoding)108 let again = dir.appendingPathComponent("w\(seed)b.orixsheet")109 try NativeFormat.write(loaded.workbook, to: again)110 for part in ["workbook.json"] + wb.sheets.flatMap({ ["sheets/\($0.id).cells", "sheets/\($0.id).meta.json"] }) {111 let x = try Data(contentsOf: url.appendingPathComponent(part)), y = try Data(contentsOf: again.appendingPathComponent(part))112 #expect(x == y, "seed \(seed): \(part) is not stable")113 }114 }115 }116}117118@Suite struct NativeFormatRobustnessTests {119 static func threeSheets() -> Workbook {120 let wb = Workbook(sheetNames: ["Alpha", "Beta", "Gamma"])121 for (i, s) in wb.sheets.enumerated() {122 for r in 0..<400 { for c in 0..<5 { s.cells.set(row: r, col: c, Cell(value: c == 0 ? .text("\(s.name) row \(r)") : .number(Double(r * c + i)))) } }123 setFormula(s, "G1", "SUM(B1:B400)", cached: .number(Double(i)))124 s.meta.colWidths[0] = 120 + Double(i)125 s.meta.merges = [RangeRef(a1: "I1:J2")!]126 }127 wb.setDefinedName(DefinedName(name: "Total", formula: "Alpha!$G$1"))128 wb.attachments["scripts/clean.py"] = Data("print('hi')".utf8)129 return wb130 }131132 @Test func comparatorDetectsEveryKindOfChange() {133 let a = Self.threeSheets()134 func changed(_ mutate: (Workbook) -> Void) -> Bool {135 let b = Self.threeSheets()136 mutate(b)137 return !WorkbookDiff.differences(a, b, options: .exact).isEmpty138 }139 #expect(WorkbookDiff.differences(a, Self.threeSheets(), options: .exact).isEmpty)140 #expect(changed { setCell($0.sheets[1], "C7", .number(-1)) })141 #expect(changed { setCell($0.sheets[1], "A7", .text("alpha row 6")) })142 #expect(changed { setFormula($0.sheets[0], "G1", "SUM(B1:B399)", cached: .number(0)) })143 #expect(changed { $0.sheets[0].cell(0, 6)!.formula!.value = .number(5) })144 #expect(changed { $0.sheets[2].cells.set(row: 0, col: 0, nil) })145 #expect(changed { var s = CellStyle(); s.bold = true; let id = $0.styles.intern(s); $0.sheets[0].cells.set(row: 900, col: 0, Cell(style: id)) })146 #expect(changed { $0.sheets[0].meta.colWidths[0] = 121 })147 #expect(changed { $0.sheets[0].meta.merges = [] })148 #expect(changed { $0.sheets[0].meta.comments[CellRef(0, 0)] = CellComment(author: "a", text: "b") })149 #expect(changed { $0.sheets[0].name = "Renamed" })150 #expect(changed { $0.attachments["scripts/clean.py"] = Data() })151 #expect(changed { $0.setDefinedName(DefinedName(name: "Total", formula: "Alpha!$G$2")) })152 #expect(changed { $0.date1904 = true })153 #expect(changed { $0.sheets.swapAt(0, 1) })154 #expect(changed { $0.sheets[0].cells.set(row: 5, col: 1, Cell(value: .number(5), isSpill: true)) })155 #expect(changed { setCell($0.sheets[0], "B1", .number(-0.0)) } == false) // 0.0 and -0.0 compare equal as values156 }157158 @Test func damagedPartsAreReportedPreciselyAndTheRestIsRecovered() throws {159 let dir = Scratch.dir("native-damage")160 defer { try? FileManager.default.removeItem(at: dir) }161 let wb = Self.threeSheets()162 let url = dir.appendingPathComponent("doc.orixsheet")163 try NativeFormat.write(wb, to: url)164 let betaID = wb.sheets[1].id165 let cellsPath = "sheets/\(betaID).cells", metaPath = "sheets/\(wb.sheets[2].id).meta.json"166 func damaged(_ part: String, _ mutate: (inout Data) -> Void) throws -> URL {167 let copy = dir.appendingPathComponent(UUID().uuidString + ".orixsheet")168 try FileManager.default.copyItem(at: url, to: copy)169 var d = try Data(contentsOf: copy.appendingPathComponent(part))170 mutate(&d)171 try d.write(to: copy.appendingPathComponent(part))172 return copy173 }174 var strict = LoadOptions(); strict.strictIntegrity = true175176 // 1. bytes flipped inside a compressed cell chunk177 let c1 = try damaged(cellsPath) { d in for i in 60..<90 { d[i] ^= 0xFF } }178 #expect(try NativeFormat.verify(url: c1) == [cellsPath])179 let r1 = try NativeFormat.read(url: c1)180 #expect(r1.issues.contains { $0.severity == .loss && $0.feature == "Integrity" && $0.location == cellsPath && $0.detail.contains("Beta") })181 #expect(r1.workbook.sheets.map(\.name) == ["Alpha", "Beta", "Gamma"] && r1.workbook.sheets[1].cells.count == 0)182 var o = WorkbookCompareOptions.exact183 o.maxDifferences = 5184 for i in [0, 2] { // the other sheets are intact185 #expect(r1.workbook.sheets[i].cells.count == wb.sheets[i].cells.count && cellValue(r1.workbook, i, "E400") == cellValue(wb, i, "E400"))186 #expect(r1.workbook.sheets[i].meta.colWidths == wb.sheets[i].meta.colWidths)187 }188 #expect(r1.workbook.attachments == wb.attachments && r1.workbook.names == wb.names)189 do { _ = try NativeFormat.read(url: c1, options: strict); Issue.record("strict mode accepted a damaged part") }190 catch let e as SheetIOError { guard case .corrupted(let part, _) = e else { Issue.record("\(e)"); return }; #expect(part == cellsPath) }191192 // 2. truncated cell part, 3. missing cell part193 let r2 = try NativeFormat.read(url: try damaged(cellsPath) { $0 = $0.prefix(40) })194 #expect(r2.issues.contains { $0.severity == .loss && $0.location == cellsPath } && r2.workbook.sheets[0].cells.count == wb.sheets[0].cells.count)195 let c3 = try damaged(cellsPath) { _ in }196 try FileManager.default.removeItem(at: c3.appendingPathComponent(cellsPath))197 let r3 = try NativeFormat.read(url: c3)198 #expect(r3.issues.contains { $0.severity == .loss && $0.detail.contains("missing") && $0.location == cellsPath })199 #expect(throws: SheetIOError.self) { _ = try NativeFormat.read(url: c3, options: strict) }200201 // 4. damaged layout: cells of that sheet survive, layout is reset202 let r4 = try NativeFormat.read(url: try damaged(metaPath) { $0 = Data("{ not json".utf8) })203 #expect(r4.issues.contains { $0.severity == .loss && $0.location == metaPath && $0.detail.contains("Gamma") })204 #expect(r4.workbook.sheets[2].cells.count == wb.sheets[2].cells.count && r4.workbook.sheets[2].meta.merges.isEmpty && r4.workbook.sheets[0].meta.merges.count == 1)205206 // 5. workbook.json is essential: precise error207 do { _ = try NativeFormat.read(url: try damaged("workbook.json") { $0 = Data("garbage".utf8) }); Issue.record("accepted") }208 catch let e as SheetIOError { guard case .corrupted(let part, _) = e else { Issue.record("\(e)"); return }; #expect(part == "workbook.json") }209210 // 6. manifest missing or unreadable: opened without verification, with a warning211 let c6 = try damaged("manifest.json") { $0 = Data("{}".utf8) }212 let r6 = try NativeFormat.read(url: c6)213 #expect(r6.issues.contains { $0.severity == .warning && $0.feature == "Integrity" })214 #expect(WorkbookDiff.differences(wb, r6.workbook, options: .exact).isEmpty)215 try FileManager.default.removeItem(at: c6.appendingPathComponent("manifest.json"))216 #expect(try NativeFormat.read(url: c6).issues.contains { $0.detail.contains("manifest is missing") })217 #expect(throws: SheetIOError.self) { _ = try NativeFormat.read(url: c6, options: strict) }218219 // 7. a damaged attachment and an edited-but-readable part are reported, nothing else is lost220 let attachment = try #require(try FileManager.default.contentsOfDirectory(atPath: url.appendingPathComponent("attachments").path).first)221 let r7 = try NativeFormat.read(url: try damaged("attachments/" + attachment) { $0.append(0x21) })222 #expect(r7.issues.contains { $0.severity == .warning && $0.detail.contains("scripts/clean.py") })223 // random damage anywhere never crashes224 var rng = SplitMix64(seed: 4)225 let parts = ["manifest.json", "workbook.json", cellsPath, metaPath]226 for _ in 0..<60 {227 let u = try damaged(parts.randomElement(using: &rng)!) { d in228 for _ in 0..<5 where !d.isEmpty { d[Int.random(in: 0..<d.count, using: &rng)] = UInt8.random(in: 0...255, using: &rng) }229 }230 do { _ = try NativeFormat.read(url: u) } catch is SheetIOError {} catch { Issue.record("unexpected \(error)") }231 }232 // not a package233 let file = dir.appendingPathComponent("flat.orixsheet")234 try Data("x".utf8).write(to: file)235 #expect(throws: SheetIOError.self) { _ = try NativeFormat.read(url: file) }236 let empty = dir.appendingPathComponent("empty.orixsheet")237 try FileManager.default.createDirectory(at: empty, withIntermediateDirectories: true)238 #expect(throws: SheetIOError.self) { _ = try NativeFormat.read(url: empty) }239 }240241 @Test func interruptedSaveLeavesTheOriginalIntact() throws {242 let dir = Scratch.dir("native-atomic")243 defer { try? FileManager.default.removeItem(at: dir) }244 let url = dir.appendingPathComponent("doc.orixsheet")245 let original = Self.threeSheets()246 try NativeFormat.write(original, to: url)247 let before = try NativeFormat.loadDirectory(url)248249 let changed = Self.threeSheets()250 setCell(changed.sheets[0], "A1", .text("NEW CONTENT"))251 struct PowerCut: Error {}252 #expect(throws: SheetIOError.self) {253 _ = try NativeFormat.write(changed, to: url, options: .init(), preserved: nil, beforeReplace: { throw PowerCut() })254 }255 // the document on disk is byte-for-byte the previous version, and no temporary directory is left behind256 #expect(try NativeFormat.loadDirectory(url) == before)257 #expect(try NativeFormat.verify(url: url).isEmpty)258 #expect(cellValue(try NativeFormat.read(url: url).workbook, 0, "A1") == .text("Alpha row 0"))259 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path) == ["doc.orixsheet"])260 // a successful save replaces it completely (stale parts of the old version disappear)261 changed.sheets.removeLast()262 try NativeFormat.write(changed, to: url)263 let after = try NativeFormat.read(url: url)264 #expect(after.issues.isEmpty && cellValue(after.workbook, 0, "A1") == .text("NEW CONTENT") && after.workbook.sheets.count == 2)265 #expect(!FileManager.default.fileExists(atPath: url.appendingPathComponent("sheets/\(original.sheets[2].id).cells").path))266 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path) == ["doc.orixsheet"])267 // an unwritable destination fails cleanly268 #expect(throws: SheetIOError.self) { try NativeFormat.write(changed, to: URL(fileURLWithPath: "/nonexistent-dir-\(UUID().uuidString)/x.orixsheet")) }269 // same guarantee for single-file formats (atomic write)270 let xlsx = dir.appendingPathComponent("book.xlsx")271 try WorkbookWriter.save(original, to: xlsx, format: .xlsx)272 let good = try Data(contentsOf: xlsx)273 let bad = Workbook(sheetNames: ["bad/name"])274 #expect(throws: SheetIOError.self) { try WorkbookWriter.save(bad, to: xlsx, format: .xlsx) }275 #expect(try Data(contentsOf: xlsx) == good)276 }277278 @Test func fileWrappersPreviewAttachmentsAndPreservedParts() throws {279 var gen = RandomWorkbook(seed: 77)280 let wb = gen.workbook()281 let png = Data([0x89, 0x50, 0x4E, 0x47, 0x0D, 0x0A, 0x1A, 0x0A, 1, 2, 3])282 var options = NativeWriteOptions()283 options.preview = png; options.appVersion = "9.9"284 let source = try WorkbookReader.load(url: Scratch.fixture("charts_openpyxl.xlsx"))285 let wrapper = try NativeFormat.fileWrappers(for: wb, options: options, preserved: source.preserved)286 #expect(wrapper.isDirectory && wrapper.fileWrappers?["manifest.json"] != nil && wrapper.fileWrappers?["sheets"]?.isDirectory == true)287 #expect(wrapper.fileWrappers?["preview.png"]?.regularFileContents == png)288 let back = try NativeFormat.read(fileWrapper: wrapper)289 #expect(WorkbookDiff.differences(wb, back.workbook, options: .exact).isEmpty && back.issues.isEmpty)290 #expect(back.preserved == source.preserved && back.preserved?.parts["xl/charts/chart1.xml"] != nil)291 // the wrapper written to disk is a valid package for the URL reader and for format detection292 let dir = Scratch.dir("native-wrapper")293 defer { try? FileManager.default.removeItem(at: dir) }294 let url = dir.appendingPathComponent("w.orixsheet")295 try wrapper.write(to: url, options: .atomic, originalContentsURL: nil)296 #expect(FileFormat.detect(url: url) == .orixsheet)297 let viaReader = try WorkbookReader.load(url: url)298 #expect(WorkbookDiff.differences(wb, viaReader.workbook, options: .exact).isEmpty && viaReader.preserved == source.preserved)299 let manifest = try JSONSerialization.jsonObject(with: Data(contentsOf: url.appendingPathComponent("manifest.json"))) as! [String: Any]300 #expect(manifest["format"] as? String == "orixsheet" && manifest["formatVersion"] as? Int == 1 && manifest["appVersion"] as? String == "9.9")301 #expect((manifest["parts"] as? [String: Any])?["preview.png"] != nil)302 // attachment names never become paths303 let evil = Workbook(sheetNames: ["s"])304 evil.attachments["../../etc/passwd"] = Data("x".utf8); evil.attachments["/abs/olute"] = Data("y".utf8); evil.attachments[""] = Data("z".utf8)305 let eurl = dir.appendingPathComponent("evil.orixsheet")306 try NativeFormat.write(evil, to: eurl)307 #expect(try FileManager.default.contentsOfDirectory(atPath: dir.path).sorted() == ["evil.orixsheet", "w.orixsheet"])308 #expect(try NativeFormat.read(url: eurl).workbook.attachments == evil.attachments)309 // save through the generic entry point and convert an Excel file with charts, then back to XLSX310 let chartDoc = dir.appendingPathComponent("charts.orixsheet")311 try WorkbookWriter.save(source.workbook, to: chartDoc, format: .orixsheet, preserved: source.preserved)312 let reloaded = try WorkbookReader.load(url: chartDoc)313 let xlsx = dir.appendingPathComponent("charts.xlsx")314 try WorkbookWriter.save(reloaded.workbook, to: xlsx, format: .xlsx, preserved: reloaded.preserved)315 #expect(try ZipArchive(url: xlsx).data(for: "xl/charts/chart1.xml") == source.preserved?.parts["xl/charts/chart1.xml"])316 }317318 @Test func versioningAndForwardCompatibility() throws {319 let wb = Self.threeSheets()320 var (parts, _) = try NativeFormat.build(wb, options: .init(), preserved: nil)321 func rewriteManifest(_ parts: inout [String: Data], version: Int, minReader: Int) throws {322 var m = try JSONSerialization.jsonObject(with: parts["manifest.json"]!) as! [String: Any]323 m["formatVersion"] = version; m["minReaderVersion"] = minReader324 m["futureField"] = ["anything": true]325 var listed = [String: Any]()326 for (path, data) in parts where path != "manifest.json" { listed[path] = ["sha256": NativeFormat.sha256(data), "size": data.count, "kind": "future"] }327 m["parts"] = listed328 parts["manifest.json"] = try JSONSerialization.data(withJSONObject: m)329 }330 // a newer minor version: unknown fields and unknown parts are ignored, the rest loads, with a warning331 var w = try JSONSerialization.jsonObject(with: parts["workbook.json"]!) as! [String: Any]332 w["newFeature"] = ["x": 1]333 var sheets = w["sheets"] as! [[String: Any]]334 sheets[0]["futureSheetSetting"] = "y"335 w["sheets"] = sheets336 parts["workbook.json"] = try JSONSerialization.data(withJSONObject: w)337 let metaPath = "sheets/\(wb.sheets[0].id).meta.json"338 var meta = try JSONSerialization.jsonObject(with: parts[metaPath]!) as! [String: Any]339 meta["sparklines"] = [["range": "A1"]]340 var cfs = meta["conditionalFormats"] as! [Any]341 cfs.append(["ranges": [], "kind": ["holographic": ["depth": 3]], "style": [:], "priority": 1, "stopIfTrue": false]) // a rule kind this version does not know342 meta["conditionalFormats"] = cfs343 parts[metaPath] = try JSONSerialization.data(withJSONObject: meta)344 parts["extensions/future.bin"] = Data([1, 2, 3])345 try rewriteManifest(&parts, version: 2, minReader: 1)346 let r = try NativeFormat.read(parts: parts, options: .init(), progress: nil)347 #expect(r.issues.contains { $0.feature == "Format version" && $0.severity == .warning })348 #expect(r.issues.contains { $0.feature == "Sheet metadata" && $0.severity == .loss })349 #expect(WorkbookDiff.differences(wb, r.workbook, options: .exact).isEmpty)350 // a version this reader must not open351 try rewriteManifest(&parts, version: 3, minReader: 2)352 do { _ = try NativeFormat.read(parts: parts, options: .init(), progress: nil); Issue.record("accepted") }353 catch let e as SheetIOError { #expect(e == .unsupportedVersion(found: 3, supported: 1)); #expect(e.errorDescription?.contains("Update") == true) }354 // an older version without a migration path is refused explicitly (the hook exists, no step is registered)355 try rewriteManifest(&parts, version: 0, minReader: 0)356 #expect(throws: SheetIOError.self) { _ = try NativeFormat.read(parts: parts, options: .init(), progress: nil) }357 var p2 = parts358 try NativeFormat.migrate(&p2, from: NativeSchema.currentVersion) // no-op at the current version359 #expect(p2 == parts)360 // a foreign manifest361 var foreign = try JSONSerialization.jsonObject(with: parts["manifest.json"]!) as! [String: Any]362 foreign["format"] = "something-else"; foreign["formatVersion"] = 1; foreign["minReaderVersion"] = 1363 parts["manifest.json"] = try JSONSerialization.data(withJSONObject: foreign)364 #expect(throws: SheetIOError.self) { _ = try NativeFormat.read(parts: parts, options: .init(), progress: nil) }365 }366367 @Test func oddSheetIdentifiersAndLambdaValues() throws {368 let dir = Scratch.dir("native-ids")369 defer { try? FileManager.default.removeItem(at: dir) }370 // identifiers that are not contiguous and not in order371 let wb = Workbook()372 for id in [7, 3, 12] { wb.sheets.append(Sheet(id: id, name: "S\(id)")) }373 wb.setDefinedName(DefinedName(name: "Local", formula: "1", scopeSheetID: 3))374 setCell(wb.sheets[1], "A1", .number(3))375 let url = dir.appendingPathComponent("ids.orixsheet")376 try NativeFormat.write(wb, to: url)377 let back = try NativeFormat.read(url: url).workbook378 #expect(back.sheets.map(\.id) == [7, 3, 12] && back.definedName("Local", sheetID: 3)?.scopeSheetID == 3)379 #expect(back.addSheet(named: "New").id == 13) // new sheets do not collide with restored ids380 // duplicate identifiers cannot be saved381 let dup = Workbook()382 dup.sheets = [Sheet(id: 1, name: "a"), Sheet(id: 1, name: "b")]383 #expect(throws: SheetIOError.self) { try NativeFormat.write(dup, to: dir.appendingPathComponent("dup.orixsheet")) }384 // LAMBDA values are the one thing that cannot be stored: reported before and after saving385 let l = Workbook(sheetNames: ["L"])386 let f = Formula(expr: try FormulaParser.parse("LAMBDA(x,x+1)"))387 f.value = .lambda(LambdaValue(params: ["x"], body: .number(1), captured: [:]))388 l.sheets[0].cells.set(row: 0, col: 0, Cell(formula: f))389 #expect(WorkbookWriter.preflight(l, format: .orixsheet).contains { $0.feature == "LAMBDA values" && $0.severity == .loss })390 let lurl = dir.appendingPathComponent("l.orixsheet")391 #expect(try NativeFormat.write(l, to: lurl).contains { $0.feature == "LAMBDA values" })392 let lb = try NativeFormat.read(url: lurl).workbook393 #expect(lb.sheets[0].cell(0, 0)?.formula?.expr.formulaText() == "LAMBDA(x,x+1)" && lb.sheets[0].cell(0, 0)?.formula?.value == .error(.calc))394 #expect(WorkbookWriter.preflight(Self.threeSheets(), format: .orixsheet).isEmpty)395 }396}397