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 (conditional formatting commands edit, duplicate, re-target and restore 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,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---| +| 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 +243,7 @@ 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 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) }) }