SPB Git

Repository Public

main.swift 14.7 KB 211 lines swift

1import AppKit2import SwiftUI3import SheetCore4import SheetUI5import FunctionsNumeric6import FeatureAudit78// Development harness for the FeatureAudit panels. All data below is synthetic sample data.9//   audit-preview [--pane <id>] [--dialog rule|table|sort|dedupe|names|filter] [--select Sheet!A1:B2]10//                 [--shot out.png] [--dark] [--size WxH] [--audit] [--bench] [--lang fr-CA]11// --pane takes an inspector, studio or bottom pane id (formula, rules, validation, table, auditor, dependencies, audit-findings).12// With --shot the window (or the dialog, when one is open) renders itself to a PNG and the process exits.1314@MainActor func demoSession() -> WorkbookSession {15    let wb = Workbook(sheetNames: ["Sales", "Model", "Summary"])16    let session = WorkbookSession(workbook: wb, locale: Locale(identifier: "en_US"))17    let regions = ["North", "South", "East", "West"]18    let reps = ["Avery", "Blake", "Casey", "Devon", "Emery", "Finley"]19    let parser = session.parser20    session.perform("Sample data") { e in21        func put(_ s: Int, _ a1: String, _ text: String) { if let c = CellRef(a1: a1) { try? e.setInput(sheet: s, row: c.row, col: c.col, text, parser: parser) } }22        // ---- Sales: 40 synthetic orders23        for (c, h) in ["Order", "Region", "Rep", "Date", "Units", "Price", "Revenue"].enumerated() { e.set(sheet: 0, row: 0, col: c, .value(.text(h))) }24        var seed: UInt64 = 2026112125        func rnd(_ n: Int) -> Int { seed = seed &* 6364136223846793005 &+ 1442695040888963407; return Int((seed >> 33) % UInt64(n)) }26        for i in 0..<40 {27            let r = i + 128            e.set(sheet: 0, row: r, col: 0, .value(.number(Double(1000 + i))))29            e.set(sheet: 0, row: r, col: 1, .value(.text(regions[rnd(4)])))30            e.set(sheet: 0, row: r, col: 2, .value(.text(reps[rnd(6)])))31            e.set(sheet: 0, row: r, col: 3, .value(.number(DateSerial.serial(year: 2026, month: 1 + i / 4, day: 1 + rnd(27)))))32            e.set(sheet: 0, row: r, col: 4, .value(.number(Double(5 + rnd(120)))))33            e.set(sheet: 0, row: r, col: 5, .value(.number(Double(1200 + rnd(8800)) / 100)))34            _ = e.setFormula(sheet: 0, row: r, col: 6, "=E\(r + 1)*F\(r + 1)")35        }36        // seeded defects37        _ = e.setFormula(sheet: 0, row: 12, col: 6, "=E13*F13*1.1")                 // inconsistent formula38        e.set(sheet: 0, row: 20, col: 6, .value(.number(4321)))                     // hardcoded value in a formula column39        e.set(sheet: 0, row: 7, col: 4, .value(.text("48")))                        // number stored as text40        e.set(sheet: 0, row: 15, col: 1, .value(.text("Nrth")))                     // breaks the list validation41        e.set(sheet: 0, row: 22, col: 4, .value(.number(950)))                      // breaks the whole-number validation42        e.set(sheet: 0, row: 9, col: 2, .blank)                                     // missing values43        e.set(sheet: 0, row: 27, col: 2, .value(.text("N/A")))44        e.set(sheet: 0, row: 31, col: 5, .blank)45        for c in 0..<6 { if let cell = wb.sheets[0].cell(5, c) { e.set(sheet: 0, row: 33, col: c, .value(cell.value)) } }   // duplicate order46        for c in 0..<6 { if let cell = wb.sheets[0].cell(18, c) { e.set(sheet: 0, row: 38, col: c, .value(cell.value)) } }47        e.setStyle(sheet: 0, range: RangeRef(a1: "D2:D41")!) { $0.numberFormat = "yyyy-mm-dd" }48        e.setStyle(sheet: 0, range: RangeRef(a1: "F2:G42")!) { $0.numberFormat = "#,##0.00" }49        e.editMeta(sheet: 0) { m in m.colWidths[3] = 92; m.colWidths[6] = 96; m.frozenRows = 1 }50        _ = try? TableActions.create(engine: e, sheet: 0, range: RangeRef(a1: "A1:G41")!, options: .init(name: "Orders", hasHeader: true, totals: true, banded: true))51        e.editMeta(sheet: 0) { m in52            let top = (m.conditionalFormats.map(\.priority).max() ?? 0)53            m.conditionalFormats.append(RulePresets.rule(.dataBars, ranges: [RangeRef(a1: "E2:E41")!], priority: top + 1))54            m.conditionalFormats.append(RulePresets.rule(.colorScale, ranges: [RangeRef(a1: "G2:G41")!], priority: top + 2))55            m.conditionalFormats.append(RulePresets.rule(.duplicates, ranges: [RangeRef(a1: "A2:A41")!], priority: top + 3))56            m.conditionalFormats.append(RulePresets.rule(.iconSet, ranges: [RangeRef(a1: "F2:F41")!], priority: top + 4))57            m.conditionalFormats.append(ConditionalFormat(ranges: [RangeRef(a1: "E2:E41")!], kind: .cellIs(op: "greaterThan", formulas: ["500"]), style: RulePresets.redStyle, priority: top + 5))58            // table banding goes last so that the highlights above win59            for i in m.conditionalFormats.indices { if case .formula(let f) = m.conditionalFormats[i].kind, f.hasPrefix("MOD(ROW()") { m.conditionalFormats[i].priority = top + 6 } }60            var list = DataValidation(ranges: [RangeRef(a1: "B2:B41")!], kind: .list, formula1: "\"North,South,East,West\"")61            list.errorTitle = "Unknown region"; list.errorMessage = "Choose North, South, East or West."62            var units = DataValidation(ranges: [RangeRef(a1: "E2:E41")!], kind: .whole, op: "between", formula1: "1", formula2: "500")63            units.errorStyle = "warning"64            let dates = DataValidation(ranges: [RangeRef(a1: "D2:D41")!], kind: .date, op: "greaterThanOrEqual", formula1: String(Int(DateSerial.serial(year: 2026, month: 1, day: 1))))65            m.validations = [list, units, dates]66        }6768        // ---- Model: a small forecast with a dependency chain69        let model: [(String, String)] = [70            ("A1", "Forecast (sample)"), ("A3", "Base revenue"), ("B3", "=SUM(Orders[Revenue])"), ("A4", "Growth"), ("B4", "0.08"),71            ("A5", "Tax rate"), ("B5", "0.265"), ("A6", "Fixed costs"), ("B6", "12500"), ("A7", "Headcount"), ("B7", "0"),72            ("A9", "Year"), ("B9", "2027"), ("C9", "=B9+1"), ("D9", "=C9+1"), ("E9", "=D9+1"),73            ("A10", "Revenue"), ("B10", "=B3*(1+Growth)"), ("C10", "=B10*(1+Growth)"), ("D10", "=C10*(1+Growth)"), ("E10", "=D10*(1+0.05)"),74            ("A11", "Costs"), ("B11", "=$B$6+B10*0.4"), ("C11", "=$B$6+C10*0.4"), ("D11", "=$B$6+D10*0.4"), ("E11", "=$B$6+E10*0.4"),75            ("A12", "Profit before tax"), ("B12", "=B10-B11"), ("C12", "=C10-C11"), ("D12", "=D10-D11"), ("E12", "=E10-E11"),76            ("A13", "Tax"), ("B13", "=IF(B12>0,B12*TaxRate,0)"), ("C13", "=IF(C12>0,C12*TaxRate,0)"), ("D13", "=IF(D12>0,D12*TaxRate,0)"), ("E13", "=IF(E12>0,E12*TaxRate,0)"),77            ("A14", "Net profit"), ("B14", "=B12-B13"), ("C14", "=C12-C13"), ("D14", "=D12-D13"), ("E14", "=E12-E13"),78            ("A15", "Profit per head"), ("B15", "=B14/$B$7"), ("C15", "=B15*1.02"),79            ("A16", "Rating"), ("B16", "=IF(B14>100000,\"Strong\",IF(B14>50000,\"Solid\",IF(B14>0,\"Thin\",\"Loss\")))"),80            ("A17", "Margin"), ("B17", "=IFERROR(ROUND(B14/B10,3),0)"),81            ("A19", "Loop A"), ("B19", "=B20+1"), ("A20", "Loop B"), ("B20", "=B19*2"),82            ("A22", "Updated"), ("B22", "=NOW()"),83        ]84        for (a, t) in model { put(1, a, t) }85        e.setDefinedName(DefinedName(name: "Growth", formula: "Model!$B$4"))86        e.setDefinedName(DefinedName(name: "TaxRate", formula: "Model!$B$5"))87        e.setDefinedName(DefinedName(name: "OldRange", formula: "Archive!$A$1:$A$9", comment: "refers to a deleted sheet"))88        e.setDefinedName(DefinedName(name: "Spare", formula: "Model!$B$6"))89        e.setStyle(sheet: 1, range: RangeRef(a1: "A1")!) { $0.bold = true; $0.fontSize = 16 }90        e.setStyle(sheet: 1, range: RangeRef(a1: "A9:E9")!) { $0.bold = true }91        e.setStyle(sheet: 1, range: RangeRef(a1: "B10:E15")!) { $0.numberFormat = "#,##0" }92        e.setStyle(sheet: 1, range: RangeRef(a1: "B22")!) { $0.numberFormat = "yyyy-mm-dd hh:mm" }93        e.editMeta(sheet: 1) { m in m.colWidths[0] = 130; m.colWidths[1] = 110; m.rowHeights[0] = 28 }9495        // ---- Summary96        let summary: [(String, String)] = [97            ("A1", "Summary"), ("A3", "Orders"), ("B3", "=COUNT(Orders[Order])"), ("A4", "Units"), ("B4", "=SUM(Orders[Units])"),98            ("A5", "Revenue"), ("B5", "=SUM(Orders[Revenue])"), ("A6", "Net profit 2027"), ("B6", "=Model!B14"),99            ("A7", "Net profit 2030"), ("B7", "=Model!E14"), ("A8", "Per head"), ("B8", "=Model!B15"), ("A9", "Outlook"), ("B9", "=Model!B16&\" / margin \"&Model!B17"),100        ]101        for (a, t) in summary { put(2, a, t) }102        e.setStyle(sheet: 2, range: RangeRef(a1: "A1")!) { $0.bold = true; $0.fontSize = 16 }103        e.setStyle(sheet: 2, range: RangeRef(a1: "B4:B7")!) { $0.numberFormat = "#,##0" }104        e.editMeta(sheet: 2) { m in m.colWidths[0] = 130; m.colWidths[1] = 130; m.rowHeights[0] = 28 }105    }106    session.select(RangeRef(a1: "G13")!, sheet: 0)107    return session108}109110/// Times the conditional-format adapter the way the grid uses it: a 50 × 30 window with 5 rules over a 100 000-row sheet.111@MainActor func benchmark() {112    let wb = Workbook(sheetNames: ["Big"])113    let engine = CalcEngine(workbook: wb)114    let rows = 100_000115    engine.transaction("fill") {116        for r in 0..<rows { for c in 0..<6 { engine.set(sheet: 0, row: r, col: c, .value(.number(Double((r * 7919 + c * 104729) % 10007) / 10))) } }117    }118    let all = RangeRef(r0: 0, c0: 0, r1: rows - 1, c1: 5)119    engine.editMeta(sheet: 0) { m in120        m.conditionalFormats = [121            RulePresets.rule(.colorScale, ranges: [RangeRef(r0: 0, c0: 0, r1: rows - 1, c1: 0)], priority: 1),122            RulePresets.rule(.dataBars, ranges: [RangeRef(r0: 0, c0: 1, r1: rows - 1, c1: 1)], priority: 2),123            RulePresets.rule(.iconSet, ranges: [RangeRef(r0: 0, c0: 2, r1: rows - 1, c1: 2)], priority: 3),124            RulePresets.rule(.top10, ranges: [RangeRef(r0: 0, c0: 3, r1: rows - 1, c1: 3)], priority: 4),125            ConditionalFormat(ranges: [all], kind: .cellIs(op: "greaterThan", formulas: ["900"]), style: RulePresets.redStyle, priority: 5),126        ]127    }128    _ = engine.recalculate()129    let adapter = ConditionalEffectsAdapter()130    func time(_ body: () -> Void) -> Double { let t = CFAbsoluteTimeGetCurrent(); body(); return (CFAbsoluteTimeGetCurrent() - t) * 1000 }131    func window(_ r0: Int) -> RangeRef { RangeRef(r0: r0, c0: 0, r1: r0 + 49, c1: 29) }132    var n = 0133    let cold = time { n = adapter.effects(workbook: wb, engine: engine, sheet: 0, range: window(0), revision: 1).count }134    var scroll = [Double]()135    for step in 1...200 { scroll.append(time { _ = adapter.effects(workbook: wb, engine: engine, sheet: 0, range: window(step * 3), revision: 1) }) }136    var jump = [Double]()137    for step in 1...50 { jump.append(time { _ = adapter.effects(workbook: wb, engine: engine, sheet: 0, range: window(step * 1900), revision: 1) }) }138    var same = [Double]()139    for _ in 0..<200 { same.append(time { _ = adapter.effects(workbook: wb, engine: engine, sheet: 0, range: window(40_000), revision: 1) }) }140    func stats(_ a: [Double]) -> String { let s = a.sorted(); return String(format: "median %.3f ms, p95 %.3f ms, max %.3f ms", s[s.count / 2], s[Int(Double(s.count) * 0.95)], s.last ?? 0) }141    print("conditional-format effects, 50x30 window, 5 rules, \(rows) rows (\(n) effects in the window)")142    print(String(format: "  first call (statistics of 4 rules over 100k rows): %.2f ms", cold))143    print("  scrolling 3 rows per frame (200 frames): " + stats(scroll))144    print("  jumping to a new 50-row window (50 jumps): " + stats(jump))145    print("  redraw of a window already seen (200 draws): " + stats(same))146}147148final class AppDelegate: NSObject, NSApplicationDelegate {149    var window: NSWindow!150    var session: WorkbookSession!151152    func applicationDidFinishLaunching(_ notification: Notification) {153        let args = CommandLine.arguments154        func value(_ flag: String) -> String? { args.firstIndex(of: flag).flatMap { $0 + 1 < args.count ? args[$0 + 1] : nil } }155        NumericFunctions.register()156        if args.contains("--bench") { benchmark(); exit(0) }157        var size = NSSize(width: 1280, height: 800)158        if let s = value("--size")?.split(separator: "x").compactMap({ Double($0) }), s.count == 2 { size = NSSize(width: s[0], height: s[1]) }159        if args.contains("--dark") { NSApp.appearance = NSAppearance(named: .darkAqua) }160        if let lang = value("--lang"), !AuditLocalization.use(language: lang) { print("no \(lang) localization in the catalog") }161        AuditFeature.register()162        session = demoSession()163        if let sel = value("--select") {164            if !session.goTo(sel) { print("cannot select \(sel)") }165        }166        if let id = value("--pane") {167            let ws = Workspace.shared168            if ws.panes(.studio).contains(where: { $0.id == id }) { session.studioPane = id }169            else if ws.panes(.bottom).contains(where: { $0.id == id }) { session.bottomPane = id }170            else { session.inspectorPane = id }171        }172        if args.contains("--audit") || value("--pane") == "auditor" || value("--pane") == "audit-findings" {173            if !args.contains("--no-audit") { AuditorModel.model(for: session).runNow(session) }174        }175        if let n = value("--finding").flatMap({ Int($0) }) {176            let m = AuditorModel.model(for: session)177            if n < m.visibleFindings.count { m.selectedID = m.visibleFindings[n].id }178        }179        window = NSWindow(contentRect: NSRect(origin: .zero, size: size), styleMask: [.titled, .closable, .resizable, .miniaturizable], backing: .buffered, defer: false)180        window.title = "ORIX Sheet — audit preview"181        window.contentView = NSHostingView(rootView: WorkbookView(session: session))182        window.center()183        window.makeKeyAndOrderFront(nil)184        NSApp.activate(ignoringOtherApps: false)185        let dialog = value("--dialog")186        if let dialog {187            DispatchQueue.main.asyncAfter(deadline: .now() + 0.4) {188                MainActor.assumeIsolated { AuditFeature.present(dialog: dialog, in: self.session) }189            }190        }191        if let out = value("--shot") {192            DispatchQueue.main.asyncAfter(deadline: .now() + (dialog == nil ? 1.4 : 2.2)) {193                MainActor.assumeIsolated {194                    let target = dialog != nil ? (self.window.attachedSheet?.contentView ?? self.window.contentView) : self.window.contentView195                    guard let view = target, let rep = view.bitmapImageRepForCachingDisplay(in: view.bounds) else { exit(2) }196                    view.cacheDisplay(in: view.bounds, to: rep)197                    try? rep.representation(using: .png, properties: [:])?.write(to: URL(fileURLWithPath: out))198                    exit(0)199                }200            }201        }202    }203    func applicationShouldTerminateAfterLastWindowClosed(_ sender: NSApplication) -> Bool { true }204}205206let app = NSApplication.shared207let delegate = AppDelegate()208app.delegate = delegate209app.setActivationPolicy(CommandLine.arguments.contains("--shot") || CommandLine.arguments.contains("--bench") ? .accessory : .regular)210app.run()211
Files main
Menu

Theme