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 (Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel does) · **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 | Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel does: the row offset becomes the column offset and the other way round, so `=A3+1` copied from A3:A4 and pasted at C3 reads the cell to its left (`=C3+1`) instead of keeping the offset it had in the column; a reference pinned with `$` is left standing, a whole row or column comes back as a cell range open to the sheet's edge, and one off the grid is `#REF!` (#2450) | | 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`) | diff --git a/crates/engine/src/cmd/edit/paste_special.rs b/crates/engine/src/cmd/edit/paste_special.rs --- a/crates/engine/src/cmd/edit/paste_special.rs +++ b/crates/engine/src/cmd/edit/paste_special.rs @@ -119,8 +119,12 @@ pub(super) fn paste_special(s: &mut Session, p: &Json) -> Result { moved.dynamic = f.dynamic; return Some(std::sync::Arc::new(moved)); } - let moved = - gridcraft_formula::adjust::shift_relative(e, dest.row as i64 - sc.row as i64, dest.col as i64 - sc.col as i64); + let moved = if transpose { + // A transposed paste turns the references instead of shifting them (#2450). + gridcraft_formula::adjust::transpose_relative(e, sc, dest) + } else { + gridcraft_formula::adjust::shift_relative(e, dest.row as i64 - sc.row as i64, dest.col as i64 - sc.col as i64) + }; Some(std::sync::Arc::new(Formula { dynamic: f.dynamic, ..Formula::from_expr(moved) })) }; if link { diff --git a/crates/engine/src/cmd/edit/tests.rs b/crates/engine/src/cmd/edit/tests.rs --- a/crates/engine/src/cmd/edit/tests.rs +++ b/crates/engine/src/cmd/edit/tests.rs @@ -285,6 +285,39 @@ fn cut_paste_that_overflows_the_grid_moves_nothing() { assert_eq!(get(&mut s, "XFD2")["value"], json!(2.0)); } +#[test] +fn transposed_paste_turns_the_formula_with_the_cell_that_carries_it() { + // #2450: the values moved but the formula kept the offset it had in the source column, + // so it read the cell above instead of the cell to its left. + let mut s = Session::new(); + s.new_workbook(); + s.execute("cell.set", json!({"cell": "A3", "input": "2025-10-31"})).unwrap(); + s.execute("cell.set", json!({"cell": "A4", "input": "=A3+1"})).unwrap(); + s.execute("edit.copy", json!({"range": "A3:A4"})).unwrap(); + s.execute("edit.pasteSpecial", json!({"what": "all", "transpose": true, "at": "C3"})).unwrap(); + let get = |s: &mut Session, a: &str| s.execute("cell.get", json!({"cell": a})).unwrap(); + let date = get(&mut s, "C3")["value"].as_f64().expect("the date pastes as its serial"); + assert_eq!(date, get(&mut s, "A3")["value"].as_f64().unwrap()); + assert_eq!(get(&mut s, "D3")["formula"], "=C3+1", "the formula reads the cell to its left, as Excel's does"); + assert_eq!(get(&mut s, "D3")["value"].as_f64().unwrap(), date + 1.0, "and shows the day after it"); +} + +#[test] +fn transposed_paste_turns_every_kind_of_reference_the_way_excel_does() { + // Observed from Excel 16: relative parts turn a quarter turn, a reference pinned with $ + // is left standing, and a whole column comes back as a range open to the sheet's edge. + let mut s = Session::new(); + s.new_workbook(); + s.execute("range.setValues", json!({"range": "A1", "values": [["=B1+1"], ["=$B$1"], ["=SUM(B1:C3)"], ["=SUM(B:D)"]]})).unwrap(); + s.execute("edit.copy", json!({"range": "A1:A4"})).unwrap(); + s.execute("edit.pasteSpecial", json!({"what": "all", "transpose": true, "at": "C10"})).unwrap(); + let formula = |s: &mut Session, a: &str| s.execute("cell.get", json!({"cell": a})).unwrap()["formula"].clone(); + assert_eq!(formula(&mut s, "C10"), "=C11+1"); + assert_eq!(formula(&mut s, "D10"), "=$B$1"); + assert_eq!(formula(&mut s, "E10"), "=SUM(C11:E12)"); + assert_eq!(formula(&mut s, "F10"), "=SUM($C11:$XFD13)"); +} + #[test] fn replace_case_insensitive() { assert_eq!(replace_ci("Apple apple APPLE", "apple", "pear"), "pear pear pear"); diff --git a/crates/formula/src/adjust.rs b/crates/formula/src/adjust.rs --- a/crates/formula/src/adjust.rs +++ b/crates/formula/src/adjust.rs @@ -1,7 +1,7 @@ //! Reference adjustment: copying formulas, inserting/deleting rows and columns, moving cells, //! renaming and deleting sheets. -use gridcraft_core::{CellError, MAX_COLS, MAX_ROWS, RangeRef}; +use gridcraft_core::{CellError, CellRef, MAX_COLS, MAX_ROWS, RangeRef}; use crate::ast::{Anchor, Expr, RefKind, Reference, SheetSel, clamp_anchor}; @@ -36,6 +36,68 @@ pub fn shift_relative_wrapping(e: Expr, dr: i64, dc: i64) -> Expr { }) } +/// Paste Special ▸ Transpose: the cell holding the formula moves from `src` to `dest`, so every +/// reference turns a quarter turn with it — its row offset becomes its column offset and the other +/// way round, `(r, c)` → `dest + (c − src.col, r − src.row)`. +/// +/// As Excel writes it out: a reference that pins a row or a column with `$` is left exactly as it +/// stands, a whole-row or whole-column reference comes back as a cell range whose open side runs +/// from the transposed far edge of the sheet, and a result that falls off the grid becomes `#REF!`. +pub fn transpose_relative(e: Expr, src: CellRef, dest: CellRef) -> Expr { + e.map(&mut |x| match x { + Expr::Ref(r) if !has_absolute(&r) => match transpose_ref(&r, src, dest) { + Some(r) => Expr::Ref(r), + None => Expr::Error(CellError::Ref), + }, + other => other, + }) +} + +/// Whether any end of the reference pins a row or a column with `$`. +fn has_absolute(r: &Reference) -> bool { + let abs = |a: &Anchor| a.row_abs || a.col_abs; + match &r.kind { + RefKind::Cell(a) => abs(a), + RefKind::Range(a, b) => abs(a) || abs(b), + RefKind::Rows(_, a0, _, a1) | RefKind::Cols(_, a0, _, a1) => *a0 || *a1, + } +} + +/// Where an end lands after the turn: its column drives the new row, its row the new column. +fn transpose_anchor(a: &Anchor, src: CellRef, dest: CellRef) -> Option<(u32, u32)> { + clamp_anchor(dest.row as i64 + a.col as i64 - src.col as i64, dest.col as i64 + a.row as i64 - src.row as i64) +} + +/// Turns one reference with `src` → `dest`: `None` when an end falls outside the sheet, which +/// the caller turns into `#REF!`. +fn transpose_ref(r: &Reference, src: CellRef, dest: CellRef) -> Option { + let kind = match &r.kind { + RefKind::Cell(a) => { + let (row, col) = transpose_anchor(a, src, dest)?; + RefKind::Cell(Anchor { row, col, ..*a }) + } + RefKind::Range(a, b) => { + let (row, col) = transpose_anchor(a, src, dest)?; + let (row2, col2) = transpose_anchor(b, src, dest)?; + RefKind::Range(Anchor { row, col, ..*a }, Anchor { row: row2, col: col2, ..*b }) + } + // A whole row turns into a cell range: its rows become columns, while its open columns + // become rows pinned with `$` — from the transposed first column down to the last row. + RefKind::Rows(r0, _, r1, _) => { + let (row, col) = clamp_anchor(dest.row as i64 - src.col as i64, dest.col as i64 + *r0 as i64 - src.row as i64)?; + let (row2, col2) = clamp_anchor(MAX_ROWS as i64 - 1, dest.col as i64 + *r1 as i64 - src.row as i64)?; + RefKind::Range(Anchor { row, col, row_abs: true, col_abs: false }, Anchor { row: row2, col: col2, row_abs: true, col_abs: false }) + } + // A whole column likewise: its columns become rows, its open rows become columns up to XFD. + RefKind::Cols(c0, _, c1, _) => { + let (row, col) = clamp_anchor(dest.row as i64 + *c0 as i64 - src.col as i64, dest.col as i64 - src.row as i64)?; + let (row2, col2) = clamp_anchor(dest.row as i64 + *c1 as i64 - src.col as i64, MAX_COLS as i64 - 1)?; + RefKind::Range(Anchor { row, col, row_abs: false, col_abs: true }, Anchor { row: row2, col: col2, row_abs: false, col_abs: true }) + } + }; + Some(Reference { sheet: r.sheet.clone(), kind }) +} + /// Whether the formula has a relative (no `$`) row or column anywhere. pub fn has_relative_refs(e: &Expr) -> bool { e.references().iter().any(|r| match r.kind { diff --git a/crates/formula/src/tests.rs b/crates/formula/src/tests.rs --- a/crates/formula/src/tests.rs +++ b/crates/formula/src/tests.rs @@ -1,5 +1,5 @@ use super::*; -use crate::adjust::{Axis, Edit, adjust, rename_sheet, shift_relative}; +use crate::adjust::{Axis, Edit, adjust, rename_sheet, shift_relative, transpose_relative}; fn rt(src: &str) -> String { print(&parse(src).unwrap_or_else(|e| panic!("{src}: {e}"))) @@ -165,6 +165,27 @@ fn copy_shift() { assert_eq!(print(&shift_relative(e, 5, 1)), "SUM(B:B)"); } +#[test] +fn copy_transpose() { + let at = gridcraft_core::CellRef::new; + // The row of each reference drives its new column and the other way round (Excel, observed: + // A1 → F6 carrying =B1+1 comes out as =F7+1). + let e = parse("B1+SUM(B1:C3)+Sheet2!A1").unwrap(); + assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "F7+SUM(F7:H8)+Sheet2!F6"); + // The turn is around the two positions of the formula's cell, not around A1. + let e = parse("G3+SUM(1:2)").unwrap(); + assert_eq!(print(&transpose_relative(e, at(1, 2), at(9, 5))), "G14+SUM(E$8:F$1048576)"); + // A reference pinned with `$` is left exactly as it stands. + let e = parse("$B$1+B$1+$B1+SUM($B$1:$C$3)").unwrap(); + assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "$B$1+B$1+$B1+SUM($B$1:$C$3)"); + // Whole rows and columns come back as a cell range open to the sheet's edge. + let e = parse("SUM(1:2)+SUM(B:D)").unwrap(); + assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "SUM(F$6:G$1048576)+SUM($F7:$XFD9)"); + // Off the grid is #REF!, like a copy that runs past the edge. + let e = parse("A16384+B1").unwrap(); + assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "#REF!+F7"); +} + #[test] fn insert_delete() { let ins = Edit::Insert { axis: Axis::Rows, at: 2, count: 2 }; diff --git a/docs/ui-parity.md b/docs/ui-parity.md --- a/docs/ui-parity.md +++ b/docs/ui-parity.md @@ -1,6 +1,6 @@ # UI parity -> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 +> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-26 · **Change:** minor (Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel does) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Interaction, controls and feel, against Excel. Part of [target-app-parity.md](target-app-parity.md). **UI/UX fidelity: ~55% ready, 50–80 h** (estimated). Ribbon command coverage is measured @@ -187,6 +187,7 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel's does (#2450): the row offset becomes the column offset and the other way round, so `=A3+1` copied from A3:A4 and pasted at C3 lands as `=C3+1` and shows the day after the cell to its left, instead of keeping the offset it had in the column and reading the cell above. A reference pinned with `$` is left exactly as Excel leaves it, a whole row or column comes back as a cell range open to the sheet's edge, and one that lands off the grid is `#REF!` | | 2026-10-25 | minor | AutoSum (Alt+=, Home › AutoSum) over a selected row puts its total to the right of the row, or in its last cell when that is empty, as in Excel; it wrote a one-cell total under each cell. A selection's empty last column takes each row's total and its empty last row each column's, with the block's total in the corner when both are empty | | 2026-10-25 | minor | Paste Name lays out only the names in view, so a workbook with thousands of names opens it at once; the arrow keys still scroll the chosen name into view. Go To matches a `Sheet2!Name` reference and hides a workbook name behind the active sheet's own name in any case and script (`Größe` = `GRÖSSE`), as formulas do. The two Page Layout view rows are one; the 0.7 in side margin used when Align with Page Margins is off is marked unverified | | 2026-10-24 | minor | Go To lists the named ranges on every sheet, as Excel's does ("The Go to popup window shows named ranges on every worksheet in your workbook"): a name scoped to another sheet appears as `Sheet2!Name`, and hidden names, constants and formulas are left out. It keeps the references entered (newest first, ten at most), and a double-click on an entry goes there. The Reference box and `edit.goTo` take several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30,H21:H29`) and select them all; `edit.goTo {list: true}` returns the list |