SPB Git

Repository Public

BuiltinNumberFormatTests.swift 13.0 KB 227 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/BuiltinNumberFormatTests.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 SheetCore90import SheetFormat91@testable import SheetIO9293/// Built-in number formats: one table (`SheetFormat.BuiltinFormats`) for the reader, the writer and94/// the format engine. Fixture: `Generators/gen_builtin_numfmt_xlsx.py` (ids only, no `<numFmts>`).95@Suite struct BuiltinNumberFormatTests {96    /// The full id → code list SheetIO reads and writes. Any change here is a file-format change.97    static let pinned: [(Int, String)] = [98        (0, "General"), (1, "0"), (2, "0.00"), (3, "#,##0"), (4, "#,##0.00"),99        (5, "$#,##0_);($#,##0)"), (6, "$#,##0_);[Red]($#,##0)"),100        (7, "$#,##0.00_);($#,##0.00)"), (8, "$#,##0.00_);[Red]($#,##0.00)"),101        (9, "0%"), (10, "0.00%"), (11, "0.00E+00"), (12, "# ?/?"), (13, "# ??/??"),102        (14, "m/d/yyyy"), (15, "d-mmm-yy"), (16, "d-mmm"), (17, "mmm-yy"),103        (18, "h:mm AM/PM"), (19, "h:mm:ss AM/PM"), (20, "h:mm"), (21, "h:mm:ss"), (22, "m/d/yyyy h:mm"),104        (37, "#,##0_);(#,##0)"), (38, "#,##0_);[Red](#,##0)"), (39, "#,##0.00_);(#,##0.00)"), (40, "#,##0.00_);[Red](#,##0.00)"),105        (41, "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)"),106        (42, "_($* #,##0_);_($* \\(#,##0\\);_($* \"-\"_);_(@_)"),107        (43, "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"),108        (44, "_($* #,##0.00_);_($* \\(#,##0.00\\);_($* \"-\"??_);_(@_)"),109        (45, "mm:ss"), (46, "[h]:mm:ss"), (47, "mm:ss.0"), (48, "##0.0E+0"), (49, "@"),110    ]111112    @Test func tableIsPinnedAndShared() {113        let table = NumberFormats.builtin114        #expect(table.keys.sorted() == Self.pinned.map(\.0))115        #expect(table.keys.sorted() == Array(0...22) + Array(37...49))116        for (id, code) in Self.pinned {117            #expect(table[id] == code, "id \(id)")118            #expect(BuiltinFormats.code(forID: id) == code, "id \(id)")              // the same table, not a copy119            #expect(NumberFormats.code(for: id, custom: [:]) == code)120            #expect(NumberFormats.builtinID(code) == id, "code \(code)")121        }122        #expect(table == BuiltinFormats.codes)123        // locale-reserved and undefined ids fall back to General; a custom definition wins over a built-in124        for id in Array(23...36) + [50, 58, 81, 163, 999] { #expect(NumberFormats.code(for: id, custom: [:]) == "General", "id \(id)") }125        #expect(NumberFormats.code(for: 14, custom: [14: "yyyy"]) == "yyyy" && NumberFormats.code(for: 30, custom: [30: "m-d-yy"]) == "m-d-yy")126        // the ECMA-376 spellings are not built-in codes for the writer: they stay custom and round-trip127        for alias in ["mm-dd-yy", "m/d/yy", "m/d/yy h:mm", "mmss.0", "\"$\"#,##0_);(\"$\"#,##0)"] { #expect(NumberFormats.builtinID(alias) == nil, "\(alias)") }128    }129130    @Test func importedBuiltinIdsDisplayAsInExcel() throws {131        let wb = try WorkbookReader.load(url: Scratch.fixture("builtin_numfmt.xlsx")).workbook132        let sheet = wb.sheets[0]133        var shown: [Int: String] = [:], codes: [Int: String] = [:]134        for r in 0..<18 {135            guard let id = sheet.value(r, 0).numberValue.map({ Int($0) }), let cell = sheet.cell(r, 1) else { Issue.record("row \(r)"); continue }136            let code = wb.styles[cell.style].numberFormat137            codes[id] = code138            shown[id] = NumberFormat.cached(code).render(cell.computed, locale: .enUS).text139        }140        // the case of the directive: numFmtId 14 holding 46347 is 21 November 2026141        #expect(sheet.value(0, 1) == .number(46347) && codes[14] == "m/d/yyyy")142        #expect(shown[14] == "11/21/2026")143        // same values as LibreOffice (en-US profile) shows for this very file144        #expect(shown[22] == "11/21/2026 13:30")145        #expect(shown[37] == "(1,235)" && shown[38] == "(1,235)" && shown[39] == "(1,234.50)" && shown[40] == "(1,234.50)")146        #expect(shown[45] == "01:23" && shown[46] == "36:00:00" && shown[47] == "01:23.3" && shown[48] == "12.3E+3" && shown[49] == "42")147        #expect(shown[5] == "($1,235)" && shown[7] == "($1,234.50)")148        for (id, digits) in [(42, "1,235"), (44, "1,234.50")] {      // accounting: "$", fill, digits, right padding149            let t = (shown[id] ?? "").trimmingCharacters(in: .whitespaces)150            #expect(t.hasPrefix("$") && t.hasSuffix(digits), "id \(id): \(t)")151        }152        for id in [14, 22] { #expect(NumberFormats.kind(of: codes[id]!) == (id == 14 ? .date : .dateTime)) }153        // ids 23–36 are reserved for CJK locales: no en-US definition, General154        #expect(codes[27] == "General" && codes[30] == "General" && codes[36] == "General" && shown[27] == "46347")155    }156157    @Test func writerUsesTheSameIdsAndKeepsAliasesCustom() throws {158        let wb = Workbook(sheetNames: ["S"])159        let formats = ["m/d/yyyy", "m/d/yyyy h:mm", "mm:ss.0", "$#,##0.00_);($#,##0.00)", "mm-dd-yy", "m/d/yy h:mm", "0.000"]160        for (i, f) in formats.enumerated() {161            var s = CellStyle(); s.numberFormat = f162            wb.sheets[0].cells.set(row: i, col: 0, Cell(value: .number(46347.5625), style: wb.styles.intern(s)))163        }164        let data = try WorkbookWriter.data(wb, format: .xlsx).data165        let dir = Scratch.dir("builtin"); defer { try? FileManager.default.removeItem(at: dir) }166        let url = dir.appendingPathComponent("w.xlsx")167        try data.write(to: url)168        let styles = String(decoding: try ZipArchive(url: url).data(for: "xl/styles.xml"), as: UTF8.self)169        for id in [14, 22, 47, 7] { #expect(styles.contains("numFmtId=\"\(id)\""), "id \(id)") }170        #expect(styles.contains("formatCode=\"mm-dd-yy\"") && styles.contains("formatCode=\"m/d/yy h:mm\"") && styles.contains("formatCode=\"0.000\""))171        #expect(!styles.contains("formatCode=\"m/d/yyyy\"") && !styles.contains("formatCode=\"mm:ss.0\""))172        let back = try WorkbookReader.load(url: url).workbook173        #expect((0..<formats.count).map { back.styles[back.sheets[0].cell($0, 0)!.style].numberFormat } == formats)174    }175}176177@Suite struct XLSBDetectionTests {178    /// A minimal Excel binary workbook package: BIFF12 main part, optionally a VBA project.179    static func xlsb(vba: Bool = false) throws -> Data {180        var w = ZipWriter()181        try w.add("[Content_Types].xml", Data("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\"><Default Extension=\"bin\" ContentType=\"application/vnd.ms-excel.sheet.binary.macroEnabled.main\"/><Default Extension=\"rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/></Types>".utf8))182        try w.add("_rels/.rels", Data("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\"><Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\" Target=\"xl/workbook.bin\"/></Relationships>".utf8))183        try w.add("xl/workbook.bin", Data([0x83, 0x01, 0x00, 0x80, 0x01]))184        try w.add("xl/worksheets/sheet1.bin", Data([0x81, 0x01, 0x00]))185        if vba { try w.add("xl/vbaProject.bin", Data([0xD0, 0xCF, 0x11, 0xE0])) }186        return w.finish()187    }188189    private func expectXLSBRefusal(_ url: URL, format: FileFormat? = nil) {190        do { _ = try WorkbookReader.load(url: url, format: format); Issue.record("xlsb accepted: \(url.lastPathComponent)") }191        catch let e as SheetIOError {192            guard case .unsupported(let m) = e else { Issue.record("\(url.lastPathComponent): \(e)"); return }193            #expect(m.contains(".xlsb") && m.contains(".xlsx"), "\(m)")194        } catch { Issue.record("\(error)") }195    }196197    @Test func xlsbIsDetectedAndRefusedBeforeTheXLSXReader() throws {198        let dir = Scratch.dir("xlsb"); defer { try? FileManager.default.removeItem(at: dir) }199        // by content, whatever the extension, with or without macros200        for (name, vba) in [("book.xlsb", false), ("macros.xlsb", true), ("renamed.xlsx", false), ("renamed.xlsm", true), ("noext", false)] {201            let url = dir.appendingPathComponent(name)202            try Self.xlsb(vba: vba).write(to: url)203            #expect(FileFormat.detect(url: url) == .xlsb, "\(name)")204            expectXLSBRefusal(url)205        }206        // by extension when the content says nothing (missing file, not an archive)207        #expect(FileFormat.detect(url: dir.appendingPathComponent("missing.xlsb")) == .xlsb)208        let junk = dir.appendingPathComponent("junk.xlsb")209        try Data("not a zip".utf8).write(to: junk)210        #expect(FileFormat.detect(url: junk) == .xlsb)211        expectXLSBRefusal(junk)212        // content still beats the extension the other way round: a real XLSX named .xlsb opens213        let real = dir.appendingPathComponent("really-xlsx.xlsb")214        try FileManager.default.copyItem(at: Scratch.fixture("features_openpyxl.xlsx"), to: real)215        #expect(FileFormat.detect(url: real) == .xlsx)216        #expect(try !WorkbookReader.load(url: real).workbook.sheets.isEmpty)217        // the XLSX reader keeps its own refusal when the format is forced218        let forced = dir.appendingPathComponent("forced.xlsb")219        try Self.xlsb().write(to: forced)220        expectXLSBRefusal(forced, format: .xlsx)221        // never readable, never writable222        #expect(!FileFormat.xlsb.canRead && !FileFormat.xlsb.canWrite && FileFormat.xlsb.displayName.contains(".xlsb"))223        #expect(throws: SheetIOError.self) { try WorkbookWriter.save(Workbook(sheetNames: ["a"]), to: dir.appendingPathComponent("o.xlsb"), format: .xlsb) }224        #expect(WorkbookWriter.preflight(Workbook(sheetNames: ["a"]), format: .xlsb).contains { $0.severity == .loss })225    }226}227
Files main
Menu

Theme