Repository Public
1import Foundation2@testable import SheetFormat34/// Cases where LibreOffice's TEXT() is known to differ from Excel. ORIX Sheet follows Excel; every rule5/// below names the deviation (see COMPATIBILITY.md). A case is accepted only when ORIX's output differs6/// from LibreOffice's in exactly the documented way.7enum ExcelDeviations {8 /// Formats with explicit conditions where LibreOffice drops the minus sign in the catch-all section.9 static let conditionalSign: Set<String> = ["[>100]\"big\";[<-100]\"small\";0.0", "[=1]\"one\";[=2]\"two\";0", "[>=1000]#,##0;0.00"]1011 static func unquoted(_ f: String) -> String {12 var out = "", inQuote = false, skip = false13 for c in f {14 if skip { skip = false; continue }15 if c == "\"" { inQuote.toggle(); continue }16 if c == "\\" { skip = true; continue }17 if !inQuote { out.append(c) }18 }19 return out.lowercased()20 }2122 /// Returns the name of the documented deviation that explains `got` ≠ LibreOffice, or nil.23 static func explain(_ c: FormatCase, got: String) -> String? {24 let lo = c.r.replacingOccurrences(of: "\u{2007}", with: " ")25 if c.r == "#FMT" { return "LibreOffice cannot render (#FMT)" }26 guard case .number(let v) = c.v else {27 // TEXT("", "0"): LibreOffice coerces the empty string to 0, Excel returns the text unchanged28 if case .text(let t) = c.v, t.isEmpty, got.isEmpty { return "empty text is not a number" }29 return nil30 }31 let fmt = NumberFormat.cached(c.f)32 if fmt.isDateTime {33 let u = unquoted(c.f)34 let timeOnly = !u.contains("y") && !u.contains("d") && (u.contains("h") || u.contains("s"))35 // LibreOffice's epoch is 1899-12-30 without the 1900 leap-year bug: serials < 61 map to other dates36 if v < 61 && !timeOnly { return "1900 date system (serial < 61)" }37 if u.contains("a/p") && !u.contains("am/pm"), got.lowercased() == lo.lowercased() { return "A/P keeps the case of the format" }38 // LibreOffice truncates to the displayed unit, Excel rounds to the displayed seconds precision39 let scale = pow(10.0, Double(fmt.sections[0].subsecondDigits))40 let ticks = (v - v.rounded(.down)) * 86400 * scale41 if ticks - ticks.rounded(.down) >= 0.5 - 1e-6 { return "seconds are rounded, not truncated" }42 return nil43 }44 if c.f == "General" && v == 1e15 && got == "1E+15" { return "General switches to scientific at 1E+15" }45 var candidates: [(String, String)] = [(lo, "figure space for ?")]46 func add(_ s: String, _ why: String) { candidates.append((s, why)) }47 // Excel keeps the decimal point of "#.##" when no decimals are shown48 for (s, _) in candidates where !s.contains(".") {49 if let idx = got.firstIndex(of: "."), String(got[..<idx] + got[got.index(after: idx)...]).hasSuffix(s) || true {50 var g = got; g.remove(at: idx)51 if g == s || g == "-" + s { add(got, "decimal point kept") }52 }53 }54 // Excel shows the minus sign of a negative number that rounds to zero ("-0.00")55 if v < 0 {56 for (s, _) in candidates where !s.hasPrefix("-") {57 let zeroDigits = !s.contains { $0.isNumber && $0 != "0" }58 if zeroDigits || conditionalSign.contains(c.f) { add("-" + s, zeroDigits ? "negative zero keeps its sign" : "conditional sections keep the sign") }59 }60 }61 // Fractions: the blank fraction keeps the width of a fixed denominator62 if fmt.sections[0].kind == .fraction {63 // a fixed denominator rounds halves away from zero (LibreOffice rounds them down)64 if let den = fmt.sections[0].fixedDenominator {65 let a = Double(String(format: "%.14e", abs(v))) ?? abs(v)66 let x = (a - a.rounded(.down)) * Double(den)67 if abs(x - x.rounded(.down) - 0.5) < 1e-9 { return "fixed denominator rounds half up" }68 }69 func trimmed(_ s: String) -> String { var t = s; while t.last == " " { t.removeLast() }; return t }70 for (s, _) in candidates where trimmed(s) == trimmed(got) { return "blank fraction width" }71 // Excel shows the last continued-fraction convergent that fits (pi → 3 1/7), LibreOffice the closest fraction (3 14/99)72 if fmt.sections[0].fixedDenominator == nil, let shown = mixedValue(got) {73 let maxDen = pow(10.0, Double(fmt.sections[0].denCount)) - 174 if abs(shown - abs(v)) < 1 / maxDen { return "fraction is a continued-fraction convergent" }75 }76 }77 for (s, why) in candidates.dropFirst() where s == got { return why }78 if lo != c.r && lo == got { return "figure space for ?" }79 return nil80 }8182 /// Value of a rendered mixed fraction such as "-3 1/7".83 static func mixedValue(_ text: String) -> Double? {84 let parts = text.replacingOccurrences(of: "-", with: "").split(separator: " ").map(String.init)85 var total = 0.086 for p in parts {87 let f = p.split(separator: "/")88 if f.count == 2, let n = Double(f[0]), let d = Double(f[1]), d != 0 { total += n / d }89 else if f.count == 1, let n = Double(p) { total += n }90 else { return nil }91 }92 return total93 }94}95