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-25 · **Change:** minor (Clear Formats removes conditional formatting; 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) 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-25 | Clear Formats and Clear All remove conditional formatting from the cleared cells, as in Excel, keeping the rest of each rule's range; rules on empty cells past the used range go too. When a cut removes a rule's first cell, its relative formulas follow the new anchor, so the remaining cells still test themselves (#2536) | | 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`) | @@ -237,6 +238,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | Change | Summary | |---|---|---| +| 2026-10-25 | minor | Progress log: Clear Formats removes conditional formatting (#2536) | | 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/data.rs b/crates/engine/src/cmd/data.rs --- a/crates/engine/src/cmd/data.rs +++ b/crates/engine/src/cmd/data.rs @@ -1066,6 +1066,56 @@ pub(super) fn remove_validation_cells(validations: &mut Vec, cuts: & validations.retain(|d| !d.ranges.is_empty()); } +/// The most ranges a clear grows a conditional format to. Each cut can add three, so a selection of +/// many small parts would otherwise grow a rule (and every lookup of it) without bound; Go To +/// Special's largest selection (2,000 areas) stays well under it. +const MAX_CF_RANGES: usize = 16_384; + +/// Cuts the cells of `cuts` (largest first) out of a rule's `ranges`. Rule formulas are relative to +/// the top-left of the first range: the piece that keeps that cell stays first, and when the cell +/// itself is cut this returns the anchor's move for the caller to rebase them. A cut that would grow +/// the rule past [`MAX_CF_RANGES`] (and past its own size) is refused. +fn cut_rule_ranges(cmd: &str, ranges: &mut Vec, cuts: &[RangeRef]) -> Result> { + let before = ranges.first().map(|r| r.start); + let limit = MAX_CF_RANGES.max(ranges.len()); + for cut in cuts { + if !ranges.iter().any(|r| r.intersects(cut)) { + continue; + } + *ranges = ranges.iter().flat_map(|r| subtract_range(*r, cut)).collect(); + if ranges.len() > limit { + return Err(bad(cmd, format!("the selection would split a conditional format into more than {limit} ranges"))); + } + } + if let Some(i) = before.and_then(|a| ranges.iter().position(|r| r.start == a)) + && let Some(head) = ranges.get_mut(..=i) + { + head.rotate_right(1); + } + Ok(before.zip(ranges.first().map(|r| r.start)).filter(|(a, b)| a != b)) +} + +/// Removes the cells of `cuts` from every conditional format, keeping the rest of each rule's ranges. +pub(super) fn remove_cond_format_cells(cmd: &str, cond_formats: &mut Vec, cuts: &[RangeRef]) -> Result<()> { + // Largest first, so a later small cut finds less to split. + let mut cuts = cuts.to_vec(); + cuts.sort_by_key(|r| std::cmp::Reverse(r.count())); + for cf in cond_formats.iter_mut() { + if let Some((from, to)) = cut_rule_ranges(cmd, &mut cf.ranges, &cuts)? { + match &mut cf.rule { + CfRule::Expression { formula, .. } => *formula = move_rule_formula(formula, from, to), + CfRule::CellIs { a, b, .. } => { + *a = move_rule_formula(a, from, to); + *b = b.as_deref().map(|f| move_rule_formula(f, from, to)); + } + _ => {} + } + } + } + cond_formats.retain(|c| !c.ranges.is_empty()); + Ok(()) +} + fn validation(s: &mut Session, p: &Json) -> Result { let ranges = target_ranges(s, p)?; let sheet = target_sheet(s, p)?; diff --git a/crates/engine/src/cmd/edit.rs b/crates/engine/src/cmd/edit.rs --- a/crates/engine/src/cmd/edit.rs +++ b/crates/engine/src/cmd/edit.rs @@ -1539,6 +1539,12 @@ fn clear(s: &mut Session, p: &Json, what: &str) -> Result { } } } + // Conditional formats are formats too; they can cover empty cells past the used range, so + // they are cut by the whole selection, keeping the rest of each rule's ranges (#2536). + if matches!(what, "all" | "formats") { + let cmd = if what == "all" { "edit.clearAll" } else { "edit.clearFormats" }; + super::data::remove_cond_format_cells(cmd, &mut cx.sheet_mut(sheet)?.cond_formats, &ranges)?; + } Ok(Json::Null) }) } diff --git a/crates/engine/src/tests.rs b/crates/engine/src/tests.rs --- a/crates/engine/src/tests.rs +++ b/crates/engine/src/tests.rs @@ -4062,6 +4062,97 @@ fn vertical_alignment_commands_and_roundtrip() { assert_eq!(reopen_style("A3").align.v, gridcraft_model::VAlign::Bottom); } +/// Clear Formats and Clear All remove conditional formatting from the cleared cells only: the rest +/// of each rule's ranges stays, a rule on empty cells past the used range goes, and Undo brings +/// the rules back (#2536). +#[test] +fn clear_formats_removes_conditional_formatting_from_the_cleared_cells() { + let mut s = s(); + let ranges = |s: &Session| -> Vec> { + s.doc().unwrap().wb.active().unwrap().cond_formats.iter().map(|c| c.ranges.iter().map(|r| r.a1()).collect()).collect() + }; + s.execute("range.setValues", json!({"range": "A1", "values": [[1], [2], [3], [4], [5]]})).unwrap(); + s.execute("home.conditionalFormat", json!({"range": "A1:A5", "rule": {"type": "iconSet", "set": "3Arrows"}})).unwrap(); + s.execute("home.conditionalFormat", json!({"range": "D10:D12", "rule": {"type": "iconSet", "set": "3Arrows"}})).unwrap(); + + s.execute("edit.clearFormats", json!({"range": "A2:A3"})).unwrap(); + // The newer rule comes first (it has priority). + assert_eq!(ranges(&s), [vec!["D10:D12"], vec!["A1", "A4:A5"]], "the cleared cells leave the rule; the rest keeps it"); + s.execute("edit.clearFormats", json!({"range": "D10:D12"})).unwrap(); + assert_eq!(ranges(&s), [vec!["A1", "A4:A5"]], "a rule on empty cells past the used range goes too"); + s.execute("edit.undo", json!({})).unwrap(); + assert_eq!(ranges(&s), [vec!["D10:D12"], vec!["A1", "A4:A5"]]); + + s.execute("edit.clearAll", json!({"range": "A1:A5"})).unwrap(); + assert_eq!(ranges(&s), [vec!["D10:D12"]], "Clear All clears formats, conditional ones included"); + s.execute("edit.clearContents", json!({"range": "D10:D12"})).unwrap(); + assert_eq!(ranges(&s), [vec!["D10:D12"]], "Clear Contents keeps them"); +} + +/// Rule formulas are relative to the top-left cell of the rule's first range: when a clear cuts that +/// cell out, the formulas follow the new anchor, so every remaining cell still tests itself (#2536). +#[test] +fn clearing_a_rules_first_cell_keeps_its_relative_formulas_on_the_same_cells() { + let mut s = s(); + s.execute("range.setValues", json!({"range": "A1", "values": [[5, 5, 5], [1, 5, 1], [5, 1, 5], [1, 1, 1], [5, 5, 5]]})).unwrap(); + let red = json!({"fill": "#FF0000"}); + s.execute("home.conditionalFormat", json!({"range": "A1:A5", "rule": {"type": "expression", "formula": "=A1>2", "style": red}})).unwrap(); + s.execute("home.conditionalFormat", json!({"range": "B1:C3", "rule": {"type": "cellIs", "operator": "greater", "value": "=B1-2", "style": red}})) + .unwrap(); + let lit = |s: &Session, a: &str| { + let wb = &s.doc().unwrap().wb; + crate::cf::CfCache::new().look(wb, 0, CellRef::parse(a).unwrap()).is_some_and(|l| l.style.is_some()) + }; + s.execute("edit.clearFormats", json!({"range": "A1"})).unwrap(); + assert_eq!([lit(&s, "A2"), lit(&s, "A3"), lit(&s, "A4"), lit(&s, "A5")], [false, true, false, true], "A2:A5 still test themselves"); + // A 2-D cut through the top-left corner: B1 goes, C1 and B2:C3 keep comparing with themselves. + s.execute("edit.clearFormats", json!({"range": "B1"})).unwrap(); + assert!(!lit(&s, "B1")); + assert_eq!([lit(&s, "C1"), lit(&s, "B2"), lit(&s, "C2"), lit(&s, "B3"), lit(&s, "C3")], [true; 5], "each cell compares x > x - 2 with itself"); + + // Undo, redo and an XLSX round trip keep the split ranges and the rebased formulas. + s.execute("edit.undo", json!({})).unwrap(); + s.execute("edit.redo", json!({})).unwrap(); + let b64 = s.execute("file.saveBytes", json!({"format": "xlsx"})).unwrap()["base64"].as_str().unwrap().to_string(); + s.execute("file.open", json!({"name": "x.xlsx", "base64": b64})).unwrap(); + assert_eq!([lit(&s, "A1"), lit(&s, "A3"), lit(&s, "A4"), lit(&s, "B1"), lit(&s, "C2")], [false, true, false, false, true]); +} + +/// A clear over many small areas (as Go To Special selects) cuts them all; one that would split a +/// rule into more than the cap is refused without changing anything, and a large area in the same +/// selection goes first, so the rule just goes. A cut that keeps the anchor cell leaves formulas be. +#[test] +fn clear_formats_over_many_areas_is_bounded() { + let mut s = s(); + let ranges = |s: &Session| -> Vec> { + s.doc().unwrap().wb.active().unwrap().cond_formats.iter().map(|c| c.ranges.iter().map(|r| r.a1()).collect()).collect() + }; + s.execute( + "home.conditionalFormat", + json!({"range": "A1:J1000", "rule": {"type": "expression", "formula": "=A1>2", "style": {"fill": "#FF0000"}}}), + ) + .unwrap(); + let blanks: Vec = (1..=500).map(|k| format!("B{}", 2 * k)).collect(); + s.execute("edit.clearFormats", json!({"range": blanks.join(",")})).unwrap(); + let wb = &s.doc().unwrap().wb; + let cf = &wb.active().unwrap().cond_formats[0]; + let formula = match &cf.rule { + gridcraft_model::CfRule::Expression { formula, .. } => formula.clone(), + _ => String::new(), + }; + assert_eq!((cf.ranges[0].a1(), formula.as_str()), ("A1:J1".to_string(), "A1>2"), "the anchor cell survived, so the formula is unchanged"); + s.execute("edit.clearFormats", json!({"range": "A1:J1000"})).unwrap(); + assert!(ranges(&s).is_empty()); + + s.execute("home.conditionalFormat", json!({"range": "A1:C12000", "rule": {"type": "iconSet", "set": "3Arrows"}})).unwrap(); + let many: Vec = (1..=5600).map(|k| format!("B{}", 2 * k)).collect(); + let before = ranges(&s); + assert!(s.execute("edit.clearFormats", json!({"range": many.join(",")})).is_err()); + assert_eq!(ranges(&s), before, "a refused clear changes nothing"); + s.execute("edit.clearFormats", json!({"range": format!("{},A1:C12000", many.join(","))})).unwrap(); + assert!(ranges(&s).is_empty(), "the large area goes first and takes the rule"); +} + /// Icon-set thresholds as read from a file (one per icon, the first the lower bound) decide the /// icon; a rule without thresholds uses Excel's defaults (percent 33 and 67) (#235). #[test]