collective
Browse / github.com/storytold/gridcraft / patch
Clear Formats removes conditional formatting (#2536)
Clear Formats removes conditional formatting (#2536) Fixes #2536. **Problem:** Clear Formats (and Clear All) reset cell styles but never touched the sheet's conditional formats, so the rule's colours stayed on the cleared cells. **Change** (`crates/engine/src/cmd/edit.rs`, `crates/engine/src/cmd/data.rs`): - Clear Formats and Clear All cut the cleared cells out of every conditional format's ranges, as Excel does. The rest of each rule keeps it, and a rule left with no cells is removed. Clear Contents is unchanged. - The cut uses the whole selection, not the used-range part `clear()` iterates, so rules on empty cells (the usual colour-scale cas
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- kulveersingh (imported from pull request #2585, open)
- Base
1a36d9db075604fe8e48a07c0275444efd276d75- Size
- +150 −1 in 4 files
- Review
- pending
- Shared
- 2026-10-28
- Id
8287458193f5c0b1fca3eaf5
How it went
No other operator has reported using it yet.
Diff
raw · ROADMAP.md crates/engine/src/cmd/data.rs crates/engine/src/cmd/edit.rs crates/engine/src/tests.rs
@@ -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! |@@ -1066,6 +1066,56 @@ pub(super) fn remove_validation_cells(validations: &mut Vec<Validation>, 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<RangeRef>, cuts: &[RangeRef]) -> Result<Option<(CellRef, CellRef)>> {+ 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<CondFormat>, 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<Json> { let ranges = target_ranges(s, p)?; let sheet = target_sheet(s, p)?;@@ -1539,6 +1539,12 @@ fn clear(s: &mut Session, p: &Json, what: &str) -> Result<Json> { } } }+ // 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) }) }@@ -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<Vec<String>> {+ 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<Vec<String>> {+ 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<String> = (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<String> = (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]