collective
Browse / github.com/storytold/gridcraft / patch
Conditional formatting: New/Edit Formatting Rule dialog
Conditional formatting: New/Edit Formatting Rule dialog Part 2 of 4, split out of #2184. **Depends on #2731** — until it merges this PR also shows its commit; the change here is the last commit only (2615df5). - Home › Conditional Formatting › New Rule… opens New Formatting Rule with the six rule types (colour scales, data bars and icon sets; cell value, text, dates, blanks and errors; top/bottom; above/below average and standard deviations; unique/duplicate; formula) and a format editor with presets, fill, font colour, border, font styles and a preview. - Given a listed rule's index and params it opens as Edit Formatting Rule, and OK updates that
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- Antxis77 (imported from pull request #2732, open)
- Base
45f5ab18e4e57bf50ee88d2526deb4b87c5b24cb- Size
- +1389 −94 in 11 files
- Review
- pending
- Shared
- 2026-10-28
- Id
694f273f3f13f27a06744a2f
How it went
No other operator has reported using it yet.
Diff
raw · ROADMAP.md crates/engine/src/cmd/insert.rs crates/engine/src/cmd/insert/cond_format.rs crates/ui-egui/src/dialogs.rs crates/ui-egui/src/dialogs/formatting_rule.rs crates/ui-egui/src/dialogs/show.rs crates/ui-egui/src/grid.rs crates/ui-egui/src/grid/cell_paint.rs crates/ui-egui/src/ribbon/home.rs crates/ui-egui/tests/it/cf_rule_pick.rs docs/ui-parity.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-27 · **Change:** minor (New Rule… opens New Formatting Rule with every rule type and a format editor) · **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,8 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---|+| 2026-10-27 | Conditional formatting: New Rule… opens a full New Formatting Rule dialog (six rule types, format editor with preview), also used as Edit Formatting Rule |+| 2026-10-27 | Conditional formatting commands edit rules: `home.conditionalFormat` takes Stop If True, plain format overrides (fill, font colour, border, font styles) and colour-scale/data-bar thresholds, and checks icon set thresholds; `home.manageRules` gains `show`, `selection`, `update`, `duplicate` and `set` and lists each rule's editable params, on any sheet | | 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 +244,8 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | Change | Summary | |---|---|---|+| 2026-10-27 | minor | Progress log: New Formatting Rule dialog |+| 2026-10-27 | minor | Progress log: conditional formatting rule editing commands | | 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! |@@ -18,6 +18,7 @@ mod table; pub use chart::{build_chart, chart_kind, recommend_charts, scatter_x_from_first, series_from_range}; use chart::{chart_delete, chart_set, chart_switch, insert_chart};+pub use cond_format::preset_style; use cond_format::{add_cf, clear_cf, insert_icon, manage_cf}; use links::{comment, delete_comment, insert_link, open_link, reply_comment, resolve_comment, show_note}; use objects::{delete_object, insert_picture, insert_shape, insert_sparkline, move_object, set_anchor_mode, shape_set_text};@@ -203,7 +204,7 @@ pub fn specs() -> Vec<CommandSpec> { "Conditional Formatting", ["Home", "Styles"], None,- "{range?, rule: {type: cellIs|expression|containsText|beginsWith|endsWith|blanks|noBlanks|errors|noErrors|duplicate|unique|top10|aboveAverage|timePeriod|colorScale|dataBar|iconSet, operator?, value?, value2?, formula?, text?, rank?, bottom?, percent?, below?, period?, colors?: [..], color?, set?, style?: {font?: {bold?, italic?, color?}, fill?: \"#FFC7CE\" | {pattern?, fg?, bg?}, borders?, num_fmt?} (colours \"#RRGGBB\") | preset?: lightRedFill|redText|yellowFill|greenFill|redBorder|lightRedFillDarkRedText…}; a text `value` is matched as text, or kept when already a quoted literal}",+ "{range?, stopIfTrue?, rule: {type: cellIs|expression|containsText|notContainsText|beginsWith|endsWith|blanks|noBlanks|errors|noErrors|duplicate|unique|top10|aboveAverage|timePeriod|colorScale|dataBar|iconSet, operator?, value?, value2?, formula?, text?, rank?, bottom?, percent?, below?, equal?, stdDev?, period?, colors?: [..], min?|mid?|max?: {type: min|max|num|percent|percentile|formula, value?}, color?, gradient?, showValue?, set?, reverse?, style?: {font..fill..}|preset: lightRedFill|redText|yellowFill|greenFill|redBorder|lightRedFillDarkRedText…, fill?, fontColor?, border?: hex|none, bold?, italic?, underline?, strike?}}", has_doc, add_cf ),@@ -213,7 +214,7 @@ pub fn specs() -> Vec<CommandSpec> { "Manage Rules…", ["Home", "Styles", "Conditional Formatting"], None,- "{delete?: index, moveUp?: index, moveDown?: index}",+ "{show?: sheet|selection, selection?: \"A1:B9,D2\"} lists the rules (index, appliesTo, stopIfTrue, params); {set?: [{rule|params, appliesTo, stopIfTrue?}] replaces them all, {update?: index, rule?: {..as home.conditionalFormat}, appliesTo?: \"=$A$1:$B$9\", stopIfTrue?, duplicate?: index, delete?: index, moveUp?: index, moveDown?: index} changes them", has_doc, manage_cf ),@@ -311,4 +312,185 @@ mod tests { s.execute("table.rename", json!({"table": "Table1", "name": "Table1"})).unwrap(); s.execute("table.rename", json!({"table": "Table1", "name": "Rates"})).unwrap(); }++ #[test]+ fn manage_rules_lists_params_that_recreate_the_rule() {+ let mut s = Session::new();+ s.new_workbook();+ let rules = [+ json!({"type": "cellIs", "operator": "between", "value": "1", "value2": "say \"hi\"", "fill": "#FFEB9C", "bold": true}),+ json!({"type": "expression", "formula": "=MOD(ROW(),2)=0", "preset": "redBorder"}),+ json!({"type": "aboveAverage", "below": true, "equal": true, "stdDev": 1}),+ json!({"type": "colorScale", "colors": ["#F8696B", "#63BE7B"], "min": {"type": "num", "value": 5}}),+ json!({"type": "dataBar", "showValue": false, "max": {"type": "formula", "value": "=$Z$1"}}),+ ];+ for r in &rules {+ s.execute("home.conditionalFormat", json!({"range": "A1:A9", "rule": r})).unwrap();+ }+ let listed = s.execute("home.manageRules", json!({})).unwrap()["rules"].as_array().cloned().unwrap();+ let before: Vec<Json> = listed.iter().map(|r| r["rule"].clone()).collect();+ // Each rule's params replace it with itself.+ for (i, r) in listed.iter().enumerate() {+ s.execute("home.manageRules", json!({"update": i, "rule": r["params"]})).unwrap();+ }+ let after = s.execute("home.manageRules", json!({})).unwrap();+ let after: Vec<Json> = after["rules"].as_array().unwrap().iter().map(|r| r["rule"].clone()).collect();+ assert_eq!(before, after);+ assert_eq!(listed[0]["appliesTo"], "=$A$1:$A$9");+ assert_eq!(listed[4]["params"]["value2"], "say \"hi\"");+ assert_eq!(listed[4]["params"]["fill"], "#FFEB9C");+ }++ #[test]+ fn listed_cell_values_read_back_as_the_same_rule() {+ let mut s = Session::new();+ s.new_workbook();+ // Text that looks like a number or a formula, and a formula that starts and ends with a quote.+ for value in [json!("\"5\""), json!("\"=x\""), json!("=\"a\"&A1&\"b\""), json!("plain text"), json!(7)] {+ s.execute("home.conditionalFormat", json!({"range": "A1:A3", "rule": {"type": "cellIs", "operator": "equal", "value": value}})).unwrap();+ let listed = s.execute("home.manageRules", json!({})).unwrap()["rules"][0].clone();+ s.execute("home.manageRules", json!({"update": 0, "rule": listed["params"]})).unwrap();+ let again = s.execute("home.manageRules", json!({})).unwrap()["rules"][0]["rule"].clone();+ assert_eq!(again, listed["rule"], "{value}");+ }+ assert_eq!(s.execute("home.manageRules", json!({})).unwrap()["total"], 5);+ }++ #[test]+ fn edit_and_restore_keep_format_details_the_plain_overrides_cant_show() {+ let mut s = Session::new();+ s.new_workbook();+ // A bottom-only medium border, a pattern fill and a double underline, as a file can hold.+ let style = json!({+ "borders": {"bottom": {"style": "Medium", "color": {"Rgb": 0x0000FF}}},+ "fill": {"pattern": "Gray125", "fg": {"Rgb": 0xFF0000}, "bg": "Auto"},+ "font": {"underline": "Double"},+ });+ s.execute("home.conditionalFormat", json!({"range": "A1:A9", "rule": {"type": "blanks", "style": style}})).unwrap();+ let listed = s.execute("home.manageRules", json!({})).unwrap()["rules"][0].clone();+ let original = listed["rule"].clone();+ // Edit Rule… with the format untouched.+ s.execute("home.manageRules", json!({"update": 0, "rule": listed["params"]})).unwrap();+ let after = s.execute("home.manageRules", json!({})).unwrap()["rules"][0]["rule"].clone();+ assert_eq!(after, original);+ // The same colours written another way (lower case) are no change either.+ let mut lower = listed["params"].clone();+ for k in ["fill", "fontColor", "border"] {+ if let Some(c) = lower[k].as_str().map(str::to_ascii_lowercase) {+ lower[k] = json!(c);+ }+ }+ s.execute("home.manageRules", json!({"update": 0, "rule": lower})).unwrap();+ let after = s.execute("home.manageRules", json!({})).unwrap()["rules"][0]["rule"].clone();+ assert_eq!(after, original);+ // Edit Rule… changing only bold keeps the rest.+ let mut bold = listed["params"].clone();+ bold["bold"] = json!(true);+ s.execute("home.manageRules", json!({"update": 0, "rule": bold})).unwrap();+ let after = s.execute("home.manageRules", json!({})).unwrap()["rules"][0]["rule"].clone();+ assert_eq!(after["Blanks"]["style"]["font"]["bold"], true);+ assert_eq!(after["Blanks"]["style"]["font"]["underline"], "Double");+ assert_eq!(after["Blanks"]["style"]["fill"], original["Blanks"]["style"]["fill"]);+ assert_eq!(after["Blanks"]["style"]["borders"], original["Blanks"]["style"]["borders"]);+ // Cancel puts back the listed rule exactly.+ let saved = json!([{"rule": original, "appliesTo": listed["appliesTo"], "stopIfTrue": false}]);+ s.execute("home.manageRules", json!({"set": saved})).unwrap();+ let back = s.execute("home.manageRules", json!({})).unwrap()["rules"][0]["rule"].clone();+ assert_eq!(back, original);+ // A serialized rule whose formula doesn't parse is refused like a new one (#1999).+ let bad_rule = json!({"Expression": {"formula": "A1>", "style": Style::default()}});+ assert!(s.execute("home.manageRules", json!({"set": [{"rule": bad_rule, "appliesTo": "=$A$1"}]})).is_err());+ let icons = json!({"type": "iconSet", "set": "3Arrows", "thresholds": [{"Formula": "=1+"}]});+ assert!(s.execute("home.conditionalFormat", json!({"range": "B1:B3", "rule": icons})).is_err());+ // A malformed serialized rule is refused, not read as a default rule.+ assert!(s.execute("home.manageRules", json!({"set": [{"rule": {"Blanks": {}}, "appliesTo": "=$A$1"}]})).is_err());+ // Icon thresholds: one per icon or one fewer, nothing else.+ let three = |n: usize| json!({"type": "iconSet", "set": "3Arrows", "thresholds": vec![json!({"Percent": 50.0}); n]});+ assert!(s.execute("home.conditionalFormat", json!({"range": "C1:C3", "rule": three(2)})).is_ok());+ assert!(s.execute("home.conditionalFormat", json!({"range": "C1:C3", "rule": three(3)})).is_ok());+ assert!(s.execute("home.conditionalFormat", json!({"range": "C1:C3", "rule": three(5)})).is_err());+ // An entry with neither `rule` nor `params` is refused.+ assert!(s.execute("home.manageRules", json!({"set": [{"appliesTo": "=$A$1"}]})).is_err());+ }++ #[test]+ fn manage_rules_work_on_a_sheet_that_is_not_active() {+ let mut s = Session::new();+ s.new_workbook();+ s.execute("home.insertSheet", json!({"name": "Two"})).unwrap();+ s.execute("sheet.activate", json!({"sheet": 1})).unwrap();+ // The manager opened on Sheet1 with A1:A3 selected; Two is active now.+ let rule = json!({"type": "blanks", "fill": "#FFC7CE"});+ s.execute("home.conditionalFormat", json!({"sheet": 0, "range": "A1:A3", "rule": rule})).unwrap();+ s.execute("home.conditionalFormat", json!({"sheet": 0, "range": "D1:D3", "rule": rule})).unwrap();+ let listed = s.execute("home.manageRules", json!({"sheet": 0, "show": "selection", "selection": "A1:A3"})).unwrap();+ let ranges: Vec<Json> = listed["rules"].as_array().unwrap().iter().map(|r| r["appliesTo"].clone()).collect();+ assert_eq!(ranges, vec![json!("=$A$1:$A$3")]);+ let two = s.execute("home.manageRules", json!({"sheet": 1})).unwrap();+ assert_eq!(two["rules"].as_array().map(Vec::len), Some(0));+ assert!(s.execute("home.manageRules", json!({"sheet": 0, "show": "selection", "selection": "nope"})).is_err());+ // Applies to may name the rule's own sheet, never another one.+ s.execute("home.manageRules", json!({"sheet": 0, "update": 0, "appliesTo": "=Sheet1!$A$1:$A$5"})).unwrap();+ let first = s.execute("home.manageRules", json!({"sheet": 0})).unwrap();+ assert_eq!(first["rules"][0]["appliesTo"], "=$A$1:$A$5");+ assert!(s.execute("home.manageRules", json!({"sheet": 0, "update": 0, "appliesTo": "=Two!$B$2"})).is_err());+ }++ #[test]+ fn malformed_overrides_and_thresholds_are_refused() {+ let mut s = Session::new();+ s.new_workbook();+ let add = |s: &mut Session, rule: Json| s.execute("home.conditionalFormat", json!({"range": "A1:A3", "rule": rule}));+ // A colour that doesn't parse, or a flag that isn't a boolean, is an error, not no change.+ for (k, v) in [("fill", json!("#nothex")), ("fontColor", json!(12)), ("border", json!({"x": 1})), ("bold", json!("yes"))] {+ let mut rule = json!({"type": "blanks"});+ rule[k] = v;+ assert!(add(&mut s, rule).is_err(), "{k}");+ }+ // Null still means no override.+ assert!(add(&mut s, json!({"type": "blanks", "fill": null, "bold": null})).is_ok());+ // A serialized icon set with the wrong threshold count is refused like params are.+ let icons = json!({"IconSet": {"set": "3Arrows", "thresholds": vec![json!({"Percent": 10.0}); 5], "reverse": false, "show_value": true}});+ assert!(s.execute("home.manageRules", json!({"set": [{"rule": icons, "appliesTo": "=$A$1"}]})).is_err());+ // A threshold that parses to infinity reads as the default 0 and lists as a number.+ add(&mut s, json!({"type": "dataBar", "min": {"type": "num", "value": "inf"}})).unwrap();+ let listed = s.execute("home.manageRules", json!({})).unwrap();+ assert_eq!(listed["rules"][0]["params"]["min"], json!({"type": "num", "value": 0.0}));+ }++ #[test]+ fn manage_rules_edits_duplicates_and_filters() {+ let mut s = Session::new();+ s.new_workbook();+ let rule = json!({"type": "blanks", "fontColor": "#FF0000"});+ s.execute("home.conditionalFormat", json!({"range": "A1:A3", "rule": rule})).unwrap();+ s.execute("home.conditionalFormat", json!({"range": "C1:C3", "rule": {"type": "errors"}, "stopIfTrue": true})).unwrap();+ s.execute("home.manageRules", json!({"update": 1, "appliesTo": "=$A$1:$A$5,$E$1", "stopIfTrue": true})).unwrap();+ s.execute("home.manageRules", json!({"duplicate": 0})).unwrap();+ let all = s.execute("home.manageRules", json!({})).unwrap();+ let all = all["rules"].as_array().unwrap();+ assert_eq!(all.len(), 3);+ assert_eq!(all[0]["params"]["type"], "errors");+ assert_eq!(all[0]["stopIfTrue"], true);+ assert_eq!(all[1]["params"]["type"], "errors");+ assert_eq!(all[2]["appliesTo"], "=$A$1:$A$5,$E$1");+ assert_eq!(all[2]["params"]["fontColor"], "#FF0000");+ // Plain overrides alone don't bring the default preset's fill.+ assert_eq!(all[2]["params"]["fill"], "none");+ s.execute("selection.set", json!({"range": "E1"})).unwrap();+ let sel = s.execute("home.manageRules", json!({"show": "selection"})).unwrap();+ let idx: Vec<u64> = sel["rules"].as_array().unwrap().iter().filter_map(|r| r["index"].as_u64()).collect();+ assert_eq!(idx, vec![2]);+ assert!(s.execute("home.manageRules", json!({"update": 0, "appliesTo": "=nope"})).is_err());+ assert!(s.execute("home.manageRules", json!({"update": 9, "stopIfTrue": true})).is_ok());+ // `set` puts back a listing taken earlier.+ let saved: Vec<Json> =+ all.iter().map(|r| json!({"params": r["params"], "appliesTo": r["appliesTo"], "stopIfTrue": r["stopIfTrue"]})).collect();+ s.execute("home.manageRules", json!({"delete": 0})).unwrap();+ s.execute("home.manageRules", json!({"set": saved})).unwrap();+ let back = s.execute("home.manageRules", json!({})).unwrap();+ assert_eq!(back["rules"].as_array().map(Vec::len), Some(3));+ assert_eq!(back["rules"][0]["stopIfTrue"], true);+ assert!(s.execute("home.manageRules", json!({"set": [{"rule": {"type": "blanks"}}]})).is_err());+ } }@@ -2,7 +2,8 @@ use super::*; // ---------------------------------------------------------------- conditional formatting -fn preset_style(name: &str) -> Style {+/// The format of a Highlight Cells preset (`lightRedFillDarkRedText`, `redBorder`…).+pub fn preset_style(name: &str) -> Style { let mut s = Style::default(); match name { "redText" => s.font.color = Color::rgb(0x9C, 0x00, 0x06),@@ -126,6 +127,17 @@ fn check_cf_rule(rule: &CfRule) -> Result<()> { check_cf_value("color scale threshold", kind)?; } }+ CfRule::IconSet { set, thresholds, .. } => {+ // The evaluator reads one threshold per icon, or one fewer (no lower bound); any other+ // count would be stored and then ignored for the defaults.+ let count = icon_count(set);+ if !thresholds.is_empty() && thresholds.len() != count && thresholds.len() + 1 != count {+ return Err(bad(CMD, format!("icon set `{set}` takes {count} thresholds, or {}", count - 1)));+ }+ for kind in thresholds {+ check_cf_value("icon set threshold", kind)?;+ }+ } _ => {} } Ok(())@@ -138,33 +150,154 @@ pub(super) fn add_cf(s: &mut Session, p: &Json) -> Result<Json> { s.ui_requests.push(crate::UiRequest::Dialog("newFormattingRule".into(), json!({}))); return ok(); };- let g = |k: &str| rule.get(k).and_then(Json::as_str).map(str::to_string);- let style = Box::new(match rule.get("style") {+ let rule = parse_rule(rule, &s.doc()?.wb.theme)?;+ let stop_if_true = bool_param(p, "stopIfTrue").unwrap_or(false);+ edit(s, |cx| {+ let sh = cx.sheet_mut(sheet)?;+ // New rules take precedence over older ones.+ sh.cond_formats.insert(0, CondFormat { ranges: ranges.clone(), rule, priority: 1, stop_if_true });+ renumber_cf(&mut sh.cond_formats);+ Ok(json!({"rules": sh.cond_formats.len()}))+ })+}++/// The plain format overrides that describe `st`, as [`rule_params`] lists them. They are+/// coarser than the style (one border colour on four thin sides, a solid fill, a single+/// underline), so [`rule_style`] applies only those that differ from these.+fn style_overrides(st: &Style, theme: &Theme) -> serde_json::Map<String, Json> {+ let hex = |c: &Color| c.hex(theme);+ let line = [st.borders.left, st.borders.right, st.borders.top, st.borders.bottom].into_iter().find(|l| l.style != BorderStyle::None);+ let mut m = serde_json::Map::new();+ m.insert("fill".into(), if st.fill.pattern == PatternType::None { json!("none") } else { hex(&st.fill.fg).map_or(Json::Null, Json::from) });+ m.insert("fontColor".into(), hex(&st.font.color).map_or(json!("auto"), Json::from));+ m.insert("border".into(), line.map_or(json!("none"), |l| json!(hex(&l.color).unwrap_or_else(|| "#000000".into()))));+ m.insert("bold".into(), json!(st.font.bold));+ m.insert("italic".into(), json!(st.font.italic));+ m.insert("underline".into(), json!(st.font.underline != Underline::None));+ m.insert("strike".into(), json!(st.font.strike));+ m+}++/// The style a rule's params give: `style` (a partial style) or a `preset`, then the plain+/// overrides `fill`, `fontColor`, `border` (colours, `none` clears), `bold`, `italic`,+/// `underline` and `strike`. An override equal to what the base style already shows is left+/// out, so a rule sent back unchanged keeps its partial borders, patterns and underline kind.+fn rule_style(rule: &Json, theme: &Theme) -> Result<Box<Style>> {+ let preset = rule.get("preset").and_then(Json::as_str);+ let mut st = match rule.get("style") { Some(st) if !st.is_null() => {- let st = cf_style_json(st)?;+ // A listed rule's `style` carries `dxf_stated` (the parts a file states at their+ // default value): it is kept as is, not checked as a format key.+ let mut st = st.clone();+ let stated = st.as_object_mut().and_then(|o| o.remove("dxf_stated"));+ let st = cf_style_json(&st)?; let base = serde_json::to_value(Style::default()).map_err(|e| EngineError::Other(e.to_string()))?;- serde_json::from_value(crate::cmd::format::merge_json(base, &st)).map_err(|e| bad("home.conditionalFormat", e.to_string()))?- }- _ => preset_style(g("preset").as_deref().unwrap_or("lightRedFillDarkRedText")),- });- let kind = g("type").unwrap_or_else(|| "cellIs".into());- let value_kind = |v: Option<&Json>, default: CfValueKind| -> CfValueKind {- match v {- Some(Json::Object(o)) => {- let t = o.get("type").and_then(Json::as_str).unwrap_or("num");- let val = o.get("value").and_then(Json::as_f64).unwrap_or(0.0);- match t {- "min" => CfValueKind::Min,- "max" => CfValueKind::Max,- "percent" => CfValueKind::Percent(val),- "percentile" => CfValueKind::Percentile(val),- "formula" => CfValueKind::Formula(o.get("value").and_then(Json::as_str).unwrap_or("").into()),- _ => CfValueKind::Number(val),- }+ let mut style: Style =+ serde_json::from_value(crate::cmd::format::merge_json(base, &st)).map_err(|e| bad("home.conditionalFormat", e.to_string()))?;+ if let Some(d) = stated {+ style.dxf_stated = serde_json::from_value(d).map_err(|e| bad("home.conditionalFormat", e.to_string()))?; }- _ => default,+ style+ }+ // Plain overrides alone start from an empty format, not from the default preset.+ _ if preset.is_none() && ["fill", "fontColor", "border", "bold", "italic", "underline", "strike"].iter().any(|k| rule.get(*k).is_some()) => {+ Style::default()+ }+ _ => preset_style(preset.unwrap_or("lightRedFillDarkRedText")),+ };+ // A malformed override is an error rather than no change; only a missing or null one is.+ for k in ["fill", "fontColor", "border"] {+ if let Some(v) = rule.get(k).filter(|v| !v.is_null())+ && crate::cmd::format::color_param(Some(v)).is_none()+ {+ return Err(bad("home.conditionalFormat", format!("invalid colour {v} for `{k}`")));+ }+ }+ for k in ["bold", "italic", "underline", "strike"] {+ if let Some(v) = rule.get(k).filter(|v| !v.is_null() && !v.is_boolean()) {+ return Err(bad("home.conditionalFormat", format!("`{k}` must be true or false, not {v}"))); }+ }+ let shown = style_overrides(&st, theme);+ let changed = |k: &str| rule.get(k).filter(|v| shown.get(k) != Some(*v));+ // Colours compare by the colour they give, so `#ffc7ce`, `#FFC7CE` and a theme slot of the+ // same shade all count as unchanged.+ let changed_color = |k: &str| {+ let c = crate::cmd::format::color_param(rule.get(k))?;+ let base = crate::cmd::format::color_param(shown.get(k));+ (base.map(|b| b.hex(theme)) != Some(c.hex(theme))).then_some(c) };+ if let Some(c) = changed_color("fill") {+ st.fill = if c == Color::Auto { Fill::default() } else { Fill::solid(c) };+ }+ if let Some(c) = changed_color("fontColor") {+ st.font.color = c;+ }+ if let Some(c) = changed_color("border") {+ st.borders = if c == Color::Auto {+ Borders::default()+ } else {+ let l = BorderLine { style: BorderStyle::Thin, color: c };+ Borders { left: l, right: l, top: l, bottom: l, ..Default::default() }+ };+ }+ let flag = |k: &str| changed(k).and_then(Json::as_bool);+ if let Some(b) = flag("bold") {+ st.font.bold = b;+ }+ if let Some(b) = flag("italic") {+ st.font.italic = b;+ }+ if let Some(b) = flag("underline") {+ st.font.underline = if b { Underline::Single } else { Underline::None };+ }+ if let Some(b) = flag("strike") {+ st.font.strike = b;+ }+ Ok(Box::new(st))+}++/// A threshold from `{type: min|max|num|percent|percentile|formula, value}`.+fn cf_value_kind(v: Option<&Json>, default: CfValueKind) -> CfValueKind {+ match v {+ Some(Json::Object(o)) => {+ let val = o+ .get("value")+ .and_then(|v| v.as_f64().or_else(|| v.as_str().and_then(|s| s.trim().parse().ok())))+ // `inf` and `NaN` parse but can't be listed or saved: they read as the default.+ .filter(|v: &f64| v.is_finite())+ .unwrap_or(0.0);+ match o.get("type").and_then(Json::as_str).unwrap_or("num") {+ "min" => CfValueKind::Min,+ "max" => CfValueKind::Max,+ "percent" => CfValueKind::Percent(val),+ "percentile" => CfValueKind::Percentile(val),+ // Kept as typed: the check and the file writer each take off one leading `=` (#1999).+ "formula" => CfValueKind::Formula(o.get("value").map(crate::cmd::edit::json_to_input).unwrap_or_default()),+ _ => CfValueKind::Number(val),+ }+ }+ _ => default,+ }+}++fn cf_value_json(k: &CfValueKind) -> Json {+ match k {+ CfValueKind::Min => json!({"type": "min"}),+ CfValueKind::Max => json!({"type": "max"}),+ CfValueKind::Number(v) => json!({"type": "num", "value": v}),+ CfValueKind::Percent(v) => json!({"type": "percent", "value": v}),+ CfValueKind::Percentile(v) => json!({"type": "percentile", "value": v}),+ CfValueKind::Formula(f) => json!({"type": "formula", "value": if f.starts_with('=') { f.clone() } else { format!("={f}") }}),+ }+}++/// A rule from its `home.conditionalFormat` params.+fn parse_rule(rule: &Json, theme: &Theme) -> Result<CfRule> {+ let g = |k: &str| rule.get(k).and_then(Json::as_str).map(str::to_string);+ let flag = |k: &str| rule.get(k).and_then(Json::as_bool);+ let kind = g("type").unwrap_or_else(|| "cellIs".into());+ let style = || rule_style(rule, theme); let rule = match kind.as_str() { "cellIs" => { // Excel's file and VBA names (`lessThan`, …) are accepted too.@@ -192,70 +325,192 @@ pub(super) fn add_cf(s: &mut Session, p: &Json) -> Result<Json> { format!("\"{}\"", v.replace('"', "\"\"")) } };- CfRule::CellIs { op, a: quote(a), b: b.map(quote), style }- }- "expression" => CfRule::Expression { formula: g("formula").unwrap_or_default().trim_start_matches('=').to_string(), style },- "containsText" => CfRule::ContainsText { text: g("text").unwrap_or_default(), style },- "notContainsText" => CfRule::NotContainsText { text: g("text").unwrap_or_default(), style },- "beginsWith" => CfRule::BeginsWith { text: g("text").unwrap_or_default(), style },- "endsWith" => CfRule::EndsWith { text: g("text").unwrap_or_default(), style },- "blanks" => CfRule::Blanks { style },- "noBlanks" => CfRule::NoBlanks { style },- "errors" => CfRule::Errors { style },- "noErrors" => CfRule::NoErrors { style },- "duplicate" => CfRule::Duplicate { style },- "unique" => CfRule::Unique { style },+ CfRule::CellIs { op, a: quote(a), b: b.map(quote), style: style()? }+ }+ "expression" => CfRule::Expression { formula: g("formula").unwrap_or_default().trim_start_matches('=').to_string(), style: style()? },+ "containsText" => CfRule::ContainsText { text: g("text").unwrap_or_default(), style: style()? },+ "notContainsText" => CfRule::NotContainsText { text: g("text").unwrap_or_default(), style: style()? },+ "beginsWith" => CfRule::BeginsWith { text: g("text").unwrap_or_default(), style: style()? },+ "endsWith" => CfRule::EndsWith { text: g("text").unwrap_or_default(), style: style()? },+ "blanks" => CfRule::Blanks { style: style()? },+ "noBlanks" => CfRule::NoBlanks { style: style()? },+ "errors" => CfRule::Errors { style: style()? },+ "noErrors" => CfRule::NoErrors { style: style()? },+ "duplicate" => CfRule::Duplicate { style: style()? },+ "unique" => CfRule::Unique { style: style()? }, "top10" => CfRule::Top10 {- bottom: rule.get("bottom").and_then(Json::as_bool).unwrap_or(false),- percent: rule.get("percent").and_then(Json::as_bool).unwrap_or(false),- rank: rule.get("rank").and_then(Json::as_u64).unwrap_or(10) as u32,- style,+ bottom: flag("bottom").unwrap_or(false),+ percent: flag("percent").unwrap_or(false),+ rank: rule.get("rank").and_then(Json::as_u64).unwrap_or(10).clamp(1, 1000) as u32,+ style: style()?,+ },+ "aboveAverage" => CfRule::AboveAverage {+ below: flag("below").unwrap_or(false),+ equal: flag("equal").unwrap_or(false),+ std_dev: rule.get("stdDev").and_then(Json::as_u64).unwrap_or(0).min(3) as u8,+ style: style()?, },- "aboveAverage" => CfRule::AboveAverage { below: rule.get("below").and_then(Json::as_bool).unwrap_or(false), equal: false, std_dev: 0, style },- "timePeriod" => CfRule::TimePeriod { period: g("period").unwrap_or_else(|| "today".into()), style },+ "timePeriod" => CfRule::TimePeriod { period: g("period").unwrap_or_else(|| "today".into()), style: style()? }, "colorScale" => { let cols: Vec<Color> = rule .get("colors") .and_then(Json::as_array) .map(|a| a.iter().filter_map(|c| crate::cmd::format::color_param(Some(c))).collect()) .unwrap_or_else(|| vec![Color::rgb(0xF8, 0x69, 0x6B), Color::rgb(0xFF, 0xEB, 0x84), Color::rgb(0x63, 0xBE, 0x7B)]);+ let min = cf_value_kind(rule.get("min"), CfValueKind::Min);+ let max = cf_value_kind(rule.get("max"), CfValueKind::Max); let stops = match cols.len() {- 2 => vec![(CfValueKind::Min, cols[0]), (CfValueKind::Max, cols[1])],+ 2 => vec![(min, cols[0]), (max, cols[1])], _ => vec![- (CfValueKind::Min, cols.first().copied().unwrap_or_default()),- (CfValueKind::Percentile(50.0), cols.get(1).copied().unwrap_or_default()),- (CfValueKind::Max, cols.get(2).copied().unwrap_or_default()),+ (min, cols.first().copied().unwrap_or_default()),+ (cf_value_kind(rule.get("mid"), CfValueKind::Percentile(50.0)), cols.get(1).copied().unwrap_or_default()),+ (max, cols.get(2).copied().unwrap_or_default()), ], }; CfRule::ColorScale { stops } } "dataBar" => CfRule::DataBar {- min: value_kind(rule.get("min"), CfValueKind::Min),- max: value_kind(rule.get("max"), CfValueKind::Max),+ min: cf_value_kind(rule.get("min"), CfValueKind::Min),+ max: cf_value_kind(rule.get("max"), CfValueKind::Max), color: crate::cmd::format::color_param(rule.get("color")).unwrap_or(Color::rgb(0x63, 0x8E, 0xC6)),- gradient: rule.get("gradient").and_then(Json::as_bool).unwrap_or(true),- show_value: true,- },- "iconSet" => CfRule::IconSet {- set: g("set").unwrap_or_else(|| "3TrafficLights1".into()),- thresholds: vec![],- reverse: rule.get("reverse").and_then(Json::as_bool).unwrap_or(false),- show_value: true,+ gradient: flag("gradient").unwrap_or(true),+ show_value: flag("showValue").unwrap_or(true), },+ "iconSet" => {+ let set = g("set").unwrap_or_else(|| "3TrafficLights1".into());+ let thresholds: Vec<CfValueKind> = match rule.get("thresholds") {+ Some(t) if !t.is_null() => {+ serde_json::from_value(t.clone()).map_err(|e| bad("home.conditionalFormat", format!("`thresholds`: {e}")))?+ }+ _ => vec![],+ };+ CfRule::IconSet { set, thresholds, reverse: flag("reverse").unwrap_or(false), show_value: flag("showValue").unwrap_or(true) }+ } other => return Err(bad("home.conditionalFormat", format!("unknown rule type `{other}`"))), }; check_cf_rule(&rule)?;- edit(s, |cx| {- let sh = cx.sheet_mut(sheet)?;- let priority = sh.cond_formats.iter().map(|c| c.priority).max().unwrap_or(0) + 1;- // New rules take precedence over older ones.- for cf in sh.cond_formats.iter_mut() {- cf.priority += 1;+ Ok(rule)+}++/// A rule as `home.conditionalFormat` params, the inverse of [`parse_rule`]: Manage Rules edits+/// a rule through these. The format is given as plain overrides, plus the whole `style` so that+/// parts the overrides don't cover (number format, partial borders) survive an edit.+fn rule_params(rule: &CfRule, theme: &Theme) -> Json {+ let hex = |c: &Color| c.hex(theme).map(Json::from).unwrap_or(Json::Null);+ let unformula = |f: &str| {+ if is_string_literal(f) {+ let t = f.get(1..f.len() - 1).unwrap_or_default().replace("\"\"", "\"");+ // Text that would read back as a number, a formula or a literal stays quoted, as+ // `parse_rule` keeps a string literal as it is.+ if gridcraft_core::parse::parse_number_text(&t).is_some() || t.starts_with('=') || is_string_literal(&t) { f.to_string() } else { t }+ } else if gridcraft_core::parse::parse_number_text(f).is_some() {+ f.to_string()+ } else {+ format!("={f}") }- sh.cond_formats.insert(0, CondFormat { ranges: ranges.clone(), rule, priority: 1, stop_if_true: false });- let _ = priority;- Ok(json!({"rules": sh.cond_formats.len()}))- })+ };+ let (mut v, style) = match rule {+ CfRule::CellIs { op, a, b, style } => {+ let op = match op {+ CfOperator::Between => "between",+ CfOperator::NotBetween => "notBetween",+ CfOperator::Equal => "equal",+ CfOperator::NotEqual => "notEqual",+ CfOperator::Greater => "greater",+ CfOperator::Less => "less",+ CfOperator::GreaterOrEqual => "greaterOrEqual",+ CfOperator::LessOrEqual => "lessOrEqual",+ };+ let mut v = json!({"type": "cellIs", "operator": op, "value": unformula(a)});+ if let Some(b) = b {+ v["value2"] = json!(unformula(b));+ }+ (v, Some(style))+ }+ CfRule::Expression { formula, style } => (json!({"type": "expression", "formula": format!("={formula}")}), Some(style)),+ CfRule::ContainsText { text, style } => (json!({"type": "containsText", "text": text}), Some(style)),+ CfRule::NotContainsText { text, style } => (json!({"type": "notContainsText", "text": text}), Some(style)),+ CfRule::BeginsWith { text, style } => (json!({"type": "beginsWith", "text": text}), Some(style)),+ CfRule::EndsWith { text, style } => (json!({"type": "endsWith", "text": text}), Some(style)),+ CfRule::Blanks { style } => (json!({"type": "blanks"}), Some(style)),+ CfRule::NoBlanks { style } => (json!({"type": "noBlanks"}), Some(style)),+ CfRule::Errors { style } => (json!({"type": "errors"}), Some(style)),+ CfRule::NoErrors { style } => (json!({"type": "noErrors"}), Some(style)),+ CfRule::Duplicate { style } => (json!({"type": "duplicate"}), Some(style)),+ CfRule::Unique { style } => (json!({"type": "unique"}), Some(style)),+ CfRule::Top10 { bottom, percent, rank, style } => (json!({"type": "top10", "bottom": bottom, "percent": percent, "rank": rank}), Some(style)),+ CfRule::AboveAverage { below, equal, std_dev, style } => {+ (json!({"type": "aboveAverage", "below": below, "equal": equal, "stdDev": std_dev}), Some(style))+ }+ CfRule::TimePeriod { period, style } => (json!({"type": "timePeriod", "period": period}), Some(style)),+ CfRule::ColorScale { stops } => {+ let mut v = json!({"type": "colorScale", "colors": stops.iter().map(|(_, c)| hex(c)).collect::<Vec<_>>()});+ let keys: &[&str] = if stops.len() == 2 { &["min", "max"] } else { &["min", "mid", "max"] };+ for (k, (kind, _)) in keys.iter().zip(stops) {+ v[*k] = cf_value_json(kind);+ }+ (v, None)+ }+ CfRule::DataBar { min, max, color, gradient, show_value } => (+ json!({"type": "dataBar", "color": hex(color), "gradient": gradient, "showValue": show_value, "min": cf_value_json(min), "max": cf_value_json(max)}),+ None,+ ),+ CfRule::IconSet { set, thresholds, reverse, show_value } => {+ (json!({"type": "iconSet", "set": set, "thresholds": thresholds, "reverse": reverse, "showValue": show_value}), None)+ }+ };+ if let Some(st) = style {+ for (k, x) in style_overrides(st, theme) {+ v[k.as_str()] = x;+ }+ v["style"] = serde_json::to_value(st).unwrap_or(Json::Null);+ }+ v+}++/// `=$A$1:$B$9,$D$2` for a rule's ranges, as Manage Rules shows them.+fn applies_to(ranges: &[RangeRef]) -> String {+ let a = |c: CellRef| format!("${}${}", gridcraft_core::col_to_letters(c.col), c.row as u64 + 1);+ let parts: Vec<String> = ranges+ .iter()+ .map(|r| {+ if r.is_single() {+ a(r.start)+ } else if r.is_full_cols() || r.is_full_rows() {+ r.a1()+ } else {+ format!("{}:{}", a(r.start), a(r.end))+ }+ })+ .collect();+ format!("={}", parts.join(","))+}++/// The ranges of an `appliesTo` like `=$A$1:$B$9,$D$2` on `sheet`. A `Sheet!` prefix must name+/// that sheet: a rule's ranges live on its own sheet, so another sheet's range is refused rather+/// than read as the same address here.+fn parse_applies_to(text: &str, wb: &Workbook, sheet: usize) -> Result<Vec<RangeRef>> {+ let body = text.trim().trim_start_matches('=');+ let mut out = Vec::new();+ for part in body.split(',').map(str::trim).filter(|t| !t.is_empty()) {+ let (name, part) = split_sheet(part);+ if let Some(name) = name+ && wb.sheet_index(&name) != Some(sheet)+ {+ return Err(bad("home.manageRules", format!("`appliesTo` names another sheet: `{name}`")));+ }+ out.push(RangeRef::parse(part).ok_or_else(|| bad("home.manageRules", format!("not a range: `{part}`")))?);+ }+ if out.is_empty() {+ return Err(bad("home.manageRules", "`appliesTo` names no range"));+ }+ Ok(out)+}++fn renumber_cf(rules: &mut [CondFormat]) {+ for (k, cf) in rules.iter_mut().enumerate() {+ cf.priority = k as u32 + 1;+ } } pub(super) fn clear_cf(s: &mut Session, p: &Json) -> Result<Json> {@@ -278,19 +533,96 @@ pub(super) fn clear_cf(s: &mut Session, p: &Json) -> Result<Json> { pub(super) fn manage_cf(s: &mut Session, p: &Json) -> Result<Json> { let sheet = target_sheet(s, p)?;- if p.get("delete").is_none() && p.get("moveUp").is_none() && p.get("moveDown").is_none() {- let list: Vec<Json> = s- .doc()?- .wb- .sheet(sheet)- .map(|sh| {- sh.cond_formats.iter().map(|c| json!({"ranges": c.ranges.iter().map(|r| r.a1()).collect::<Vec<_>>(), "rule": c.rule})).collect()- })- .unwrap_or_default();- return Ok(json!({"rules": list}));+ if let Some(list) = p.get("set") {+ // The whole list at once, highest priority first (Manage Rules › Cancel puts it back).+ // A listed `rule` restores exactly; `params` go through the same parsing as a new rule.+ let theme = s.doc()?.wb.theme.clone();+ let mut rules = Vec::new();+ for (i, x) in list.as_array().ok_or_else(|| bad("home.manageRules", "`set` is a list of rules"))?.iter().enumerate() {+ let ranges = parse_applies_to(x.get("appliesTo").and_then(Json::as_str).unwrap_or(""), &s.doc()?.wb, sheet)?;+ let rule = match (x.get("rule"), x.get("params")) {+ (Some(r), _) => match serde_json::from_value::<CfRule>(r.clone()) {+ Ok(rule) => {+ check_cf_rule(&rule)?;+ rule+ }+ // Params written as `rule` (they name a `type`) still parse; a serialized rule+ // that doesn't deserialize is refused rather than read as a default rule.+ Err(_) if r.get("type").is_some_and(Json::is_string) => parse_rule(r, &theme)?,+ Err(e) => return Err(bad("home.manageRules", format!("`set` entry {i}: {e}"))),+ },+ (None, Some(p)) => parse_rule(p, &theme)?,+ (None, None) => return Err(bad("home.manageRules", format!("`set` entry {i} has no `rule` or `params`"))),+ };+ let stop_if_true = bool_param(x, "stopIfTrue").unwrap_or(false);+ rules.push(CondFormat { ranges, rule, priority: i as u32 + 1, stop_if_true });+ }+ return edit(s, |cx| {+ cx.sheet_mut(sheet)?.cond_formats = rules;+ Ok(Json::Null)+ });+ }+ if ["update", "duplicate", "delete", "moveUp", "moveDown"].iter().all(|k| p.get(*k).is_none()) {+ let d = s.doc()?;+ // Current Selection lists the rules that touch the selection (`selection`, else the+ // active sheet's); the default is the whole sheet.+ let only = match (str_param(p, "show"), str_param(p, "selection")) {+ (Some("selection"), Some(t)) => Some(parse_applies_to(t, &d.wb, sheet)?),+ (Some("selection"), None) if sheet == d.wb.active_sheet() => Some(d.selection.ranges.clone()),+ _ => None,+ };+ let list: Vec<Json> =+ d.wb.sheet(sheet)+ .map(|sh| {+ sh.cond_formats+ .iter()+ .enumerate()+ .filter(|(_, c)| only.as_ref().is_none_or(|sel| c.ranges.iter().any(|r| sel.iter().any(|x| x.intersects(r)))))+ .map(|(i, c)| {+ json!({+ "index": i,+ "ranges": c.ranges.iter().map(|r| r.a1()).collect::<Vec<_>>(),+ "appliesTo": applies_to(&c.ranges),+ "stopIfTrue": c.stop_if_true,+ "params": rule_params(&c.rule, &d.wb.theme),+ "rule": c.rule,+ })+ })+ .collect()+ })+ .unwrap_or_default();+ let total = d.wb.sheet(sheet).map_or(0, |sh| sh.cond_formats.len());+ return Ok(json!({"rules": list, "total": total})); }+ let update = u32_param(p, "update").map(|v| v as usize);+ let rule = match (update, p.get("rule")) {+ (Some(_), Some(r)) => Some(parse_rule(r, &s.doc()?.wb.theme)?),+ _ => None,+ };+ let ranges = match (update, str_param(p, "appliesTo")) {+ (Some(_), Some(t)) => Some(parse_applies_to(t, &s.doc()?.wb, sheet)?),+ _ => None,+ };+ let stop = bool_param(p, "stopIfTrue"); edit(s, |cx| { let sh = cx.sheet_mut(sheet)?;+ if let Some(cf) = update.and_then(|i| sh.cond_formats.get_mut(i)) {+ if let Some(r) = rule {+ cf.rule = r;+ }+ if let Some(r) = ranges {+ cf.ranges = r;+ }+ if let Some(b) = stop {+ cf.stop_if_true = b;+ }+ }+ // The copy goes right below the rule it copies.+ if let Some(i) = u32_param(p, "duplicate").map(|v| v as usize)+ && let Some(cf) = sh.cond_formats.get(i).cloned()+ {+ sh.cond_formats.insert(i + 1, cf);+ } // The count is taken again after each step: a delete shortens the list. if let Some(i) = u32_param(p, "delete").map(|v| v as usize) && i < sh.cond_formats.len()@@ -308,9 +640,7 @@ pub(super) fn manage_cf(s: &mut Session, p: &Json) -> Result<Json> { { sh.cond_formats.swap(i, i + 1); }- for (k, cf) in sh.cond_formats.iter_mut().enumerate() {- cf.priority = k as u32 + 1;- }+ renumber_cf(&mut sh.cond_formats); Ok(Json::Null) }) }@@ -21,6 +21,7 @@ mod cond_format_rules; mod data; mod find; mod format_cells;+mod formatting_rule; mod insert_function; mod macros; mod names;@@ -33,6 +34,7 @@ use analysis::toolpak_defaults; use charts::select_chart; use data::{abs, data_validation_fields, remove_duplicates_range, validation_defaults}; use find::go_to_entries;+use formatting_rule::cf_apply_preset; pub(crate) use names::insert_name; use names::pasteable_names; pub(crate) use show::build_params;@@ -80,6 +82,9 @@ pub struct Dialog { #[derive(Clone, Debug)] struct RefPick { key: &'static str,+ /// The object in the dialog's values that holds `key`, when the field isn't a value of its+ /// own (New Formatting Rule keeps its whole rule in `rule`).+ nested: Option<&'static str>, /// The sheet the dialog was opened on, by name: its index changes when sheets are inserted or /// moved while the dialog is open. References to another sheet carry that sheet's name. sheet: String,@@ -128,7 +133,11 @@ impl Dialog { /// Whether a click on the grid goes to a formula box of this dialog rather than the selection. pub fn picks_references(&self) -> bool {- self.pick.is_some()+ match self.pick.as_ref().and_then(|pick| pick.nested) {+ // New Formatting Rule points only while its formula box shows.+ Some(obj) => self.values.get(obj).and_then(|r| r.get("type")).and_then(Json::as_str) == Some("expression"),+ None => self.pick.is_some(),+ } } /// A click (`extend` false) or a drag reaching `cell`: writes the absolute reference at the@@ -138,7 +147,11 @@ impl Dialog { /// dialog's own. pub fn pick_reference(&mut self, cell: CellRef, active_sheet: &str, extend: bool) { let Some(pick) = self.pick.as_mut() else { return };- let text = self.values.get(pick.key).and_then(Json::as_str).unwrap_or("").to_string();+ let holder = match pick.nested {+ Some(obj) => self.values.get(obj).and_then(Json::as_object),+ None => Some(&self.values),+ };+ let text = holder.and_then(|h| h.get(pick.key)).and_then(Json::as_str).unwrap_or("").to_string(); let mut ed = crate::editor::EditState::new(0, 0, cell, text, true, false); ed.caret = pick.caret.min(ed.text.chars().count()); ed.point = pick.point;@@ -163,7 +176,16 @@ impl Dialog { pick.caret = ed.caret; pick.point = ed.point; pick.refocus = true;- self.values.insert(pick.key.into(), json!(ed.text));+ match pick.nested {+ Some(obj) => {+ if let Some(h) = self.values.get_mut(obj).and_then(Json::as_object_mut) {+ h.insert(pick.key.into(), json!(ed.text));+ }+ }+ None => {+ self.values.insert(pick.key.into(), json!(ed.text));+ }+ } } fn form(name: &str, title: &str, command: &'static str, fields: Vec<Field>, defaults: Json) -> Dialog {@@ -820,7 +842,16 @@ impl Dialog { ) }) .unwrap_or_default();- d.pick = Some(RefPick { key: "formula", sheet, caret: 0, point: None, anchor: CellRef::new(0, 0), refocus: false, applies_to });+ d.pick = Some(RefPick {+ key: "formula",+ nested: None,+ sheet,+ caret: 0,+ point: None,+ anchor: CellRef::new(0, 0),+ refocus: false,+ applies_to,+ }); } d.values.insert("type".into(), json!(ty)); d.values.insert("operator".into(), p("operator"));@@ -897,6 +928,56 @@ impl Dialog { dialog } "manageRules" => Dialog::custom("manageRules", "Conditional Formatting Rules Manager", json!({})),+ "newFormattingRule" => {+ // New Rule… starts from Format only cells that contain › Cell Value between;+ // Manage Rules › Edit Rule… passes the rule's index and params.+ let index = p("index").as_u64();+ let mut rule = p("rule").as_object().cloned().unwrap_or_else(|| {+ json!({"type": "cellIs", "operator": "between", "value": "", "value2": ""}).as_object().cloned().unwrap_or_default()+ });+ if !rule.contains_key("fill") {+ let theme = app.session.active().map(|doc| doc.wb.theme.clone()).unwrap_or_default();+ let preset = rule.get("preset").and_then(Json::as_str).unwrap_or("lightRedFillDarkRedText").to_string();+ cf_apply_preset(&mut rule, &preset, &theme);+ }+ let title = if index.is_some() { "Edit Formatting Rule" } else { "New Formatting Rule" };+ // The sheet (by name: inserting or moving sheets changes its index) and the cells+ // the rule applies to are fixed as the dialog opens, so pointing at cells for the+ // formula, on this sheet or another, doesn't move the rule.+ let (sheet, range) = app+ .session+ .active()+ .map(|doc| {+ let sheet = match p("sheet") {+ Json::Null => doc.wb.active(),+ v => v+ .as_u64()+ .and_then(|i| doc.wb.sheet(i as usize))+ .or_else(|| v.as_str().and_then(|n| doc.wb.sheet_index(n)).and_then(|i| doc.wb.sheet(i))),+ };+ let range = match p("range") {+ Json::Null if index.is_none() => json!(doc.selection.ranges.iter().map(RangeRef::a1).collect::<Vec<_>>().join(",")),+ r => r,+ };+ (sheet.map(|sh| sh.name.clone()).unwrap_or_default(), range)+ })+ .unwrap_or_default();+ let sheet_param = if sheet.is_empty() { p("sheet") } else { json!(sheet) };+ let mut d = Dialog::custom("newFormattingRule", title, json!({"rule": rule, "index": index, "range": range, "sheet": sheet_param}));+ d.command = Some(if index.is_some() { "home.manageRules" } else { "home.conditionalFormat" });+ let applies_to = range.as_str().unwrap_or_default().to_string();+ d.pick = Some(RefPick {+ key: "formula",+ nested: Some("rule"),+ sheet,+ caret: 0,+ point: None,+ anchor: CellRef::new(0, 0),+ refocus: false,+ applies_to,+ });+ d+ } "commandSearch" => Dialog::custom("commandSearch", "Search Commands", json!({})), "agents" => Dialog::custom("agents", "Agent Control", json!({})), "about" => {new file mode 100644@@ -0,0 +1,604 @@+use super::show::{ok_cancel, track_pick};+use super::*;++/// The six rule types in New Formatting Rule, with the rule `type` each starts from.+const CF_RULE_KINDS: [(&str, &str); 6] = [+ ("colorScale", "Format all cells based on their values"),+ ("cellIs", "Format only cells that contain"),+ ("top10", "Format only top or bottom ranked values"),+ ("aboveAverage", "Format only values that are above or below average"),+ ("duplicate", "Format only unique or duplicate values"),+ ("expression", "Use a formula to determine which cells to format"),+];++const CF_PRESETS: [(&str, &str); 6] = [+ ("lightRedFillDarkRedText", "Light Red Fill with Dark Red Text"),+ ("yellowFillDarkYellowText", "Yellow Fill with Dark Yellow Text"),+ ("greenFillDarkGreenText", "Green Fill with Dark Green Text"),+ ("lightRedFill", "Light Red Fill"),+ ("redText", "Red Text"),+ ("redBorder", "Red Border"),+];++const CF_OPERATORS: [(&str, &str); 8] = [+ ("between", "CF|between"),+ ("notBetween", "CF|not between"),+ ("equal", "CF|equal to"),+ ("notEqual", "CF|not equal to"),+ ("greater", "CF|greater than"),+ ("less", "CF|less than"),+ ("greaterOrEqual", "CF|greater than or equal to"),+ ("lessOrEqual", "CF|less than or equal to"),+];++const CF_PERIODS: [(&str, &str); 10] = [+ ("yesterday", "Yesterday"),+ ("today", "Today"),+ ("tomorrow", "Tomorrow"),+ ("last7Days", "In the last 7 days"),+ ("lastWeek", "Last week"),+ ("thisWeek", "This week"),+ ("nextWeek", "Next week"),+ ("lastMonth", "Last month"),+ ("thisMonth", "This month"),+ ("nextMonth", "Next month"),+];++const CF_ICON_SETS: [(&str, &str); 6] = [+ ("3Arrows", "3 Arrows"),+ ("3TrafficLights1", "3 Traffic Lights"),+ ("3Symbols", "3 Symbols"),+ ("4Rating", "4 Ratings"),+ ("5Arrows", "5 Arrows"),+ ("5Quarters", "5 Quarters"),+];++/// Which of [`CF_RULE_KINDS`] a rule `type` belongs to.+fn cf_kind_of(ty: &str) -> usize {+ match ty {+ "colorScale" | "dataBar" | "iconSet" => 0,+ "top10" => 2,+ "aboveAverage" => 3,+ "duplicate" | "unique" => 4,+ "expression" => 5,+ _ => 1,+ }+}++fn cf_str(rule: &Map<String, Json>, key: &str) -> String {+ match rule.get(key) {+ Some(Json::String(s)) => s.clone(),+ Some(Json::Null) | None => String::new(),+ Some(v) => v.to_string(),+ }+}++fn cf_flag(rule: &Map<String, Json>, key: &str) -> bool {+ rule.get(key).and_then(Json::as_bool).unwrap_or(false)+}++fn hex_color(s: &str) -> Option<Color32> {+ let h = s.trim().strip_prefix('#')?;+ let v = u32::from_str_radix(h.get(..6)?, 16).ok()?;+ Some(Color32::from_rgb((v >> 16) as u8, (v >> 8) as u8, v as u8))+}++/// Sets a rule's plain format overrides to a Highlight Cells preset.+pub(super) fn cf_apply_preset(rule: &mut Map<String, Json>, preset: &str, theme: &gridcraft_engine::model::Theme) {+ use gridcraft_engine::model::{BorderStyle, PatternType, Underline};+ let st = gridcraft_engine::cmd::insert::preset_style(preset);+ let hex = |c: gridcraft_engine::model::Color| c.hex(theme);+ rule.insert("fill".into(), json!(if st.fill.pattern == PatternType::None { None } else { hex(st.fill.fg) }.unwrap_or_else(|| "none".into())));+ rule.insert("fontColor".into(), json!(hex(st.font.color).unwrap_or_else(|| "auto".into())));+ let border = (st.borders.left.style != BorderStyle::None).then(|| hex(st.borders.left.color)).flatten();+ rule.insert("border".into(), json!(border.unwrap_or_else(|| "none".into())));+ rule.insert("bold".into(), json!(st.font.bold));+ rule.insert("italic".into(), json!(st.font.italic));+ rule.insert("underline".into(), json!(st.font.underline != Underline::None));+ rule.insert("strike".into(), json!(st.font.strike));+ rule.remove("preset");+ rule.remove("style");+}++/// A combo box over `(value, label)` pairs for `rule[key]`; true when the user picked one.+fn cf_combo(ui: &mut egui::Ui, id: &str, rule: &mut Map<String, Json>, key: &str, options: &[(&str, &str)], width: f32) -> bool {+ let lang = crate::i18n::current(ui.ctx());+ let current = cf_str(rule, key);+ let label = options.iter().find(|(v, _)| *v == current).or(options.first()).map(|(_, l)| lang.tr(l)).unwrap_or_default();+ let mut picked = None;+ egui::ComboBox::from_id_salt(("cfRule", id)).width(width).selected_text(label).show_ui(ui, |ui| {+ for (v, l) in options {+ if ui.selectable_label(*v == current, lang.tr(l)).clicked() {+ picked = Some(*v);+ }+ }+ });+ if let Some(v) = picked {+ rule.insert(key.into(), json!(v));+ }+ picked.is_some()+}++fn cf_text(ui: &mut egui::Ui, rule: &mut Map<String, Json>, key: &str, width: f32) {+ let mut text = cf_str(rule, key);+ if ui.add(egui::TextEdit::singleline(&mut text).desired_width(width)).changed() {+ rule.insert(key.into(), json!(text));+ }+}++/// A swatch and a menu with our colour palette; returns the picked hex or `none`.+fn cf_color_button(ui: &mut egui::Ui, label: &str, current: Option<Color32>, none_label: &str, colors: &[u32; 12]) -> Option<String> {+ let lang = crate::i18n::current(ui.ctx());+ let mut picked = None;+ ui.horizontal(|ui| {+ let (r, _) = ui.allocate_exact_size(vec2(14.0, 14.0), Sense::hover());+ let border = ui.visuals().widgets.inactive.fg_stroke.color;+ match current {+ Some(c) => ui.painter().rect_filled(r, 2.0, c),+ None => ui.painter().line_segment([r.left_bottom(), r.right_top()], Stroke::new(1.0, border)),+ };+ ui.painter().rect_stroke(r, 2.0, Stroke::new(1.0, border), StrokeKind::Inside);+ ui.menu_button(lang.tr(label), |ui| {+ if let Some(c) = crate::widgets::color_palette(ui, colors, lang.tr(none_label)) {+ picked = Some(c);+ ui.close();+ }+ });+ });+ picked+}++/// Fill, font and border of a rule's format, as a cell would show them.+fn cf_preview(ui: &mut egui::Ui, rule: &Map<String, Json>, size: egui::Vec2) {+ let lang = crate::i18n::current(ui.ctx());+ let fill = hex_color(&cf_str(rule, "fill"));+ let font = hex_color(&cf_str(rule, "fontColor"));+ let border = hex_color(&cf_str(rule, "border"));+ let plain = fill.is_none() && font.is_none() && border.is_none() && !["bold", "italic", "underline", "strike"].iter().any(|k| cf_flag(rule, k));+ let (r, _) = ui.allocate_exact_size(size, Sense::hover());+ let p = ui.painter();+ p.rect_filled(r, 0.0, fill.unwrap_or(Color32::WHITE));+ p.rect_stroke(r, 0.0, Stroke::new(if border.is_some() { 1.5 } else { 1.0 }, border.unwrap_or(Color32::from_gray(190))), StrokeKind::Inside);+ if plain {+ p.text(r.center(), egui::Align2::CENTER_CENTER, lang.tr("No Format Set"), egui::FontId::proportional(12.0), Color32::from_gray(90));+ return;+ }+ let mut text = egui::RichText::new("AaBbCcYyZz").color(font.unwrap_or(Color32::BLACK)).size(12.5);+ if cf_flag(rule, "bold") {+ text = text.strong();+ }+ if cf_flag(rule, "italic") {+ text = text.italics();+ }+ if cf_flag(rule, "underline") {+ text = text.underline();+ }+ if cf_flag(rule, "strike") {+ text = text.strikethrough();+ }+ let galley = egui::WidgetText::from(text).into_galley(ui, Some(egui::TextWrapMode::Extend), r.width(), egui::FontSelection::Default);+ let pos = r.center() - galley.size() / 2.0;+ ui.painter().galley(pos, galley, Color32::BLACK);+}++/// The cell format a rule applies: preset, colours, font styles and a preview.+fn cf_format_editor(ui: &mut egui::Ui, rule: &mut Map<String, Json>, theme: &gridcraft_engine::model::Theme) {+ let lang = crate::i18n::current(ui.ctx());+ ui.horizontal(|ui| {+ ui.label(lang.tr("Preview:"));+ cf_preview(ui, rule, vec2(200.0, 26.0));+ let mut preset = Map::new();+ preset.insert("p".into(), json!("custom"));+ let mut options = vec![("custom", "Custom Format")];+ options.extend(CF_PRESETS);+ if cf_combo(ui, "preset", &mut preset, "p", &options, 220.0) {+ let p = cf_str(&preset, "p");+ if p != "custom" {+ cf_apply_preset(rule, &p, theme);+ }+ }+ });+ ui.horizontal(|ui| {+ let colors = theme.colors;+ if let Some(c) = cf_color_button(ui, "CF|Fill", hex_color(&cf_str(rule, "fill")), "No Color", &colors) {+ rule.insert("fill".into(), json!(c));+ }+ if let Some(c) = cf_color_button(ui, "Font Color", hex_color(&cf_str(rule, "fontColor")), "Automatic", &colors) {+ rule.insert("fontColor".into(), json!(if c == "none" { "auto".into() } else { c }));+ }+ if let Some(c) = cf_color_button(ui, "CF|Border", hex_color(&cf_str(rule, "border")), "No Border", &colors) {+ rule.insert("border".into(), json!(c));+ }+ for (key, label, tip) in [("bold", "B", "Bold"), ("italic", "I", "Italic"), ("underline", "U", "Underline"), ("strike", "S", "Strikethrough")]+ {+ let mut text = egui::RichText::new(label);+ text = match key {+ "bold" => text.strong(),+ "italic" => text.italics(),+ "underline" => text.underline(),+ _ => text.strikethrough(),+ };+ let on = cf_flag(rule, key);+ if ui.add(egui::Button::selectable(on, text).min_size(vec2(22.0, 20.0))).on_hover_text(lang.tr(tip)).clicked() {+ rule.insert(key.into(), json!(!on));+ }+ }+ if ui.button(lang.tr("Clear")).clicked() {+ for (k, v) in [("fill", json!("none")), ("fontColor", json!("auto")), ("border", json!("none"))] {+ rule.insert(k.into(), v);+ }+ for k in ["bold", "italic", "underline", "strike"] {+ rule.insert(k.into(), json!(false));+ }+ rule.remove("style");+ }+ });+}++/// One column of a colour scale or data bar: the threshold type, its value and colour.+fn cf_threshold(ui: &mut egui::Ui, rule: &mut Map<String, Json>, key: &str, label: &str, value_width: f32) {+ let lang = crate::i18n::current(ui.ctx());+ let ends: &[(&str, &str)] = match key {+ "min" => &[("min", "Lowest Value")],+ "max" => &[("max", "Highest Value")],+ _ => &[],+ };+ let mut options: Vec<(&str, &str)> = ends.to_vec();+ options.extend([("num", "Number"), ("percent", "Percent"), ("formula", "Formula"), ("percentile", "Percentile")]);+ let mut t = rule.get(key).and_then(Json::as_object).cloned().unwrap_or_default();+ if !t.contains_key("type") {+ let default = if key == "mid" { "percentile" } else { key };+ t.insert("type".into(), json!(default));+ if key == "mid" {+ t.insert("value".into(), json!("50"));+ }+ }+ ui.vertical(|ui| {+ ui.strong(lang.tr(label));+ cf_combo(ui, &format!("{key}Type"), &mut t, "type", &options, value_width);+ if !matches!(cf_str(&t, "type").as_str(), "min" | "max") {+ cf_text(ui, &mut t, "value", value_width);+ }+ });+ rule.insert(key.into(), Json::Object(t));+}++/// Rule type 1: colour scales, data bars and icon sets.+fn cf_values_editor(ui: &mut egui::Ui, rule: &mut Map<String, Json>, theme: &gridcraft_engine::model::Theme) {+ let lang = crate::i18n::current(ui.ctx());+ let ty = cf_str(rule, "type");+ let colors = rule.get("colors").and_then(Json::as_array).map_or(0, Vec::len);+ let mut style = Map::new();+ style.insert("s".into(), json!(if ty == "colorScale" { if colors == 2 { "2color" } else { "3color" } } else { ty.as_str() }));+ ui.horizontal(|ui| {+ ui.label(lang.tr("Format Style:"));+ let options = [("2color", "2-Color Scale"), ("3color", "3-Color Scale"), ("dataBar", "Data Bar"), ("iconSet", "Icon Sets")];+ if cf_combo(ui, "formatStyle", &mut style, "s", &options, 160.0) {+ for k in ["colors", "min", "mid", "max", "color", "set", "thresholds"] {+ rule.remove(k);+ }+ match cf_str(&style, "s").as_str() {+ "2color" => {+ rule.insert("type".into(), json!("colorScale"));+ rule.insert("colors".into(), json!(["#F8696B", "#63BE7B"]));+ }+ "3color" => {+ rule.insert("type".into(), json!("colorScale"));+ rule.insert("colors".into(), json!(["#F8696B", "#FFEB84", "#63BE7B"]));+ }+ "dataBar" => {+ rule.insert("type".into(), json!("dataBar"));+ rule.insert("color".into(), json!("#638EC6"));+ }+ _ => {+ rule.insert("type".into(), json!("iconSet"));+ rule.insert("set".into(), json!("3TrafficLights1"));+ }+ }+ }+ });+ ui.add_space(6.0);+ let palette = theme.colors;+ match cf_str(rule, "type").as_str() {+ "colorScale" => {+ let mut cols: Vec<String> = rule+ .get("colors")+ .and_then(Json::as_array)+ .map(|a| a.iter().filter_map(|c| c.as_str().map(str::to_string)).collect())+ .unwrap_or_else(|| ["#F8696B", "#FFEB84", "#63BE7B"].map(String::from).to_vec());+ let keys: &[(&str, &str)] = if cols.len() == 2 {+ &[("min", "Minimum"), ("max", "Maximum")]+ } else {+ &[("min", "Minimum"), ("mid", "Midpoint"), ("max", "Maximum")]+ };+ ui.horizontal(|ui| {+ for (i, (key, label)) in keys.iter().enumerate() {+ ui.vertical(|ui| {+ cf_threshold(ui, rule, key, label, 140.0);+ if let Some(slot) = cols.get_mut(i)+ && let Some(c) = cf_color_button(ui, "Color", hex_color(slot), "Automatic", &palette)+ && c != "none"+ {+ *slot = c;+ }+ });+ }+ });+ // The scale itself, as the cells will grade.+ let (r, _) = ui.allocate_exact_size(vec2(ui.available_width().min(420.0), 14.0), Sense::hover());+ let stops: Vec<Color32> = cols.iter().filter_map(|c| hex_color(c)).collect();+ let n = 40;+ for k in 0..n {+ let t = k as f32 / (n - 1) as f32 * (stops.len().max(2) - 1) as f32;+ let i = (t.floor() as usize).min(stops.len().saturating_sub(2));+ let (a, b) = (stops.get(i).copied().unwrap_or(Color32::WHITE), stops.get(i + 1).copied().unwrap_or(Color32::WHITE));+ let f = t - i as f32;+ let c = Color32::from_rgb(+ (a.r() as f32 + (b.r() as f32 - a.r() as f32) * f) as u8,+ (a.g() as f32 + (b.g() as f32 - a.g() as f32) * f) as u8,+ (a.b() as f32 + (b.b() as f32 - a.b() as f32) * f) as u8,+ );+ let w = r.width() / n as f32;+ ui.painter().rect_filled(egui::Rect::from_min_size(r.min + vec2(w * k as f32, 0.0), vec2(w + 0.5, r.height())), 0.0, c);+ }+ rule.insert("colors".into(), json!(cols));+ }+ "dataBar" => {+ ui.horizontal(|ui| {+ cf_threshold(ui, rule, "min", "Minimum", 140.0);+ cf_threshold(ui, rule, "max", "Maximum", 140.0);+ });+ ui.horizontal(|ui| {+ if let Some(c) = cf_color_button(ui, "Bar Color", hex_color(&cf_str(rule, "color")), "Automatic", &palette)+ && c != "none"+ {+ rule.insert("color".into(), json!(c));+ }+ let mut gradient = rule.get("gradient").and_then(Json::as_bool).unwrap_or(true);+ if ui.checkbox(&mut gradient, lang.tr("Gradient Fill")).changed() {+ rule.insert("gradient".into(), json!(gradient));+ }+ let mut only = !rule.get("showValue").and_then(Json::as_bool).unwrap_or(true);+ if ui.checkbox(&mut only, lang.tr("Show Bar Only")).changed() {+ rule.insert("showValue".into(), json!(!only));+ }+ });+ }+ _ => {+ ui.horizontal(|ui| {+ ui.label(lang.tr("Icon Style:"));+ if cf_combo(ui, "iconSet", rule, "set", &CF_ICON_SETS, 160.0) {+ // Another set has another number of icons: back to the default thresholds.+ rule.remove("thresholds");+ }+ let set = cf_str(rule, "set");+ let n = gridcraft_engine::cf::icon_count(&set);+ for i in 0..n {+ let (r, _) = ui.allocate_exact_size(vec2(16.0, 16.0), Sense::hover());+ crate::grid::paint_cf_icon(ui.painter(), r.shrink(2.0), &set, i);+ }+ });+ ui.horizontal(|ui| {+ let mut reverse = cf_flag(rule, "reverse");+ if ui.checkbox(&mut reverse, lang.tr("Reverse Icon Order")).changed() {+ rule.insert("reverse".into(), json!(reverse));+ }+ let mut only = !rule.get("showValue").and_then(Json::as_bool).unwrap_or(true);+ if ui.checkbox(&mut only, lang.tr("Show Icon Only")).changed() {+ rule.insert("showValue".into(), json!(!only));+ }+ });+ }+ }+}++/// Rule type 2: cell value, specific text, dates, blanks and errors.+fn cf_contains_editor(ui: &mut egui::Ui, rule: &mut Map<String, Json>) {+ let lang = crate::i18n::current(ui.ctx());+ let ty = cf_str(rule, "type");+ let group = match ty.as_str() {+ "containsText" | "notContainsText" | "beginsWith" | "endsWith" => "text",+ "timePeriod" => "date",+ "blanks" | "noBlanks" | "errors" | "noErrors" => ty.as_str(),+ _ => "cellIs",+ };+ ui.label(lang.tr("Format only cells with:"));+ ui.horizontal(|ui| {+ let mut g = Map::new();+ g.insert("g".into(), json!(group));+ let groups = [+ ("cellIs", "Cell Value"),+ ("text", "Specific Text"),+ ("date", "Dates Occurring"),+ ("blanks", "Blanks"),+ ("noBlanks", "No Blanks"),+ ("errors", "Errors"),+ ("noErrors", "No Errors"),+ ];+ if cf_combo(ui, "contains", &mut g, "g", &groups, 130.0) {+ let t = match cf_str(&g, "g").as_str() {+ "text" => "containsText",+ "date" => "timePeriod",+ "cellIs" => "cellIs",+ other => other,+ }+ .to_string();+ rule.insert("type".into(), json!(t));+ }+ match cf_str(rule, "type").as_str() {+ "cellIs" => {+ if !rule.contains_key("operator") {+ rule.insert("operator".into(), json!("between"));+ }+ cf_combo(ui, "operator", rule, "operator", &CF_OPERATORS, 170.0);+ cf_text(ui, rule, "value", 90.0);+ if matches!(cf_str(rule, "operator").as_str(), "between" | "notBetween") {+ ui.label(lang.tr("and"));+ cf_text(ui, rule, "value2", 90.0);+ }+ }+ "containsText" | "notContainsText" | "beginsWith" | "endsWith" => {+ let ops = [+ ("containsText", "containing"),+ ("notContainsText", "not containing"),+ ("beginsWith", "beginning with"),+ ("endsWith", "ending with"),+ ];+ cf_combo(ui, "textOp", rule, "type", &ops, 140.0);+ cf_text(ui, rule, "text", 140.0);+ }+ "timePeriod" => {+ if !rule.contains_key("period") {+ rule.insert("period".into(), json!("yesterday"));+ }+ cf_combo(ui, "period", rule, "period", &CF_PERIODS, 160.0);+ }+ _ => {}+ }+ });+ if cf_str(rule, "type") == "cellIs" {+ ui.label(egui::RichText::new(lang.tr("Type a number, text, or a formula such as =$B$1.")).small().weak());+ }+}++/// New Formatting Rule (or Edit Formatting Rule from Manage Rules):+/// the rule type, then the rule's description and format.+pub(super) fn formatting_rule(app: &mut SheetApp, ui: &mut egui::Ui, d: &mut Dialog, confirm: &mut bool, open: &mut bool) {+ let lang = app.ui.language;+ let theme = app.session.active().map(|doc| doc.wb.theme.clone()).unwrap_or_default();+ let mut rule = d.values.get("rule").and_then(Json::as_object).cloned().unwrap_or_default();+ let kind = cf_kind_of(&cf_str(&rule, "type"));+ ui.label(lang.tr("Select a Rule Type:"));+ egui::Frame::group(ui.style()).show(ui, |ui| {+ ui.set_width(ui.available_width());+ for (i, (start, label)) in CF_RULE_KINDS.iter().enumerate() {+ if ui.add(egui::Button::selectable(i == kind, format!("► {}", lang.tr(label)))).clicked() && i != kind {+ rule.insert("type".into(), json!(start));+ match *start {+ "colorScale" => {+ rule.insert("colors".into(), json!(["#F8696B", "#FFEB84", "#63BE7B"]));+ }+ "top10" => {+ rule.insert("rank".into(), json!(10));+ }+ _ => {}+ }+ }+ }+ });+ ui.add_space(6.0);+ ui.label(lang.tr("Edit the Rule Description:"));+ egui::Frame::group(ui.style()).show(ui, |ui| {+ ui.set_width(ui.available_width());+ match cf_kind_of(&cf_str(&rule, "type")) {+ 0 => cf_values_editor(ui, &mut rule, &theme),+ 1 => cf_contains_editor(ui, &mut rule),+ 2 => {+ ui.label(lang.tr("Format values that rank in the:"));+ ui.horizontal(|ui| {+ let mut m = Map::new();+ m.insert("b".into(), json!(if cf_flag(&rule, "bottom") { "bottom" } else { "top" }));+ if cf_combo(ui, "topBottom", &mut m, "b", &[("top", "CF|Top"), ("bottom", "CF|Bottom")], 90.0) {+ rule.insert("bottom".into(), json!(cf_str(&m, "b") == "bottom"));+ }+ let mut rank = rule.get("rank").and_then(Json::as_u64).unwrap_or(10);+ if ui.add(egui::DragValue::new(&mut rank).range(1..=1000)).changed() {+ rule.insert("rank".into(), json!(rank));+ }+ let mut percent = cf_flag(&rule, "percent");+ if ui.checkbox(&mut percent, lang.tr("% of the selected range")).changed() {+ rule.insert("percent".into(), json!(percent));+ }+ });+ }+ 3 => {+ ui.label(lang.tr("Format values that are:"));+ let current = match (cf_flag(&rule, "below"), cf_flag(&rule, "equal"), rule.get("stdDev").and_then(Json::as_u64).unwrap_or(0)) {+ (b, _, n @ 1..) => format!("{}{n}", if b { "sdBelow" } else { "sdAbove" }),+ (false, false, _) => "above".into(),+ (true, false, _) => "below".into(),+ (false, true, _) => "equalAbove".into(),+ (true, true, _) => "equalBelow".into(),+ };+ let mut m = Map::new();+ m.insert("a".into(), json!(current));+ let options = [+ ("above", "CF|above"),+ ("below", "CF|below"),+ ("equalAbove", "equal or above"),+ ("equalBelow", "equal or below"),+ ("sdAbove1", "1 std dev above"),+ ("sdBelow1", "1 std dev below"),+ ("sdAbove2", "2 std dev above"),+ ("sdBelow2", "2 std dev below"),+ ("sdAbove3", "3 std dev above"),+ ("sdBelow3", "3 std dev below"),+ ];+ ui.horizontal(|ui| {+ if cf_combo(ui, "average", &mut m, "a", &options, 150.0) {+ let a = cf_str(&m, "a");+ let sd = a.strip_prefix("sdAbove").or_else(|| a.strip_prefix("sdBelow")).and_then(|n| n.parse::<u64>().ok()).unwrap_or(0);+ rule.insert("below".into(), json!(a.contains("elow")));+ rule.insert("equal".into(), json!(a.starts_with("equal")));+ rule.insert("stdDev".into(), json!(sd));+ }+ ui.label(lang.tr("the average for the selected range"));+ });+ }+ 4 => {+ ui.label(lang.tr("Format all:"));+ ui.horizontal(|ui| {+ cf_combo(ui, "uniqueDup", &mut rule, "type", &[("duplicate", "CF|duplicate"), ("unique", "CF|unique")], 110.0);+ ui.label(lang.tr("values in the selected range"));+ });+ }+ _ => {+ ui.label(lang.tr("Format values where this formula is true:"));+ let mut f = cf_str(&rule, "formula");+ if f.is_empty() {+ f.push('=');+ }+ let r = ui.add(egui::TextEdit::singleline(&mut f).desired_width(ui.available_width()).font(egui::TextStyle::Monospace));+ if r.changed() {+ rule.insert("formula".into(), json!(f));+ }+ // A click or drag on the grid writes a reference at the caret (`Dialog::pick_reference`).+ if let Some(pick) = d.pick.as_mut() {+ track_pick(pick, &r, r.changed());+ }+ let top_left = d.values.get("range").and_then(Json::as_str).and_then(|r| r.split([':', ',']).next()).map(str::to_string);+ let top_left = top_left.or_else(|| app.session.active().map(|doc| doc.selection.current().start.a1())).unwrap_or_else(|| "A1".into());+ ui.label(+ egui::RichText::new(format!(+ "{} {top_left}. {}",+ lang.tr("The formula is written for the top-left cell of the range,"),+ lang.tr("Example: =$B2>100 highlights each row whose column B is over 100.")+ ))+ .small()+ .weak(),+ );+ }+ }+ if kind != 0 && cf_kind_of(&cf_str(&rule, "type")) != 0 {+ ui.add_space(8.0);+ ui.separator();+ cf_format_editor(ui, &mut rule, &theme);+ }+ });+ // No rule without something to compare against.+ let missing = match cf_str(&rule, "type").as_str() {+ "cellIs" => {+ cf_str(&rule, "value").trim().is_empty()+ || (matches!(cf_str(&rule, "operator").as_str(), "between" | "notBetween") && cf_str(&rule, "value2").trim().is_empty())+ }+ "expression" => cf_str(&rule, "formula").trim().trim_start_matches('=').trim().is_empty(),+ "containsText" | "notContainsText" | "beginsWith" | "endsWith" => cf_str(&rule, "text").is_empty(),+ _ => false,+ };+ d.values.insert("rule".into(), Json::Object(rule));+ ok_cancel(ui, confirm, open);+ if *confirm && missing {+ *confirm = false;+ app.message = Some((d.title.clone(), lang.tr("Enter a value, text or formula for the rule.").into()));+ }+}@@ -6,6 +6,7 @@ use super::cond_format_rules::manage_rules; use super::data::{advanced_filter_form, data_validation_form, sort}; use super::find::{find, go_to}; use super::format_cells::format_cells;+use super::formatting_rule::formatting_rule; use super::insert_function::insert_function; use super::macros::macros; use super::names::{name_manager, paste_name};@@ -71,6 +72,16 @@ pub(crate) fn build_params(d: &Dialog) -> Json { } } }+ "newFormattingRule" => {+ let rule = d.values.get("rule").cloned().unwrap_or(Json::Null);+ v = match d.values.get("index").and_then(Json::as_u64) {+ Some(i) => json!({"update": i, "rule": rule, "sheet": d.values.get("sheet")}),+ None => match d.values.get("range").and_then(Json::as_str) {+ Some(r) => json!({"range": r, "rule": rule, "sheet": d.values.get("sheet")}),+ None => json!({"rule": rule}),+ },+ };+ } "cfQuick" => { let mut rule = Map::new(); for (k, val) in &d.values {@@ -117,6 +128,7 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { "formatCells" => 560.0, "about" => 660.0, "insertFunction" | "commandSearch" | "nameManager" | "manageRules" | "journal" | "agents" | "macros" => 520.0,+ "newFormattingRule" => 560.0, "recommendedCharts" => 600.0, "analysisToolPak" => 420.0, "solver" => 460.0,@@ -152,6 +164,7 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { "nameManager" => name_manager(app, ui, &mut d, &mut open), "pasteName" => paste_name(app, ui, &mut d, &mut confirm, &mut open), "manageRules" => manage_rules(app, ui),+ "newFormattingRule" => formatting_rule(app, ui, &mut d, &mut confirm, &mut open), "recommendedCharts" => recommended_charts(app, ui, &mut d, &mut confirm, &mut open), "selectData" => select_data(app, ui, &mut d, &mut confirm, &mut open), "commandSearch" => command_search(app, ui, &mut d, &mut confirm),@@ -459,7 +472,7 @@ fn take_first_focus(d: &mut Dialog, r: &egui::Response) { /// Keeps a picking formula box's caret, and gives it the keyboard back after a grid click put a /// reference in it (the click took the focus), its caret after the reference.-fn track_pick(pick: &mut RefPick, r: &egui::Response, typed: bool) {+pub(super) fn track_pick(pick: &mut RefPick, r: &egui::Response, typed: bool) { if typed { pick.point = None; }@@ -27,11 +27,11 @@ mod selection; mod show; mod tables; -pub(crate) use cell_paint::paint_border; #[cfg(test)] pub(crate) use cell_paint::{cell_text_y, rotated_text_pos, wrapped_cell_text_y}; #[cfg(test)] use cell_paint::{center_across_end, display_text, look, numfmt_color, painted_halign};+pub(crate) use cell_paint::{paint_border, paint_cf_icon}; pub use editor::validation_arrow; #[cfg(test)] use editor::{editor_slack, editor_width, list_picker_height};@@ -253,7 +253,7 @@ pub(crate) fn paint_border(p: &Painter, pts: [Pos2; 2], style: BorderStyle, col: } } -pub(super) fn paint_cf_icon(p: &Painter, r: Rect, set: &str, idx: usize) {+pub(crate) fn paint_cf_icon(p: &Painter, r: Rect, set: &str, idx: usize) { let n = gridcraft_engine::cf::icon_count(set); let palette3 = [Color32::from_rgb(0xD1, 0x3B, 0x2F), Color32::from_rgb(0xF2, 0xB8, 0x2E), Color32::from_rgb(0x2E, 0x9E, 0x4F)]; let col = if n <= 3 {@@ -767,7 +767,7 @@ fn cf_menu(app: &mut SheetApp, ui: &mut Ui) { }); ui.separator(); if ui.button("New Rule…").clicked() {- app.open_dialog("cfQuick", json!({"type": "expression", "title": "New Formatting Rule"}));+ app.open_dialog("newFormattingRule", json!({})); } ui.menu_button("Clear Rules", |ui| { menu_items(@@ -9,6 +9,15 @@ use serde_json::json; type Harness = egui_kittest::Harness<'static, SheetApp>; fn harness() -> Harness {+ harness_with(|app| app.open_dialog("cfQuick", json!({"type": "expression", "title": "New Formatting Rule"})))+}++/// Home › Conditional Formatting › New Rule…, with Use a formula picked.+fn new_rule() -> Harness {+ harness_with(|app| app.open_dialog("newFormattingRule", json!({"rule": {"type": "expression"}})))+}++fn harness_with(open: impl FnOnce(&mut SheetApp)) -> Harness { let mut session = Session::new(); session.new_workbook(); session.execute("home.insertSheet", json!({})).unwrap();@@ -22,13 +31,23 @@ fn harness() -> Harness { crate::common::app(session), ); h.run_steps(4);- h.state_mut().open_dialog("cfQuick", json!({"type": "expression", "title": "New Formatting Rule"}));+ open(h.state_mut()); h.run_steps(6); h } +/// The formula box's text: a value of its own in the quick dialog, the rule's in New Formatting Rule. fn formula(h: &Harness) -> String {- h.state().dialog.as_ref().and_then(|d| d.values.get("formula")).and_then(|v| v.as_str()).unwrap_or("").to_string()+ let d = h.state().dialog.as_ref();+ let v = d.and_then(|d| d.values.get("formula").or_else(|| d.values.get("rule").and_then(|r| r.get("formula"))));+ v.and_then(|v| v.as_str()).unwrap_or("").to_string()+}++/// The ranges of the rule added last on the sheet named `name`.+fn rule_ranges(h: &Harness, name: &str) -> Vec<String> {+ let wb = &h.state().session.active().unwrap().wb;+ let sheet = wb.sheets.iter().find(|s| s.name == name).unwrap();+ sheet.cond_formats.first().map(|cf| cf.ranges.iter().map(|r| r.a1()).collect()).unwrap_or_default() } fn selection(h: &Harness) -> String {@@ -195,3 +214,63 @@ fn the_rule_follows_its_sheet_when_sheets_move_while_the_dialog_is_open() { let cf = sheet1.cond_formats.last().expect("the rule stays on Sheet1"); assert_eq!(cf.ranges.iter().map(|r| r.a1()).collect::<Vec<_>>(), ["F8:G9"]); }++#[test]+fn new_rule_takes_references_clicked_on_the_grid() {+ let mut h = new_rule();+ click(&mut h, "C3");+ assert_eq!(formula(&h), "=$C$3");+ assert_eq!(selection(&h), "F8:G9", "the range the rule applies to is still selected");+ click(&mut h, "B2");+ assert_eq!(formula(&h), "=$B$2", "a second click replaces the reference");+ type_text(&mut h, ">0");+ assert_eq!(formula(&h), "=$B$2>0", "the box has the keyboard back");+}++#[test]+fn new_rule_applies_to_the_selection_it_opened_with() {+ let mut h = new_rule();+ click(&mut h, "B2");+ type_text(&mut h, ">0");+ // The selection changes while the dialog is open; the rule keeps the cells it opened for.+ h.state_mut().session.execute("selection.set", json!({"range": "A1"})).unwrap();+ h.run_steps(2);+ h.key_press(Key::Enter);+ h.run_steps(4);+ assert!(h.state().dialog.is_none(), "OK confirmed the rule");+ assert_eq!(rule_ranges(&h, "Sheet1"), ["F8:G9"]);+}++#[test]+fn new_rule_pointing_on_another_sheet_keeps_the_rule_on_its_own_sheet() {+ let mut h = new_rule();+ h.state_mut().session.execute("sheet.activate", json!({"sheet": 1})).unwrap();+ h.run_steps(3);+ click(&mut h, "A1");+ assert_eq!(formula(&h), "=Sheet2!$A$1");+ h.key_press(Key::Enter);+ h.run_steps(4);+ assert!(h.state().dialog.is_none(), "Enter confirmed the rule");+ assert!(rule_ranges(&h, "Sheet2").is_empty(), "nothing lands on the sheet pointed at");+ assert_eq!(rule_ranges(&h, "Sheet1"), ["F8:G9"]);+}++#[test]+fn new_rule_follows_its_sheet_when_sheets_move_while_it_is_open() {+ let mut h = new_rule();+ click(&mut h, "B2");+ h.state_mut().session.execute("sheet.move", json!({"sheet": 0, "to": 1})).unwrap();+ h.run_steps(3);+ h.key_press(Key::Enter);+ h.run_steps(4);+ assert!(h.state().dialog.is_none(), "Enter confirmed the rule");+ assert!(rule_ranges(&h, "Sheet2").is_empty(), "nothing lands on the sheet that took index 0");+ assert_eq!(rule_ranges(&h, "Sheet1"), ["F8:G9"]);+}++#[test]+fn new_rule_without_a_formula_box_leaves_grid_clicks_alone() {+ let mut h = harness_with(|app| app.open_dialog("newFormattingRule", json!({})));+ click(&mut h, "B2");+ assert_eq!(selection(&h), "B2", "Cell Value rules don't take references from the grid");+}@@ -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-27 · **Change:** minor (New Rule… opens New Formatting Rule with every rule type and a format editor) · **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@@ -152,7 +152,8 @@ a character (e.g. Ctrl+Alt+A for `ä`) never trigger a shortcut, Ctrl+letter one ## Dialogs Format Cells, Insert Function/Formula Builder, Find and Replace, Go To and Go To Special, Sort,-Name Manager, Data Validation, Paste Special, Conditional Formatting manager, Page Setup, Goal+Name Manager, Data Validation, Paste Special, Conditional Formatting (New/Edit Formatting Rule with+the six rule types and a format editor; Rules Manager), Page Setup, Goal Seek, Scenario Manager, Data Analysis, Forecast Sheet and others exist. Missing or partial: Options, Solver, Power Query editor, Insert Object, Equation editor, Protect/Allow Edit Ranges, Workbook Links, Accessibility checker. Like Excel's, every dialog opens centred and moves by its@@ -187,6 +188,7 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---|+| 2026-10-27 | minor | Conditional formatting: New Rule… opens New Formatting Rule with the six rule types (cell values: colour scales, data bars, icon sets with their thresholds; cell value/text/dates/blanks/errors; top/bottom; above/below average and standard deviations; unique/duplicate; formula, with a hint about relative references; its formula box takes references clicked or dragged on the grid, and the rule keeps the sheet and cells it opened for, as Use a formula did since #2348) and a format editor (presets, fill, font colour, border, bold/italic/underline/strikethrough, preview); it opens as Edit Formatting Rule for a rule `home.manageRules` lists | | 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 |