Repository Public
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