ravenrs_sheet/engine/cmd/data/
filter.rs

1use super::sort::sort_key;
2use super::{
3    AutoFilter, CellRef, EngineError, FilterCriterion, Json, Ordering, RangeRef, Result, Session, Sheet, Value, Workbook, bad, bool_param, col_param,
4    current_region, data_target, edit, json,
5};
6
7// ---------------------------------------------------------------- filter
8
9pub(super) fn toggle_filter(s: &mut Session, p: &Json) -> Result<Json> {
10    let (sheet, r) = data_target(s, p, "data.filter")?;
11    crate::engine::cmd::refuse_unless_allowed(s, sheet, "Filtering", |p| p.autofilter)?;
12    // The active cell belongs to the active sheet: on another sheet the table is the one at `range`.
13    let at = if sheet == s.doc()?.wb.active_sheet() { s.doc()?.selection.active } else { r.start };
14    let has = s.doc()?.wb.sheet(sheet).is_some_and(|sh| sh.autofilter.is_some());
15    let in_table = s.doc()?.wb.sheet(sheet).and_then(|sh| sh.table_at(at).map(|t| t.name.clone()));
16    if let Some(name) = in_table {
17        return edit(s, |cx| {
18            let sh = cx.sheet_mut(sheet)?;
19            if let Some(t) = sh.tables.iter_mut().find(|t| t.name == name) {
20                t.filter_button = !t.filter_button;
21                if !t.filter_button {
22                    t.filter.clear();
23                }
24            }
25            Ok(Json::Null)
26        });
27    }
28    if has {
29        return edit(s, |cx| {
30            let sh = cx.sheet_mut(sheet)?;
31            // Reveal only rows the filter hid; rows hidden by hand or by a collapsed group stay hidden.
32            let filtered: Vec<u32> = sh.rows.keys().copied().filter(|r| sh.is_row_filtered(*r)).collect();
33            sh.autofilter = None;
34            for r in filtered {
35                if let Some(info) = sh.rows.get_mut(&r) {
36                    info.hidden = false;
37                }
38            }
39            cx.structural = true;
40            Ok(json!({"filter": false}))
41        });
42    }
43    install_filter(s, p)
44}
45
46/// Turn a sheet AutoFilter on over `p.range`, or over the data range of the selection.
47fn install_filter(s: &mut Session, p: &Json) -> Result<Json> {
48    let (sheet, mut r) = data_target(s, p, "data.filter")?;
49    // A selected header row filters the data below it (#201).
50    if p.get("range").is_none() && r.height() == 1 {
51        let d = s.doc()?;
52        let sh = d.wb.active().ok_or(EngineError::NoDocument)?;
53        let below = current_region(sh, r.start).end.row;
54        if below > r.end.row {
55            r = RangeRef::new(r.start, CellRef::new(below, r.end.col));
56        }
57    }
58    edit(s, |cx| {
59        cx.sheet_mut(sheet)?.autofilter = Some(AutoFilter { range: r, criteria: vec![] });
60        Ok(json!({"filter": true, "range": r.a1()}))
61    })
62}
63
64pub(super) fn filter_by(s: &mut Session, p: &Json) -> Result<Json> {
65    let sheet = s.doc()?.wb.active_sheet();
66    crate::engine::cmd::refuse_unless_allowed(s, sheet, "Filtering", |p| p.autofilter)?;
67    let Some(sh) = s.doc()?.wb.sheet(sheet) else { return Err(EngineError::NoDocument) };
68    let af = match &sh.autofilter {
69        Some(a) => a.clone(),
70        None => {
71            install_filter(s, &json!({}))?;
72            s.doc()?.wb.sheet(sheet).and_then(|sh| sh.autofilter.clone()).ok_or_else(|| EngineError::Other("no filter".into()))?
73        }
74    };
75    let col = match col_param(p.get("column"), af.range.start.col) {
76        Some(c) => c,
77        // A number too large to be a column is outside the filter range, not a missing parameter.
78        None if p.get("column").is_some_and(|v| v.as_u64().is_some()) => return Err(bad("data.filterBy", "column outside the filter range")),
79        None => return Err(bad("data.filterBy", "missing `column`")),
80    };
81    if col < af.range.start.col || col > af.range.end.col {
82        return Err(bad("data.filterBy", "column outside the filter range"));
83    }
84    let off = col - af.range.start.col;
85    let crit = if bool_param(p, "clear").unwrap_or(false) {
86        None
87    } else if let Some(vals) = p.get("values").and_then(Json::as_array) {
88        Some(FilterCriterion::Values {
89            values: vals.iter().map(crate::engine::cmd::edit::json_to_input).collect(),
90            blanks: bool_param(p, "blanks").unwrap_or(false),
91        })
92    } else if let Some(c) = p.get("custom") {
93        let a = (
94            c.get("op").and_then(Json::as_str).unwrap_or("=").to_string(),
95            c.get("value").map(crate::engine::cmd::edit::json_to_input).unwrap_or_default(),
96        );
97        let b = c
98            .get("op2")
99            .and_then(Json::as_str)
100            .map(|o| (o.to_string(), c.get("value2").map(crate::engine::cmd::edit::json_to_input).unwrap_or_default()));
101        Some(FilterCriterion::Custom { a, b, and: c.get("and").and_then(Json::as_bool).unwrap_or(true) })
102    } else if let Some(t) = p.get("top") {
103        Some(FilterCriterion::Top10 {
104            bottom: t.get("bottom").and_then(Json::as_bool).unwrap_or(false),
105            percent: t.get("percent").and_then(Json::as_bool).unwrap_or(false),
106            count: t.get("count").and_then(Json::as_u64).unwrap_or(10) as u32,
107        })
108    } else {
109        bool_param(p, "aboveAverage").map(FilterCriterion::AboveAverage)
110    };
111    edit(s, |cx| {
112        let sh = cx.sheet_mut(sheet)?;
113        if let Some(a) = sh.autofilter.as_mut() {
114            a.criteria.retain(|(o, _)| *o != off);
115            if let Some(c) = crit.clone() {
116                a.criteria.push((off, c));
117            }
118        }
119        apply_filter(&mut cx.wb, sheet);
120        cx.structural = true;
121        Ok(Json::Null)
122    })?;
123    let hidden = s.doc()?.wb.sheet(sheet).map(|sh| sh.rows.values().filter(|r| r.hidden).count()).unwrap_or(0);
124    Ok(json!({"hiddenRows": hidden}))
125}
126
127/// Does a value pass a custom comparison (`>`, `=*abc*`, `<>x`…)?
128fn custom_ok(v: &Value, text: &str, op: &str, crit: &str) -> bool {
129    let num = crate::base::parse::parse_number_text(crit);
130    let cmp = match (v.as_f64(), num) {
131        (Some(a), Some(b)) => a.partial_cmp(&b),
132        // A numeric ordering comparison only matches numbers; blanks and text never do.
133        (None, Some(_)) if matches!(op, "<" | "<=" | ">" | ">=") => None,
134        _ => Some(crate::base::compare_text(text, crit)),
135    };
136    let wild = || crate::engine::cmd::edit::wildcard(&text.to_lowercase(), &crit.to_lowercase());
137    match op {
138        "=" => {
139            if crit.contains(['*', '?']) {
140                wild()
141            } else {
142                cmp == Some(Ordering::Equal)
143            }
144        }
145        "<>" => {
146            if crit.contains(['*', '?']) {
147                !wild()
148            } else {
149                cmp != Some(Ordering::Equal)
150            }
151        }
152        ">" => cmp == Some(Ordering::Greater),
153        "<" => cmp == Some(Ordering::Less),
154        ">=" => matches!(cmp, Some(Ordering::Greater | Ordering::Equal)),
155        "<=" => matches!(cmp, Some(Ordering::Less | Ordering::Equal)),
156        "beginsWith" => text.to_lowercase().starts_with(&crit.to_lowercase()),
157        "endsWith" => text.to_lowercase().ends_with(&crit.to_lowercase()),
158        "contains" => text.to_lowercase().contains(&crit.to_lowercase()),
159        "notContains" => !text.to_lowercase().contains(&crit.to_lowercase()),
160        _ => true,
161    }
162}
163
164/// The filter's "(Blanks)" group: truly empty cells and zero-length text such as a `=""` result.
165fn filter_blank(v: &Value) -> bool {
166    v.is_empty() || matches!(v, Value::Text(t) if t.is_empty())
167}
168
169/// Excel grows an AutoFilter range when a value is entered in the row right below it, in one of its columns (#2342).
170/// A table's own filter is left to the table, and a row typed after a blank row does not count.
171/// A formula counts before it is calculated: this runs inside the edit, ahead of the recalc.
172pub(crate) fn grow_autofilter(sh: &mut Sheet, at: CellRef) {
173    if !sh.cells.get(at).is_some_and(|c| !filter_blank(&c.value) || c.formula.is_some()) {
174        return;
175    }
176    let Some(af) = sh.autofilter.as_ref() else { return };
177    let r = af.range;
178    if r.end.row.checked_add(1) != Some(at.row) || at.col < r.start.col || at.col > r.end.col || sh.tables.iter().any(|t| t.range == r) {
179        return;
180    }
181    let grown = RangeRef::new(r.start, CellRef::new(at.row, r.end.col));
182    if let Some(af) = sh.autofilter.as_mut() {
183        af.range = grown;
184    }
185}
186
187/// Hides rows of the autofilter range that fail any criterion.
188pub(crate) fn apply_filter(wb: &mut Workbook, sheet: usize) {
189    let Some(sh) = wb.sheet(sheet) else { return };
190    let Some(af) = sh.autofilter.clone() else { return };
191    let r = af.range;
192    let mut hide = Vec::new();
193    // Precompute per-criterion top10 threshold and average once, not per row.
194    let mut col_stats: std::collections::HashMap<usize, (Option<f64>, f64)> = std::collections::HashMap::new();
195    for (i, (off, c)) in af.criteria.iter().enumerate() {
196        if matches!(c, FilterCriterion::Top10 { .. } | FilterCriterion::AboveAverage(_)) {
197            let col = r.start.col + off;
198            let mut nums: Vec<f64> = (r.start.row + 1..=r.end.row).filter_map(|row| sh.value(CellRef::new(row, col)).as_f64()).collect();
199            let avg = if nums.is_empty() { 0.0 } else { nums.iter().sum::<f64>() / nums.len() as f64 };
200            let thr = match c {
201                FilterCriterion::Top10 { bottom, percent, count } => {
202                    nums.sort_by(|a, b| if *bottom { a.partial_cmp(b) } else { b.partial_cmp(a) }.unwrap_or(Ordering::Equal));
203                    let k = if *percent { ((nums.len() as f64) * (*count as f64) / 100.0).ceil() as usize } else { *count as usize };
204                    let k = k.clamp(1, nums.len().max(1));
205                    nums.get(k - 1).copied()
206                }
207                _ => None,
208            };
209            col_stats.insert(i, (thr, avg));
210        }
211    }
212    for row in r.start.row + 1..=r.end.row {
213        let mut visible = true;
214        for (i, (off, crit)) in af.criteria.iter().enumerate() {
215            let c = CellRef::new(row, r.start.col + off);
216            let v = sh.value(c);
217            let text = crate::engine::display::cell_text(wb, sh, c);
218            let ok = match crit {
219                FilterCriterion::Values { values, blanks } => {
220                    if filter_blank(&v) {
221                        *blanks
222                    } else {
223                        values.iter().any(|x| x.eq_ignore_ascii_case(&text) || x.eq_ignore_ascii_case(&v.display()))
224                    }
225                }
226                FilterCriterion::Custom { a, b, and } => {
227                    let x = custom_ok(&v, &text, &a.0, &a.1);
228                    match b {
229                        Some(b) => {
230                            let y = custom_ok(&v, &text, &b.0, &b.1);
231                            if *and { x && y } else { x || y }
232                        }
233                        None => x,
234                    }
235                }
236                FilterCriterion::Top10 { bottom, .. } => match (v.as_f64(), col_stats.get(&i)) {
237                    (Some(n), Some((thr, _))) => {
238                        let thr = thr.unwrap_or(n);
239                        if *bottom { n <= thr } else { n >= thr }
240                    }
241                    _ => false,
242                },
243                FilterCriterion::AboveAverage(above) => match (v.as_f64(), col_stats.get(&i)) {
244                    (Some(n), Some((_, avg))) => {
245                        if *above {
246                            n > *avg
247                        } else {
248                            n < *avg
249                        }
250                    }
251                    _ => false,
252                },
253                FilterCriterion::FillColor(col) => wb.styles.get(sh.style_id(c)).fill.fg == *col,
254                FilterCriterion::FontColor(col) => wb.styles.get(sh.style_id(c)).font.color == *col,
255                // Icons are not evaluated here: the row keeps its current visibility.
256                FilterCriterion::Icon { .. } => !sh.is_row_hidden(row),
257            };
258            if !ok {
259                visible = false;
260                break;
261            }
262        }
263        hide.push((row, !visible));
264    }
265    let Some(shm) = wb.sheet_mut(sheet) else { return };
266    for (row, h) in hide {
267        if h {
268            shm.rows.entry(row).or_default().hidden = true;
269        } else if let Some(e) = shm.rows.get_mut(&row) {
270            e.hidden = false;
271        }
272    }
273}
274
275pub(super) fn clear_filter(s: &mut Session, _: &Json) -> Result<Json> {
276    let sheet = s.doc()?.wb.active_sheet();
277    crate::engine::cmd::refuse_unless_allowed(s, sheet, "Filtering", |p| p.autofilter)?;
278    edit(s, |cx| {
279        let sh = cx.sheet_mut(sheet)?;
280        if let Some(a) = sh.autofilter.as_mut() {
281            a.criteria.clear();
282        }
283        apply_filter(&mut cx.wb, sheet);
284        cx.structural = true;
285        Ok(Json::Null)
286    })
287}
288
289pub(super) fn reapply(s: &mut Session, _: &Json) -> Result<Json> {
290    let sheet = s.doc()?.wb.active_sheet();
291    crate::engine::cmd::refuse_unless_allowed(s, sheet, "Filtering", |p| p.autofilter)?;
292    edit(s, |cx| {
293        apply_filter(&mut cx.wb, sheet);
294        cx.structural = true;
295        Ok(Json::Null)
296    })
297}
298
299/// Distinct display values of a filter column (for the dropdown list).
300pub fn filter_values(wb: &Workbook, sheet: usize, col: u32) -> Vec<(String, bool)> {
301    let Some(sh) = wb.sheet(sheet) else { return vec![] };
302    let Some(af) = &sh.autofilter else { return vec![] };
303    let off = col.saturating_sub(af.range.start.col);
304    let selected: Option<&FilterCriterion> = af.criteria.iter().find(|(o, _)| *o == off).map(|(_, c)| c);
305    let mut seen = std::collections::BTreeMap::new();
306    for row in af.range.start.row + 1..=af.range.end.row.min(af.range.start.row + 1_000_000) {
307        let c = CellRef::new(row, col);
308        let v = sh.value(c);
309        let blank = filter_blank(&v);
310        let t = if blank { "(Blanks)".to_string() } else { crate::engine::display::cell_text(wb, sh, c) };
311        let on = match selected {
312            Some(FilterCriterion::Values { values, blanks }) => {
313                if blank {
314                    *blanks
315                } else {
316                    values.iter().any(|x| x.eq_ignore_ascii_case(&t))
317                }
318            }
319            _ => true,
320        };
321        let key = if blank { sort_key(&Value::Empty) } else { sort_key(&v) };
322        seen.entry((key, t)).or_insert(on);
323    }
324    seen.into_iter().map(|((_, t), on)| (t, on)).collect()
325}
ravenrs_sheet/engine/cmd/data/filter.rs

ravenrs_sheet/engine/cmd/data/
filter.rs
