diff --git a/ROADMAP.md b/ROADMAP.md --- a/ROADMAP.md +++ b/ROADMAP.md @@ -2,7 +2,7 @@ **Stage: alpha** · next: beta, ~25 points of "ready for real work" and ~160–240 h away -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right, and fills a selection's empty last row and column) · **Target:** Microsoft Excel (Microsoft 365) +> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-27 · **Change:** minor (New Rule… opens New Formatting Rule with every rule type and a format editor) · **Target:** Microsoft Excel (Microsoft 365) GridCraft aims at full Microsoft Excel parity, and at being better in places: faster, open (XLSX is the native format), fully scriptable by agents (MCP, control channel, CLI), and on the web. @@ -135,6 +135,8 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---| +| 2026-10-27 | Conditional formatting: New Rule… opens a full New Formatting Rule dialog (six rule types, format editor with preview), also used as Edit Formatting Rule | +| 2026-10-27 | Conditional formatting commands edit rules: `home.conditionalFormat` takes Stop If True, plain format overrides (fill, font colour, border, font styles) and colour-scale/data-bar thresholds, and checks icon set thresholds; `home.manageRules` gains `show`, `selection`, `update`, `duplicate` and `set` and lists each rule's editable params, on any sheet | | 2026-10-25 | AutoSum over a selection does what Excel does: a selected row gets one total to its right (in its last cell if that is empty), not a one-cell total under each cell; a block whose last column is empty gets each row's total there, one whose last row is empty each column's, and with both the corner gets the total of the block; otherwise totals go under each column as before | | 2026-10-24 | Go To lists the named ranges of every sheet (`Sheet2!Name` for a name scoped to another sheet; hidden names, constants and formulas left out) and the references entered before, a double-click goes to an entry, and the Reference box takes several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30`) | | 2026-10-24 | Status bar: a right-click opens a menu that sets which selection statistics show (Average, Count and Sum by default; Numerical Count, Minimum and Maximum are new), kept with the preferences (`view.statusBar`) | @@ -242,6 +244,8 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | Change | Summary | |---|---|---| +| 2026-10-27 | minor | Progress log: New Formatting Rule dialog | +| 2026-10-27 | minor | Progress log: conditional formatting rule editing commands | | 2026-10-25 | minor | Progress log: AutoSum over a selected row, and a selection's empty last row and column | | 2026-10-23 | minor | Progress log: accounting negatives in number text | | 2026-10-23 | minor | Progress log: INDEX with one index on a 2-D reference is #REF! | diff --git a/crates/engine/src/cmd/insert.rs b/crates/engine/src/cmd/insert.rs --- a/crates/engine/src/cmd/insert.rs +++ b/crates/engine/src/cmd/insert.rs @@ -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 { "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 { "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 = 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 = 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 = 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 = 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 = + 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()); + } } diff --git a/crates/engine/src/cmd/insert/cond_format.rs b/crates/engine/src/cmd/insert/cond_format.rs --- a/crates/engine/src/cmd/insert/cond_format.rs +++ b/crates/engine/src/cmd/insert/cond_format.rs @@ -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 { 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 { + 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> { + 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 { + 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 { 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 = 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 = 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::>()}); + 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 = 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> { + 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 { @@ -278,19 +533,96 @@ pub(super) fn clear_cf(s: &mut Session, p: &Json) -> Result { pub(super) fn manage_cf(s: &mut Session, p: &Json) -> Result { 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 = s - .doc()? - .wb - .sheet(sheet) - .map(|sh| { - sh.cond_formats.iter().map(|c| json!({"ranges": c.ranges.iter().map(|r| r.a1()).collect::>(), "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::(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 = + 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::>(), + "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 { { 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) }) } diff --git a/crates/ui-egui/src/dialogs.rs b/crates/ui-egui/src/dialogs.rs --- a/crates/ui-egui/src/dialogs.rs +++ b/crates/ui-egui/src/dialogs.rs @@ -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, defaults: Json) -> Dialog { @@ -820,7 +842,16 @@ impl Dialog { ) }) .unwrap_or_default(); - d.pick = Some(RefPick { key: "formula", sheet, caret: 0, point: None, anchor: CellRef::new(0, 0), refocus: false, applies_to }); + d.pick = Some(RefPick { + key: "formula", + nested: None, + sheet, + caret: 0, + point: None, + anchor: CellRef::new(0, 0), + refocus: false, + applies_to, + }); } d.values.insert("type".into(), json!(ty)); d.values.insert("operator".into(), p("operator")); @@ -897,6 +928,56 @@ impl Dialog { dialog } "manageRules" => Dialog::custom("manageRules", "Conditional Formatting Rules Manager", json!({})), + "newFormattingRule" => { + // New Rule… starts from Format only cells that contain › Cell Value between; + // Manage Rules › Edit Rule… passes the rule's index and params. + let index = p("index").as_u64(); + let mut rule = p("rule").as_object().cloned().unwrap_or_else(|| { + json!({"type": "cellIs", "operator": "between", "value": "", "value2": ""}).as_object().cloned().unwrap_or_default() + }); + if !rule.contains_key("fill") { + let theme = app.session.active().map(|doc| doc.wb.theme.clone()).unwrap_or_default(); + let preset = rule.get("preset").and_then(Json::as_str).unwrap_or("lightRedFillDarkRedText").to_string(); + cf_apply_preset(&mut rule, &preset, &theme); + } + let title = if index.is_some() { "Edit Formatting Rule" } else { "New Formatting Rule" }; + // The sheet (by name: inserting or moving sheets changes its index) and the cells + // the rule applies to are fixed as the dialog opens, so pointing at cells for the + // formula, on this sheet or another, doesn't move the rule. + let (sheet, range) = app + .session + .active() + .map(|doc| { + let sheet = match p("sheet") { + Json::Null => doc.wb.active(), + v => v + .as_u64() + .and_then(|i| doc.wb.sheet(i as usize)) + .or_else(|| v.as_str().and_then(|n| doc.wb.sheet_index(n)).and_then(|i| doc.wb.sheet(i))), + }; + let range = match p("range") { + Json::Null if index.is_none() => json!(doc.selection.ranges.iter().map(RangeRef::a1).collect::>().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" => { diff --git a/crates/ui-egui/src/dialogs/formatting_rule.rs b/crates/ui-egui/src/dialogs/formatting_rule.rs new file mode 100644 --- /dev/null +++ b/crates/ui-egui/src/dialogs/formatting_rule.rs @@ -0,0 +1,604 @@ +use super::show::{ok_cancel, track_pick}; +use super::*; + +/// The six rule types in New Formatting Rule, with the rule `type` each starts from. +const CF_RULE_KINDS: [(&str, &str); 6] = [ + ("colorScale", "Format all cells based on their values"), + ("cellIs", "Format only cells that contain"), + ("top10", "Format only top or bottom ranked values"), + ("aboveAverage", "Format only values that are above or below average"), + ("duplicate", "Format only unique or duplicate values"), + ("expression", "Use a formula to determine which cells to format"), +]; + +const CF_PRESETS: [(&str, &str); 6] = [ + ("lightRedFillDarkRedText", "Light Red Fill with Dark Red Text"), + ("yellowFillDarkYellowText", "Yellow Fill with Dark Yellow Text"), + ("greenFillDarkGreenText", "Green Fill with Dark Green Text"), + ("lightRedFill", "Light Red Fill"), + ("redText", "Red Text"), + ("redBorder", "Red Border"), +]; + +const CF_OPERATORS: [(&str, &str); 8] = [ + ("between", "CF|between"), + ("notBetween", "CF|not between"), + ("equal", "CF|equal to"), + ("notEqual", "CF|not equal to"), + ("greater", "CF|greater than"), + ("less", "CF|less than"), + ("greaterOrEqual", "CF|greater than or equal to"), + ("lessOrEqual", "CF|less than or equal to"), +]; + +const CF_PERIODS: [(&str, &str); 10] = [ + ("yesterday", "Yesterday"), + ("today", "Today"), + ("tomorrow", "Tomorrow"), + ("last7Days", "In the last 7 days"), + ("lastWeek", "Last week"), + ("thisWeek", "This week"), + ("nextWeek", "Next week"), + ("lastMonth", "Last month"), + ("thisMonth", "This month"), + ("nextMonth", "Next month"), +]; + +const CF_ICON_SETS: [(&str, &str); 6] = [ + ("3Arrows", "3 Arrows"), + ("3TrafficLights1", "3 Traffic Lights"), + ("3Symbols", "3 Symbols"), + ("4Rating", "4 Ratings"), + ("5Arrows", "5 Arrows"), + ("5Quarters", "5 Quarters"), +]; + +/// Which of [`CF_RULE_KINDS`] a rule `type` belongs to. +fn cf_kind_of(ty: &str) -> usize { + match ty { + "colorScale" | "dataBar" | "iconSet" => 0, + "top10" => 2, + "aboveAverage" => 3, + "duplicate" | "unique" => 4, + "expression" => 5, + _ => 1, + } +} + +fn cf_str(rule: &Map, 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, key: &str) -> bool { + rule.get(key).and_then(Json::as_bool).unwrap_or(false) +} + +fn hex_color(s: &str) -> Option { + 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, preset: &str, theme: &gridcraft_engine::model::Theme) { + use gridcraft_engine::model::{BorderStyle, PatternType, Underline}; + let st = gridcraft_engine::cmd::insert::preset_style(preset); + let hex = |c: gridcraft_engine::model::Color| c.hex(theme); + rule.insert("fill".into(), json!(if st.fill.pattern == PatternType::None { None } else { hex(st.fill.fg) }.unwrap_or_else(|| "none".into()))); + rule.insert("fontColor".into(), json!(hex(st.font.color).unwrap_or_else(|| "auto".into()))); + let border = (st.borders.left.style != BorderStyle::None).then(|| hex(st.borders.left.color)).flatten(); + rule.insert("border".into(), json!(border.unwrap_or_else(|| "none".into()))); + rule.insert("bold".into(), json!(st.font.bold)); + rule.insert("italic".into(), json!(st.font.italic)); + rule.insert("underline".into(), json!(st.font.underline != Underline::None)); + rule.insert("strike".into(), json!(st.font.strike)); + rule.remove("preset"); + rule.remove("style"); +} + +/// A combo box over `(value, label)` pairs for `rule[key]`; true when the user picked one. +fn cf_combo(ui: &mut egui::Ui, id: &str, rule: &mut Map, 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, 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, none_label: &str, colors: &[u32; 12]) -> Option { + let lang = crate::i18n::current(ui.ctx()); + let mut picked = None; + ui.horizontal(|ui| { + let (r, _) = ui.allocate_exact_size(vec2(14.0, 14.0), Sense::hover()); + let border = ui.visuals().widgets.inactive.fg_stroke.color; + match current { + Some(c) => ui.painter().rect_filled(r, 2.0, c), + None => ui.painter().line_segment([r.left_bottom(), r.right_top()], Stroke::new(1.0, border)), + }; + ui.painter().rect_stroke(r, 2.0, Stroke::new(1.0, border), StrokeKind::Inside); + ui.menu_button(lang.tr(label), |ui| { + if let Some(c) = crate::widgets::color_palette(ui, colors, lang.tr(none_label)) { + picked = Some(c); + ui.close(); + } + }); + }); + picked +} + +/// Fill, font and border of a rule's format, as a cell would show them. +fn cf_preview(ui: &mut egui::Ui, rule: &Map, 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, 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, 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, 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 = 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 = 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) { + 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::().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())); + } +} diff --git a/crates/ui-egui/src/dialogs/show.rs b/crates/ui-egui/src/dialogs/show.rs --- a/crates/ui-egui/src/dialogs/show.rs +++ b/crates/ui-egui/src/dialogs/show.rs @@ -6,6 +6,7 @@ use super::cond_format_rules::manage_rules; use super::data::{advanced_filter_form, data_validation_form, sort}; use super::find::{find, go_to}; use super::format_cells::format_cells; +use super::formatting_rule::formatting_rule; use super::insert_function::insert_function; use super::macros::macros; use super::names::{name_manager, paste_name}; @@ -71,6 +72,16 @@ pub(crate) fn build_params(d: &Dialog) -> Json { } } } + "newFormattingRule" => { + let rule = d.values.get("rule").cloned().unwrap_or(Json::Null); + v = match d.values.get("index").and_then(Json::as_u64) { + Some(i) => json!({"update": i, "rule": rule, "sheet": d.values.get("sheet")}), + None => match d.values.get("range").and_then(Json::as_str) { + Some(r) => json!({"range": r, "rule": rule, "sheet": d.values.get("sheet")}), + None => json!({"rule": rule}), + }, + }; + } "cfQuick" => { let mut rule = Map::new(); for (k, val) in &d.values { @@ -117,6 +128,7 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { "formatCells" => 560.0, "about" => 660.0, "insertFunction" | "commandSearch" | "nameManager" | "manageRules" | "journal" | "agents" | "macros" => 520.0, + "newFormattingRule" => 560.0, "recommendedCharts" => 600.0, "analysisToolPak" => 420.0, "solver" => 460.0, @@ -152,6 +164,7 @@ pub fn show(app: &mut SheetApp, ctx: &egui::Context) { "nameManager" => name_manager(app, ui, &mut d, &mut open), "pasteName" => paste_name(app, ui, &mut d, &mut confirm, &mut open), "manageRules" => manage_rules(app, ui), + "newFormattingRule" => formatting_rule(app, ui, &mut d, &mut confirm, &mut open), "recommendedCharts" => recommended_charts(app, ui, &mut d, &mut confirm, &mut open), "selectData" => select_data(app, ui, &mut d, &mut confirm, &mut open), "commandSearch" => command_search(app, ui, &mut d, &mut confirm), @@ -459,7 +472,7 @@ fn take_first_focus(d: &mut Dialog, r: &egui::Response) { /// Keeps a picking formula box's caret, and gives it the keyboard back after a grid click put a /// reference in it (the click took the focus), its caret after the reference. -fn track_pick(pick: &mut RefPick, r: &egui::Response, typed: bool) { +pub(super) fn track_pick(pick: &mut RefPick, r: &egui::Response, typed: bool) { if typed { pick.point = None; } diff --git a/crates/ui-egui/src/grid.rs b/crates/ui-egui/src/grid.rs --- a/crates/ui-egui/src/grid.rs +++ b/crates/ui-egui/src/grid.rs @@ -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}; diff --git a/crates/ui-egui/src/grid/cell_paint.rs b/crates/ui-egui/src/grid/cell_paint.rs --- a/crates/ui-egui/src/grid/cell_paint.rs +++ b/crates/ui-egui/src/grid/cell_paint.rs @@ -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 { diff --git a/crates/ui-egui/src/ribbon/home.rs b/crates/ui-egui/src/ribbon/home.rs --- a/crates/ui-egui/src/ribbon/home.rs +++ b/crates/ui-egui/src/ribbon/home.rs @@ -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( diff --git a/crates/ui-egui/tests/it/cf_rule_pick.rs b/crates/ui-egui/tests/it/cf_rule_pick.rs --- a/crates/ui-egui/tests/it/cf_rule_pick.rs +++ b/crates/ui-egui/tests/it/cf_rule_pick.rs @@ -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 { + 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::>(), ["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"); +} diff --git a/docs/ui-parity.md b/docs/ui-parity.md --- a/docs/ui-parity.md +++ b/docs/ui-parity.md @@ -1,6 +1,6 @@ # UI parity -> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 +> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-27 · **Change:** minor (New Rule… opens New Formatting Rule with every rule type and a format editor) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Interaction, controls and feel, against Excel. Part of [target-app-parity.md](target-app-parity.md). **UI/UX fidelity: ~55% ready, 50–80 h** (estimated). Ribbon command coverage is measured @@ -152,7 +152,8 @@ a character (e.g. Ctrl+Alt+A for `ä`) never trigger a shortcut, Ctrl+letter one ## Dialogs Format Cells, Insert Function/Formula Builder, Find and Replace, Go To and Go To Special, Sort, -Name Manager, Data Validation, Paste Special, Conditional Formatting manager, Page Setup, Goal +Name Manager, Data Validation, Paste Special, Conditional Formatting (New/Edit Formatting Rule with +the six rule types and a format editor; Rules Manager), Page Setup, Goal Seek, Scenario Manager, Data Analysis, Forecast Sheet and others exist. Missing or partial: Options, Solver, Power Query editor, Insert Object, Equation editor, Protect/Allow Edit Ranges, Workbook Links, Accessibility checker. Like Excel's, every dialog opens centred and moves by its @@ -187,6 +188,7 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---| +| 2026-10-27 | minor | Conditional formatting: New Rule… opens New Formatting Rule with the six rule types (cell values: colour scales, data bars, icon sets with their thresholds; cell value/text/dates/blanks/errors; top/bottom; above/below average and standard deviations; unique/duplicate; formula, with a hint about relative references; its formula box takes references clicked or dragged on the grid, and the rule keeps the sheet and cells it opened for, as Use a formula did since #2348) and a format editor (presets, fill, font colour, border, bold/italic/underline/strikethrough, preview); it opens as Edit Formatting Rule for a rule `home.manageRules` lists | | 2026-10-25 | minor | AutoSum (Alt+=, Home › AutoSum) over a selected row puts its total to the right of the row, or in its last cell when that is empty, as in Excel; it wrote a one-cell total under each cell. A selection's empty last column takes each row's total and its empty last row each column's, with the block's total in the corner when both are empty | | 2026-10-25 | minor | Paste Name lays out only the names in view, so a workbook with thousands of names opens it at once; the arrow keys still scroll the chosen name into view. Go To matches a `Sheet2!Name` reference and hides a workbook name behind the active sheet's own name in any case and script (`Größe` = `GRÖSSE`), as formulas do. The two Page Layout view rows are one; the 0.7 in side margin used when Align with Page Margins is off is marked unverified | | 2026-10-24 | minor | Go To lists the named ranges on every sheet, as Excel's does ("The Go to popup window shows named ranges on every worksheet in your workbook"): a name scoped to another sheet appears as `Sheet2!Name`, and hidden names, constants and formulas are left out. It keeps the references entered (newest first, ten at most), and a double-click on an entry goes there. The Reference box and `edit.goTo` take several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30,H21:H29`) and select them all; `edit.goTo {list: true}` returns the list |