ravenrs_sheet/engine/
pivot.rs

1//! PivotTables: reading the source list, grouping and aggregating, laying out the report and
2//! writing it onto the sheet as static, styled cells.
3//!
4//! Behaviour follows Excel's documented PivotTable model (rows/columns/values/filters areas,
5//! Compact/Outline/Tabular layouts, subtotals, grand totals, show-values-as); the code and the
6//! look (our own palette) are original.
7
8use std::cmp::Ordering;
9use std::collections::{HashMap, HashSet};
10
11use crate::base::date::datetime_from_serial;
12pub use crate::base::quote_sheet;
13use crate::base::{CellError, CellRef, MAX_COLS, MAX_ROWS, RangeRef, Value, split_sheet};
14use crate::model::{
15    BorderLine, BorderStyle, Cell, Chart, Color, Fill, HAlign, NumFmt, PivotDateGroup, PivotField, PivotFilter, PivotFunc, PivotLayout, PivotShowAs,
16    PivotSort, PivotTable, PivotValue, Series, Style, StyleId, Workbook,
17};
18
19use crate::engine::pivot_calc::CalcSet;
20
21mod charts;
22mod compute;
23mod items;
24mod output;
25
26use charts::rebind_charts;
27pub use charts::{chart_link, chart_series, clear_output, links_to, parse_chart_link};
28use compute::compute_from;
29pub use compute::{NOT_A_VALUE_CELL, detail_cells, detail_records};
30pub use items::{distinct_labels, func_caption, item_label};
31#[cfg(test)]
32use output::data_cols_exceed_sheet;
33pub use output::{CellKind, OutCell, Output, RowKind};
34use output::{cell_style, look};
35
36/// Largest source block read (cells).
37pub const MAX_SOURCE_CELLS: u64 = 10_000_000;
38/// Largest report written (cells).
39pub const MAX_OUTPUT_CELLS: u64 = 2_000_000;
40/// Most fields in the Rows plus Columns areas.
41pub const MAX_AXIS_FIELDS: usize = 32;
42/// Most fields in the Values area.
43pub const MAX_VALUE_FIELDS: usize = 256;
44
45const MONTHS: [&str; 12] = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
46
47// ---------------------------------------------------------------- source
48
49/// The source list: header names, per-column number formats and the records.
50#[derive(Clone, Debug)]
51pub struct Source {
52    pub sheet: usize,
53    pub range: RangeRef,
54    pub headers: Vec<String>,
55    pub formats: Vec<String>,
56    /// Every non-blank value in the column is a number.
57    pub numeric: Vec<bool>,
58    /// Numeric with a date number format.
59    pub dates: Vec<bool>,
60    pub rows: Vec<Vec<Value>>,
61}
62
63impl Source {
64    pub fn col(&self, name: &str) -> Option<usize> {
65        self.headers.iter().position(|h| h.eq_ignore_ascii_case(name))
66    }
67}
68
69/// Absolute reference text for a range on a sheet: `Sheet1!$A$1:$E$20`.
70pub fn range_text(sheet_name: &str, r: RangeRef) -> String {
71    let abs = |c: CellRef| format!("${}${}", crate::base::col_to_letters(c.col), c.row as u64 + 1);
72    format!("{}!{}:{}", quote_sheet(sheet_name), abs(r.start), abs(r.end))
73}
74
75/// Resolves source text (range, table name or defined name) to (sheet, range including header).
76pub fn resolve_source(wb: &Workbook, text: &str, default_sheet: usize) -> Result<(usize, RangeRef), String> {
77    resolve_inner(wb, text, default_sheet, 0)
78}
79
80fn resolve_inner(wb: &Workbook, text: &str, default_sheet: usize, depth: u32) -> Result<(usize, RangeRef), String> {
81    const INVALID: &str = "The reference isn't valid.";
82    let t = text.trim().trim_start_matches('=').trim();
83    if t.is_empty() || depth > 4 {
84        return Err(INVALID.into());
85    }
86    if let Some((si, ti)) = wb.table(t)
87        && let Some(tb) = wb.sheet(si).and_then(|s| s.tables.get(ti))
88    {
89        if !tb.header_row {
90            return Err("The table needs a header row to be used as a PivotTable source.".into());
91        }
92        let end_row = tb.range.end.row.saturating_sub(tb.totals_row as u32).max(tb.range.start.row);
93        return Ok((si, RangeRef::new(tb.range.start, CellRef::new(end_row, tb.range.end.col))));
94    }
95    let (sheet, body) = split_sheet(t);
96    if let Some(r) = RangeRef::parse(body) {
97        let si = match sheet {
98            Some(n) => wb.sheet_index(&n).ok_or_else(|| INVALID.to_string())?,
99            None => default_sheet,
100        };
101        let sh = wb.sheet(si).ok_or_else(|| INVALID.to_string())?;
102        let r = if r.is_full_cols() || r.is_full_rows() {
103            let used = sh.used_range().ok_or_else(|| INVALID.to_string())?;
104            r.intersection(&used).ok_or_else(|| INVALID.to_string())?
105        } else {
106            r
107        };
108        return Ok((si, r));
109    }
110    if sheet.is_none()
111        && let Some(n) = wb.name(t, default_sheet)
112    {
113        let f = n.formula.clone();
114        return resolve_inner(wb, &f, default_sheet, depth + 1);
115    }
116    Err(INVALID.into())
117}
118
119const FIELD_NAME_ERR: &str =
120    "The PivotTable field name is not valid. To create a PivotTable, you must use data that is organized as a list with labeled columns.";
121
122/// Reads the source list.
123pub fn read_source(wb: &Workbook, text: &str, default_sheet: usize) -> Result<Source, String> {
124    let (si, r) = resolve_source(wb, text, default_sheet)?;
125    let sh = wb.sheet(si).ok_or("The reference isn't valid.")?;
126    if r.count() > MAX_SOURCE_CELLS {
127        return Err("The source range is too large for a PivotTable.".into());
128    }
129    let mut headers: Vec<String> = Vec::new();
130    // Names taken so far (lower-case), and the next suffix to try for each repeated header, so
131    // that thousands of equal headers don't take cubic time.
132    let mut taken: HashSet<String> = HashSet::new();
133    let mut next_suffix: HashMap<String, usize> = HashMap::new();
134    let mut formats = Vec::new();
135    for c in r.start.col..=r.end.col {
136        let h = crate::engine::display::cell_text(wb, sh, CellRef::new(r.start.row, c)).trim().to_string();
137        if h.is_empty() {
138            return Err(FIELD_NAME_ERR.into());
139        }
140        let key = h.to_ascii_lowercase();
141        let mut n = h.clone();
142        let mut k = next_suffix.get(&key).copied().unwrap_or(2);
143        while taken.contains(&n.to_ascii_lowercase()) {
144            n = format!("{h}{k}");
145            k += 1;
146        }
147        next_suffix.insert(key, k);
148        taken.insert(n.to_ascii_lowercase());
149        headers.push(n);
150        let fmt = if r.end.row > r.start.row {
151            wb.styles.get(sh.style_id(CellRef::new(r.start.row + 1, c))).num_fmt.as_str().to_string()
152        } else {
153            "General".into()
154        };
155        formats.push(fmt);
156    }
157    let w = headers.len();
158    let mut rows = Vec::new();
159    let mut numeric = vec![true; w];
160    let mut any = vec![false; w];
161    if r.end.row > r.start.row {
162        for row in r.start.row + 1..=r.end.row {
163            let rec: Vec<Value> = (r.start.col..=r.end.col).map(|c| sh.value(CellRef::new(row, c)).scalar()).collect();
164            if rec.iter().all(Value::is_empty) {
165                continue;
166            }
167            for (i, v) in rec.iter().enumerate() {
168                if !v.is_empty() {
169                    if let Some(a) = any.get_mut(i) {
170                        *a = true;
171                    }
172                    if !v.is_number()
173                        && let Some(n) = numeric.get_mut(i)
174                    {
175                        *n = false;
176                    }
177                }
178            }
179            rows.push(rec);
180        }
181    }
182    for (n, a) in numeric.iter_mut().zip(any.iter()) {
183        *n = *n && *a;
184    }
185    let dates = numeric.iter().zip(formats.iter()).map(|(n, f)| *n && crate::engine::display::number_format(f).is_date()).collect();
186    Ok(Source { sheet: si, range: r, headers, formats, numeric, dates, rows })
187}
188
189/// Computes a PivotTable report from the workbook.
190pub fn compute(wb: &Workbook, pt: &PivotTable, pivot_sheet: usize) -> Result<Output, String> {
191    let src = read_source(wb, &pt.source, pivot_sheet)?;
192    compute_from(wb, pt, &src, None)
193}
194
195/// Recomputes a pivot and writes its report. With `replace` false, refuses when the target area
196/// holds other data. Returns the written area.
197pub fn refresh(wb: &mut Workbook, sheet: usize, idx: usize, replace: bool) -> Result<RangeRef, String> {
198    let pt = wb.sheet(sheet).and_then(|s| s.pivots.get(idx)).cloned().ok_or("There's no such PivotTable.")?;
199    let out = compute(wb, &pt, sheet)?;
200    let h = out.height().max(1);
201    let w = out.width.max(1);
202    let k = out.body_offset;
203    let origin_row = if pt.anchor.row >= k { pt.anchor.row - k } else { pt.anchor.row };
204    let origin = CellRef::new(origin_row, pt.anchor.col);
205    let end_row = origin.row as u64 + h as u64 - 1;
206    let end_col = origin.col as u64 + w as u64 - 1;
207    if end_row >= MAX_ROWS as u64 || end_col >= MAX_COLS as u64 {
208        return Err("The PivotTable report will not fit on the sheet. Choose a different location.".into());
209    }
210    let area = RangeRef::new(origin, CellRef::new(end_row as u32, end_col as u32));
211    let sh = wb.sheet(sheet).ok_or("There's no such sheet.")?;
212    for (j, other) in sh.pivots.iter().enumerate() {
213        if j != idx && other.last_range.is_some_and(|r| r.intersects(&area)) {
214            return Err("A PivotTable report cannot overlap another PivotTable report.".into());
215        }
216    }
217    if sh.is_protected() {
218        return Err("The cell or chart you're trying to change is on a protected sheet.".into());
219    }
220    if let Ok((ssi, sr)) = resolve_source(wb, &pt.source, sheet)
221        && ssi == sheet
222        && sr.intersects(&area)
223    {
224        return Err("The PivotTable report can't overlap its source data. Choose a different location.".into());
225    }
226    if sh.tables.iter().any(|t| t.range.intersects(&area)) {
227        return Err("A PivotTable report cannot overlap a table.".into());
228    }
229    if !replace {
230        for (c, cell) in sh.cells.iter_range(area) {
231            if cell.has_content() && !pt.last_range.is_some_and(|r| r.contains(c)) {
232                return Err(format!(
233                    "There's already data in {}!{}. Do you want to replace it? (Run again with replace: true to overwrite.)",
234                    quote_sheet(&sh.name),
235                    c.a1()
236                ));
237            }
238        }
239        if let Some(c) = sh.spill.keys().find(|c| area.contains(**c) && !pt.last_range.is_some_and(|r| r.contains(**c))) {
240            return Err(format!(
241                "There's already data in {}!{}. Do you want to replace it? (Run again with replace: true to overwrite.)",
242                quote_sheet(&sh.name),
243                c.a1()
244            ));
245        }
246    }
247    // Styles first (the style table is workbook-wide).
248    let lk = look(&pt.style);
249    let header_last = out.row_kinds.iter().rposition(|k| *k == RowKind::Header);
250    let mut cache: HashMap<Style, StyleId> = HashMap::new();
251    let mut cells: Vec<(CellRef, Cell)> = Vec::new();
252    for (ri, row) in out.grid.iter().enumerate() {
253        let rk = out.row_kinds.get(ri).copied().unwrap_or(RowKind::Blank);
254        if rk == RowKind::Blank {
255            continue;
256        }
257        for (ci, oc) in row.iter().enumerate() {
258            if rk == RowKind::Filter && oc.value.is_empty() {
259                continue;
260            }
261            if rk == RowKind::Placeholder && oc.value.is_empty() {
262                continue;
263            }
264            let flag = match rk {
265                RowKind::Header => Some(ri) == header_last,
266                RowKind::Filter => ci == 0,
267                _ => false,
268            };
269            let st = cell_style(&lk, rk, oc, flag);
270            let id = match cache.get(&st) {
271                Some(id) => *id,
272                None => {
273                    let id = wb.styles.intern(st.clone());
274                    cache.insert(st, id);
275                    id
276                }
277            };
278            if oc.value.is_empty() && id == StyleId::default() {
279                continue;
280            }
281            cells.push((
282                CellRef::new(origin.row + ri as u32, origin.col + ci as u32),
283                Cell { value: oc.value.clone(), formula: None, style: id, py: None },
284            ));
285        }
286    }
287    // Column widths that fit the labels.
288    let mut widths: HashMap<u32, f32> = HashMap::new();
289    for (c, cell) in &cells {
290        if cell.value.is_empty() {
291            continue;
292        }
293        let st = wb.styles.get(cell.style);
294        let text = crate::engine::display::format(&cell.value, st.num_fmt.as_str(), wb).text;
295        let wpt = crate::engine::cmd::format::approx_text_width(&text, st.font.size, st.font.bold) + st.align.indent as f32 * 9.0 + 14.0;
296        let e = widths.entry(c.col).or_insert(0.0);
297        *e = e.max(wpt.min(320.0));
298    }
299    let shm = wb.sheet_mut(sheet).ok_or("There's no such sheet.")?;
300    if let Some(old) = pt.last_range {
301        shm.take_cells(old);
302    }
303    shm.take_cells(area);
304    for (c, cell) in cells {
305        shm.set_cell(c, cell);
306    }
307    for (col, wpt) in widths {
308        if wpt > shm.col_width(col) && !shm.is_col_hidden(col) {
309            shm.cols.entry(col).or_default().size = Some(wpt);
310        }
311    }
312    if let Some(p) = shm.pivots.get_mut(idx) {
313        p.last_range = Some(area);
314    }
315    let sheet_name = shm.name.clone();
316    rebind_charts(wb, &sheet_name, &pt.name, origin, &out);
317    Ok(area)
318}
319
320#[cfg(test)]
321mod fit_tests {
322    use super::data_cols_exceed_sheet;
323
324    #[test]
325    fn column_budget_counts_value_fields_and_grand_total() {
326        assert!(!data_cols_exceed_sheet(64, true, 128));
327        assert!(data_cols_exceed_sheet(64, true, 256));
328        assert!(!data_cols_exceed_sheet(64, false, 256));
329        assert!(!data_cols_exceed_sheet(16_384, false, 0));
330        assert!(data_cols_exceed_sheet(16_385, false, 0));
331    }
332}
ravenrs_sheet/engine/pivot.rs

ravenrs_sheet/engine/
pivot.rs
