SPB Git

Repository Public

XLSXReadTests.swift 23.8 KB 377 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/XLSXReadTests.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 SheetIO9192private func near(_ c: RGBA?, _ hex: UInt32, tolerance: Int = 2) -> Bool {93    guard let c else { return false }94    let e = RGBA(hex: hex)95    return abs(Int(c.r) - Int(e.r)) <= tolerance && abs(Int(c.g) - Int(e.g)) <= tolerance && abs(Int(c.b) - Int(e.b)) <= tolerance96}97private func style(_ wb: Workbook, _ sheet: Int, _ a1: String) -> CellStyle {98    let r = CellRef(a1: a1)!99    return wb.styles[wb.sheets[sheet].styleID(r.row, r.col)]100}101102@Suite struct XLSXReadTests {103    /// Checks shared by the openpyxl file and its LibreOffice re-save.104    private func checkCommonContent(_ wb: Workbook, libreOffice: Bool) {105        #expect(wb.sheets.map(\.name) == ["Data", "Table", "Filter", "Hidden"])106        #expect(cellValue(wb, 0, "A1") == .text("Text"))107        #expect(cellValue(wb, 0, "B1") == .number(42))108        #expect(cellValue(wb, 0, "C1") == .number(3.14159))109        #expect(cellValue(wb, 0, "D1") == .bool(true))110        #expect(cellValue(wb, 0, "E1") == .number(DateSerial.serial(year: 2024, month: 2, day: 29)))111        #expect(abs((cellValue(wb, 0, "F1").numberValue ?? 0) - (45351 + (13 * 3600 + 30 * 60 + 15) / 86400.0)) < 1e-9)112        #expect(cellValue(wb, 0, "G1") == .number(6.75 / 24))113        #expect(cellValue(wb, 0, "I1") == .error(.na))114        #expect(cellValue(wb, 0, "A2") == .text("Unicode: é ü 日本 😀"))115        #expect(cellValue(wb, 0, "C2") == .number(1e-10))116        #expect(cellValue(wb, 0, "E2") == .text("  leading and trailing  "))117        #expect(cellValue(wb, 0, "F2") == .text("line1\nline2"))118        #expect(cellValue(wb, 0, "G2") == .number(12345678901234))119        #expect(cellValue(wb, 0, "A3") == .text("plain bold tail"))120        #expect(cellFormula(wb, 0, "B5") == "A5*2")121        #expect(cellFormula(wb, 0, "B6") == "SUM(A5:A7)")122        #expect(cellFormula(wb, 0, "B7") == "IF(A7>2,\"big\",\"small\")")123        #expect(cellFormula(wb, 0, "C5") == "Table!B2+Rate")124        #expect(cellFormula(wb, 0, "C6") == "XLOOKUP(2,A5:A7,B5:B7)")125        #expect(cellFormula(wb, 0, "C7") == "CONCAT(A1,\"-\",A2)")126        #expect(cellFormula(wb, 0, "E5") == "$A$5+A$6+$A7")127        #expect(cellFormula(wb, 0, "D5") == "A5:A7*10")128        #expect(wb.sheets[0].cell(4, 3)?.formula?.spill == RangeRef(a1: "D5:D7"))129        // styles130        let a9 = style(wb, 0, "A9")131        #expect(a9.fontName == "Arial" && a9.fontSize == 14 && a9.bold && a9.italic && a9.strikethrough && a9.underline == .double)132        #expect(a9.textColor == RGBA(hex: 0xFF0000))133        #expect(style(wb, 0, "B9").fill == RGBA(hex: 0x00B0F0))134        #expect(near(style(wb, 0, "C9").textColor, 0x95B3D7), "theme 4 + tint 0.4 → \(String(describing: style(wb, 0, "C9").textColor?.hexString))")135        #expect(near(style(wb, 0, "C9").fill, 0xE36C09), "theme 9 − 25% → \(String(describing: style(wb, 0, "C9").fill?.hexString))")136        let d9 = style(wb, 0, "D9").borders137        #expect(d9.left == BorderEdge(style: .thin, color: .black))138        #expect(d9.right == BorderEdge(style: .medium, color: RGBA(hex: 0x00FF00)))139        #expect(d9.top == BorderEdge(style: .double, color: RGBA(hex: 0x0000FF)))140        #expect(d9.bottom == BorderEdge(style: .dotted, color: RGBA(hex: 0xFF00FF)))141        let e9 = style(wb, 0, "E9")142        #expect(e9.hAlign == .center && e9.vAlign == .top && e9.wrap && e9.rotation == 45)143        let f9 = style(wb, 0, "F9")144        #expect(f9.hAlign == .right && f9.vAlign == .center && f9.rotation == -45 && f9.indent == 2)145        #expect(!style(wb, 0, "G9").locked && style(wb, 0, "G9").formulaHidden)146        #expect(style(wb, 0, "A10").numberFormat == "#,##0.00")147        #expect(style(wb, 0, "B10").numberFormat == (libreOffice ? "0.00\\ %" : "0.00%"))148        #expect(NumberFormats.isDateTime(style(wb, 0, "C10").numberFormat))149        #expect(style(wb, 0, "E10").numberFormat == "0.00E+00")150        #expect(style(wb, 0, "F10").numberFormat == "@")151        // layout152        let m = wb.sheets[0].meta153        #expect(Set(m.merges) == [RangeRef(a1: "A12:C13")!, RangeRef(a1: "E12:E15")!])154        #expect(m.frozenRows == 1 && m.frozenCols == 1)155        #expect(m.zoom == 1.5 && !m.showGridlines)156        #expect(m.hiddenCols.contains(9) && m.hiddenRows.contains(19))157        #expect(abs((m.rowHeights[8] ?? 0) - 40) < 0.5)158        #expect(m.rowOutline[21] == 1 && m.rowOutline[22] == 2 && m.colOutline[11] == 1)159        #expect(abs((m.colWidths[0] ?? 0) - 30 * 5.25) < 4 && abs((m.colWidths[1] ?? 0) - 12.5 * 5.25) < 4)160        // links, comments161        #expect(m.hyperlinks[CellRef(a1: "A16")!]?.target == "https://example.org/page?a=1&b=2")162        #expect(m.hyperlinks[CellRef(a1: "B16")!]?.target.hasSuffix("#Table!A1") == true)163        if !libreOffice { #expect(m.hyperlinks[CellRef(a1: "B16")!]?.target == "#Table!A1") }164        #expect(m.comments[CellRef(a1: "A17")!]?.text == "Review this value\nsecond line")165        #expect(m.comments[CellRef(a1: "A17")!]?.author == "Alice")166        #expect(m.comments[CellRef(a1: "C17")!]?.text == "Note on an empty cell")167        // validations168        #expect(m.validations.count == 4)169        let list = m.validations.first { $0.kind == .list }170        #expect(list?.formula1 == "\"Red,Green,Blue\"" && list?.ranges == [RangeRef(a1: "H12:H20")!] && list?.allowBlank == true)171        #expect(list?.errorMessage == "Choose a colour" && list?.errorTitle == "Invalid colour" && list?.promptMessage == "Pick from the list")172        #expect(list?.showDropdown == true && list?.errorStyle == "warning")173        let whole = m.validations.first { $0.kind == .whole }174        #expect(whole?.formula1 == "1" && whole?.formula2 == "100" && whole?.op == "between" && whole?.allowBlank == false)175        #expect((whole?.ranges ?? []).reduce(0) { $0 + $1.count } == 10 && whole?.ranges.contains(RangeRef(a1: "K12")!) == true)176        #expect(m.validations.first { $0.kind == .custom }?.formula1 == "ISNUMBER(J12)")177        #expect(m.validations.first { $0.kind == .decimal }?.op == "greaterThanOrEqual")178        // conditional formats179        func rule(_ range: String) -> ConditionalFormat? { m.conditionalFormats.first { $0.ranges == [RangeRef(a1: range)!] } }180        #expect(rule("A5:A7")?.kind == .cellIs(op: "greaterThan", formulas: ["1"]))181        #expect(rule("A5:A7")?.style.fill == RGBA(hex: 0xFFC7CE))182        #expect(rule("B5:B7")?.kind == .cellIs(op: "between", formulas: ["1", "5"]))183        if !libreOffice { #expect(rule("B5:B7")?.stopIfTrue == true) }184        #expect(rule("A5:E7")?.kind == .formula("MOD(ROW(),2)=0"))185        if case .colorScale(let pts)? = rule("A20:A30")?.kind {186            #expect(pts.map(\.kind) == ["min", "percentile", "max"] && pts[1].value == "50")187            #expect(pts.map { $0.color?.hexString } == ["F8696B", "FFEB84", "63BE7B"])188        } else { Issue.record("colour scale missing") }189        if case .dataBar(let lo, let hi, let color)? = rule("B20:B30")?.kind { #expect(lo.kind == "min" && hi.kind == "max" && color == RGBA(hex: 0x638EC6)) }190        else { Issue.record("data bar missing") }191        if case .iconSet(let name, let th, let rev)? = rule("C20:C30")?.kind { #expect(name == "3TrafficLights1" && th.count == 3 && th[1].value == "33" && rev) }192        else { Issue.record("icon set missing") }193        #expect(rule("D20:D30")?.kind == .top(rank: 3, percent: false, bottom: true))194        #expect(rule("E20:E30")?.kind == .aboveAverage(above: false, equal: true))195        #expect(rule("F20:F30")?.kind == .duplicates(unique: false))196        #expect(rule("G20:G30")?.kind == .duplicates(unique: true))197        #expect(rule("H20:H30")?.kind == .containsText("err"))198        #expect(rule("I20:I30")?.kind == (libreOffice ? .formula("LEN(TRIM(I20))=0") : .blanks(true)))199        #expect(rule("J20:J30")?.kind == .errors(true))200        #expect(rule("K20:K30")?.kind == .formula(libreOffice ? "LEFT(K20,LEN(\"ab\"))=\"ab\"" : "LEFT(K20,2)=\"ab\""))201        // print202        #expect(m.print.landscape && m.print.paperName == "A4")203        #expect(m.print.fitToWidth == 1 && m.print.fitToHeight == 0)204        #expect(m.print.marginLeft == 36 && m.print.marginTop == 72 && m.print.marginBottom == 54)205        #expect(m.print.header == "&CCorpus &P" && m.print.footer == "&RPage &P of &N")206        #expect(m.print.showGridlines && m.print.centerHorizontally)207        #expect(m.print.rowBreaks == [15] && m.print.colBreaks == [6])208        // table, filter, names, sheets209        let t = wb.sheets[1].meta.tables.first210        #expect(t?.name == "Sales" && t?.range == RangeRef(a1: "A1:C6")! && t?.hasTotals == true && t?.hasHeader == true)211        #expect(t?.columns.map(\.name) == ["Region", "Amount", "Tax"] && t?.columns[1].totalsFunction == "sum")212        #expect(t?.styleName == "TableStyleMedium9" && t?.showRowStripes == true)213        #expect(cellFormula(wb, 1, "C2") == "Sales[[#This Row],[Amount]]*Rate")214        #expect(wb.sheets[1].meta.isProtected)215        #expect(wb.sheets[2].meta.autoFilter == RangeRef(a1: "A1:B9")!)216        #expect(wb.sheets[3].meta.isHidden && !wb.sheets[0].meta.isHidden)217        #expect(wb.definedName("Rate")?.formula == "0.15")218        #expect(wb.definedName("Amounts")?.formula == "Table!$B$2:$B$5")219        #expect(wb.definedName("LocalTotal", sheetID: wb.sheets[1].id)?.scopeSheetID == wb.sheets[1].id)220        #expect(wb.activeSheet == 1)221        #expect(wb.properties.author == "SheetIO generator" && wb.properties.comments == "Generated by gen_openpyxl_corpus.py")222        if !libreOffice { #expect(wb.properties.title == "openpyxl corpus" && wb.properties.keywords == "corpus, test") }223    }224225    @Test func openpyxlCorpus() throws {226        let r = try WorkbookReader.load(url: Scratch.fixture("features_openpyxl.xlsx"))227        let wb = r.workbook228        checkCommonContent(wb, libreOffice: false)229        #expect(!r.hasMacros && !r.hasExternalLinks)230        #expect(wb.sheets[0].meta.tabColor == RGBA(hex: 0x7030A0))231        #expect(wb.sheets[0].meta.print.printArea == RangeRef(a1: "A1:L30")!)232        #expect(wb.sheets[0].meta.print.repeatRows == 0...1 && wb.sheets[0].meta.print.repeatCols == 0...0)233        #expect(wb.definedName("Amounts")?.comment == "All amounts")234        #expect(wb.properties.created == ISO8601DateFormatter().date(from: "2024-01-02T03:04:05Z"))235        #expect(style(wb, 0, "H9").textColor == RGBA(hex: 0xFF0000))        // indexed 10236        #expect(style(wb, 0, "D10").numberFormat == "[$€-40C] #,##0.00;[Red]-[$€-40C] #,##0.00")237        #expect(r.issues.contains { $0.feature == "Functions" && $0.location == "XLOOKUP" })238        #expect(r.issues.contains { $0.feature == "Array formulas" })239        #expect(!r.issues.contains { $0.severity == .loss })240    }241242    @Test func libreOfficeResavedCorpus() throws {243        let r = try WorkbookReader.load(url: Scratch.fixture("features_lo.xlsx"))244        checkCommonContent(r.workbook, libreOffice: true)245        // LibreOffice recalculated the formulas when it saved the file246        #expect(cellValue(r.workbook, 0, "B5") == .number(2))247        #expect(cellValue(r.workbook, 0, "B6") == .number(6))248        #expect(cellValue(r.workbook, 0, "B7") == .text("big"))249        #expect(cellValue(r.workbook, 0, "D6") == .number(20) && cellValue(r.workbook, 0, "D7") == .number(30))250        #expect(r.workbook.sheets[0].cell(5, 3)?.isSpill == true)251        #expect(cellValue(r.workbook, 1, "C2") == .number(30))252        #expect(cellValue(r.workbook, 1, "B6") == .number(1400))253    }254255    @Test func cachedValuesAgreeWithTheEngine() throws {256        let r = try WorkbookReader.load(url: Scratch.fixture("features_lo.xlsx"))257        let cached = ["B5", "B6", "B7", "E5", "E6"].map { cellValue(r.workbook, 0, $0) }258        let trusting = CalcEngine(workbook: r.workbook, trustCachedValues: true)259        #expect(trusting.pendingCount == 0)260        let engine = CalcEngine(workbook: r.workbook)261        engine.recalculate()262        #expect(["B5", "B6", "B7", "E5", "E6"].map { cellValue(r.workbook, 0, $0) } == cached)263    }264265    @Test func rawFeatures() throws {266        let r = try WorkbookReader.load(url: Scratch.fixture("raw_features.xlsx"))267        let wb = r.workbook268        #expect(wb.sheets.map(\.name) == ["Raw", "Second"])            // the chart sheet is skipped269        #expect(wb.activeSheet == 1)                                    // activeTab=2 pointed at "Second"270        #expect(cellValue(wb, 0, "A1") == .text("shared zero"))271        #expect(cellValue(wb, 0, "B1") == .text("inline text"))272        #expect(cellValue(wb, 0, "C1") == .number(DateSerial.serial(year: 2024, month: 3, day: 5) + 0.5))273        #expect(NumberFormats.isDateTime(style(wb, 0, "C1").numberFormat))274        #expect(cellValue(wb, 0, "D1") == .text("string") && cellFormula(wb, 0, "D1") == "\"str\"&\"ing\"")275        #expect(cellValue(wb, 0, "E1") == .bool(true) && cellValue(wb, 0, "F1") == .error(.ref))276        #expect(cellValue(wb, 0, "G1") == .text("rich bold"))           // phonetic run ignored277        #expect(cellValue(wb, 0, "H1") == .text("carriage\rreturn and _x0041_ kept"))278        #expect(cellValue(wb, 0, "I1") == .text("  padded  "))279        #expect(style(wb, 0, "J1").numberFormat == "m/d/yyyy")          // built-in id 14 (Excel: system short date)280        // shared formula expansion281        #expect(["B2", "B3", "B4", "B5"].map { cellFormula(wb, 0, $0) } == ["A2*2+$A$2", "A3*2+$A$2", "A4*2+$A$2", "A5*2+$A$2"])282        #expect(["B2", "B3", "B4", "B5"].map { cellValue(wb, 0, $0) } == [.number(9), .number(5), .number(11), .number(7)])283        // dynamic and legacy arrays284        #expect(cellFormula(wb, 0, "D2") == "SORT(A2:A5)")285        #expect(wb.sheets[0].cell(1, 3)?.formula?.spill == RangeRef(a1: "D2:D5"))286        #expect(wb.sheets[0].cell(2, 3)?.isSpill == true && cellValue(wb, 0, "D4") == .number(3))287        #expect(wb.sheets[0].cell(1, 5)?.formula?.spill == RangeRef(a1: "F2:F3") && wb.sheets[0].cell(2, 5)?.isSpill == true)288        #expect(cellFormula(wb, 0, "G2") == "LET(x,A2,x*2)")289        #expect(cellFormula(wb, 0, "I2") == "SUM(D2#)")290        #expect(cellFormula(wb, 0, "H2") == nil && cellValue(wb, 0, "H2") == .number(100))      // external reference291        #expect(cellFormula(wb, 0, "J2") == "MYUDF(A2)+FUTUREFN(1)" && cellValue(wb, 0, "J2") == .number(7))292        // cells and rows without r attributes continue the sequence293        #expect(cellValue(wb, 0, "A7") == .number(70) && cellValue(wb, 0, "B7") == .number(71) && cellValue(wb, 0, "C7") == .text("run1run2"))294        #expect(cellValue(wb, 0, "B8") == .bool(false) && cellValue(wb, 0, "C8") == .error(.div0))295        #expect(wb.sheets[0].cell(7, 3)?.formula?.value == .blank)296        // styles: theme + tint, indexed, gradient, pattern297        #expect(near(style(wb, 0, "A6").fill, 0xD9D9D9), "\(String(describing: style(wb, 0, "A6").fill?.hexString))")298        #expect(near(style(wb, 0, "A6").textColor, 0x376092, tolerance: 3), "\(String(describing: style(wb, 0, "A6").textColor?.hexString))")299        #expect(style(wb, 0, "A6").bold)300        let b6 = style(wb, 0, "B6")301        #expect(b6.fontName == "Courier New" && b6.fontSize == 9 && b6.italic && b6.underline == .single && b6.textColor == RGBA(hex: 0x0000FF))302        #expect(b6.numberFormat == "0.0\" u\"" && b6.borders.left?.style == .hair && b6.borders.bottom == BorderEdge(style: .thick, color: RGBA(hex: 0xC0504D)))303        #expect(style(wb, 0, "C6").fill == RGBA(hex: 0xFF0000) && style(wb, 0, "D6").fill == RGBA(hex: 0x00FF00))304        #expect(style(wb, 0, "E6").numberFormat == "m/d/yyyy h:mm")305        #expect(wb.defaultFontName == "Calibri" && wb.defaultFontSize == 11)306        // layout307        let m = wb.sheets[0].meta308        #expect(m.frozenRows == 1 && m.frozenCols == 0 && m.rowHeights[5] == 33)309        #expect(m.colWidths[0] == 105 && m.colWidths[2] == 65.625 && m.colWidths[3] == 65.625)310        #expect(wb.styles[m.colStyles[0] ?? 0].bold)311        // comments: legacy note, threaded comment with reply312        #expect(m.comments[CellRef(a1: "A2")!] == CellComment(author: "Legacy Author", text: "Legacy Author:\nlegacy note"))313        let thread = m.comments[CellRef(a1: "B2")!]314        #expect(thread?.isNote == false && thread?.author == "Thread Starter" && thread?.text == "First message\nReplier: A reply")315        #expect(thread?.date != nil)316        // x14 validation317        #expect(m.validations.count == 1 && m.validations[0].kind == .list && m.validations[0].formula1 == "Second!$A$1:$A$3")318        #expect(m.validations[0].ranges == [RangeRef(a1: "J5:J6")!])319        // names and print320        #expect(m.print.printArea == RangeRef(a1: "A1:H5")!)321        #expect(wb.sheets[1].meta.print.repeatRows == 0...0 && wb.sheets[1].meta.print.repeatCols == 0...0)322        #expect(wb.definedName("HiddenName")?.hidden == true && wb.definedName("HiddenName")?.comment == "a secret")323        #expect(wb.definedName("ExtName")?.formula == "[1]Ext!$A$1")324        #expect(wb.definedName("SecondLocal", sheetID: wb.sheets[1].id)?.scopeSheetID == wb.sheets[1].id)325        #expect(wb.definedName("Future")?.formula == "XLOOKUP(1,Raw!$A$2:$A$5,Raw!$B$2:$B$5)")326        #expect(wb.properties.title == "Raw fixture" && wb.properties.author == "gen_raw_xlsx")327        // preserved content and reports328        #expect(r.hasExternalLinks && !r.hasMacros)329        let p = try #require(r.preserved)330        #expect(p.externalLinks == ["xl/externalLinks/externalLink1.xml"])331        #expect(p.pivotCaches == [.init(cacheID: 7, part: "xl/pivotCache/pivotCacheDefinition1.xml")])332        #expect(p.sheets[wb.sheets[1].id]?.pivotTables == ["xl/pivotTables/pivotTable1.xml"])333        #expect(p.sheets[wb.sheets[0].id]?.codeName == "RawSheet" && p.workbookCodeName == "ThisWorkbook")334        #expect(p.customXML == ["customXml/item1.xml"] && p.customProperties == "docProps/custom.xml")335        #expect(p.parts["xl/pivotCache/pivotCacheRecords1.xml"] != nil && p.parts["customXml/itemProps1.xml"] != nil)336        func has(_ feature: String, _ sev: CompatibilityIssue.Severity) -> Bool { r.issues.contains { $0.feature == feature && $0.severity == sev } }337        #expect(has("Formulas", .loss) && has("Sparklines", .warning) && has("Chart sheets", .warning) && has("External links", .warning))338        #expect(has("Pivot tables", .info) && has("Custom XML", .info) && has("Borders", .warning) && has("Fills", .warning))339        #expect(r.issues.contains { $0.feature == "Functions" && $0.location == "MYUDF" })340        #expect(r.issues.contains { $0.feature == "Functions" && $0.location == "SORT" })341        #expect(!r.issues.contains { $0.feature == "Functions" && $0.location == "LET" })342    }343344    @Test func date1904() throws {345        let r = try WorkbookReader.load(url: Scratch.fixture("dates1904_openpyxl.xlsx"))346        #expect(r.workbook.date1904)347        let v = cellValue(r.workbook, 0, "A1").numberValue ?? 0348        let c = DateSerial.components(v, date1904: true)349        #expect(c.year == 2024 && c.month == 2 && c.day == 29)350        #expect(cellValue(r.workbook, 0, "A2") == .number(1.5))351    }352353    @Test func importExportReimportIsStable() throws {354        let dir = Scratch.dir("reimport")355        defer { try? FileManager.default.removeItem(at: dir) }356        for name in ["features_openpyxl.xlsx", "features_lo.xlsx", "raw_features.xlsx", "charts_openpyxl.xlsx", "dates1904_openpyxl.xlsx", "macro.xlsm"] {357            let first = try WorkbookReader.load(url: Scratch.fixture(name))358            let out = dir.appendingPathComponent(name)359            let format: FileFormat = name.hasSuffix("xlsm") ? .xlsm : .xlsx360            let issues = try WorkbookWriter.save(first.workbook, to: out, format: format, preserved: first.preserved)361            let second = try WorkbookReader.load(url: out)362            var o = WorkbookCompareOptions()363            o.properties = name != "raw_features.xlsx" && !name.hasPrefix("macro")364            var diff = WorkbookDiff.differences(first.workbook, second.workbook, options: o)365            // threaded comments are written as notes: the only expected change366            if name == "raw_features.xlsx" {367                #expect(issues.contains { $0.feature == "Comments" && $0.severity == .warning })368                diff.removeAll { $0.contains("comments") }369                #expect(second.workbook.sheets[0].meta.comments[CellRef(a1: "B2")!]?.text == "First message\nReplier: A reply")370            }371            #expect(diff.isEmpty, "\(name): \(diff.joined(separator: "\n"))")372            #expect(second.preserved?.parts == first.preserved?.parts, "\(name): preserved parts changed")373            #expect(second.hasMacros == first.hasMacros && second.hasExternalLinks == first.hasExternalLinks)374        }375    }376}377
Files main
Menu

Theme