ravenrs_sheet/engine/cmd/
data.rs

1//! Data tab: sort, filter, remove duplicates, text to columns, data validation, outline.
2
3use std::cmp::Ordering;
4use std::sync::Arc;
5
6use crate::base::{CellRef, RangeRef, Value, sort_compare};
7use crate::model::{
8    Anchor, AnchorMode, AutoFilter, Cell, CfOperator, Comment, ErrorStyle, FilterCriterion, Formula, Hyperlink, LineInfo, Sheet, Validation,
9    ValidationKind, Workbook,
10};
11use serde_json::{Value as Json, json};
12
13use super::{
14    CommandSpec, EngineError, Result, Session, bad, bool_param, cell_param, cell_param_on, cmd, edit, has_doc, ok, parse_lines, split_sheet,
15    str_param, target_range, target_ranges, target_sheet,
16};
17use crate::engine::outline;
18use crate::engine::selection::current_region;
19
20mod filter;
21mod grouping;
22mod sort;
23mod tools;
24mod validation;
25
26pub use filter::filter_values;
27pub(crate) use filter::{apply_filter, grow_autofilter};
28use filter::{clear_filter, filter_by, reapply, toggle_filter};
29use grouping::{detail, group, outline_settings, show_levels, subtotal};
30use sort::{quick_sort, sort};
31#[cfg(test)]
32use tools::split_delimited;
33use tools::{remove_duplicates, text_to_columns};
34pub use validation::{check_validation, list_items};
35use validation::{circle_invalid, validate_cmd, validation};
36pub(super) use validation::{move_rule_formula, remove_validation_cells};
37
38pub fn specs() -> Vec<CommandSpec> {
39    vec![
40        cmd!("data.sortAscending", "Sort A to Z", ["Data", "Sort & Filter"], None, "{range?, column?: \"B\"}", has_doc, |s, p| quick_sort(
41            s, p, true
42        )),
43        cmd!("data.sortDescending", "Sort Z to A", ["Data", "Sort & Filter"], None, "{range?, column?}", has_doc, |s, p| quick_sort(s, p, false)),
44        cmd!(
45            "data.sort",
46            "Sort…",
47            ["Data", "Sort & Filter"],
48            None,
49            "{range?, header?: bool, keys: [{column: \"B\", order: asc|desc, by?: values|cellColor|fontColor, customList?: [..]}], orientation?: rows|columns, matchCase?}",
50            has_doc,
51            sort
52        ),
53        cmd!(
54            "data.filter",
55            "Filter",
56            ["Data", "Sort & Filter"],
57            Some("Ctrl+Shift+L"),
58            "{range?} toggles AutoFilter on the current region",
59            has_doc,
60            toggle_filter
61        ),
62        cmd!(
63            "data.filterBy",
64            "Filter Column",
65            [],
66            None,
67            "{column: \"B\" (or 0-based offset), values?: [..], blanks?: bool, custom?: {op: \">\", value: \"10\", op2?, value2?, and?}, top?: {count, bottom?, percent?}, aboveAverage?: bool, clear?: bool}",
68            has_doc,
69            filter_by
70        ),
71        cmd!("data.clearFilter", "Clear", ["Data", "Sort & Filter"], None, "{}", has_doc, clear_filter),
72        cmd!("data.reapply", "Reapply", ["Data", "Sort & Filter"], None, "{}", has_doc, reapply),
73        cmd!(
74            "data.removeDuplicates",
75            "Remove Duplicates",
76            ["Data", "Data Tools"],
77            None,
78            "{range?, columns?: [\"A\",\"C\"], header?: bool}",
79            has_doc,
80            remove_duplicates
81        ),
82        cmd!(
83            "data.textToColumns",
84            "Text to Columns",
85            ["Data", "Data Tools"],
86            None,
87            "{range?, delimiters?: [\",\", \"\\t\", \" \", \";\"], other?: \"|\", fixedWidths?: [5, 10], treatConsecutive?: bool, textQualifier?: \"\\\"\", destination?: \"B1\"}",
88            has_doc,
89            text_to_columns
90        ),
91        cmd!(
92            "data.validation",
93            "Data Validation…",
94            ["Data", "Data Tools"],
95            None,
96            "{range?, type: any|whole|decimal|list|date|time|textLength|custom, operator?: between|notBetween|equal|notEqual|greater|less|greaterOrEqual|lessOrEqual, formula1, formula2?, ignoreBlank?, dropdown?, inputTitle?, inputMessage?, errorStyle?: stop|warning|information, errorTitle?, errorMessage?, clear?: bool}",
97            has_doc,
98            validation
99        ),
100        cmd!(query "data.validate", "Check Value Against Validation", [], None, "{cell?, input} → {ok, message?}", has_doc, validate_cmd),
101        cmd!(noundo "data.circleInvalid", "Circle Invalid Data", ["Data", "Data Tools", "Data Validation"], None, "{} → invalid cells", has_doc, circle_invalid),
102        CommandSpec {
103            shortcut_mac: Some("Cmd+Shift+K"),
104            ..cmd!(
105                "data.group",
106                "Group",
107                ["Data", "Outline"],
108                Some("Alt+Shift+Right"),
109                "{rows?: \"2:5\" | cols?: \"B:C\" | by?: rows|cols (the selection's rows or columns; a block of cells groups rows by default), sheet?}",
110                has_doc,
111                |s, p| group(s, p, true)
112            )
113        },
114        CommandSpec {
115            shortcut_mac: Some("Cmd+Shift+J"),
116            ..cmd!(
117                "data.ungroup",
118                "Ungroup",
119                ["Data", "Outline"],
120                Some("Alt+Shift+Left"),
121                "{rows? | cols? | by?: rows|cols, sheet?}",
122                has_doc,
123                |s, p| { group(s, p, false) }
124            )
125        },
126        cmd!(
127            "data.hideDetail",
128            "Hide Detail",
129            ["Data", "Outline"],
130            None,
131            "{rows? | cols? | by?, level?: 1-7} (the innermost group, or the one at `level`, holding the lines or summarised by them)",
132            has_doc,
133            |s, p| detail(s, p, true)
134        ),
135        cmd!("data.showDetail", "Show Detail", ["Data", "Outline"], None, "{rows? | cols? | by?, level?: 1-7}", has_doc, |s, p| detail(s, p, false)),
136        cmd!(
137            "data.showLevels",
138            "Show Outline Levels",
139            [],
140            None,
141            "{rows?: 1-8, cols?: 1-8} (1 shows only the top-level summaries, as the outline's level buttons)",
142            has_doc,
143            show_levels
144        ),
145        cmd!(
146            "data.outlineSettings",
147            "Outline Settings",
148            ["Data", "Outline"],
149            None,
150            "{sheet?, summaryBelow?: bool, summaryRight?: bool} (summary rows below their detail, summary columns right of it; off puts them above / left) → {summaryBelow, summaryRight}",
151            has_doc,
152            outline_settings
153        ),
154        cmd!(
155            "data.subtotal",
156            "Subtotal",
157            ["Data", "Outline"],
158            None,
159            "{range?, groupBy: \"A\", function?: sum|count|average|max|min|product, columns: [\"C\"], header?: true}",
160            has_doc,
161            subtotal
162        ),
163        cmd!("data.flashFill", "Flash Fill", ["Data", "Data Tools"], None, "{range?}", has_doc, |s, p| s.execute("edit.flashFill", p.clone())),
164    ]
165}
166
167fn col_param(v: Option<&Json>, base: u32) -> Option<u32> {
168    match v? {
169        Json::String(s) => crate::base::letters_to_col(s.trim_start_matches('$')),
170        Json::Number(n) => n.as_u64().and_then(|o| u32::try_from(o).ok()).and_then(|o| base.checked_add(o)),
171        _ => None,
172    }
173}
174
175/// The range a data command works on: the selection when it's a block, else the current
176/// region (trimmed to the used range for whole columns).
177fn data_range(s: &Session, p: &Json) -> Result<RangeRef> {
178    let d = s.doc()?;
179    let sh = d.wb.active().ok_or(EngineError::NoDocument)?;
180    if p.get("range").is_some() {
181        return target_range(s, p);
182    }
183    if let Some(t) = sh.table_at(d.selection.active) {
184        return Ok(t.data_range().map(|dr| RangeRef::new(t.range.start, dr.end)).unwrap_or(t.range));
185    }
186    let sel = d.selection.current();
187    if !sel.is_single() {
188        // An empty sheet has no data to work on: just the active cell.
189        let Some(used) = sh.used_range() else { return Ok(RangeRef::cell(d.selection.active)) };
190        return Ok(sel.intersection(&used).unwrap_or(sel));
191    }
192    Ok(current_region(sh, d.selection.active))
193}
194
195/// The sheet and range a data command works on: the sheet `sheet` or a `Sheet!` prefix on `range`
196/// names, else the active one. The selection belongs to the active sheet, so another sheet needs `range`.
197fn data_target(s: &Session, p: &Json, cmd: &str) -> Result<(usize, RangeRef)> {
198    let sheet = target_sheet(s, p)?;
199    let d = s.doc()?;
200    if let Some(t) = str_param(p, "range")
201        && let (Some(name), _) = split_sheet(t)
202        && d.wb.sheet_index(&name) != Some(sheet)
203    {
204        return Err(bad(cmd, format!("`{t}` names a sheet other than `sheet`")));
205    }
206    if sheet != d.wb.active_sheet() && p.get("range").is_none() {
207        return Err(bad(cmd, "missing `range`: the selection is on the active sheet"));
208    }
209    Ok((sheet, data_range(s, p)?))
210}
211
212/// Excel's header guess: the first row is text over a column of numbers, or is bold.
213fn guess_header(wb: &Workbook, sh: &Sheet, r: RangeRef) -> bool {
214    if let Some(t) = sh.table_at(r.start) {
215        return t.header_row;
216    }
217    if r.height() < 2 {
218        return false;
219    }
220    let mut votes = 0i32;
221    for c in r.start.col..=r.end.col {
222        let a = sh.value(CellRef::new(r.start.row, c));
223        let b = sh.value(CellRef::new(r.start.row + 1, c));
224        if a.is_text() && !b.is_text() && !b.is_empty() {
225            votes += 1;
226        }
227        if wb.styles.get(sh.style_id(CellRef::new(r.start.row, c))).font.bold
228            != wb.styles.get(sh.style_id(CellRef::new(r.start.row + 1, c))).font.bold
229        {
230            votes += 1;
231        }
232        if a.is_text() && b.is_text() {
233            votes -= 0;
234        }
235    }
236    votes > 0
237}
238
239#[cfg(test)]
240mod tests;
ravenrs_sheet/engine/cmd/data.rs

ravenrs_sheet/engine/cmd/
data.rs
