1687a9c05b0c
pivot.rs
//! PivotTable commands (Insert โบ PivotTable, PivotTable Analyze, PivotTable Design, Data โบ
//! Refresh All). Every change re-renders the report as one undo step.
//!
//! `pivot?` parameters name a PivotTable (name or numeric id); by default it is the pivot whose
//! report contains the active cell, else the last pivot on the active sheet.
use std::sync::Arc;
use gridcraft_core::{CellRef, RangeRef};
use gridcraft_model::{
Anchor, Cell, Chart, ChartKind, LegendPos, NumFmt, PivotCalcField, PivotDateGroup, PivotField, PivotFilter, PivotFunc, PivotLayout, PivotShowAs,
PivotSort, PivotTable, PivotValue, Sheet, StyleId, Workbook,
};
use serde_json::{Value as Json, json};
use super::{
CommandSpec, Ctx, EngineError, Result, Session, anchor_mode_param, bad, bool_param, cell_param, cmd, edit, f64_param, has_doc, ok, str_param,
u32_param,
};
use crate::pivot as pv;
use crate::selection::current_region;
mod charts;
mod create;
mod fields;
mod layout;
mod params;
use charts::{insert_pivot_chart, recommended};
use create::{insert_pivot, options, show_details, summarize_table};
use fields::{add_field, calculated_field, fields, list, move_field, remove_field, value_settings};
use layout::{change_source, collapse, delete, filter, grand_totals, group, layout, refresh, refresh_all, sort};
pub fn specs() -> Vec<CommandSpec> {
vec![
cmd!(
"insert.pivotTable",
"PivotTable",
["Insert", "Tables"],
None,
"{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}",
has_doc,
insert_pivot
),
cmd!(
"insert.recommendedPivotTables",
"Recommended PivotTables",
["Insert", "Tables"],
None,
"{source?, apply?: index, destination?} โ suggestions [{index, title, rows, columns, values}]; with apply, creates that one",
has_doc,
recommended
),
cmd!(
"table.summarizePivot",
"Summarize with PivotTable",
["Table Design", "Tools"],
None,
"{table?, destination?} creates a PivotTable from the table",
has_doc,
summarize_table
),
cmd!(
"pivot.calculatedField",
"Calculated Field",
["PivotTable Analyze", "Calculations"],
None,
"{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}",
has_doc,
calculated_field
),
cmd!(
"insert.pivotChart",
"PivotChart",
["Insert", "Charts"],
None,
"{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}",
has_doc,
insert_pivot_chart
),
cmd!(query "pivot.fields", "PivotTable Fields", [], None, "{pivot?} โ source fields (name, numeric, date, samples, areas) and the current layout", has_doc, fields),
cmd!(query "pivot.fieldList", "Field List", ["PivotTable Analyze", "Show"], None, "{pivot?} (same as pivot.fields)", has_doc, fields),
cmd!(query "pivot.list", "PivotTables", [], None, "{} โ every PivotTable in the workbook", has_doc, list),
cmd!(
"pivot.addField",
"Add Field",
[],
None,
"{pivot?, field, area: rows|columns|values|filters, func?: sum|count|average|max|min|product|countNumbers|stdDev|stdDevP|var|varP, name?, position?: index, replace?}",
has_doc,
add_field
),
cmd!(
"pivot.removeField",
"Remove Field",
[],
None,
"{pivot?, field (or value caption), area?: rows|columns|values|filters, replace?}",
has_doc,
remove_field
),
cmd!(
"pivot.moveField",
"Move Field",
[],
None,
"{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?}",
has_doc,
move_field
),
cmd!(
"pivot.valueSettings",
"Value Field Settings",
["PivotTable Analyze", "Active Field"],
None,
"{pivot?, field: caption or source field | index: n, func?, name?, showAs?: normal|percentOfGrandTotal|percentOfColumnTotal|percentOfRowTotal|runningTotal|rank, numberFormat?: \"#,##0\" | null, replace?}",
has_doc,
value_settings
),
cmd!("pivot.filter", "Filter Items", [], None, "{pivot?, field, selected: [\"East\", ..] | null (all), replace?}", has_doc, filter),
cmd!("pivot.sort", "Sort Field", [], None, "{pivot?, field, order: asc|desc|none, replace?}", has_doc, sort),
cmd!(
"pivot.group",
"Group Field",
["PivotTable Analyze", "Group"],
None,
"{pivot?, field, by: years|quarters|months|days|none, replace?}",
has_doc,
group
),
cmd!(
"pivot.collapse",
"Expand/Collapse Field",
["PivotTable Analyze", "Active Field"],
None,
"{pivot?, field, items?: [labels] (default: all), collapse?: true, replace?}",
has_doc,
collapse
),
cmd!(
"pivot.layout",
"PivotTable Layout",
["PivotTable Design", "Layout"],
None,
"{pivot?, layout?: compact|outline|tabular, grandTotalsRows?: bool, grandTotalsCols?: bool, subtotals?: bool|top|bottom|none, style?: \"PivotStyleLight16\", showHeaders?: bool, replace?}",
has_doc,
layout
),
cmd!(
"pivot.grandTotals",
"Grand Totals",
["PivotTable Design", "Layout"],
None,
"{pivot?, mode?: off|on|rows|columns, rows?: bool, cols?: bool}",
has_doc,
grand_totals
),
cmd!("pivot.subtotals", "Subtotals", ["PivotTable Design", "Layout"], None, "{pivot?, mode: none|top|bottom}", has_doc, |s, p| {
let mode = str_param(p, "mode").unwrap_or("top");
layout(s, &with(p, "subtotals", json!(mode)))
}),
cmd!(
"pivot.reportLayout",
"Report Layout",
["PivotTable Design", "Layout"],
None,
"{pivot?, layout: compact|outline|tabular}",
has_doc,
layout
),
cmd!(
"pivot.style",
"PivotTable Styles",
["PivotTable Design", "PivotTable Styles"],
None,
"{pivot?, style: \"PivotStyleMedium9\"}",
has_doc,
layout
),
cmd!("pivot.refresh", "Refresh", ["PivotTable Analyze", "Data"], Some("Alt+F5"), "{pivot?, replace?}", has_doc, refresh),
cmd!(
"data.refreshAll",
"Refresh All",
["Data", "Queries & Connections"],
Some("Cmd+Alt+F5"),
"{replace?} refreshes every PivotTable โ {refreshed, errors}",
has_doc,
refresh_all
),
cmd!(
"pivot.changeSource",
"Change Data Source",
["PivotTable Analyze", "Data"],
None,
"{pivot?, source: \"Sheet1!$A$1:$E$300\" | table name, replace?} (fields missing from the new source are removed)",
has_doc,
change_source
),
cmd!(
"pivot.showDetails",
"Show Details",
[],
None,
"{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).",
has_doc,
show_details
),
cmd!(
"pivot.options",
"PivotTable Options",
["PivotTable Analyze", "PivotTable"],
None,
"{pivot?, enableShowDetails?: bool (Data โบ Enable show details: off, Show Details and double-clicking a value cell list no records)} โ {enableShowDetails}",
has_doc,
options
),
cmd!(
"pivot.delete",
"Delete PivotTable",
["PivotTable Analyze", "Actions"],
None,
"{pivot?} clears the report and removes the PivotTable",
has_doc,
delete
),
]
}
fn with(p: &Json, k: &str, v: Json) -> Json {
let mut m = p.as_object().cloned().unwrap_or_default();
m.insert(k.into(), v);
Json::Object(m)
}
fn other(msg: impl Into<String>) -> EngineError {
EngineError::Other(msg.into())
}
// ---------------------------------------------------------------- lookup
/// (sheet, index) of the pivot a call targets.
fn find_pivot(s: &Session, p: &Json) -> Result<(usize, usize)> {
let d = s.doc()?;
let active = d.wb.active_sheet();
if let Some(v) = p.get("pivot").filter(|v| !v.is_null()) {
let mut order: Vec<usize> = vec![active];
order.extend((0..d.wb.sheets.len()).filter(|i| *i != active));
for si in order {
let Some(sh) = d.wb.sheet(si) else { continue };
let hit = sh.pivots.iter().position(|pt| match v {
Json::String(n) => pt.name.eq_ignore_ascii_case(n),
Json::Number(n) => n.as_u64() == Some(pt.id as u64),
_ => false,
});
if let Some(i) = hit {
return Ok((si, i));
}
}
return Err(bad("pivot", format!("no PivotTable `{}`", v.as_str().map(str::to_string).unwrap_or_else(|| v.to_string()))));
}
let sh = d.wb.active().ok_or(EngineError::NoDocument)?;
let at = d.selection.active;
if let Some(i) = sh.pivots.iter().position(|pt| pt.last_range.is_some_and(|r| r.contains(at))) {
return Ok((active, i));
}
if !sh.pivots.is_empty() {
return Ok((active, sh.pivots.len() - 1));
}
Err(EngineError::Disabled("pivot".into(), "select a cell in a PivotTable (or pass `pivot`)".into()))
}
#[cfg(test)]
mod tests;
No file is open. Choose one in the tree, or press Ctrl+O (โO on a Mac) to find one by name.
Syntax highlighting could not load. The unmodified read-only text remains available above.