Repository Public
1import Foundation2import Testing3import SheetCore4import FunctionsLookup56/// Registers the package's functions into the standard registry exactly once.7enum Setup {8 static let once: Void = {9 LookupFunctions.register()10 FinancialFunctions.register()11 }()12}1314/// One-sheet workbook driven by A1 strings.15final class Book {16 let wb = Workbook(sheetNames: ["Sheet1"])17 let engine: CalcEngine1819 init() {20 _ = Setup.once21 engine = CalcEngine(workbook: wb)22 }2324 func set(_ a1: String, _ v: Value) {25 let ref = CellRef(a1: a1)!26 engine.set(sheet: 0, row: ref.row, col: ref.col, .value(v))27 }28 func set(_ a1: String, _ s: String) {29 if s.hasPrefix("=") {30 let ref = CellRef(a1: a1)!31 #expect(engine.setFormula(sheet: 0, row: ref.row, col: ref.col, s), "formula did not parse: \(s)")32 } else {33 set(a1, .text(s))34 }35 }36 /// Fills a column starting at `a1` (nil = leave empty).37 func column(_ a1: String, _ values: [Value?]) {38 let ref = CellRef(a1: a1)!39 for (i, v) in values.enumerated() {40 if let v { engine.set(sheet: 0, row: ref.row + i, col: ref.col, .value(v)) }41 }42 }43 func row(_ a1: String, _ values: [Value?]) {44 let ref = CellRef(a1: a1)!45 for (i, v) in values.enumerated() {46 if let v { engine.set(sheet: 0, row: ref.row, col: ref.col + i, .value(v)) }47 }48 }49 func get(_ a1: String) -> Value {50 engine.recalculate()51 let ref = CellRef(a1: a1)!52 return wb.sheets[0].value(ref.row, ref.col)53 }54 func eval(_ f: String) -> Value { engine.evaluate(f, sheet: 0, at: CellRef(500, 60)) }55 func num(_ f: String) -> Double { eval(f).numberValue ?? .nan }56}5758extension Value: @retroactive ExpressibleByIntegerLiteral, @retroactive ExpressibleByFloatLiteral,59 @retroactive ExpressibleByStringLiteral, @retroactive ExpressibleByBooleanLiteral {60 public init(integerLiteral value: Int) { self = .number(Double(value)) }61 public init(floatLiteral value: Double) { self = .number(value) }62 public init(stringLiteral value: String) { self = .text(value) }63 public init(booleanLiteral value: Bool) { self = .bool(value) }64}6566func grid(_ rows: [[Value]]) -> Value { .array(ArrayValue(rows)) }67let NA = Value.error(.na)6869func near(_ a: Double, _ b: Double, _ tol: Double = 1e-9) -> Bool {70 abs(a - b) <= tol * max(1, abs(a), abs(b))71}7273// MARK: Reference fixtures7475enum Fixtures {76 static let errorNames: Set<String> = ["#N/A", "#VALUE!", "#REF!", "#DIV/0!", "#NUM!", "#NAME?", "#NULL!"]7778 static func load(_ name: String) throws -> [String: Any] {79 let url = try #require(Bundle.module.url(forResource: name, withExtension: "json", subdirectory: "Fixtures"))80 return try #require(try JSONSerialization.jsonObject(with: Data(contentsOf: url)) as? [String: Any])81 }8283 static func isBool(_ x: Any) -> Bool {84 guard let n = x as? NSNumber else { return false }85 return CFGetTypeID(n) == CFBooleanGetTypeID()86 }8788 /// Compares an engine value with a reference scalar read back from a spreadsheet.89 /// LibreOffice's xlsx export stores logical values as 1/0 and reports several distinct errors90 /// as #VALUE!, so only #N/A and #DIV/0! are compared by kind.91 static func matchScalar(_ got: Value, _ want: Any, tol: Double) -> Bool {92 if want is NSNull {93 switch got {94 case .blank: return true95 case .number(let n): return n == 096 case .text(let s): return s.isEmpty97 default: return false98 }99 }100 if isBool(want) {101 let b = (want as! NSNumber).boolValue102 return got == .bool(b) || got == .number(b ? 1 : 0)103 }104 if let n = want as? NSNumber {105 let d = n.doubleValue106 switch got {107 case .number(let x): return near(x, d, tol)108 case .bool(let b): return d == (b ? 1 : 0)109 case .blank: return d == 0110 default: return false111 }112 }113 if let s = want as? String {114 if errorNames.contains(s) {115 guard case .error(let e) = got else { return false }116 if s == "#N/A" { return e == .na }117 if s == "#DIV/0!" { return e == .div0 }118 return true119 }120 if case .blank = got { return s.isEmpty }121 return got == .text(s)122 }123 return false124 }125126 static func match(_ got: Value, _ want: Any, tol: Double) -> Bool {127 if let d = want as? [String: Any] {128 guard let rows = d["rows"] as? Int, let cols = d["cols"] as? Int, let items = d["items"] as? [Any] else { return false }129 let arr: ArrayValue130 if case .array(let a) = got { arr = a } else { arr = ArrayValue(rows: 1, cols: 1, items: [got]) }131 guard arr.rows == rows, arr.cols == cols else { return false }132 return zip(arr.items, items).allSatisfy { matchScalar($0, $1, tol: tol) }133 }134 if case .array(let a) = got { return a.count == 1 && matchScalar(a.items[0], want, tol: tol) }135 return matchScalar(got, want, tol: tol)136 }137138 static func describe(_ v: Value) -> String {139 if case .array(let a) = v { return "[\(a.rows)x\(a.cols)] " + a.items.map { describe($0) }.joined(separator: " | ") }140 switch v {141 case .blank: return "<blank>"142 case .text(let s): return "\"\(s)\""143 case .bool(let b): return b ? "TRUE" : "FALSE"144 case .number(let n): return String(n)145 default: return v.description146 }147 }148149 /// Runs every case of a fixture; returns (count, failure descriptions).150 static func run(_ name: String, defaultTolerance: Double = 1e-9) throws -> (Int, [String]) {151 let doc = try load(name)152 let book = Book()153 for (a1, raw) in (doc["data"] as? [String: Any]) ?? [:] {154 if isBool(raw) { book.set(a1, .bool((raw as! NSNumber).boolValue)) }155 else if let n = raw as? NSNumber { book.set(a1, .number(n.doubleValue)) }156 else if let s = raw as? String { book.set(a1, s) }157 }158 book.engine.recalculate()159 var failures = [String]()160 let cases = (doc["cases"] as? [[String: Any]]) ?? []161 for c in cases {162 guard let f = c["f"] as? String else { continue }163 let tol = (c["tol"] as? NSNumber)?.doubleValue ?? defaultTolerance164 // LibreOffice's iterative solvers stop earlier than ours: its own tolerance when given165 let loTol = (c["source"] as? String)?.hasPrefix("libreoffice") == true ? (c["lotol"] as? NSNumber)?.doubleValue ?? tol : tol166 let got = book.eval(f)167 if let want = c["expect"], !match(got, want, tol: loTol) {168 failures.append("\(f)\n got \(describe(got))\n want \(want) [\(c["source"] as? String ?? "")]")169 } else if let py = c["py"], !match(got, py, tol: tol) {170 failures.append("\(f)\n got \(describe(got))\n python \(py)")171 }172 }173 return (cases.count, failures)174 }175176 static func check(_ name: String, minimum: Int, tolerance: Double = 1e-9) throws {177 let (count, failures) = try run(name, defaultTolerance: tolerance)178 #expect(count >= minimum, "fixture \(name) has only \(count) cases")179 #expect(failures.isEmpty, "\(failures.count) of \(count) reference cases failed in \(name):\n\(failures.prefix(60).joined(separator: "\n"))")180 }181}182