ravenrs_sheet/engine/cmd/data/
validation.rs

1use super::{
2    Cell, CellRef, CfOperator, EngineError, ErrorStyle, Json, RangeRef, Result, Session, Validation, ValidationKind, Value, Workbook, bad,
3    bool_param, cell_param, edit, json, ok, str_param, target_ranges, target_sheet,
4};
5
6// ---------------------------------------------------------------- validation
7
8fn op_param(s: Option<&str>) -> CfOperator {
9    match s.unwrap_or("between") {
10        "notBetween" => CfOperator::NotBetween,
11        "equal" => CfOperator::Equal,
12        "notEqual" => CfOperator::NotEqual,
13        "greater" | "greaterThan" => CfOperator::Greater,
14        "less" | "lessThan" => CfOperator::Less,
15        "greaterOrEqual" | "greaterThanOrEqual" => CfOperator::GreaterOrEqual,
16        "lessOrEqual" | "lessThanOrEqual" => CfOperator::LessOrEqual,
17        _ => CfOperator::Between,
18    }
19}
20
21/// Removes a set of cuts from one validation range in a row sweep, updating the result once
22/// instead of fragmenting it again for every cut.
23fn subtract_ranges(r: RangeRef, cuts: &[RangeRef]) -> Vec<RangeRef> {
24    let mut events = std::collections::BTreeMap::<u64, Vec<((u32, u32), bool)>>::new();
25    for cut in cuts {
26        let Some(i) = r.intersection(cut) else { continue };
27        let columns = (i.start.col, i.end.col);
28        events.entry(u64::from(i.start.row)).or_default().push((columns, true));
29        events.entry(u64::from(i.end.row) + 1).or_default().push((columns, false));
30    }
31    if events.is_empty() {
32        return vec![r];
33    }
34    events.entry(u64::from(r.end.row) + 1).or_default();
35
36    let mut active = std::collections::BTreeMap::<(u32, u32), usize>::new();
37    let mut out: Vec<RangeRef> = Vec::new();
38    let mut last_by_columns = std::collections::BTreeMap::<(u32, u32), usize>::new();
39    let mut previous_row = u64::from(r.start.row);
40    for (row, changes) in events {
41        if previous_row < row {
42            let start_row = previous_row as u32;
43            let end_row = (row - 1).min(u64::from(r.end.row)) as u32;
44            let mut covered = Vec::<(u32, u32)>::new();
45            for &(start, end) in active.keys() {
46                match covered.last_mut() {
47                    Some(last) if start <= last.1.saturating_add(1) => last.1 = last.1.max(end),
48                    _ => covered.push((start, end)),
49                }
50            }
51            let mut next_col = u64::from(r.start.col);
52            let mut remaining = Vec::new();
53            for (start, end) in covered {
54                if next_col < u64::from(start) {
55                    remaining.push((next_col as u32, start - 1));
56                }
57                next_col = u64::from(end) + 1;
58            }
59            if next_col <= u64::from(r.end.col) {
60                remaining.push((next_col as u32, r.end.col));
61            }
62            for (start_col, end_col) in remaining {
63                let columns = (start_col, end_col);
64                if let Some(&index) = last_by_columns.get(&columns)
65                    && out[index].end.row.saturating_add(1) == start_row
66                {
67                    out[index].end.row = end_row;
68                } else {
69                    let index = out.len();
70                    out.push(RangeRef::new(CellRef::new(start_row, start_col), CellRef::new(end_row, end_col)));
71                    last_by_columns.insert(columns, index);
72                }
73            }
74        }
75        for (columns, add) in changes {
76            if add {
77                *active.entry(columns).or_default() += 1;
78            } else if let Some(count) = active.get_mut(&columns) {
79                *count -= 1;
80                if *count == 0 {
81                    active.remove(&columns);
82                }
83            }
84        }
85        previous_row = row;
86    }
87    out
88}
89
90/// Removes the cells of `cuts` from every validation, keeping the rest of each rule's ranges.
91/// When the rule's top-left cell goes, its relative formulas are rewritten for the new top-left
92/// cell, so the cells left keep checking the same cells.
93pub(in super::super) fn remove_validation_cells(validations: &mut Vec<Validation>, cuts: &[RangeRef]) {
94    for dv in validations.iter_mut() {
95        let before = RangeRef::top_left(&dv.ranges);
96        dv.ranges = dv.ranges.iter().flat_map(|r| subtract_ranges(*r, cuts)).collect();
97        if let (Some(from), Some(to)) = (before, RangeRef::top_left(&dv.ranges)) {
98            dv.f1 = move_rule_formula(&dv.f1, from, to);
99            dv.f2 = dv.f2.take().map(|f| move_rule_formula(&f, from, to));
100        }
101    }
102    validations.retain(|d| !d.ranges.is_empty());
103}
104
105pub(super) fn validation(s: &mut Session, p: &Json) -> Result<Json> {
106    let ranges = target_ranges(s, p)?;
107    let sheet = target_sheet(s, p)?;
108    if bool_param(p, "clear").unwrap_or(false) {
109        return edit(s, |cx| {
110            let sh = cx.sheet_mut(sheet)?;
111            remove_validation_cells(&mut sh.validations, &ranges);
112            Ok(Json::Null)
113        });
114    }
115    let Some(kind) = str_param(p, "type") else {
116        s.ui_requests.push(crate::engine::UiRequest::Dialog("dataValidation".into(), json!({})));
117        return ok();
118    };
119    let kind = match kind {
120        "whole" => ValidationKind::Whole,
121        "decimal" => ValidationKind::Decimal,
122        "list" => ValidationKind::List,
123        "date" => ValidationKind::Date,
124        "time" => ValidationKind::Time,
125        "textLength" => ValidationKind::TextLength,
126        "custom" => ValidationKind::Custom,
127        "any" => ValidationKind::Any,
128        // An unknown type used to give an "any value" rule, saved as `type="none"`: no validation.
129        other => return Err(bad("data.validation", format!("unknown type `{other}` (any, whole, decimal, list, date, time, textLength, custom)"))),
130    };
131    if kind == ValidationKind::List && str_param(p, "formula1").is_none_or(|f| f.trim().trim_start_matches('=').is_empty()) {
132        return Err(bad("data.validation", "a list validation needs `formula1`"));
133    }
134    const OPERATORS: [&str; 12] = [
135        "between",
136        "notBetween",
137        "equal",
138        "notEqual",
139        "greater",
140        "greaterThan",
141        "less",
142        "lessThan",
143        "greaterOrEqual",
144        "greaterThanOrEqual",
145        "lessOrEqual",
146        "lessThanOrEqual",
147    ];
148    if let Some(op) = str_param(p, "operator").filter(|op| !OPERATORS.contains(op)) {
149        return Err(bad("data.validation", format!("unknown operator `{op}` ({})", OPERATORS.join(", "))));
150    }
151    let error_style = match str_param(p, "errorStyle") {
152        None | Some("stop") => ErrorStyle::Stop,
153        Some("warning") => ErrorStyle::Warning,
154        Some("information") => ErrorStyle::Information,
155        Some(other) => return Err(bad("data.validation", format!("unknown errorStyle `{other}` (stop, warning, information)"))),
156    };
157    let f1 = str_param(p, "formula1").unwrap_or("");
158    let f1 = if kind == ValidationKind::List { list_source(f1) } else { f1.trim_start_matches('=').to_string() };
159    let dv = Validation {
160        ranges: ranges.clone(),
161        kind,
162        op: op_param(str_param(p, "operator")),
163        f1,
164        f2: str_param(p, "formula2").map(|f| f.trim_start_matches('=').to_string()),
165        allow_blank: bool_param(p, "ignoreBlank").unwrap_or(true),
166        in_cell_dropdown: bool_param(p, "dropdown").unwrap_or(true),
167        input_title: str_param(p, "inputTitle").unwrap_or("").into(),
168        input_message: str_param(p, "inputMessage").unwrap_or("").into(),
169        show_input: true,
170        error_title: str_param(p, "errorTitle").unwrap_or("").into(),
171        error_message: str_param(p, "errorMessage").unwrap_or("").into(),
172        error_style,
173        show_error: true,
174    };
175    edit(s, |cx| {
176        let sh = cx.sheet_mut(sheet)?;
177        remove_validation_cells(&mut sh.validations, &ranges);
178        sh.validations.push(dv.clone());
179        Ok(Json::Null)
180    })
181}
182
183/// Whether a list rule's source is a literal list (`"a,b"`, or `a,b` typed without quotes) rather
184/// than a reference or a name to evaluate.
185fn is_literal_list(f: &str) -> bool {
186    f.starts_with('"')
187        || (!f.contains('!')
188            && crate::formula::parse(f).map(|e| !matches!(e, crate::formula::Expr::Ref(_) | crate::formula::Expr::Name(_))).unwrap_or(true))
189}
190
191/// A list rule's source as a file stores it. A literal list typed without quotes, as in Excel's
192/// Source box (`Open,In progress,Closed`), becomes the quoted string Excel saves
193/// (`"Open,In progress,Closed"`, inner quotes doubled); unquoted, the file would read it as a
194/// formula. A formula (`=…`), a reference, a name or an already quoted list is kept as before.
195fn list_source(f: &str) -> String {
196    let t = f.trim();
197    if t.is_empty() || t.starts_with('=') || t.starts_with('"') || !is_literal_list(t) {
198        return f.trim_start_matches('=').to_string();
199    }
200    format!("\"{}\"", t.replace('"', "\"\""))
201}
202
203/// List entries of a list validation (literal or from a range) for the cell `at`. A relative
204/// source is written for the rule's top-left cell and read from `at`, as in Excel (`=$B1:$B3` on
205/// A1:A9 lists B5:B7 at A5).
206pub fn list_items(wb: &Workbook, sheet: usize, at: CellRef, dv: &Validation) -> Vec<String> {
207    let f = dv.f1.trim();
208    if is_literal_list(f) {
209        // A quoted list is a string literal: `""` inside stands for one `"`.
210        let items = match f.strip_prefix('"').and_then(|x| x.strip_suffix('"')) {
211            Some(body) => body.replace("\"\"", "\""),
212            None => f.trim_matches('"').to_string(),
213        };
214        return items.split(',').map(|x| x.trim().to_string()).filter(|x| !x.is_empty()).collect();
215    }
216    let first = RangeRef::top_left(&dv.ranges).unwrap_or(at);
217    match crate::calc::evaluate(wb, sheet, at, &formula_at(f, first, at)) {
218        Value::Array(a) => a.data.iter().filter(|v| !v.is_empty()).map(Value::display).collect(),
219        v if !v.is_empty() && !v.is_error() => vec![v.display()],
220        _ => vec![],
221    }
222}
223
224/// Checks typed input against the validation of a cell. `Ok(None)` = valid.
225pub fn check_validation(wb: &Workbook, sheet: usize, at: CellRef, input: &str) -> Option<(Validation, String)> {
226    let sh = wb.sheet(sheet)?;
227    let dv = sh.validations.iter().find(|d| d.ranges.iter().any(|r| r.contains(at)))?;
228    if dv.kind == ValidationKind::Any || !dv.show_error {
229        return None;
230    }
231    if input.is_empty() && dv.allow_blank {
232        return None;
233    }
234    let v = crate::base::parse::parse_input(input, wb.date_system).value;
235    // The formulas are written for the top-left cell of all the areas and see the value being entered,
236    // as in Excel (a custom `=A1>10` on A1:A9 checks A5's new value at A5).
237    let first = RangeRef::top_left(&dv.ranges).unwrap_or(at);
238    let original = wb;
239    let entered;
240    let wb = if sh.value(at) == v {
241        wb
242    } else {
243        let mut w = wb.clone();
244        if let Some(sh) = w.sheet_mut(sheet) {
245            let style = sh.style_id(at);
246            sh.cells.set(at, Cell { value: v.clone(), style, ..Default::default() });
247        }
248        entered = w;
249        &entered
250    };
251    let eval = |f: &str| crate::calc::evaluate(wb, sheet, at, &formula_at(f, first, at));
252    let num = |f: &str| eval(f).to_number().ok();
253    let ok = match dv.kind {
254        // The list is the one the dropdown showed before the entry, as in Excel: a source that takes
255        // in the cell itself (`=$A1:$A2` on A2:A3 is A2:A3 at A3) doesn't list the value being typed.
256        ValidationKind::List => list_items(original, sheet, at, dv).iter().any(|x| x.eq_ignore_ascii_case(input)),
257        ValidationKind::Custom => eval(&dv.f1).to_bool().unwrap_or(false),
258        ValidationKind::TextLength => {
259            let n = input.chars().count() as f64;
260            compare_op(dv.op, n, num(&dv.f1), dv.f2.as_deref().and_then(num))
261        }
262        _ => match v.as_f64() {
263            Some(n) => {
264                if dv.kind == ValidationKind::Whole && n.fract() != 0.0 {
265                    false
266                } else {
267                    compare_op(dv.op, n, num(&dv.f1), dv.f2.as_deref().and_then(num))
268                }
269            }
270            None => false,
271        },
272    };
273    if ok {
274        return None;
275    }
276    let msg = if dv.error_message.is_empty() {
277        "This value doesn't match the data validation restrictions defined for this cell.".to_string()
278    } else {
279        dv.error_message.clone()
280    };
281    Some((dv.clone(), msg))
282}
283
284/// A formula written for `first`, as seen from `at` (relative references shifted).
285fn formula_at(f: &str, first: CellRef, at: CellRef) -> String {
286    if first == at {
287        return f.to_string();
288    }
289    match crate::formula::parse(f.trim_start_matches('=')) {
290        Ok(e) => {
291            crate::formula::print(&crate::formula::adjust::shift_relative(e, at.row as i64 - first.row as i64, at.col as i64 - first.col as i64))
292        }
293        Err(_) => f.to_string(),
294    }
295}
296
297/// Moves the relative references of a rule formula written for `first` so it reads the same cells from `to`
298/// (pasting a rule); text without relative references (constants, literal lists) stays as typed.
299pub(in super::super) fn move_rule_formula(f: &str, first: CellRef, to: CellRef) -> String {
300    if first == to {
301        return f.to_string();
302    }
303    let moved = formula_at(f, first, to);
304    let has_relative = crate::formula::parse(f.trim_start_matches('=')).is_ok_and(|e| crate::formula::print(&e) != moved);
305    let eq = if f.starts_with('=') { "=" } else { "" };
306    if has_relative { format!("{eq}{moved}") } else { f.to_string() }
307}
308
309fn compare_op(op: CfOperator, n: f64, a: Option<f64>, b: Option<f64>) -> bool {
310    let Some(a) = a else { return false };
311    match op {
312        CfOperator::Between => b.is_some_and(|b| n >= a.min(b) && n <= a.max(b)),
313        CfOperator::NotBetween => b.is_some_and(|b| n < a.min(b) || n > a.max(b)),
314        CfOperator::Equal => n == a,
315        CfOperator::NotEqual => n != a,
316        CfOperator::Greater => n > a,
317        CfOperator::Less => n < a,
318        CfOperator::GreaterOrEqual => n >= a,
319        CfOperator::LessOrEqual => n <= a,
320    }
321}
322
323pub(super) fn validate_cmd(s: &mut Session, p: &Json) -> Result<Json> {
324    let d = s.doc()?;
325    let at = cell_param(s, p, "cell")?.unwrap_or(d.selection.active);
326    let input = str_param(p, "input").unwrap_or("");
327    match check_validation(&d.wb, d.wb.active_sheet(), at, input) {
328        None => Ok(json!({"ok": true})),
329        Some((dv, msg)) => Ok(json!({"ok": false, "message": msg, "title": dv.error_title, "style": format!("{:?}", dv.error_style)})),
330    }
331}
332
333pub(super) fn circle_invalid(s: &mut Session, _: &Json) -> Result<Json> {
334    let d = s.doc()?;
335    let sheet = d.wb.active_sheet();
336    let Some(sh) = d.wb.sheet(sheet) else { return Err(EngineError::NoDocument) };
337    let mut bad_cells = Vec::new();
338    for dv in &sh.validations {
339        for r in &dv.ranges {
340            let r = sh.used_range().and_then(|u| r.intersection(&u));
341            let Some(r) = r else { continue };
342            for c in r.iter().take(100_000) {
343                let text = sh.input_text(c);
344                if !text.is_empty() && check_validation(&d.wb, sheet, c, &text).is_some() {
345                    bad_cells.push(c.a1());
346                }
347            }
348        }
349    }
350    Ok(json!({"invalid": bad_cells}))
351}
ravenrs_sheet/engine/cmd/data/validation.rs

ravenrs_sheet/engine/cmd/data/
validation.rs
