diff --git a/ROADMAP.md b/ROADMAP.md --- a/ROADMAP.md +++ b/ROADMAP.md @@ -2,7 +2,7 @@ **Stage: alpha** · next: beta, ~25 points of "ready for real work" and ~160–240 h away -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right, and fills a selection's empty last row and column) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (`cargo xtask bench`: measured recalc baselines) · **Target:** Microsoft Excel (Microsoft 365) GridCraft aims at full Microsoft Excel parity, and at being better in places: faster, open (XLSX is the native format), fully scriptable by agents (MCP, control channel, CLI), and on the web. @@ -135,6 +135,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---| +| 2026-10-26 | `cargo xtask bench`: ten fixed in-code workbooks (100k rows) time edits, formula entry, Calculate Now, sort and XLSX save/open, check their results and compare with a saved baseline. On an M2 Pro a 100k-dependent edit recalculates in 90–106 ms and 300k formulas in 395–455 ms | | 2026-10-25 | AutoSum over a selection does what Excel does: a selected row gets one total to its right (in its last cell if that is empty), not a one-cell total under each cell; a block whose last column is empty gets each row's total there, one whose last row is empty each column's, and with both the corner gets the total of the block; otherwise totals go under each column as before | | 2026-10-24 | Go To lists the named ranges of every sheet (`Sheet2!Name` for a name scoped to another sheet; hidden names, constants and formulas left out) and the references entered before, a double-click goes to an entry, and the Reference box takes several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30`) | | 2026-10-24 | Status bar: a right-click opens a menu that sets which selection statistics show (Average, Count and Sum by default; Numerical Count, Minimum and Maximum are new), kept with the preferences (`view.statusBar`) | @@ -242,6 +243,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Progress log: `cargo xtask bench` | | 2026-10-25 | minor | Progress log: AutoSum over a selected row, and a selection's empty last row and column | | 2026-10-23 | minor | Progress log: accounting negatives in number text | | 2026-10-23 | minor | Progress log: INDEX with one index on a 2-D reference is #REF! | diff --git a/crates/engine/examples/bench.rs b/crates/engine/examples/bench.rs new file mode 100644 --- /dev/null +++ b/crates/engine/examples/bench.rs @@ -0,0 +1,352 @@ +//! Calculation benchmarks on fixed workbooks built in code (release build). Run through +//! `cargo xtask bench`, which builds this in release and compares against a saved baseline, or +//! directly: `cargo run --release -p gridcraft-engine --example bench -- [--quick] [--rows N] +//! [--reps N] [--filter NAME] [--json PATH]`. +//! +//! Every scenario builds its workbook untimed, runs one warm-up, then times `reps` runs of one +//! operation through `Session::execute` (so a timing includes what an edit costs the user: undo +//! history, graph update and recalculation). Results are checked so a scenario that silently +//! stopped recalculating fails instead of getting fast. + +#![allow(clippy::disallowed_methods)] // a native-only benchmark: wasm never runs it + +use std::time::{Duration, Instant}; + +use gridcraft_core::{CellRef, Value}; +use gridcraft_engine::Session; +use serde_json::{Value as Json, json}; + +struct Cfg { + rows: usize, + reps: usize, + filter: Option, +} + +struct Outcome { + name: &'static str, + what: String, + times: Vec, + cells: usize, +} + +type R = Result; + +fn main() -> std::process::ExitCode { + match run() { + Ok(()) => std::process::ExitCode::SUCCESS, + Err(e) => { + eprintln!("bench: {e}"); + std::process::ExitCode::FAILURE + } + } +} + +fn run() -> R<()> { + let args: Vec = std::env::args().skip(1).collect(); + let flag = |name: &str| args.iter().position(|a| a == name).and_then(|i| args.get(i + 1)).cloned(); + let quick = args.iter().any(|a| a == "--quick"); + let cfg = Cfg { + rows: flag("--rows").and_then(|s| s.parse().ok()).unwrap_or(if quick { 10_000 } else { 100_000 }).max(200), + reps: flag("--reps").and_then(|s| s.parse().ok()).unwrap_or(if quick { 3 } else { 7 }).max(1), + filter: flag("--filter"), + }; + let scenarios: [(&'static str, fn(&Cfg) -> R); 10] = [ + ("edit.chain", edit_chain), + ("edit.fanout", edit_fanout), + ("edit.sliding", edit_sliding), + ("edit.sumifs", edit_sumifs), + ("edit.lookup", edit_lookup), + ("enter.formulas", enter_formulas), + ("recalc.full", recalc_full), + ("data.sort", data_sort), + ("xlsx.save", xlsx_save), + ("xlsx.open", xlsx_open), + ]; + println!("GridCraft calc bench: {} rows, {} reps (median / min)\n", cfg.rows, cfg.reps); + println!("{:<16} {:>10} {:>10} {:>9} workload", "scenario", "median", "min", "cells"); + let mut results = Vec::new(); + // A failed scenario is reported and the others still run, so one regression doesn't hide + // the numbers for everything else. + let mut failed = Vec::new(); + let selected: Vec<_> = scenarios.into_iter().filter(|(name, _)| cfg.filter.as_deref().is_none_or(|p| name.contains(p))).collect(); + if selected.is_empty() { + // An empty run would otherwise save as a baseline with nothing in it. + return Err(format!("--filter {} matches no scenario", cfg.filter.as_deref().unwrap_or(""))); + } + for (name, f) in selected { + let o = match f(&cfg) { + Ok(o) => o, + Err(e) => { + println!("{name:<16} {:>10} {e}", "FAILED"); + failed.push(name); + continue; + } + }; + let (med, min) = stats(&o.times); + let cells = if o.cells == 0 { "-".to_string() } else { o.cells.to_string() }; + println!("{:<16} {:>10} {:>10} {:>9} {}", o.name, ms(med), ms(min), cells, o.what); + results.push(json!({"name": o.name, "what": o.what, "median_ms": med, "min_ms": min, "cells": o.cells})); + } + if let Some(path) = flag("--json") { + let out = json!({"rows": cfg.rows, "reps": cfg.reps, "results": results, "failed": failed}); + let text = serde_json::to_string_pretty(&out).map_err(|e| e.to_string())?; + std::fs::write(&path, text).map_err(|e| format!("{path}: {e}"))?; + } + if failed.is_empty() { Ok(()) } else { Err(format!("{} scenario(s) failed: {}", failed.len(), failed.join(", "))) } +} + +fn stats(times: &[Duration]) -> (f64, f64) { + let mut v: Vec = times.iter().map(|d| d.as_secs_f64() * 1000.0).collect(); + v.sort_by(f64::total_cmp); + let med = v.get(v.len() / 2).copied().unwrap_or(0.0); + (med, v.first().copied().unwrap_or(0.0)) +} + +fn ms(v: f64) -> String { + if v >= 100.0 { format!("{v:.0} ms") } else { format!("{v:.2} ms") } +} + +fn exec(s: &mut Session, cmd: &str, p: Json) -> R { + s.execute(cmd, p).map_err(|e| format!("{cmd}: {e}")) +} + +fn session() -> R { + let mut s = Session::new(); + s.new_workbook(); + Ok(s) +} + +/// Writes `rows` rows starting at `col` row 1, `make(i)` giving row i (0-based), in chunks. +fn fill(s: &mut Session, col: &str, rows: usize, make: impl Fn(usize) -> Json) -> R<()> { + let chunk = 10_000; + let mut r = 0; + while r < rows { + let n = chunk.min(rows - r); + let vals: Vec = (r..r + n).map(&make).collect(); + exec(s, "range.setValues", json!({"range": format!("{col}{}", r + 1), "values": vals}))?; + r += n; + } + Ok(()) +} + +fn num(s: &Session, at: &str) -> R { + let c = CellRef::parse(at).ok_or_else(|| format!("bad ref {at}"))?; + let d = s.doc().map_err(|e| e.to_string())?; + match d.wb.sheet(0).map(|sh| sh.value(c)) { + Some(Value::Number(n)) => Ok(n), + other => Err(format!("{at} = {other:?}, expected a number")), + } +} + +fn expect(s: &Session, at: &str, want: f64) -> R<()> { + let got = num(s, at)?; + if (got - want).abs() <= 1e-6 * want.abs().max(1.0) { Ok(()) } else { Err(format!("{at} = {got}, expected {want}")) } +} + +fn recalc_cells(s: &Session) -> usize { + s.doc().map(|d| d.calc.last_recalc_cells).unwrap_or(0) +} + +/// Runs a warm-up then `reps` timed calls of `op(rep)`, then `check(rep)` (untimed) after each. +fn time( + cfg: &Cfg, + s: &mut Session, + op: impl FnMut(&mut Session, usize) -> R<()>, + check: impl Fn(&Session, usize) -> R<()>, +) -> R<(Vec, usize)> { + time_prepared(cfg, s, |_| Ok(()), op, check) +} + +/// [`time`] with `prep` run (untimed) before each call, to put back the state `op` starts from. +fn time_prepared( + cfg: &Cfg, + s: &mut Session, + mut prep: impl FnMut(&mut Session) -> R<()>, + mut op: impl FnMut(&mut Session, usize) -> R<()>, + check: impl Fn(&Session, usize) -> R<()>, +) -> R<(Vec, usize)> { + let mut times = Vec::with_capacity(cfg.reps); + let mut cells = 0; + for rep in 0..=cfg.reps { + prep(s)?; + let t = Instant::now(); + op(s, rep)?; + let dt = t.elapsed(); + check(s, rep)?; + cells = recalc_cells(s); + if rep > 0 { + times.push(dt); + } + } + Ok((times, cells)) +} + +/// Value typed into A1 on rep `r`: alternates so every rep changes something. +fn a1(r: usize) -> f64 { + (r % 2 + 2) as f64 +} + +/// A running sum `B[n] = B[n-1] + A[n]`: one edit at the top recalculates a dependency chain +/// `rows` deep. +fn edit_chain(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + fill(&mut s, "A", n, |_| json!([1]))?; + fill(&mut s, "B", n, |i| json!([if i == 0 { "=A1".to_string() } else { format!("=B{i}+A{}", i + 1) }]))?; + let (times, cells) = time( + cfg, + &mut s, + |s, r| exec(s, "cell.set", json!({"cell": "A1", "input": a1(r).to_string()})).map(drop), + |s, r| expect(s, &format!("B{n}"), (n - 1) as f64 + a1(r)), + )?; + Ok(Outcome { name: "edit.chain", what: format!("edit A1 under a {n}-deep running sum"), times, cells }) +} + +/// `rows` formulas that each read `$A$1`: one edit, a wide and shallow dirty set. +fn edit_fanout(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + exec(&mut s, "cell.set", json!({"cell": "A1", "input": "1"}))?; + fill(&mut s, "B", n, |i| json!([format!("=$A$1*{}", i + 1)]))?; + let (times, cells) = time( + cfg, + &mut s, + |s, r| exec(s, "cell.set", json!({"cell": "A1", "input": a1(r).to_string()})).map(drop), + |s, r| expect(s, &format!("B{n}"), a1(r) * n as f64), + )?; + Ok(Outcome { name: "edit.fanout", what: format!("edit $A$1 read by {n} formulas"), times, cells }) +} + +/// `rows` overlapping 100-row window sums; an edit in the middle dirties ~100 of them, so this +/// measures the range index lookup, not evaluation. +fn edit_sliding(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + fill(&mut s, "A", n + 99, |_| json!([1]))?; + fill(&mut s, "B", n, |i| json!([format!("=SUM(A{}:A{})", i + 1, i + 100)]))?; + let mid = n / 2; + let (times, cells) = time( + cfg, + &mut s, + |s, r| exec(s, "cell.set", json!({"cell": format!("A{mid}"), "input": a1(r).to_string()})).map(drop), + |s, r| expect(s, &format!("B{mid}"), 99.0 + a1(r)), + )?; + Ok(Outcome { name: "edit.sliding", what: format!("edit one cell under {n} 100-row SUM windows"), times, cells }) +} + +/// 100 whole-column SUMIFS over `rows` rows of data, all dirtied by one data edit. +fn edit_sumifs(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + fill(&mut s, "A", n, |i| json!([1, (i % 100) as f64]))?; + fill(&mut s, "D", 100, |k| json!([format!("=SUMIFS(A:A,B:B,{k})")]))?; + // Rows whose category is 0 (i % 100 == 0), A1 among them. + let per = n.div_ceil(100) as f64; + let (times, cells) = time( + cfg, + &mut s, + |s, r| exec(s, "cell.set", json!({"cell": "A1", "input": a1(r).to_string()})).map(drop), + |s, r| expect(s, "D1", per - 1.0 + a1(r)), + )?; + Ok(Outcome { name: "edit.sumifs", what: format!("edit data under 100 SUMIFS(A:A,B:B,k) over {n} rows"), times, cells }) +} + +/// 100 exact-match XLOOKUPs over whole columns of `rows` keys, all dirtied by one key edit. +fn edit_lookup(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + fill(&mut s, "A", n, |i| json!([(i + 1) as f64, (2 * (i + 1)) as f64]))?; + let step = n / 100; + fill(&mut s, "D", 100, |k| json!([format!("=XLOOKUP({},A:A,B:B,0)", (k + 1) * step)]))?; + let last = (2 * 100 * step) as f64; + let (times, cells) = time( + cfg, + &mut s, + // D1 looks up the key in A{step}: removing it (-step) makes D1 fall back to 0, putting it + // back finds it again. Every lookup reads A:A, so all 100 recalculate either way. + |s, r| { + let key = if r % 2 == 0 { format!("-{step}") } else { step.to_string() }; + exec(s, "cell.set", json!({"cell": format!("A{step}"), "input": key})).map(drop) + }, + |s, r| { + expect(s, "D1", if r % 2 == 0 { 0.0 } else { (2 * step) as f64 })?; + expect(s, "D100", last) + }, + )?; + Ok(Outcome { name: "edit.lookup", what: format!("edit a key under 100 XLOOKUPs over {n} rows"), times, cells }) +} + +/// Entering `rows` formulas in one command: parse, graph insertion and first evaluation. +fn enter_formulas(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + fill(&mut s, "A", n, |i| json!([(i % 97) as f64]))?; + let vals: Vec = (0..n).map(|i| json!([format!("=A{}*2+1", i + 1)])).collect(); + let (times, cells) = time_prepared( + cfg, + &mut s, + // Each run enters the formulas into empty cells, not over the previous run's. + |s| exec(s, "edit.clearAll", json!({"range": format!("B1:B{n}")})).map(drop), + |s, _| exec(s, "range.setValues", json!({"range": "B1", "values": vals})).map(drop), + |s, _| expect(s, &format!("B{n}"), ((n - 1) % 97) as f64 * 2.0 + 1.0), + )?; + Ok(Outcome { name: "enter.formulas", what: format!("enter {n} formulas =An*2+1 in one paste"), times, cells }) +} + +/// A model with 3 formulas per row (arithmetic and a row SUM), recalculated in full. +fn model(n: usize) -> R { + let mut s = session()?; + fill(&mut s, "A", n, |i| json!([(i % 97) as f64, format!("=A{}*2", i + 1), format!("=B{0}+A{0}", i + 1), format!("=SUM(A{0}:C{0})", i + 1)]))?; + Ok(s) +} + +fn recalc_full(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = model(n)?; + let last = ((n - 1) % 97) as f64 * 6.0; + let (times, cells) = time(cfg, &mut s, |s, _| exec(s, "formulas.calculateNow", json!({})).map(drop), |s, _| expect(s, &format!("D{n}"), last))?; + Ok(Outcome { name: "recalc.full", what: format!("Calculate Now, {} formulas (3 per row)", 3 * n), times, cells }) +} + +fn data_sort(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = session()?; + fill(&mut s, "A", n, |i| json!([format!("Item {i}"), (i % 97) as f64, ((i * 7919) % 1000) as f64]))?; + let (times, _) = time( + cfg, + &mut s, + |s, r| { + let cmd = if r % 2 == 0 { "data.sortDescending" } else { "data.sortAscending" }; + exec(s, cmd, json!({"range": format!("A1:C{n}"), "column": "B"})).map(drop) + }, + |s, r| expect(s, "B1", if r % 2 == 0 { 96.0 } else { 0.0 }), + )?; + // A sort with no formulas recalculates nothing; 0 shows as "-". + Ok(Outcome { name: "data.sort", what: format!("sort {n} rows x 3 columns by a numeric key"), times, cells: 0 }) +} + +fn save_bytes(s: &mut Session) -> R { + let r = exec(s, "file.saveBytes", json!({"format": "xlsx"}))?; + r.get("base64").and_then(Json::as_str).map(str::to_string).ok_or_else(|| "file.saveBytes: no base64".to_string()) +} + +fn xlsx_save(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = model(n)?; + let (times, _) = time(cfg, &mut s, |s, _| save_bytes(s).map(drop), |_, _| Ok(()))?; + Ok(Outcome { name: "xlsx.save", what: format!("save the {n}-row model as XLSX"), times, cells: 0 }) +} + +fn xlsx_open(cfg: &Cfg) -> R { + let n = cfg.rows; + let mut s = model(n)?; + let b64 = save_bytes(&mut s)?; + let last = ((n - 1) % 97) as f64 * 6.0; + let (times, cells) = time( + cfg, + &mut s, + |s, _| exec(s, "file.open", json!({"name": "bench.xlsx", "base64": b64})).map(drop), + |s, _| expect(s, &format!("D{n}"), last), + )?; + Ok(Outcome { name: "xlsx.open", what: format!("open the {n}-row model from XLSX (with full recalc)"), times, cells }) +} diff --git a/crates/engine/examples/perf.rs b/crates/engine/examples/perf.rs deleted file mode 100644 --- a/crates/engine/examples/perf.rs +++ /dev/null @@ -1,60 +0,0 @@ -//! Performance scenarios on large workbooks (release build): -//! `cargo run --release -p gridcraft-engine --example perf -- [rows]` - -#![allow(clippy::disallowed_methods)] // a native-only benchmark: wasm never runs it - -use std::time::Instant; - -use gridcraft_engine::Session; -use serde_json::json; - -fn main() { - let rows: usize = std::env::args().nth(1).and_then(|s| s.parse().ok()).unwrap_or(100_000); - let mut s = Session::new(); - s.new_workbook(); - // Data: rows × 6 numeric columns, built through range.setValues in chunks. - let t = Instant::now(); - let chunk = 10_000; - let mut r = 0; - while r < rows { - let n = chunk.min(rows - r); - let vals: Vec = (0..n) - .map(|i| json!([format!("Item {}", r + i), (r + i) as f64, ((r + i) % 97) as f64, ((r + i) % 13) as f64 * 1.5, 1.0, 2.0])) - .collect(); - s.execute("range.setValues", json!({"range": format!("A{}", r + 1), "values": vals})).expect("set"); - r += n; - } - println!("fill {rows}x6 values: {:?}", t.elapsed()); - // Formulas: G = B*C+D, H = running sum of G (chain). - let t = Instant::now(); - s.execute("cell.set", json!({"cell": "G1", "input": "=B1*C1+D1"})).expect("g1"); - s.execute("edit.autoFill", json!({"source": "G1", "target": format!("G1:G{rows}")})).expect("fill g"); - println!("fill {rows} formulas G=B*C+D: {:?}", t.elapsed()); - let t = Instant::now(); - s.execute("cell.set", json!({"cell": "H1", "input": "=G1"})).expect("h1"); - s.execute("cell.set", json!({"cell": "H2", "input": "=H1+G2"})).expect("h2"); - s.execute("edit.autoFill", json!({"source": "H2", "target": format!("H2:H{rows}")})).expect("fill h"); - println!("fill {rows} chained running-sum formulas: {:?}", t.elapsed()); - let t = Instant::now(); - s.execute("cell.set", json!({"cell": "J1", "input": format!("=SUM(G1:G{rows})")})).expect("sum"); - s.execute("cell.set", json!({"cell": "J2", "input": "=SUMIFS(B:B,C:C,\">50\")"})).expect("sumifs"); - s.execute("cell.set", json!({"cell": "J3", "input": "=XLOOKUP(\"Item 77777\",A:A,B:B)"})).expect("xlookup"); - println!("aggregate formulas: {:?}", t.elapsed()); - // Edit at the top: everything below recalculates. - let t = Instant::now(); - s.execute("cell.set", json!({"cell": "B1", "input": "5"})).expect("edit"); - println!("edit B1 → recalc {} cells: {:?}", s.doc().map(|d| d.calc.last_recalc_cells).unwrap_or(0), t.elapsed()); - let t = Instant::now(); - s.execute("data.sortDescending", json!({"range": format!("A1:F{rows}"), "column": "C"})).expect("sort"); - println!("sort {rows} rows: {:?}", t.elapsed()); - let t = Instant::now(); - let r = s.execute("file.saveBytes", json!({"format": "xlsx"})).expect("save"); - let b64 = r["base64"].as_str().unwrap_or("").to_string(); - println!("save xlsx ({} bytes): {:?}", r["bytes"], t.elapsed()); - let t = Instant::now(); - s.execute("file.open", json!({"name": "big.xlsx", "base64": b64})).expect("open"); - println!("open xlsx + full recalc: {:?}", t.elapsed()); - let t = Instant::now(); - s.execute("edit.undo", json!({})).ok(); - println!("undo: {:?}", t.elapsed()); -} diff --git a/docs/egui-037-migration-plan.md b/docs/egui-037-migration-plan.md --- a/docs/egui-037-migration-plan.md +++ b/docs/egui-037-migration-plan.md @@ -1013,8 +1013,8 @@ Fixtures are built in code and never committed as large binaries: - `Session::new()` + `file.new {sample: "sales"|"budget"|"grades"}` for the three samples. - A 100,000-row × 6-column workbook built through `range.setValues` in 10,000-row chunks, plus - formulas `G=B*C+D`, a chained running sum, `SUM`/`SUMIFS`/`XLOOKUP` and a sort, mirroring - `crates/engine/examples/perf.rs`. + formulas `G=B*C+D`, a chained running sum, `SUM`/`SUMIFS`/`XLOOKUP` and a sort, like the + workloads in `crates/engine/examples/bench.rs` (`cargo xtask bench`). - A two-sheet workbook with a formula being pointed across sheets, a right-to-left sheet, frozen panes, merged cells, a table, a chart, comments/notes, validation and a cell picture. - Password-protected and corrupt-font fixtures through `gridcraft-xlsx`'s `test-support` feature diff --git a/docs/gaps.md b/docs/gaps.md --- a/docs/gaps.md +++ b/docs/gaps.md @@ -1,6 +1,6 @@ # Gaps -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-24 · **Change:** minor (§8: PivotTable Enable show details; earlier: PivotTable Show Details) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (`cargo xtask bench`: measured recalc baselines) · **Target:** Microsoft Excel (Microsoft 365) Every known shortfall against Excel, one entry each, ranked by user impact (what blocks someone using GridCraft for real work first). This is the work list: pick from the top, check the open @@ -26,9 +26,11 @@ No alpha blockers: every core workflow passes the [alpha gate](roadmap.md#alpha- - **Estimate:** 15–25 h, plus owner time in Excel. [file-format-parity.md](file-format-parity.md) ### 2. Large workbooks don't open; recalc is single-threaded (P, H) -- **Missing:** streaming read for ~100 MB XLSX; multi-threaded recalc; 100k-dependent edit under - 100 ms (was ~0.4 s); whole-sheet operations bounded. -- **Evidence:** #175; no threading in `crates/calc`; PR #152 (whole-sheet Fill/Remove Duplicates +- **Missing:** streaming read for ~100 MB XLSX; multi-threaded recalc; faster whole-column + SUMIFS and lookups (100 of either over 100k rows take 0.16–0.59 s per edit); whole-sheet + operations bounded. A 100k-dependent edit measures 90–106 ms, around the 100 ms target + (`cargo xtask bench`). +- **Evidence:** #175; `cargo xtask bench`; no threading in `crates/calc`; PR #152 (whole-sheet Fill/Remove Duplicates out of memory), PR #151 (current region past 10,000 rows). - **Impact:** finance and data users routinely have big workbooks. - **Estimate:** 25–40 h. [target-app-parity.md](target-app-parity.md#performance), [hardware-parity.md](hardware-parity.md) @@ -183,6 +185,7 @@ No alpha blockers: every core workflow passes the [alpha gate](roadmap.md#alpha- | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | #2: measured with `cargo xtask bench`; 100k-dependent edit around 100 ms, whole-column SUMIFS/XLOOKUP added | | 2026-10-24 | minor | §8: PivotTable Enable show details: `pivot.options` `enableShowDetails` turns Show Details off for a PivotTable, read and written in XLSX as `enableDrill` | | 2026-10-23 | minor | §8: PivotTable Show Details: double-clicking a value cell lists its source records on a new sheet (#2364) | | 2026-10-22 | minor | §20: a native window per workbook window, New Window on the same workbook and Arrange All on the desktop; left: Arrange All across monitors, the web's one window, pen pressure (estimate 10–14 h → 5–8 h) | diff --git a/docs/roadmap.md b/docs/roadmap.md --- a/docs/roadmap.md +++ b/docs/roadmap.md @@ -1,6 +1,6 @@ # Roadmap detail -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-23 · **Change:** minor (test command) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (`cargo xtask bench`: measured recalc baselines) · **Target:** Microsoft Excel (Microsoft 365) Forward-looking plan. The one-page summary is [`ROADMAP.md`](../ROADMAP.md); the ranked work list is [`gaps.md`](gaps.md); numbers and method are in @@ -107,12 +107,14 @@ to check our files, and Windows machines with the reported GPU drivers. - Function coverage: the script method in [function-parity.md](function-parity.md). A `cargo xtask functions` that diffs the registry against a checked-in Excel name list would make it a CI number (1–2 h). -- No `cargo xtask scorecard` or `bench` yet. +- `cargo xtask bench`: recalc, entry, sort and XLSX timings on fixed in-code workbooks, compared + with a local baseline (`--save`). No `cargo xtask scorecard` yet. ## Revision history | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Measuring: `cargo xtask bench` | | 2026-10-23 | minor | Measuring: tests run through `cargo xtask test` | | 2026-10-11 | minor | PivotTable calculated fields landed | | 2026-10-10 | minor | Current focus and M15 updated for the 10-10 merges | diff --git a/docs/target-app-parity.md b/docs/target-app-parity.md --- a/docs/target-app-parity.md +++ b/docs/target-app-parity.md @@ -1,6 +1,6 @@ # Target-app parity: GridCraft vs Microsoft Excel -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-22 · **Change:** minor (several windows: a native window per workbook window, New Window on the same workbook, Arrange All; no score change) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (`cargo xtask bench`: measured recalc baselines) · **Target:** Microsoft Excel (Microsoft 365) This is the authoritative parity assessment. [`ROADMAP.md`](../ROADMAP.md) summarizes it, [`gaps.md`](gaps.md) lists every shortfall, and the area checklists hold the detail: @@ -204,14 +204,26 @@ Excel recalculates on every core (multi-threaded recalculation), streams large f |---|---|---|---| | Sheet size limits | 1,048,576 × 16,384 | same | measured (`crates/core/src/addr.rs`) | | Recalc threads | 1 | all cores | measured (no threading in `crates/calc`) | -| Edit → recalc, 100k dependents | ~0.4 s (2026-10-07), target < 100 ms | tens of ms | measured on the owner's machine; #66 since made range-formula recalc linear, not re-timed | +| Edit → recalc, 100k-deep chain | 90–106 ms (target < 100 ms) | tens of ms | measured (`cargo xtask bench`, `edit.chain`) | +| Edit → recalc, 100k formulas reading one cell | 71–87 ms | tens of ms | measured (`edit.fanout`) | +| Edit → 100 whole-column SUMIFS / XLOOKUP over 100k rows | 420–590 ms / 160–250 ms | not measured | measured (`edit.sumifs`, `edit.lookup`) | +| Calculate Now, 300k formulas | 395–455 ms | not measured | measured (`recalc.full`) | +| Save the 100k-row model (300k formulas) as XLSX | ~530 ms | not measured | measured (`xlsx.save`) | +| Reopen that file | drops the last ~9,000 rows (element cap from #2413; fix in PR #2527, then ~1.3 s with full recalc) | opens | measured (`xlsx.open`) | | Grid scrolling | virtualised, O(log n) geometry with custom row heights | smooth | measured in code | | Open a ~100 MB XLSX | fails (#175) | opens | user report | | Whole-sheet operations | Fill and Remove Duplicates on a whole-sheet selection run out of memory or time (fix in PR #152) | fine | open PR | Work: streaming XLSX reader with shared-string and style dedup, multi-threaded recalc of independent chains (native only; WASM stays single-threaded), whole-column reference -clamping, a `cargo xtask bench` with fixed workbooks so the numbers become measured. +clamping, and faster whole-column SUMIFS and lookups (each formula rescans the column on every +edit). + +Benchmarks: `cargo xtask bench` builds ten fixed workbooks in code, times each operation through +`Session::execute` (median of 7 after a warm-up, results checked) and compares with a baseline +saved by `--save`. The numbers above are ranges over several runs on an Apple M2 Pro, release +build, 100,000 rows, 2026-10-26; recalculation is single-threaded, so core count doesn't change +them. ## Platforms @@ -327,13 +339,13 @@ The inventory carried over from the 2026-10-07 ROADMAP.md, updated for what land - Pixel comparison against Excel screenshots (planned; screenshots stay in `plan/excel/`). - Function results against Excel-computed oracles (a corpus repo is the planned home). -- Recalc and open-time benchmarks on fixed workbooks (no `cargo xtask bench` yet). - Whether Excel opens our XLSX output without repair, across features. ## Revision history | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Performance: `cargo xtask bench` measures recalc, entry, sort and XLSX save/open on fixed in-code workbooks; the 100k-dependent edit is 90–106 ms (was ~0.4 s, an old timing), around the 100 ms target for simple formulas; a 100k-row model doesn't reopen whole until PR #2527 lands (no score change) | | 2026-10-22 | minor | Several windows on the desktop: a native window per workbook window, New Window on the same workbook, Switch Windows and Arrange All; "one window" removed from UI/UX fidelity, Hardware and the interaction-fidelity discount; no score change | | 2026-10-21 | minor | Page Setup paper sizes: `B4`/`B5`/`B6` are the JIS sizes Japanese Excel uses (ISO B4/B5/B6 by name), and Japanese Postcard, Japanese Double Postcard, the chou/kaku envelopes and A6 are added; PDF MediaBox and `file.print` follow, and the Size menu lists them | | 2026-10-18 | trivial | Stale chart notes: all chart kinds survive XLSX round-trip (PRs #158, #161) and trendlines landed | diff --git a/xtask/Cargo.toml b/xtask/Cargo.toml --- a/xtask/Cargo.toml +++ b/xtask/Cargo.toml @@ -4,7 +4,7 @@ version.workspace = true edition.workspace = true license.workspace = true publish = false -description = "GridCraft workspace tooling: cargo xtask ci | layers | assets | wasm | parity | i18n-report | version | stats | ico" +description = "GridCraft workspace tooling: cargo xtask ci | layers | assets | wasm | parity | i18n-report | bench | version | stats | ico" [lints] workspace = true diff --git a/xtask/src/bench.rs b/xtask/src/bench.rs new file mode 100644 --- /dev/null +++ b/xtask/src/bench.rs @@ -0,0 +1,107 @@ +//! `cargo xtask bench`: runs the calculation benchmarks (`crates/engine/examples/bench.rs`) in a +//! release build, writes the results to `/bench/latest.json` and compares them with a +//! baseline (`/bench/baseline.json` unless `--baseline PATH`). `--save` makes this run +//! the baseline. Timings are machine-specific, so baselines stay in the target directory; the +//! reference numbers in docs/target-app-parity.md name the machine they came from. + +use std::path::{Path, PathBuf}; + +use serde_json::Value; + +/// Flags passed through to the bench binary (the others are xtask's own). +const PASS: [&str; 3] = ["--rows", "--reps", "--filter"]; + +pub fn run(root: &Path, args: &[&str]) -> Result<(), String> { + // Cargo resolves a relative CARGO_TARGET_DIR against the directory it runs in, which for the + // bench is the workspace root; resolve it the same way here. + let target = std::env::var_os("CARGO_TARGET_DIR").map(|d| root.join(d)).unwrap_or_else(|| root.join("target")); + let dir = target.join("bench"); + std::fs::create_dir_all(&dir).map_err(|e| format!("{}: {e}", dir.display()))?; + let latest = dir.join("latest.json"); + let mut baseline = dir.join("baseline.json"); + let mut save = false; + let mut pass: Vec = Vec::new(); + let mut it = args.iter(); + while let Some(&a) = it.next() { + match a { + "--save" => save = true, + "--quick" => pass.push(a.into()), + "--baseline" => baseline = it.next().map(PathBuf::from).ok_or("--baseline needs a path")?, + _ if PASS.contains(&a) => { + let v = it.next().ok_or_else(|| format!("{a} needs a value"))?; + pass.extend([a.to_string(), v.to_string()]); + } + other => return Err(format!("bench: unknown flag `{other}`")), + } + } + let mut cmd = crate::cargo(); + cmd.args(["run", "--release", "--quiet", "-p", "gridcraft-engine", "--example", "bench", "--"]).args(&pass).arg("--json").arg(&latest); + // A stale file from an earlier run must not pass for this one's results. + let _ = std::fs::remove_file(&latest); + // The bench exits non-zero when a scenario fails but still writes the others' results: + // compare those, then report the failure. + let status = crate::run(cmd, "cargo run --release -p gridcraft-engine --example bench"); + let now = match read(&latest) { + Ok(v) => v, + Err(e) => return status.and(Err(e)), + }; + if save && status.is_err() { + return status.map_err(|e| format!("{e}\nnot saved as the baseline: a scenario failed")); + } + if save { + std::fs::copy(&latest, &baseline).map_err(|e| format!("{}: {e}", baseline.display()))?; + println!("\nSaved as the baseline: {}", baseline.display()); + return Ok(()); + } + if baseline.exists() { + // A baseline that is there but unreadable is an error, not a missing one. + compare(&read(&baseline)?, &now); + } else { + println!("\nNo baseline at {} (run `cargo xtask bench --save` to make one).", baseline.display()); + } + status +} + +fn read(path: &Path) -> Result { + let text = std::fs::read_to_string(path).map_err(|e| format!("{}: {e}", path.display()))?; + serde_json::from_str(&text).map_err(|e| format!("{}: {e}", path.display())) +} + +fn results(v: &Value) -> Vec<(String, f64)> { + v.get("results") + .and_then(Value::as_array) + .map(|a| a.iter().filter_map(|r| Some((r.get("name")?.as_str()?.to_string(), r.get("median_ms")?.as_f64()?))).collect()) + .unwrap_or_default() +} + +/// Prints the median change per scenario; a different row count makes the numbers incomparable. +fn compare(base: &Value, now: &Value) { + if base.get("rows") != now.get("rows") { + println!("\nBaseline was run with {} rows, this run with {}: not compared.", base["rows"], now["rows"]); + return; + } + let old = results(base); + println!("\nAgainst the baseline (median):"); + for (name, ms) in results(now) { + match old.iter().find(|(n, _)| *n == name) { + Some((_, was)) if *was > 0.0 => { + let pct = (ms - was) / was * 100.0; + println!(" {name:<16} {was:>10.2} ms -> {ms:>10.2} ms {pct:+6.1}%"); + } + _ => println!(" {name:<16} {:>13} -> {ms:>10.2} ms", "new"), + } + } +} + +#[cfg(test)] +mod tests { + use super::*; + use serde_json::json; + + #[test] + fn reads_results() { + let v = json!({"rows": 10, "results": [{"name": "edit.chain", "median_ms": 1.5}, {"name": "bad"}]}); + assert_eq!(results(&v), vec![("edit.chain".to_string(), 1.5)]); + assert!(results(&json!({})).is_empty()); + } +} diff --git a/xtask/src/main.rs b/xtask/src/main.rs --- a/xtask/src/main.rs +++ b/xtask/src/main.rs @@ -6,6 +6,7 @@ #![cfg_attr(test, allow(clippy::unwrap_used, clippy::expect_used, clippy::panic))] mod assets; +mod bench; mod i18n_report; mod ico; mod keytips; @@ -36,6 +37,9 @@ commands: regenerate crates/ui-egui/src/keytips/generated.rs from keytips.json (--check: fail if stale) version [set X.Y.Z[-pre]] print or set the workspace version ([workspace.package] in Cargo.toml) + bench [--quick] [--rows N] [--reps N] [--filter NAME] [--save] [--baseline PATH] + release-build calculation benchmarks on fixed in-code workbooks; compares + with /bench/baseline.json (--save: make this run the baseline) stats [--exact] count tests and lines per crate (--exact: ask the test harness via `-- --list`) ico ... pack square PNGs (<= 256 px) into a Windows .ico (see packaging/icons.sh) @@ -51,6 +55,7 @@ fn main() -> ExitCode { Some("wasm") => cmd_wasm(), Some("test") => cmd_test().and_then(|()| cmd_doctest()), Some("ci") => cmd_ci(), + Some("bench") => bench::run(&root(), &rest), Some("stats") => stats::run(&root(), rest.contains(&"--exact")), Some("parity") => parity::run(&root(), &rest), Some("keytips") => keytips::run(&root(), &rest),