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/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