SPB Git

Repository Public

XLSXEdgeTests.swift 26.7 KB 424 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/XLSXEdgeTests.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/// Rewrites an archive, replacing, adding or removing parts.93func rebuildZip(_ url: URL, replacing: [String: Data?]) throws -> Data {94    let zip = try ZipArchive(url: url)95    var w = ZipWriter()96    var done = Set<String>()97    for e in zip.entries {98        if let r = replacing[e.name] {99            done.insert(e.name)100            if let r { try w.add(e.name, r) }101        } else { try w.add(e.name, try zip.data(for: e)) }102    }103    for (name, data) in replacing where !done.contains(name) { if let data { try w.add(name, data) } }104    return w.finish()105}106107func partText(_ url: URL, _ part: String) throws -> String { String(decoding: try ZipArchive(url: url).data(for: part), as: UTF8.self) }108109@Suite struct XLSXEdgeTests {110    @Test func macrosAreNeverRunAndOnlyKeptInXLSM() throws {111        let dir = Scratch.dir("macro")112        defer { try? FileManager.default.removeItem(at: dir) }113        let src = Scratch.fixture("macro.xlsm")114        #expect(FileFormat.detect(url: src) == .xlsm)115        let disguised = dir.appendingPathComponent("renamed.xlsx")116        try FileManager.default.copyItem(at: src, to: disguised)117        #expect(FileFormat.detect(url: disguised) == .xlsm)          // content wins over the extension118        let r = try WorkbookReader.load(url: disguised)119        #expect(r.hasMacros && r.preserved?.hasMacros == true)120        #expect(r.issues.contains { $0.feature == "Macros" && $0.severity == .warning })121        let vba = try ZipArchive(url: src).data(for: "xl/vbaProject.bin")122123        // .xlsx: the project is dropped and reported, before and after saving124        #expect(WorkbookWriter.preflight(r.workbook, format: .xlsx, preserved: r.preserved).contains { $0.feature == "Macros" && $0.severity == .loss })125        #expect(!WorkbookWriter.preflight(r.workbook, format: .xlsm, preserved: r.preserved).contains { $0.feature == "Macros" })126        let x = dir.appendingPathComponent("out.xlsx")127        let issues = try WorkbookWriter.save(r.workbook, to: x, format: .xlsx, preserved: r.preserved)128        #expect(issues.contains { $0.feature == "Macros" && $0.severity == .loss })129        let zx = try ZipArchive(url: x)130        #expect(!zx.names.contains { $0.lowercased().contains("vba") })131        #expect(try partText(x, "[Content_Types].xml").contains(XLSXWriter.ctWorkbook))132        #expect(!(try partText(x, "[Content_Types].xml")).contains("macroEnabled"))133        #expect(!(try partText(x, "xl/worksheets/sheet1.xml")).contains("codeName"))134        #expect(try WorkbookReader.load(url: x).hasMacros == false)135136        // .xlsm: bytes untouched, code names and content type kept137        let m = dir.appendingPathComponent("out.xlsm")138        let mIssues = try WorkbookWriter.save(r.workbook, to: m, format: .xlsm, preserved: r.preserved)139        #expect(!mIssues.contains { $0.severity == .loss })140        #expect(try ZipArchive(url: m).data(for: "xl/vbaProject.bin") == vba)141        #expect(try partText(m, "[Content_Types].xml").contains(XLSXWriter.ctWorkbookMacro))142        #expect(try partText(m, "xl/worksheets/sheet1.xml").contains("codeName=\"Sheet1\""))143        #expect(try partText(m, "xl/workbook.xml").contains("codeName=\"ThisWorkbook\""))144        #expect(try partText(m, "xl/_rels/workbook.xml.rels").contains("relationships/vbaProject"))145        let again = try WorkbookReader.load(url: m)146        #expect(again.hasMacros && again.preserved?.parts["xl/vbaProject.bin"] == vba)147        if Tools.hasPython {148            let r = try Tools.run(Tools.python, ["-I", "-c", "import openpyxl,sys; wb=openpyxl.load_workbook(sys.argv[1], keep_vba=True); print(wb.vba_archive is not None, wb.active['B1'].value)", m.path])149            #expect(r.out.trimmingCharacters(in: .whitespacesAndNewlines) == "True =A1+1", "\(r.err)")150        }151    }152153    @Test func externalLinksAndPivotPartsAreWrittenBack() throws {154        let dir = Scratch.dir("ext")155        defer { try? FileManager.default.removeItem(at: dir) }156        let src = Scratch.fixture("raw_features.xlsx")157        let r = try WorkbookReader.load(url: src)158        let out = dir.appendingPathComponent("out.xlsx")159        try WorkbookWriter.save(r.workbook, to: out, format: .xlsx, preserved: r.preserved)160        let a = try ZipArchive(url: src), b = try ZipArchive(url: out)161        for part in ["xl/externalLinks/externalLink1.xml", "xl/externalLinks/_rels/externalLink1.xml.rels", "xl/pivotTables/pivotTable1.xml",162                     "xl/pivotCache/pivotCacheDefinition1.xml", "xl/pivotCache/pivotCacheRecords1.xml", "customXml/item1.xml", "customXml/itemProps1.xml",163                     "docProps/custom.xml", "xl/theme/theme1.xml"] {164            #expect(try a.data(for: part) == b.data(for: part), "\(part)")165        }166        let wbXML = try partText(out, "xl/workbook.xml")167        #expect(wbXML.contains("<externalReferences><externalReference r:id=") && wbXML.contains("<pivotCache cacheId=\"7\""))168        #expect(wbXML.contains(">[1]Ext!$A$1</definedName>"))169        #expect(try partText(out, "xl/worksheets/_rels/sheet2.xml.rels").contains("pivotTables/pivotTable1.xml"))170        #expect(!b.names.contains { $0.contains("chartsheet") || $0.contains("sparkline") })171        // deleting the sheet that owns the pivot table drops the table and its caches172        let r2 = try WorkbookReader.load(url: src)173        r2.workbook.sheets.removeLast()174        r2.workbook.activeSheet = 0175        let out2 = dir.appendingPathComponent("out2.xlsx")176        let issues = try WorkbookWriter.save(r2.workbook, to: out2, format: .xlsx, preserved: r2.preserved)177        #expect(issues.contains { $0.feature == "Preserved content" })178        #expect(!(try ZipArchive(url: out2)).names.contains { $0.contains("pivot") })179        if Tools.hasPython { _ = try Tools.openpyxlDump(out); _ = try Tools.openpyxlDump(out2) }180    }181182    @Test func corruptedInputsProduceCleanErrors() throws {183        let dir = Scratch.dir("corrupt")184        defer { try? FileManager.default.removeItem(at: dir) }185        let src = Scratch.fixture("features_openpyxl.xlsx"), rawSrc = Scratch.fixture("raw_features.xlsx")186        let original = try Data(contentsOf: src)187        func load(_ data: Data, _ name: String = "t.xlsx") throws -> LoadResult {188            let u = dir.appendingPathComponent(name)189            try data.write(to: u)190            return try WorkbookReader.load(url: u, format: .xlsx)191        }192        // truncation anywhere193        for cut in stride(from: 0, to: original.count, by: max(1, original.count / 60)) {194            #expect(throws: SheetIOError.self) { _ = try load(original.prefix(cut)) }195        }196        // malformed XML in a worksheet, with the part named in the error197        do {198            _ = try load(try rebuildZip(src, replacing: ["xl/worksheets/sheet1.xml": Data("<worksheet><sheetData><row><c r=\"A1\"><v>1</v></row>".utf8)]))199            Issue.record("bad XML accepted")200        } catch let e as SheetIOError {201            guard case .corrupted(let part, _) = e else { Issue.record("\(e)"); return }202            #expect(part == "xl/worksheets/sheet1.xml")203        }204        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(src, replacing: ["xl/styles.xml": Data("<<<<".utf8)])) }205        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(rawSrc, replacing: ["xl/sharedStrings.xml": Data([0xFF, 0xFE, 0x00, 0x01])])) }206        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(src, replacing: ["xl/workbook.xml": Data?.none])) }207        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(src, replacing: ["[Content_Types].xml": Data?.none])) }208        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(src, replacing: ["xl/worksheets/sheet2.xml": Data?.none])) }209        // wrong content types: a Word document, an unknown main part210        let ct = try partText(src, "[Content_Types].xml")211        let word = ct.replacingOccurrences(of: XLSXWriter.ctWorkbook, with: "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml")212        do { _ = try load(try rebuildZip(src, replacing: ["[Content_Types].xml": Data(word.utf8)])); Issue.record("Word accepted") }213        catch let e as SheetIOError { guard case .unsupported(let m) = e else { Issue.record("\(e)"); return }; #expect(m.contains("Word")) }214        let odd = ct.replacingOccurrences(of: XLSXWriter.ctWorkbook, with: "text/plain")215        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(src, replacing: ["[Content_Types].xml": Data(odd.utf8)])) }216        // out-of-range references inside otherwise valid XML217        let raw = try partText(rawSrc, "xl/worksheets/sheet1.xml")218        #expect(raw.contains("<c r=\"A1\" t=\"s\"><v>0</v>") && raw.contains("<row r=\"8\">"))219        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(rawSrc, replacing: ["xl/worksheets/sheet1.xml": Data(raw.replacingOccurrences(of: "<c r=\"A1\" t=\"s\"><v>0</v>", with: "<c r=\"A1\" t=\"s\"><v>999999</v>").utf8)])) }220        #expect(throws: SheetIOError.self) {221            _ = try load(try rebuildZip(Scratch.fixture("raw_features.xlsx"), replacing: ["xl/worksheets/sheet1.xml": Data(raw.replacingOccurrences(of: "<row r=\"8\">", with: "<row r=\"1048576\"></row><row>").utf8)]))222        }223        // entity expansion attacks and DTDs are refused224        let bomb = "<?xml version=\"1.0\"?><!DOCTYPE lolz [<!ENTITY lol \"lol\"><!ENTITY lol2 \"&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;&lol;\"><!ENTITY lol3 \"&lol2;&lol2;&lol2;&lol2;&lol2;&lol2;&lol2;&lol2;\">]><sst xmlns=\"\(OPC.mainNS)\"><si><t>&lol3;</t></si></sst>"225        do { _ = try load(try rebuildZip(rawSrc, replacing: ["xl/sharedStrings.xml": Data(bomb.utf8)])); Issue.record("DTD accepted") }226        catch let e as SheetIOError { guard case .unsafeArchive = e else { Issue.record("\(e)"); return } }227        let xxe = "<?xml version=\"1.0\"?><!DOCTYPE x [<!ENTITY xxe SYSTEM \"file:///etc/passwd\">]><sst xmlns=\"\(OPC.mainNS)\"><si><t>&xxe;</t></si></sst>"228        #expect(throws: SheetIOError.self) { _ = try load(try rebuildZip(rawSrc, replacing: ["xl/sharedStrings.xml": Data(xxe.utf8)])) }229        // not archives at all230        #expect(throws: SheetIOError.self) { _ = try load(Data("just some text".utf8)) }231        #expect(throws: SheetIOError.self) { _ = try load(Data()) }232        do { _ = try load(Data([0xD0, 0xCF, 0x11, 0xE0, 0xA1, 0xB1, 0x1A, 0xE1] + [UInt8](repeating: 0, count: 600))); Issue.record("CFB accepted") }233        catch let e as SheetIOError { guard case .unsupported(let m) = e else { Issue.record("\(e)"); return }; #expect(m.contains("password") && m.contains(".xls")) }234        // random flips inside the archive never crash235        var rng = SplitMix64(seed: 5)236        for _ in 0..<150 {237            var d = original238            for _ in 0..<4 { d[Int.random(in: 0..<d.count, using: &rng)] = UInt8.random(in: 0...255, using: &rng) }239            do { _ = try load(d) } catch is SheetIOError {} catch { Issue.record("unexpected \(error)") }240        }241        #expect(throws: SheetIOError.self) { _ = try WorkbookReader.load(url: dir.appendingPathComponent("does-not-exist.xlsx")) }242    }243244    @Test func excelLimitsAreEnforced() throws {245        let dir = Scratch.dir("limits")246        defer { try? FileManager.default.removeItem(at: dir) }247        let wb = Workbook()248        let s = wb.addSheet(named: "Long")249        let long = String(repeating: "é", count: 40_000)250        setCell(s, "A1", .text(long))251        setFormula(s, "A2", "REPT(\"é\",40000)", cached: .text(long))252        setCell(s, "XFD1048576", .number(1))253        let pre = WorkbookWriter.preflight(wb, format: .xlsx)254        #expect(pre.contains { $0.feature == "Text length" && $0.severity == .loss })255        let url = dir.appendingPathComponent("long.xlsx")256        let issues = try WorkbookWriter.save(wb, to: url, format: .xlsx)257        #expect(issues.contains { $0.feature == "Text length" && $0.severity == .loss && ($0.location ?? "").contains("Long!A1") })258        let back = try WorkbookReader.load(url: url).workbook259        #expect(cellValue(back, 0, "A1").textValue?.count == 32_767 && cellValue(back, 0, "A2").textValue?.count == 32_767)260        #expect(cellValue(back, 0, "XFD1048576") == .number(1))261262        // sheet names263        let bad = Workbook(sheetNames: ["Bad/Name"])264        #expect(WorkbookWriter.preflight(bad, format: .xlsx).contains { $0.feature == "Sheet names" && $0.severity == .loss })265        #expect(throws: SheetIOError.self) { try WorkbookWriter.save(bad, to: dir.appendingPathComponent("bad.xlsx"), format: .xlsx) }266        let dup = Workbook(sheetNames: ["Same"])267        dup.sheets.append(Sheet(id: 99, name: "same"))268        #expect(throws: SheetIOError.self) { try WorkbookWriter.save(dup, to: dir.appendingPathComponent("dup.xlsx"), format: .xlsx) }269        #expect(throws: SheetIOError.self) { try WorkbookWriter.save(Workbook(), to: dir.appendingPathComponent("empty.xlsx"), format: .xlsx) }270        #expect(!FileManager.default.fileExists(atPath: dir.appendingPathComponent("bad.xlsx").path))271        // cells outside the grid cannot be stored272        let out = Workbook(sheetNames: ["S"])273        out.sheets[0].cells.set(row: Limits.maxRows, col: 0, Cell(value: .number(1)))274        #expect(WorkbookWriter.preflight(out, format: .xlsx).contains { $0.feature == "Grid size" })275        #expect(throws: SheetIOError.self) { try WorkbookWriter.save(out, to: dir.appendingPathComponent("grid.xlsx"), format: .xlsx) }276277        // overlapping merges and tables, invalid validation lists are dropped with a report, the file stays valid278        let v = Workbook(sheetNames: ["V"])279        let vs = v.sheets[0]280        for c in 0..<3 { for r in 0..<4 { vs.cells.set(row: r, col: c, Cell(value: r == 0 ? .text("H\(c)") : .number(Double(r)))) } }281        vs.meta.merges = [RangeRef(a1: "E1:F2")!, RangeRef(a1: "F2:G3")!]282        vs.meta.tables = [TableDef(id: 1, name: "T1", range: RangeRef(a1: "A1:C4")!, columns: (0..<3).map { TableColumn(name: "H\($0)") }),283                          TableDef(id: 2, name: "T2", range: RangeRef(a1: "B2:D5")!, columns: (0..<3).map { TableColumn(name: "X\($0)") })]284        vs.meta.validations = [DataValidation(ranges: [RangeRef(a1: "H1")!], kind: .list, formula1: "\"" + String(repeating: "abc,", count: 100) + "\"")]285        vs.meta.autoFilter = RangeRef(a1: "A1:C4")!286        let vURL = dir.appendingPathComponent("v.xlsx")287        let vIssues = try WorkbookWriter.save(v, to: vURL, format: .xlsx)288        #expect(vIssues.contains { $0.feature == "Merged cells" } && vIssues.contains { $0.feature == "Tables" } && vIssues.contains { $0.feature == "Data validation" } && vIssues.contains { $0.feature == "AutoFilter" })289        let vBack = try WorkbookReader.load(url: vURL).workbook290        #expect(vBack.sheets[0].meta.merges.count == 1 && vBack.sheets[0].meta.tables.map(\.name) == ["T1"] && vBack.sheets[0].meta.validations.isEmpty)291        if Tools.hasPython { _ = try Tools.openpyxlDump(vURL) }292293        // reader-side cell budget294        var o = LoadOptions()295        o.maxCells = 10296        #expect(throws: SheetIOError.self) { _ = try WorkbookReader.load(url: Scratch.fixture("features_openpyxl.xlsx"), options: o) }297    }298299    @Test func hugeSharedStringTables() throws {300        let dir = Scratch.dir("sst")301        defer { try? FileManager.default.removeItem(at: dir) }302        let wb = Workbook(sheetNames: ["S"])303        let s = wb.sheets[0]304        var col0 = [Int: Cell](), col1 = [Int: Cell]()305        for r in 0..<120_000 {306            col0[r] = Cell(value: .text("unique string number \(r) with some padding to make it longer é"))307            col1[r] = Cell(value: .text("repeated \(r % 7)"))308        }309        s.cells.replaceColumn(0, with: col0); s.cells.replaceColumn(1, with: col1)310        setCell(s, "C1", .text(String(repeating: "long ", count: 6000)))       // 30,000 characters in one string311        let url = dir.appendingPathComponent("sst.xlsx")312        let t0 = Date()313        try WorkbookWriter.save(wb, to: url, format: .xlsx)314        let t1 = Date()315        let back = try WorkbookReader.load(url: url).workbook316        let t2 = Date()317        print(String(format: "[timing] 240k string cells (120k unique): write %.2fs, read %.2fs, file %d KB", t1.timeIntervalSince(t0), t2.timeIntervalSince(t1), (try Data(contentsOf: url)).count / 1024))318        var o = WorkbookCompareOptions(); o.layout = false319        #expect(WorkbookDiff.differences(wb, back, options: o).isEmpty)320        #expect(try partText(url, "xl/sharedStrings.xml").contains("uniqueCount=\"120008\""))321        // a single shared string of several megabytes written by another producer322        let big = String(repeating: "0123456789abcdef", count: 300_000)323        let raw = try rebuildZip(Scratch.fixture("raw_features.xlsx"), replacing: ["xl/sharedStrings.xml": Data(("<sst xmlns=\"\(OPC.mainNS)\"><si><t>" + big + "</t></si><si><t>b</t></si><si><t>c</t></si><si><t>d</t></si></sst>").utf8)])324        let bigURL = dir.appendingPathComponent("big.xlsx")325        try raw.write(to: bigURL)326        #expect(cellValue(try WorkbookReader.load(url: bigURL).workbook, 0, "A1").textValue?.utf8.count == big.utf8.count)327    }328329    @Test func randomWorkbooksRoundTrip() throws {330        let dir = Scratch.dir("random-xlsx")331        defer { try? FileManager.default.removeItem(at: dir) }332        for seed in 20...60 {333            let wb = makeRandomXLSXWorkbook(seed: UInt64(seed))334            let url = dir.appendingPathComponent("r.xlsx")335            try WorkbookWriter.save(wb, to: url, format: .xlsx)336            var progressValues = [Double]()337            let back = try WorkbookReader.load(url: url, progress: { progressValues.append($0) }).workbook338            var o = WorkbookCompareOptions()339            o.spillState = false340            let diff = WorkbookDiff.differences(wb, back, options: o)341            #expect(diff.isEmpty, "seed \(seed): \(diff.prefix(4).joined(separator: "\n"))")342            #expect(progressValues.last == 1 && progressValues == progressValues.sorted())343        }344    }345346    @Test func dynamicArraysCarryTheMetadataExcelExpects() throws {347        let dir = Scratch.dir("dyn")348        defer { try? FileManager.default.removeItem(at: dir) }349        let wb = Workbook(sheetNames: ["D"])350        let s = wb.sheets[0]351        setFormula(s, "A1", "SEQUENCE(3)", cached: .number(1), spill: "A1:A3")352        s.cells.set(row: 1, col: 0, Cell(value: .number(2), isSpill: true)); s.cells.set(row: 2, col: 0, Cell(value: .number(3), isSpill: true))353        setFormula(s, "C1", "UNIQUE(A1:A3)", cached: .error(.spill))          // blocked spill, still a dynamic array354        setFormula(s, "D1", "SUM(A1#)", cached: .number(6))                    // scalar: ordinary formula355        setFormula(s, "E1", "A1+1", cached: .number(2))356        let url = dir.appendingPathComponent("d.xlsx")357        try WorkbookWriter.save(wb, to: url, format: .xlsx)358        let xml = try partText(url, "xl/worksheets/sheet1.xml")359        #expect(xml.contains("<c r=\"A1\" cm=\"1\"><f t=\"array\" ref=\"A1:A3\">_xlfn.SEQUENCE(3)</f><v>1</v></c>"))360        #expect(xml.contains("<c r=\"C1\" cm=\"1\" t=\"e\"><f t=\"array\" ref=\"C1\">_xlfn.UNIQUE(A1:A3)</f><v>#SPILL!</v></c>"))361        #expect(xml.contains("<c r=\"D1\"><f>SUM(_xlfn.ANCHORARRAY(A1))</f><v>6</v></c>"))362        #expect(xml.contains("<c r=\"E1\"><f>A1+1</f><v>2</v></c>"))363        #expect(try partText(url, "xl/metadata.xml").contains("XLDAPR") && (try partText(url, "[Content_Types].xml")).contains("sheetMetadata+xml"))364        #expect(try partText(url, "xl/_rels/workbook.xml.rels").contains("relationships/sheetMetadata"))365        // without dynamic arrays the part is not written366        let plain = dir.appendingPathComponent("p.xlsx")367        try WorkbookWriter.save(Workbook(sheetNames: ["P"]), to: plain, format: .xlsx)368        #expect(!(try ZipArchive(url: plain)).contains("xl/metadata.xml"))369    }370371    @Test func formatDetectionAndUnsupportedFormats() throws {372        let dir = Scratch.dir("detect")373        defer { try? FileManager.default.removeItem(at: dir) }374        #expect(FileFormat.detect(url: Scratch.fixture("features_openpyxl.xlsx")) == .xlsx)375        #expect(FileFormat.detect(url: Scratch.fixture("features_lo.ods")) == .ods)376        // content beats a misleading extension377        let lying = dir.appendingPathComponent("really-ods.xlsx")378        try FileManager.default.copyItem(at: Scratch.fixture("features_lo.ods"), to: lying)379        #expect(FileFormat.detect(url: lying) == .ods)380        #expect(try WorkbookReader.load(url: lying).workbook.sheets.count == 4)381        // Numbers: single-file and package flavours382        var w = ZipWriter()383        try w.add("Index/Document.iwa", Data([0, 1, 2, 3])); try w.add("Metadata/Properties.plist", Data("x".utf8))384        let numbers = dir.appendingPathComponent("doc.numbers")385        try w.finish().write(to: numbers)386        #expect(FileFormat.detect(url: numbers) == .numbers)387        do { _ = try WorkbookReader.load(url: numbers); Issue.record("Numbers accepted") }388        catch let e as SheetIOError { guard case .unsupported(let m) = e else { Issue.record("\(e)"); return }; #expect(m.contains("Export") && m.contains("Numbers")) }389        let pkg = dir.appendingPathComponent("pkg.numbers")390        try FileManager.default.createDirectory(at: pkg, withIntermediateDirectories: true)391        try Data("x".utf8).write(to: pkg.appendingPathComponent("Index.zip"))392        #expect(FileFormat.detect(url: pkg) == .numbers)393        // legacy XLS394        let xls = dir.appendingPathComponent("old.xls")395        try Data([0xD0, 0xCF, 0x11, 0xE0, 0xA1, 0xB1, 0x1A, 0xE1] + [UInt8](repeating: 0, count: 1000)).write(to: xls)396        #expect(FileFormat.detect(url: xls) == .xls)397        #expect(throws: SheetIOError.self) { _ = try WorkbookReader.load(url: xls) }398        #expect(!FileFormat.xls.canRead && !FileFormat.xls.canWrite && !FileFormat.numbers.canRead && !FileFormat.numbers.canWrite)399        for f in FileFormat.allCases where f != .xls && f != .numbers && f != .xlsb { #expect(f.canRead && f.canWrite) }400        #expect(throws: SheetIOError.self) { try WorkbookWriter.save(Workbook(sheetNames: ["a"]), to: dir.appendingPathComponent("o.xls"), format: .xls) }401        // text formats402        for (name, content, expected) in [("a.csv", "a,b\n1,2\n", FileFormat.csv), ("a.tsv", "a\tb\n", .tsv), ("a.json", "[1]", .json), ("a.jsonl", "{}\n", .jsonl), ("noext", " [ {\"a\":1} ]", .json)] {403            let u = dir.appendingPathComponent(name)404            try Data(content.utf8).write(to: u)405            #expect(FileFormat.detect(url: u) == expected, "\(name)")406        }407        let unknown = dir.appendingPathComponent("mystery.bin")408        try Data([1, 2, 3]).write(to: unknown)409        #expect(FileFormat.detect(url: unknown) == nil)410        #expect(throws: SheetIOError.self) { _ = try WorkbookReader.load(url: unknown) }411    }412413    @Test func unknownFunctionReportingIsOptional() throws {414        var o = LoadOptions()415        o.functionRegistry = nil416        #expect(!(try WorkbookReader.load(url: Scratch.fixture("raw_features.xlsx"), options: o)).issues.contains { $0.feature == "Functions" })417        let custom = FunctionRegistry()418        custom.scalar("MYUDF", 1...1, .math) { _, a in a[0] }419        o.functionRegistry = custom420        let issues = try WorkbookReader.load(url: Scratch.fixture("raw_features.xlsx"), options: o).issues421        #expect(!issues.contains { $0.location == "MYUDF" } && issues.contains { $0.location == "FUTUREFN" } && issues.contains { $0.location == "SUM" })422    }423}424
Files main
Menu

Theme