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 (pictures, shapes and charts keep their place, size or follow their cells as their placement says when rows and columns are resized, hidden, inserted or deleted) · **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 | Pictures, shapes and charts follow their cells as their placement says (#2343): "Don't move or size with cells" stays put, "Move but don't size" keeps its size, "Move and size" stretches, when rows or columns are resized, hidden, filtered, inserted or deleted; a deleted anchor line drops the offset into it. Numbers checked against Excel through COM | | 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: object placement under row and column changes (#2343) | | 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/src/cmd/sheet.rs b/crates/engine/src/cmd/sheet.rs --- a/crates/engine/src/cmd/sheet.rs +++ b/crates/engine/src/cmd/sheet.rs @@ -1,10 +1,11 @@ //! Worksheets and structure: insert/delete rows, columns and cells; sheet tabs. +use std::collections::HashMap; use std::sync::Arc; use gridcraft_core::{CellRef, MAX_COLS, MAX_ROWS, RangeRef}; use gridcraft_formula::adjust::{Axis, Edit}; -use gridcraft_model::{Anchor, AnchorMode, Sheet, Visibility}; +use gridcraft_model::{Anchor, AnchorMode, Sheet, Visibility, Workbook}; use serde_json::{Value as Json, json}; use super::{ @@ -95,8 +96,14 @@ fn shift_anchor(sh: &Sheet, a: Anchor, corner: (u32, u32, f32, f32), axis: Axis, return a; } let main = if axis == Axis::Rows { a.cell.row } else { a.cell.col }; - // A deleted anchor line collapses onto the line that takes its place. + // A deleted anchor line collapses onto the line that takes its place, and Excel drops the + // offset into it: the object's edge then sits on that line. let nmain = map_line(main).unwrap_or(at); + let a = match (map_line(main), axis) { + (None, Axis::Rows) => Anchor { dy: 0.0, ..a }, + (None, Axis::Cols) => Anchor { dx: 0.0, ..a }, + _ => a, + }; let (nr, nc) = if axis == Axis::Rows { (nmain, a.cell.col) } else { (a.cell.row, nmain) }; if a.mode == AnchorMode::MoveOnly { return Anchor { cell: CellRef::new(nr, nc), ..a }; @@ -114,6 +121,94 @@ fn shift_anchor(sh: &Sheet, a: Anchor, corner: (u32, u32, f32, f32), axis: Axis, Anchor { cell: CellRef::new(nr, nc), dx: a.dx, dy: a.dy, width: (xn - x1).max(0.0) as f32, height: (yn - y1).max(0.0) as f32, mode: a.mode } } +/// Where an object's anchor lands after a command changed row heights, column widths or hidden +/// lines (`old` and `new` are the sheet before and after), as Excel places it: "move and size" +/// keeps its top-left corner on its cell and follows its bottom-right corner (hiding a line shrinks +/// it); "move but don't size" keeps its cell and size; "don't move or size" keeps its place on the +/// sheet, so its anchor cell is found again in the new grid. An anchor the command itself changed +/// (a move, a resize, an insert or delete that already re-fitted it) is left alone. +fn refit_anchor(old: &Sheet, new: &Sheet, was: &Anchor, now: &Anchor) -> Anchor { + if was != now { + return *now; + } + match now.mode { + AnchorMode::MoveOnly => *now, + AnchorMode::Absolute => { + let x = old.col_left(was.cell.col) + was.dx as f64; + let y = old.row_top(was.cell.row) + was.dy as f64; + let (col, row) = (new.col_at(x), new.row_at(y)); + let (dx, dy) = ((x - new.col_left(col)).max(0.0) as f32, (y - new.row_top(row)).max(0.0) as f32); + Anchor { cell: CellRef::new(row, col), dx, dy, ..*now } + } + AnchorMode::MoveAndSize => { + // A hidden first line has no room for the offset into it: Excel measures from its edge, + // in the old grid (where the corner is found) as in the new one. + let at = Anchor { + dx: (was.dx as f64).min(old.col_width(was.cell.col) as f64) as f32, + dy: (was.dy as f64).min(old.row_height(was.cell.row) as f64) as f32, + ..*was + }; + let (tc, tr, tdx, tdy) = anchor_corner(old, &at); + let x1 = new.col_left(now.cell.col) + (now.dx as f64).min(new.col_width(now.cell.col) as f64); + let y1 = new.row_top(now.cell.row) + (now.dy as f64).min(new.row_height(now.cell.row) as f64); + let width = (new.col_left(tc) + tdx as f64 - x1).max(0.0) as f32; + let height = (new.row_top(tr) + tdy as f64 - y1).max(0.0) as f32; + Anchor { width, height, ..*now } + } + } +} + +/// Re-fits every floating object of `after` to the grid its command left, honouring each object's +/// [`AnchorMode`] (see [`refit_anchor`]). `before` is the workbook as the command found it; sheets +/// are matched by position and name, and objects by id. Does nothing, and copies nothing, when no +/// object's anchor changes. +pub(crate) fn refit_objects(before: &Workbook, after: &mut Arc) { + let mut fixes: Vec<(usize, Vec, Vec, Vec)> = Vec::new(); + for (i, (old, new)) in before.sheets.iter().zip(after.sheets.iter()).enumerate() { + // Most commands leave most sheets alone, and most sheets have no objects. + if Arc::ptr_eq(old, new) || (old.charts.is_empty() && old.images.is_empty() && old.shapes.is_empty()) { + continue; + } + let same_grid = old.rows == new.rows + && old.cols == new.cols + && old.default_row_height == new.default_row_height + && old.default_col_width == new.default_col_width; + if same_grid || old.name != new.name { + continue; + } + // Prior anchors by object id, so a sheet of many objects is matched in one pass. + let old_charts: HashMap = old.charts.iter().map(|o| (o.id, &o.anchor)).collect(); + let old_images: HashMap = old.images.iter().map(|o| (o.id, &o.anchor)).collect(); + let old_shapes: HashMap = old.shapes.iter().map(|o| (o.id, &o.anchor)).collect(); + let fit = |olds: &HashMap, id: u32, now: &Anchor| olds.get(&id).map_or(*now, |was| refit_anchor(old, new, was, now)); + let charts: Vec = new.charts.iter().map(|n| fit(&old_charts, n.id, &n.anchor)).collect(); + let images: Vec = new.images.iter().map(|n| fit(&old_images, n.id, &n.anchor)).collect(); + let shapes: Vec = new.shapes.iter().map(|n| fit(&old_shapes, n.id, &n.anchor)).collect(); + let unchanged = new.charts.iter().map(|o| &o.anchor).eq(charts.iter()) + && new.images.iter().map(|o| &o.anchor).eq(images.iter()) + && new.shapes.iter().map(|o| &o.anchor).eq(shapes.iter()); + if !unchanged { + fixes.push((i, charts, images, shapes)); + } + } + if fixes.is_empty() { + return; + } + let wb = Arc::make_mut(after); + for (i, charts, images, shapes) in fixes { + let Some(sh) = wb.sheet_mut(i) else { continue }; + for (o, a) in sh.charts.iter_mut().zip(charts) { + o.anchor = a; + } + for (o, a) in sh.images.iter_mut().zip(images) { + o.anchor = a; + } + for (o, a) in sh.shapes.iter_mut().zip(shapes) { + o.anchor = a; + } + } +} + /// Shifts everything that has a position on the sheet for an insert/delete. fn shift_sheet_features(sh: &mut Sheet, axis: Axis, at: u32, count: u32, insert: bool) { let map_line = |v: u32| -> Option { diff --git a/crates/engine/src/lib.rs b/crates/engine/src/lib.rs --- a/crates/engine/src/lib.rs +++ b/crates/engine/src/lib.rs @@ -699,6 +699,17 @@ impl Session { // it leaves the group its step and makes one of its own for what it changed after the end. // A command that opened and closed its own groups makes one step of its own, which // replaces the steps those groups made. + // Floating objects keep the place their "Move and size with cells" setting gives them + // when the command resized, hid or moved the rows and columns around them. + if result.is_ok() + && spec.undoable + && self.depth == 0 + && let Some((uid, wb, _)) = &before + && let Some(d) = self.docs.iter_mut().find(|d| d.uid == *uid) + && !Arc::ptr_eq(&d.wb, wb) + { + cmd::sheet::refit_objects(wb, &mut d.wb); + } let epoch_changed = self.group_epoch != group_epoch; let in_group_now = before.as_ref().is_some_and(|(uid, ..)| self.in_group(*uid)); let was_open = start.is_some_and(|(was_open, _)| was_open); diff --git a/crates/engine/tests/it/main.rs b/crates/engine/tests/it/main.rs --- a/crates/engine/tests/it/main.rs +++ b/crates/engine/tests/it/main.rs @@ -17,6 +17,7 @@ mod hidden_sheet_navigation; mod ink_roundtrip; mod line_count_overflow; mod long_operator_chains; +mod object_placement; mod offset_overflow; mod percent_entry; mod pivot_unknown_func; diff --git a/crates/engine/tests/it/object_placement.rs b/crates/engine/tests/it/object_placement.rs new file mode 100644 --- /dev/null +++ b/crates/engine/tests/it/object_placement.rs @@ -0,0 +1,247 @@ +//! Pictures, shapes and charts follow their cells as their placement says ("Move and size with +//! cells", "Move but don't size with cells", "Don't move or size with cells") when rows and columns +//! are resized, hidden, inserted or deleted under them. The expected numbers (points) were measured +//! in Excel for Microsoft 365 through COM: one object of each kind per placement, from C5 (offset 3 +//! pt right, 4 pt down) over 144 x 57.6 pt, on a sheet with 14.4 pt rows and 48 pt columns. + +use gridcraft_engine::Session; +use gridcraft_model::AnchorMode; +use serde_json::json; + +/// A 1 x 1 PNG. +const PNG: &str = "iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNkYPhfDwAChwGA60e6kgAAAABJRU5ErkJggg=="; +/// The model sizes lines and objects in pixels at 96 dpi; Excel reports points. +const PT: f64 = 0.75; + +#[derive(Clone, Copy)] +enum Kind { + Picture, + Shape, + Chart, +} + +/// One object of each kind in each placement, stacked at the same spot. +struct Fixture { + s: Session, + objects: Vec<(Kind, u32, AnchorMode)>, +} + +const MODES: [AnchorMode; 3] = [AnchorMode::MoveAndSize, AnchorMode::MoveOnly, AnchorMode::Absolute]; + +/// The `mode` parameter value for a placement. +fn mode_name(m: AnchorMode) -> &'static str { + match m { + AnchorMode::MoveAndSize => "moveAndSize", + AnchorMode::MoveOnly => "moveOnly", + AnchorMode::Absolute => "absolute", + } +} + +/// A sheet with Excel's measured row and column sizes and the nine objects. +fn fixture() -> Fixture { + let mut s = Session::new(); + s.new_workbook(); + { + let wb = std::sync::Arc::make_mut(&mut s.doc_mut().unwrap().wb); + let sh = wb.sheet_mut(0).unwrap(); + sh.default_row_height = (14.4 / PT) as f32; + sh.default_col_width = (48.0 / PT) as f32; + } + s.execute("range.setValues", json!({"range": "A1", "values": [["h", 1], ["x", 2], ["y", 3]]})).unwrap(); + let (w, h) = (144.0 / PT, 57.6 / PT); + let mut objects = Vec::new(); + for mode in MODES { + let m = mode_name(mode); + let r = s.execute("insert.picture", json!({"base64": PNG, "at": "C5", "width": w, "height": h, "mode": m})).unwrap(); + objects.push((Kind::Picture, r["image"].as_u64().unwrap() as u32, mode)); + let r = s.execute("insert.shape", json!({"kind": "rectangle", "at": "C5", "width": w, "height": h, "mode": m})).unwrap(); + objects.push((Kind::Shape, r["shape"].as_u64().unwrap() as u32, mode)); + let r = s.execute("insert.chart", json!({"range": "A1:B3", "at": "C5", "width": w, "height": h, "mode": m})).unwrap(); + objects.push((Kind::Chart, r["chart"].as_u64().unwrap() as u32, mode)); + } + for &(kind, id, _) in &objects { + let kind = match kind { + Kind::Picture => "image", + Kind::Shape => "shape", + Kind::Chart => "chart", + }; + s.execute("object.move", json!({"kind": kind, "id": id, "dx": 3.0 / PT, "dy": 4.0 / PT})).unwrap(); + } + Fixture { s, objects } +} + +impl Fixture { + /// Left, top, width, height in points, as Excel reports them. + fn rect(&self, kind: Kind, id: u32) -> [f64; 4] { + let sh = self.s.doc().unwrap().wb.sheet(0).unwrap(); + let a = match kind { + Kind::Picture => sh.images.iter().find(|o| o.id == id).unwrap().anchor, + Kind::Shape => sh.shapes.iter().find(|o| o.id == id).unwrap().anchor, + Kind::Chart => sh.charts.iter().find(|o| o.id == id).unwrap().anchor, + }; + [(sh.col_left(a.cell.col) + a.dx as f64) * PT, (sh.row_top(a.cell.row) + a.dy as f64) * PT, a.width as f64 * PT, a.height as f64 * PT] + } + + /// Runs `action`, then checks every object against the rectangle Excel gave its placement. + fn expect(&mut self, what: &str, action: impl FnOnce(&mut Session), moves_and_sizes: [f64; 4], moves: [f64; 4], free: [f64; 4]) { + action(&mut self.s); + for &(kind, id, mode) in &self.objects { + let want = match mode { + AnchorMode::MoveAndSize => moves_and_sizes, + AnchorMode::MoveOnly => moves, + AnchorMode::Absolute => free, + }; + let got = self.rect(kind, id); + for (g, w) in got.iter().zip(want) { + assert!((g - w).abs() < 0.01, "{what}: {} {:?} is {got:?}, Excel gives {want:?}", mode_name(mode), id); + } + } + } +} + +const HOME: [f64; 4] = [99.0, 61.6, 144.0, 57.6]; + +/// Runs a row command on `rows`. +fn rows(s: &mut Session, id: &str, rows: &str) { + s.execute(id, json!({"rows": rows})).unwrap(); +} + +/// Runs a column command on `cols`. +fn cols(s: &mut Session, id: &str, cols: &str) { + s.execute(id, json!({"cols": cols})).unwrap(); +} + +#[test] +/// Row resize above inside and below. +fn row_resize_above_inside_and_below() { + let height = |s: &mut Session, r: &str, pt: f64| s.execute("home.rowHeight", json!({"rows": r, "points": pt})).unwrap(); + // Row 2 to 60 pt: everything but "don't move or size" slides down by 45.6 pt. + let down = [99.0, 107.2, 144.0, 57.6]; + fixture().expect("row 2 to 60 pt", |s| drop(height(s, "2", 60.0)), down, down, HOME); + // Rows 5 (the object's first), 6 and 8 inside it: only "move and size" stretches. + let tall = [99.0, 61.6, 144.0, 103.2]; + for r in ["5", "6", "8"] { + fixture().expect(&format!("row {r} to 60 pt"), |s| drop(height(s, r, 60.0)), tall, HOME, HOME); + } + // Row 12 is below it. + fixture().expect("row 12 to 60 pt", |s| drop(height(s, "12", 60.0)), HOME, HOME, HOME); +} + +#[test] +/// Column resize left inside and right. +fn column_resize_left_inside_and_right() { + // 221.6 px is 166.2 pt, Excel's width for 30 characters on the machine it was measured on. + let width = |s: &mut Session, c: &str| s.execute("home.columnWidth", json!({"cols": c, "width": 166.2 / PT})).unwrap(); + let right = [217.2, 61.6, 144.0, 57.6]; + fixture().expect("column A", |s| drop(width(s, "A")), right, right, HOME); + let wide = [99.0, 61.6, 262.2, 57.6]; + fixture().expect("column D", |s| drop(width(s, "D")), wide, HOME, HOME); + fixture().expect("column H", |s| drop(width(s, "H")), HOME, HOME, HOME); +} + +#[test] +/// Row insert and delete. +fn row_insert_and_delete() { + let down = [99.0, 76.0, 144.0, 57.6]; + fixture().expect("insert row 2", |s| rows(s, "home.insertRows", "2"), down, down, HOME); + fixture().expect("insert row 5", |s| rows(s, "home.insertRows", "5"), down, down, HOME); + let tall = [99.0, 61.6, 144.0, 72.0]; + fixture().expect("insert row 6", |s| rows(s, "home.insertRows", "6"), tall, HOME, HOME); + // Row 9 holds the object's bottom edge. + fixture().expect("insert row 9", |s| rows(s, "home.insertRows", "9"), tall, HOME, HOME); + fixture().expect("insert row 12", |s| rows(s, "home.insertRows", "12"), HOME, HOME, HOME); + let up = [99.0, 47.2, 144.0, 57.6]; + fixture().expect("delete row 2", |s| rows(s, "home.deleteRows", "2"), up, up, HOME); + fixture().expect("delete row 12", |s| rows(s, "home.deleteRows", "12"), HOME, HOME, HOME); + fixture().expect("delete row 6", |s| rows(s, "home.deleteRows", "6"), [99.0, 61.6, 144.0, 43.2], HOME, HOME); + // The rows under the object's top edge go: the edge sits on the line that takes their place. + fixture().expect("delete rows 5:8", |s| rows(s, "home.deleteRows", "5:8"), [99.0, 57.6, 144.0, 4.0], [99.0, 57.6, 144.0, 57.6], HOME); + fixture().expect("delete rows 4:6", |s| rows(s, "home.deleteRows", "4:6"), [99.0, 43.2, 144.0, 32.8], [99.0, 43.2, 144.0, 57.6], HOME); +} + +#[test] +/// Column insert and delete. +fn column_insert_and_delete() { + let right = [147.0, 61.6, 144.0, 57.6]; + fixture().expect("insert column B", |s| cols(s, "home.insertColumns", "B"), right, right, HOME); + fixture().expect("insert column D", |s| cols(s, "home.insertColumns", "D"), [99.0, 61.6, 192.0, 57.6], HOME, HOME); + let left = [51.0, 61.6, 144.0, 57.6]; + fixture().expect("delete column B", |s| cols(s, "home.deleteColumns", "B"), left, left, HOME); + fixture().expect("delete column D", |s| cols(s, "home.deleteColumns", "D"), [99.0, 61.6, 96.0, 57.6], HOME, HOME); + fixture().expect("delete columns C:E", |s| cols(s, "home.deleteColumns", "C:E"), [96.0, 61.6, 3.0, 57.6], [96.0, 61.6, 144.0, 57.6], HOME); +} + +#[test] +/// Hiding rows and columns. +fn hiding_rows_and_columns() { + let up = [99.0, 47.2, 144.0, 57.6]; + fixture().expect("hide row 2", |s| rows(s, "home.hideRows", "2"), up, up, HOME); + fixture().expect("hide row 6", |s| rows(s, "home.hideRows", "6"), [99.0, 61.6, 144.0, 43.2], HOME, HOME); + let left = [51.0, 61.6, 144.0, 57.6]; + fixture().expect("hide column B", |s| cols(s, "home.hideColumns", "B"), left, left, HOME); + fixture().expect("hide column D", |s| cols(s, "home.hideColumns", "D"), [99.0, 61.6, 96.0, 57.6], HOME, HOME); + // Unhiding gives everything back, whichever placement. + for (hide, show, r) in [("home.hideRows", "home.unhideRows", "2:2"), ("home.hideRows", "home.unhideRows", "5:8")] { + fixture().expect( + &format!("hide and unhide rows {r}"), + |s| { + rows(s, hide, r); + rows(s, show, r); + }, + HOME, + HOME, + HOME, + ); + } +} + +#[test] +/// Hiding the rows an object spans. +fn hiding_the_rows_an_object_spans() { + // Excel measures a hidden top row from its edge, so the object keeps what is below it: 4 pt. + // (It also draws the object's top at the row's edge, 57.6 pt; GridCraft keeps the 3 pt/4 pt + // offset in the file and draws the top 4 pt lower.) + let mut f = fixture(); + rows(&mut f.s, "home.hideRows", "5:8"); + for &(kind, id, mode) in &f.objects { + let [_, _, _, h] = f.rect(kind, id); + let want = if mode == AnchorMode::MoveAndSize { 4.0 } else { 57.6 }; + assert!((h - want).abs() < 0.01, "{} height {h}", mode_name(mode)); + } + let mut f = fixture(); + rows(&mut f.s, "home.hideRows", "5"); + for &(kind, id, mode) in &f.objects { + let [_, _, _, h] = f.rect(kind, id); + let want = if mode == AnchorMode::MoveAndSize { 47.2 } else { 57.6 }; + assert!((h - want).abs() < 0.01, "{} height {h}", mode_name(mode)); + } +} + +#[test] +/// An applied filter hides rows like hiding them. +fn an_applied_filter_hides_rows_like_hiding_them() { + // Rows 2 and 3 hold "x" and "y": keeping "y" hides row 2, above the object. + let up = [99.0, 47.2, 144.0, 57.6]; + fixture().expect( + "filter hides row 2", + |s| { + s.execute("data.filter", json!({"range": "A1:A3"})).unwrap(); + s.execute("data.filterBy", json!({"column": "A", "values": ["y"]})).unwrap(); + assert!(s.doc().unwrap().wb.sheet(0).unwrap().is_row_hidden(1)); + }, + up, + up, + HOME, + ); +} + +#[test] +/// Undo puts objects back. +fn undo_puts_objects_back() { + let mut f = fixture(); + let before: Vec<[f64; 4]> = f.objects.iter().map(|&(k, id, _)| f.rect(k, id)).collect(); + f.s.execute("home.rowHeight", json!({"rows": "2", "points": 60.0})).unwrap(); + f.s.execute("edit.undo", json!({})).unwrap(); + let after: Vec<[f64; 4]> = f.objects.iter().map(|&(k, id, _)| f.rect(k, id)).collect(); + assert_eq!(before, after); +}