ravenrs_sheet/engine/cmd/
pivot.rs

1//! PivotTable commands (Insert › PivotTable, PivotTable Analyze, PivotTable Design, Data ›
2//! Refresh All). Every change re-renders the report as one undo step.
3//!
4//! `pivot?` parameters name a PivotTable (name or numeric id); by default it is the pivot whose
5//! report contains the active cell, else the last pivot on the active sheet.
6
7use std::sync::Arc;
8
9use crate::base::{CellRef, RangeRef};
10use crate::model::{
11    Anchor, Cell, Chart, ChartKind, LegendPos, NumFmt, PivotCalcField, PivotDateGroup, PivotField, PivotFilter, PivotFunc, PivotLayout, PivotShowAs,
12    PivotSort, PivotTable, PivotValue, Sheet, StyleId, Workbook,
13};
14use serde_json::{Value as Json, json};
15
16use super::{
17    CommandSpec, Ctx, EngineError, Result, Session, anchor_mode_param, bad, bool_param, cell_param, cmd, edit, f64_param, has_doc, ok, str_param,
18    u32_param,
19};
20use crate::engine::pivot as pv;
21use crate::engine::selection::current_region;
22
23mod charts;
24mod create;
25mod fields;
26mod layout;
27mod params;
28
29use charts::{insert_pivot_chart, recommended};
30use create::{insert_pivot, options, show_details, summarize_table};
31use fields::{add_field, calculated_field, fields, list, move_field, remove_field, value_settings};
32use layout::{change_source, collapse, delete, filter, grand_totals, group, layout, refresh, refresh_all, sort};
33
34pub fn specs() -> Vec<CommandSpec> {
35    vec![
36        cmd!(
37            "insert.pivotTable",
38            "PivotTable",
39            ["Insert", "Tables"],
40            None,
41            "{source?: \"Sheet1!$A$1:$E$200\" | table name (default: the table or current region of the selection), destination?: \"new\" (default: new sheet before the current one, report at A3) | \"Sheet1!H3\", name?, rows?: [field | {field, group?: years|quarters|months|days}], columns?: [..], values?: [field | {field, func?, name?}], filters?: [field], replace?: bool} → {id, name, sheet, anchor, fields}",
42            has_doc,
43            insert_pivot
44        ),
45        cmd!(
46            "insert.recommendedPivotTables",
47            "Recommended PivotTables",
48            ["Insert", "Tables"],
49            None,
50            "{source?, apply?: index, destination?} → suggestions [{index, title, rows, columns, values}]; with apply, creates that one",
51            has_doc,
52            recommended
53        ),
54        cmd!(
55            "table.summarizePivot",
56            "Summarize with PivotTable",
57            ["Table Design", "Tools"],
58            None,
59            "{table?, destination?} creates a PivotTable from the table",
60            has_doc,
61            summarize_table
62        ),
63        cmd!(
64            "pivot.calculatedField",
65            "Calculated Field",
66            ["PivotTable Analyze", "Calculations"],
67            None,
68            "{pivot?, name, formula?: \"=Sales*0.1\" (fields bare or 'quoted'; evaluated on each cell's sums), delete?: bool, values?: bool (add a new field to Values, default true)} adds, changes or deletes a calculated field; without `name` opens the dialog → {name, formula, calculatedFields}",
69            has_doc,
70            calculated_field
71        ),
72        cmd!(
73            "insert.pivotChart",
74            "PivotChart",
75            ["Insert", "Charts"],
76            None,
77            "{pivot?: name | id | a cell of the report (\"Sheet2!B5\"; default: the PivotTable at the active cell), type?: column|bar|line|pie|area|… (default column), subtype?, title?, at?: \"H2\", width?, height?} adds a chart bound to the PivotTable's report: categories are the innermost row items, series the column items × value fields; it follows refreshes and layout changes → {chart, pivot, series}",
78            has_doc,
79            insert_pivot_chart
80        ),
81        cmd!(query "pivot.fields", "PivotTable Fields", [], None, "{pivot?} → source fields (name, numeric, date, samples, areas) and the current layout", has_doc, fields),
82        cmd!(query "pivot.fieldList", "Field List", ["PivotTable Analyze", "Show"], None, "{pivot?} (same as pivot.fields)", has_doc, fields),
83        cmd!(query "pivot.list", "PivotTables", [], None, "{} → every PivotTable in the workbook", has_doc, list),
84        cmd!(
85            "pivot.addField",
86            "Add Field",
87            [],
88            None,
89            "{pivot?, field, area: rows|columns|values|filters, func?: sum|count|average|max|min|product|countNumbers|stdDev|stdDevP|var|varP, name?, position?: index, replace?}",
90            has_doc,
91            add_field
92        ),
93        cmd!(
94            "pivot.removeField",
95            "Remove Field",
96            [],
97            None,
98            "{pivot?, field (or value caption), area?: rows|columns|values|filters, replace?}",
99            has_doc,
100            remove_field
101        ),
102        cmd!(
103            "pivot.moveField",
104            "Move Field",
105            [],
106            None,
107            "{pivot?, field (\"Σ Values\" moves the value fields between rows and columns; in columns it stays after the last field), from: rows|columns|values|filters, to: rows|columns|values|filters, position?: index, replace?}",
108            has_doc,
109            move_field
110        ),
111        cmd!(
112            "pivot.valueSettings",
113            "Value Field Settings",
114            ["PivotTable Analyze", "Active Field"],
115            None,
116            "{pivot?, field: caption or source field | index: n, func?, name?, showAs?: normal|percentOfGrandTotal|percentOfColumnTotal|percentOfRowTotal|runningTotal|rank, numberFormat?: \"#,##0\" | null, replace?}",
117            has_doc,
118            value_settings
119        ),
120        cmd!("pivot.filter", "Filter Items", [], None, "{pivot?, field, selected: [\"East\", ..] | null (all), replace?}", has_doc, filter),
121        cmd!("pivot.sort", "Sort Field", [], None, "{pivot?, field, order: asc|desc|none, replace?}", has_doc, sort),
122        cmd!(
123            "pivot.group",
124            "Group Field",
125            ["PivotTable Analyze", "Group"],
126            None,
127            "{pivot?, field, by: years|quarters|months|days|none, replace?}",
128            has_doc,
129            group
130        ),
131        cmd!(
132            "pivot.collapse",
133            "Expand/Collapse Field",
134            ["PivotTable Analyze", "Active Field"],
135            None,
136            "{pivot?, field, items?: [labels] (default: all), collapse?: true, replace?}",
137            has_doc,
138            collapse
139        ),
140        cmd!(
141            "pivot.layout",
142            "PivotTable Layout",
143            ["PivotTable Design", "Layout"],
144            None,
145            "{pivot?, layout?: compact|outline|tabular, grandTotalsRows?: bool, grandTotalsCols?: bool, subtotals?: bool|top|bottom|none, style?: \"PivotStyleLight16\", showHeaders?: bool, replace?}",
146            has_doc,
147            layout
148        ),
149        cmd!(
150            "pivot.grandTotals",
151            "Grand Totals",
152            ["PivotTable Design", "Layout"],
153            None,
154            "{pivot?, mode?: off|on|rows|columns, rows?: bool, cols?: bool}",
155            has_doc,
156            grand_totals
157        ),
158        cmd!("pivot.subtotals", "Subtotals", ["PivotTable Design", "Layout"], None, "{pivot?, mode: none|top|bottom}", has_doc, |s, p| {
159            let mode = str_param(p, "mode").unwrap_or("top");
160            layout(s, &with(p, "subtotals", json!(mode)))
161        }),
162        cmd!(
163            "pivot.reportLayout",
164            "Report Layout",
165            ["PivotTable Design", "Layout"],
166            None,
167            "{pivot?, layout: compact|outline|tabular}",
168            has_doc,
169            layout
170        ),
171        cmd!(
172            "pivot.style",
173            "PivotTable Styles",
174            ["PivotTable Design", "PivotTable Styles"],
175            None,
176            "{pivot?, style: \"PivotStyleMedium9\"}",
177            has_doc,
178            layout
179        ),
180        cmd!("pivot.refresh", "Refresh", ["PivotTable Analyze", "Data"], Some("Alt+F5"), "{pivot?, replace?}", has_doc, refresh),
181        cmd!(
182            "data.refreshAll",
183            "Refresh All",
184            ["Data", "Queries & Connections"],
185            Some("Cmd+Alt+F5"),
186            "{replace?} refreshes every PivotTable → {refreshed, errors}",
187            has_doc,
188            refresh_all
189        ),
190        cmd!(
191            "pivot.changeSource",
192            "Change Data Source",
193            ["PivotTable Analyze", "Data"],
194            None,
195            "{pivot?, source: \"Sheet1!$A$1:$E$300\" | table name, replace?} (fields missing from the new source are removed)",
196            has_doc,
197            change_source
198        ),
199        cmd!(
200            "pivot.showDetails",
201            "Show Details",
202            [],
203            None,
204            "{cell?: \"B5\" (a value, subtotal or grand total cell of a PivotTable on the active sheet; default: the active cell)} puts the source records behind that value on a new sheet before the current one → {sheet, records}. An error when the PivotTable's Enable show details option is off (`pivot.options` `{enableShowDetails: true}` turns it on).",
205            has_doc,
206            show_details
207        ),
208        cmd!(
209            "pivot.options",
210            "PivotTable Options",
211            ["PivotTable Analyze", "PivotTable"],
212            None,
213            "{pivot?, enableShowDetails?: bool (Data › Enable show details: off, Show Details and double-clicking a value cell list no records)} → {enableShowDetails}",
214            has_doc,
215            options
216        ),
217        cmd!(
218            "pivot.delete",
219            "Delete PivotTable",
220            ["PivotTable Analyze", "Actions"],
221            None,
222            "{pivot?} clears the report and removes the PivotTable",
223            has_doc,
224            delete
225        ),
226    ]
227}
228
229fn with(p: &Json, k: &str, v: Json) -> Json {
230    let mut m = p.as_object().cloned().unwrap_or_default();
231    m.insert(k.into(), v);
232    Json::Object(m)
233}
234
235fn other(msg: impl Into<String>) -> EngineError {
236    EngineError::Other(msg.into())
237}
238
239// ---------------------------------------------------------------- lookup
240
241/// (sheet, index) of the pivot a call targets.
242fn find_pivot(s: &Session, p: &Json) -> Result<(usize, usize)> {
243    let d = s.doc()?;
244    let active = d.wb.active_sheet();
245    if let Some(v) = p.get("pivot").filter(|v| !v.is_null()) {
246        let mut order: Vec<usize> = vec![active];
247        order.extend((0..d.wb.sheets.len()).filter(|i| *i != active));
248        for si in order {
249            let Some(sh) = d.wb.sheet(si) else { continue };
250            let hit = sh.pivots.iter().position(|pt| match v {
251                Json::String(n) => pt.name.eq_ignore_ascii_case(n),
252                Json::Number(n) => n.as_u64() == Some(pt.id as u64),
253                _ => false,
254            });
255            if let Some(i) = hit {
256                return Ok((si, i));
257            }
258        }
259        return Err(bad("pivot", format!("no PivotTable `{}`", v.as_str().map(str::to_string).unwrap_or_else(|| v.to_string()))));
260    }
261    let sh = d.wb.active().ok_or(EngineError::NoDocument)?;
262    let at = d.selection.active;
263    if let Some(i) = sh.pivots.iter().position(|pt| pt.last_range.is_some_and(|r| r.contains(at))) {
264        return Ok((active, i));
265    }
266    if !sh.pivots.is_empty() {
267        return Ok((active, sh.pivots.len() - 1));
268    }
269    Err(EngineError::Disabled("pivot".into(), "select a cell in a PivotTable (or pass `pivot`)".into()))
270}
271
272#[cfg(test)]
273mod tests;
ravenrs_sheet/engine/cmd/pivot.rs

ravenrs_sheet/engine/cmd/
pivot.rs
