collective
Browse / github.com/storytold/gridcraft / patch
Conditional formatting: Rules Manager
Conditional formatting: Rules Manager Part 3 of 4, split out of #2184. **Depends on #2732** (and #2731) — the change here is the last commit only (3a3ff1f). - Manage Rules shows a table (rule, format preview, applies to, Stop If True) and filters by Current Selection or This Worksheet. - New, Edit (through Edit Formatting Rule), Delete and Duplicate Rule, priority arrows, editable Applies to, Stop If True. - Cancel restores the rules the dialog opened with. Every action, New Rule… included, works on the sheet and selection the dialog opened with. - Replaces the minimal list in `dialogs/cond_format_rules.rs`. Stack: #2731 → #2732
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- Antxis77 (imported from pull request #2733, open)
- Base
45f5ab18e4e57bf50ee88d2526deb4b87c5b24cb- Size
- +1760 −118 in 14 files
- Review
- pending
- Shared
- 2026-10-28
- Id
0dda3ccec1d194ab9b000e59
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/cond_format_rules.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 crates/ui-egui/tests/it/cf_rules_manager.rs crates/ui-egui/tests/it/main.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; Conditional Formatting Rules Manager edits, duplicates, reorders and re-targets rules) · **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,9 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---|+| 2026-10-27 | Conditional Formatting Rules Manager: a table of the rules with their format and Applies to, filtered by Current Selection or This Worksheet; New, Edit, Delete and Duplicate Rule, Move Up/Move Down, Stop If True, and Cancel puts back the rules it opened with |+| 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 +245,9 @@ 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: Conditional Formatting Rules Manager |+| 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"));@@ -896,7 +927,72 @@ impl Dialog { dialog.result = Some(json!(names)); dialog }- "manageRules" => Dialog::custom("manageRules", "Conditional Formatting Rules Manager", json!({})),+ "manageRules" => {+ // What Cancel puts back: the active sheet's rules as the dialog opens.+ let sheet = app.session.active().map(|doc| doc.wb.active_sheet());+ // Current Selection and New Rule… keep to the selection the dialog opened with.+ let selection = app.session.active().map(|doc| doc.selection.ranges.iter().map(|r| r.a1()).collect::<Vec<_>>().join(","));+ let now = app.session.run("home.manageRules", json!({"sheet": sheet})).unwrap_or(Json::Null);+ let snapshot: Vec<Json> = now["rules"]+ .as_array()+ .map(|a| a.iter().map(|r| json!({"rule": r["rule"], "appliesTo": r["appliesTo"], "stopIfTrue": r["stopIfTrue"]})).collect())+ .unwrap_or_default();+ Dialog::custom(+ "manageRules",+ "Conditional Formatting Rules Manager",+ json!({"show": "selection", "sheet": sheet, "selection": selection, "snapshot": snapshot}),+ )+ }+ "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" => {@@ -1,28 +1,277 @@+use super::formatting_rule::{CF_OPERATORS, CF_PERIODS, cf_combo, cf_preview, hex_color}; use super::*; -pub(super) fn manage_rules(app: &mut SheetApp, ui: &mut egui::Ui) {- let list = app.session.run("home.manageRules", json!({})).unwrap_or(Json::Null);+/// The Rule column of Manage Rules.+fn cf_describe(p: &Json, lang: crate::i18n::Language) -> String {+ let s = |k: &str| match p.get(k) {+ Some(Json::String(s)) => s.clone(),+ Some(Json::Null) | None => String::new(),+ Some(v) => v.to_string(),+ };+ let label = |options: &[(&str, &str)], v: &str| {+ options.iter().find(|(k, _)| *k == v).map(|(_, l)| lang.tr(l).to_string()).unwrap_or_else(|| v.to_string())+ };+ let flag = |k: &str| p.get(k).and_then(Json::as_bool).unwrap_or(false);+ match s("type").as_str() {+ "cellIs" => {+ let op = s("operator");+ let symbol = match op.as_str() {+ "equal" => "=",+ "notEqual" => "<>",+ "greater" => ">",+ "less" => "<",+ "greaterOrEqual" => ">=",+ "lessOrEqual" => "<=",+ _ => "",+ };+ if symbol.is_empty() {+ format!("{} {} {} {} {}", lang.tr("Cell Value"), label(&CF_OPERATORS, &op), s("value"), lang.tr("and"), s("value2"))+ } else {+ format!("{} {symbol} {}", lang.tr("Cell Value"), s("value"))+ }+ }+ "expression" => format!("{} {}", lang.tr("Formula:"), s("formula")),+ "containsText" => format!("{} \"{}\"", lang.tr("Cell value contains"), s("text")),+ "notContainsText" => format!("{} \"{}\"", lang.tr("Cell value does not contain"), s("text")),+ "beginsWith" => format!("{} \"{}\"", lang.tr("Cell value begins with"), s("text")),+ "endsWith" => format!("{} \"{}\"", lang.tr("Cell value ends with"), s("text")),+ "blanks" => lang.tr("Blanks").into(),+ "noBlanks" => lang.tr("No Blanks").into(),+ "errors" => lang.tr("Errors").into(),+ "noErrors" => lang.tr("No Errors").into(),+ "duplicate" => lang.tr("Duplicate Values").into(),+ "unique" => lang.tr("Unique Values").into(),+ "timePeriod" => label(&CF_PERIODS, &s("period")),+ "top10" => {+ format!("{} {}{}", lang.tr(if flag("bottom") { "CF|Bottom" } else { "CF|Top" }), s("rank"), if flag("percent") { "%" } else { "" })+ }+ "aboveAverage" => {+ let sd = p.get("stdDev").and_then(Json::as_u64).unwrap_or(0);+ if sd > 0 {+ format!("{sd} {}", lang.tr(if flag("below") { "std dev below average" } else { "std dev above average" }))+ } else {+ lang.tr(match (flag("below"), flag("equal")) {+ (false, false) => "Above Average",+ (true, false) => "Below Average",+ (false, true) => "Equal to or Above Average",+ (true, true) => "Equal to or Below Average",+ })+ .into()+ }+ }+ "colorScale" => lang.tr("Graded Color Scale").into(),+ "dataBar" => lang.tr("Data Bar").into(),+ "iconSet" => lang.tr("Icon Set").into(),+ other => other.to_string(),+ }+}++/// The Format column of Manage Rules.+fn cf_format_cell(ui: &mut egui::Ui, p: &Json) {+ let size = vec2(130.0, 20.0);+ match p.get("type").and_then(Json::as_str).unwrap_or("") {+ "colorScale" => {+ let cols: Vec<Color32> = p+ .get("colors")+ .and_then(Json::as_array)+ .map(|a| a.iter().filter_map(|c| c.as_str().and_then(hex_color)).collect())+ .unwrap_or_default();+ let (r, _) = ui.allocate_exact_size(size, Sense::hover());+ let w = r.width() / cols.len().max(1) as f32;+ for (i, c) in cols.iter().enumerate() {+ ui.painter().rect_filled(egui::Rect::from_min_size(r.min + vec2(w * i as f32, 0.0), vec2(w, r.height())), 0.0, *c);+ }+ }+ "dataBar" => {+ let (r, _) = ui.allocate_exact_size(size, Sense::hover());+ let c = p.get("color").and_then(Json::as_str).and_then(hex_color).unwrap_or(Color32::from_rgb(0x63, 0x8E, 0xC6));+ ui.painter().rect_filled(r, 0.0, Color32::WHITE);+ ui.painter().rect_filled(egui::Rect::from_min_size(r.min + vec2(2.0, 3.0), vec2(r.width() * 0.6, r.height() - 6.0)), 0.0, c);+ }+ "iconSet" => {+ let set = p.get("set").and_then(Json::as_str).unwrap_or("3TrafficLights1");+ ui.horizontal(|ui| {+ for i in 0..gridcraft_engine::cf::icon_count(set) {+ 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);+ }+ });+ }+ _ => cf_preview(ui, p.as_object().unwrap_or(&Map::new()), size),+ }+}++/// Conditional Formatting Rules Manager: which rules to show, New/Edit/+/// Delete/Duplicate and priority arrows, the rule table, and the selected rule's Applies to.+/// Changes apply at once; Cancel puts back the rules the dialog opened with.+pub(super) fn manage_rules(app: &mut SheetApp, ui: &mut egui::Ui, d: &mut Dialog, confirm: &mut bool, open: &mut bool) {+ let lang = app.ui.language;+ // Every action targets the sheet the dialog opened on, the one Cancel restores.+ let sheet = d.values.get("sheet").cloned().unwrap_or(Json::Null);+ let show = d.values.get("show").and_then(Json::as_str).unwrap_or("selection").to_string();+ let list =+ app.session.run("home.manageRules", on_sheet(&sheet, json!({"show": show, "selection": d.values.get("selection")}))).unwrap_or(Json::Null); let rules = list["rules"].as_array().cloned().unwrap_or_default();- if rules.is_empty() {- ui.label("There are no conditional formatting rules on this sheet.");+ let total = list["total"].as_u64().unwrap_or(0);+ // The selected rule is followed by its place on the sheet: a move swaps it with a neighbour+ // the Current Selection view may hide, so its row in the list can stay where it was.+ if let Some(at) = d.values.get("selectedRule").and_then(Json::as_u64)+ && let Some(i) = rules.iter().position(|r| r["index"].as_u64() == Some(at))+ {+ d.list_index = i;+ }+ if d.list_index >= rules.len() {+ d.list_index = rules.len().saturating_sub(1); }- for (i, r) in rules.iter().enumerate() {- ui.horizontal(|ui| {- let kind = r["rule"].as_object().and_then(|o| o.keys().next().cloned()).unwrap_or_default();- ui.label(format!(- "{}. {kind} applies to {}",- i + 1,- r["ranges"].as_array().map(|a| a.iter().filter_map(|x| x.as_str()).collect::<Vec<_>>().join(",")).unwrap_or_default()- ));- if ui.small_button("▲").clicked() {- app.run_or_alert("home.manageRules", json!({"moveUp": i}));- }- if ui.small_button("▼").clicked() {- app.run_or_alert("home.manageRules", json!({"moveDown": i}));- }- if ui.small_button("Delete").clicked() {- app.run_or_alert("home.manageRules", json!({"delete": i}));+ let selected = rules.get(d.list_index).cloned();+ let index = selected.as_ref().and_then(|r| r["index"].as_u64());+ ui.horizontal(|ui| {+ ui.label(lang.tr("Show formatting rules for:"));+ let mut m = Map::new();+ m.insert("show".into(), json!(show));+ if cf_combo(ui, "show", &mut m, "show", &[("selection", "Current Selection"), ("sheet", "This Worksheet")], 160.0) {+ d.values.insert("show".into(), m.get("show").cloned().unwrap_or(Json::Null));+ d.list_index = 0;+ }+ });+ ui.add_space(4.0);+ ui.horizontal(|ui| {+ if ui.button(lang.tr("New Rule…")).clicked() {+ app.open_dialog("newFormattingRule", json!({"sheet": sheet, "range": d.values.get("selection")}));+ }+ let some = index.is_some();+ if ui.add_enabled(some, egui::Button::new(lang.tr("Edit Rule…"))).clicked()+ && let Some(r) = &selected+ {+ app.open_dialog("newFormattingRule", json!({"index": index, "rule": r["params"], "sheet": sheet}));+ }+ if ui.add_enabled(some, egui::Button::new(lang.tr("Delete Rule"))).clicked() {+ app.run_or_alert("home.manageRules", on_sheet(&sheet, json!({"delete": index})));+ }+ if ui.add_enabled(some, egui::Button::new(lang.tr("Duplicate Rule"))).clicked() {+ app.run_or_alert("home.manageRules", on_sheet(&sheet, json!({"duplicate": index})));+ }+ ui.with_layout(egui::Layout::right_to_left(egui::Align::Center), |ui| {+ let first = index == Some(0);+ let last = index.is_some_and(|i| i + 1 >= total);+ if ui.add_enabled(some && !last, egui::Button::new(lang.tr("Move Down"))).clicked() {+ app.run_or_alert("home.manageRules", on_sheet(&sheet, json!({"moveDown": index})));+ d.values.insert("selectedRule".into(), json!(index.map(|i| i + 1)));+ }+ if ui.add_enabled(some && !first, egui::Button::new(lang.tr("Move Up"))).clicked() {+ app.run_or_alert("home.manageRules", on_sheet(&sheet, json!({"moveUp": index})));+ d.values.insert("selectedRule".into(), json!(index.map(|i| i.saturating_sub(1))));+ }+ });+ });+ ui.add_space(4.0);+ egui::Frame::group(ui.style()).show(ui, |ui| {+ ui.set_min_height(220.0);+ ui.set_width(ui.available_width());+ egui::ScrollArea::vertical().max_height(260.0).show(ui, |ui| {+ egui::Grid::new("cfRules").striped(true).num_columns(4).spacing(vec2(12.0, 6.0)).show(ui, |ui| {+ ui.strong(lang.tr("Rule (applied in order shown)"));+ ui.strong(lang.tr("CF|Format"));+ ui.strong(lang.tr("Applies to"));+ ui.strong(lang.tr("Stop If True"));+ ui.end_row();+ for (i, r) in rules.iter().enumerate() {+ let text: String = cf_describe(&r["params"], lang).chars().take(48).collect();+ let resp = ui.add(egui::Button::selectable(i == d.list_index, text).min_size(vec2(220.0, 20.0)));+ if resp.clicked() {+ d.list_index = i;+ d.values.insert("selectedRule".into(), r["index"].clone());+ d.values.remove("appliesTo");+ }+ if resp.double_clicked() {+ app.open_dialog("newFormattingRule", json!({"index": r["index"], "rule": r["params"], "sheet": sheet}));+ }+ cf_format_cell(ui, &r["params"]);+ ui.label(r["appliesTo"].as_str().unwrap_or(""));+ // Colour scales, data bars and icon sets don't stop other rules.+ let styled = !matches!(r["params"]["type"].as_str(), Some("colorScale" | "dataBar" | "iconSet"));+ let mut stop = r["stopIfTrue"].as_bool().unwrap_or(false);+ if ui.add_enabled(styled, egui::Checkbox::without_text(&mut stop)).changed() {+ app.run_or_alert("home.manageRules", on_sheet(&sheet, json!({"update": r["index"], "stopIfTrue": stop})));+ }+ ui.end_row();+ }+ });+ if rules.is_empty() {+ ui.add_space(8.0);+ ui.label(+ egui::RichText::new(lang.tr(if show == "selection" {+ "No rules apply to the selected cells. Show This Worksheet to see every rule, or click New Rule…"+ } else {+ "There are no conditional formatting rules on this sheet."+ }))+ .italics()+ .weak(),+ ); } });+ });+ ui.add_space(6.0);+ ui.label(lang.tr("Applies to:"));+ ui.horizontal(|ui| {+ let current = selected.as_ref().and_then(|r| r["appliesTo"].as_str()).unwrap_or("").to_string();+ let mut text = d.values.get("appliesTo").and_then(Json::as_str).map(str::to_string).unwrap_or_else(|| current.clone());+ let edited = text != current;+ let field = ui.add_enabled(index.is_some(), egui::TextEdit::singleline(&mut text).desired_width(ui.available_width() - 140.0));+ if field.changed() {+ d.values.insert("appliesTo".into(), json!(text));+ }+ let enter = field.lost_focus() && ui.input(|i| i.key_pressed(Key::Enter));+ if ui.add_enabled(edited, egui::Button::new(lang.tr("Apply"))).clicked() || (enter && edited) {+ // A refused range stays in the box to be corrected.+ if app.run("home.manageRules", on_sheet(&sheet, json!({"update": index, "appliesTo": text}))).is_ok() {+ d.values.remove("appliesTo");+ } else {+ app.message = Some((d.title.clone(), lang.tr("Applies to must be a range such as =$A$1:$B$10.").into()));+ }+ }+ if ui.add_enabled(edited, egui::Button::new(lang.tr("Cancel"))).clicked() {+ d.values.remove("appliesTo");+ }+ });+ ui.add_space(8.0);+ ui.with_layout(egui::Layout::right_to_left(egui::Align::Center), |ui| {+ let ok = ui.add(+ egui::Button::new(egui::RichText::new(format!(" {} ", lang.tr("OK"))).color(Color32::WHITE))+ .fill(crate::theme::Tokens::get(ui.ctx()).accent),+ );+ if ok.clicked() {+ // A pending Applies to edit is kept on OK; a refused one keeps the dialog open.+ let pending = d.values.get("appliesTo").and_then(Json::as_str).map(str::to_string);+ let applied = pending.is_none_or(|t| app.run("home.manageRules", on_sheet(&sheet, json!({"update": index, "appliesTo": t}))).is_ok());+ if applied {+ d.values.remove("appliesTo");+ d.values.insert("ok".into(), json!(true));+ *confirm = true;+ } else {+ app.message = Some((d.title.clone(), lang.tr("Applies to must be a range such as =$A$1:$B$10.").into()));+ }+ }+ if ui.button(format!(" {} ", lang.tr("Cancel"))).clicked() {+ *open = false;+ }+ });+}++/// `params` aimed at `sheet` (Manage Rules works on the sheet it opened on).+fn on_sheet(sheet: &Json, mut params: Json) -> Json {+ params["sheet"] = sheet.clone();+ params+}++/// Manage Rules closed without OK: put back the sheet's rules as they were when it opened.+pub(super) fn manage_rules_revert(app: &mut SheetApp, d: &Dialog) {+ let Some(saved) = d.values.get("snapshot") else { return };+ let now = app.session.run("home.manageRules", json!({"sheet": d.values.get("sheet")})).unwrap_or(Json::Null);+ let now = now["rules"]+ .as_array()+ .map(|a| a.iter().map(|r| json!({"rule": r["rule"], "appliesTo": r["appliesTo"], "stopIfTrue": r["stopIfTrue"]})).collect::<Vec<_>>());+ if now.as_ref() != saved.as_array() {+ app.run_or_alert("home.manageRules", json!({"sheet": d.values.get("sheet"), "set": saved})); } }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"),+];++pub(super) 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"),+];++pub(super) 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)+}++pub(super) 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.+pub(super) 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.+pub(super) 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()));+ }+}@@ -2,10 +2,11 @@ use super::about::about; use super::analysis::{analysis_toolpak, goal_seek, solver, solver_results}; use super::charts::{recommended_charts, select_data}; use super::command_search::command_search;-use super::cond_format_rules::manage_rules;+use super::cond_format_rules::{manage_rules, manage_rules_revert}; 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 {@@ -116,7 +127,9 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { let width = match d.name.as_str() { "formatCells" => 560.0, "about" => 660.0,- "insertFunction" | "commandSearch" | "nameManager" | "manageRules" | "journal" | "agents" | "macros" => 520.0,+ "insertFunction" | "commandSearch" | "nameManager" | "journal" | "agents" | "macros" => 520.0,+ "manageRules" => 680.0,+ "newFormattingRule" => 560.0, "recommendedCharts" => 600.0, "analysisToolPak" => 420.0, "solver" => 460.0,@@ -151,7 +164,8 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { "sort" => sort(app, ui, &mut d, &mut confirm), "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),+ "manageRules" => manage_rules(app, ui, &mut d, &mut confirm, &mut open),+ "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),@@ -354,6 +368,9 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { if ctx.input(|i| i.key_pressed(Key::Escape)) || !win_open { open = false; }+ if !open && d.name == "manageRules" && d.values.get("ok").is_none() {+ manage_rules_revert(app, &d);+ } if confirm && d.name == "insertPicture" && d.picture_target != app.view_key() { app.message = Some((d.title.clone(), "Return to the original worksheet to insert this picture, or cancel and choose another cell.".into())); confirm = false;@@ -459,7 +476,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");+}new file mode 100644@@ -0,0 +1,74 @@+//! Conditional Formatting Rules Manager ▸ Applies to: a range the rule can't take is reported and+//! stays in the box to be corrected, and OK doesn't close the manager over it.++use egui_kittest::kittest::Queryable;+use gridcraft_engine::Session;+use gridcraft_ui_egui::SheetApp;+use serde_json::{Value as Json, json};++type Harness = egui_kittest::Harness<'static, SheetApp>;++fn harness() -> Harness {+ let mut session = Session::new();+ session.new_workbook();+ session.execute("home.conditionalFormat", json!({"range": "A1:A3", "rule": {"type": "blanks", "fill": "#FFC7CE"}})).unwrap();+ session.execute("selection.set", json!({"range": "A1"})).unwrap();+ let mut h = crate::common::builder().with_size(egui::vec2(1200.0, 800.0)).build_ui_state(+ |ui, app: &mut SheetApp| {+ app.logic(ui.ctx());+ app.ui(ui);+ },+ crate::common::app(session),+ );+ h.run_steps(4);+ h.state_mut().open_dialog("manageRules", json!({}));+ h.run_steps(6);+ h+}++/// Types `text` as the pending Applies to edit of the selected rule.+fn edit_applies_to(h: &mut Harness, text: &str) {+ h.state_mut().dialog.as_mut().unwrap().values.insert("appliesTo".into(), json!(text));+ h.run_steps(2);+}++fn pending(h: &Harness) -> Option<Json> {+ h.state().dialog.as_ref().and_then(|d| d.values.get("appliesTo").cloned())+}++fn ranges(h: &Harness) -> Vec<String> {+ let sheet = h.state().session.active().unwrap().wb.active().unwrap();+ sheet.cond_formats[0].ranges.iter().map(|r| r.a1()).collect()+}++#[test]+fn a_refused_range_stays_in_the_box() {+ let mut h = harness();+ edit_applies_to(&mut h, "=nope!");+ h.get_by_label("Apply").click();+ h.run_steps(3);+ assert!(h.state().message.is_some(), "the refusal is reported");+ assert_eq!(pending(&h), Some(json!("=nope!")), "the typed text is kept to be corrected");+ assert_eq!(ranges(&h), ["A1:A3"]);+}++#[test]+fn ok_over_a_refused_range_keeps_the_manager_open() {+ let mut h = harness();+ edit_applies_to(&mut h, "=nope!");+ h.get_by_label(" OK ").click();+ h.run_steps(3);+ assert!(h.state().dialog.as_ref().is_some_and(|d| d.name == "manageRules"), "the manager stays open");+ assert!(h.state().message.is_some(), "the refusal is reported");+ assert_eq!(pending(&h), Some(json!("=nope!")));+}++#[test]+fn ok_applies_a_pending_range_and_closes() {+ let mut h = harness();+ edit_applies_to(&mut h, "=$A$1:$A$5");+ h.get_by_label(" OK ").click();+ h.run_steps(3);+ assert!(h.state().dialog.is_none(), "OK closed the manager");+ assert_eq!(ranges(&h), ["A1:A5"], "the edit was kept, not reverted");+}@@ -14,6 +14,7 @@ mod borders; mod cell_editor_caret; mod cell_pictures; mod cf_rule_pick;+mod cf_rules_manager; mod chart_resize; mod clipboard; mod comments_pane;@@ -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; Conditional Formatting Rules Manager edits, duplicates, reorders, re-targets rules and has Stop If True) · **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,9 @@ 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 with selection/sheet filter, New/Edit/Delete/+Duplicate, Move Up/Move Down, Applies to, Stop If True, OK/Cancel), 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 +189,8 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---|+| 2026-10-27 | minor | Conditional formatting: Manage Rules shows a table (rule, format preview, applies to, Stop If True), filters by Current Selection or This Worksheet, edits (Edit Formatting Rule), duplicates, deletes and reorders rules, edits Applies to, and Cancel restores the rules it opened with; every action, New Rule… included, works on the sheet and selection the dialog opened with |+| 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 |