SPB Git

Repository Public

ExcelDeviations.swift 5.3 KB 95 lines swift

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
Files main
Menu

Theme