ravenrs_sheet/engine/pivot/
items.rs

1use super::{CellError, HashMap, MONTHS, Ordering, PivotDateGroup, PivotField, PivotFunc, PivotSort, Source, Value, Workbook, datetime_from_serial};
2
3// ---------------------------------------------------------------- items
4
5#[derive(Clone, Debug)]
6pub(super) struct Item {
7    pub(super) value: Value,
8    pub(super) label: String,
9    class: u8,
10    num: f64,
11    text: String,
12    first: usize,
13    /// The value is a number shown with the source format.
14    pub(super) formatted: bool,
15}
16
17#[derive(Default)]
18pub(super) struct FieldItems {
19    items: Vec<Item>,
20    index: HashMap<String, u32>,
21}
22
23fn cmp_items(a: &Item, b: &Item) -> Ordering {
24    a.class.cmp(&b.class).then(a.num.total_cmp(&b.num)).then_with(|| a.text.cmp(&b.text))
25}
26
27/// (identity key, item) for a source value under a field's date grouping.
28fn make_item(wb: &Workbook, v: &Value, group: PivotDateGroup, fmt: &str, first: usize) -> (String, Item) {
29    let it = |value: Value, label: String, class: u8, num: f64, formatted: bool| Item {
30        text: label.to_lowercase(),
31        value,
32        label,
33        class,
34        num,
35        first,
36        formatted,
37    };
38    match v {
39        Value::Empty => ("b".into(), it(Value::text("(blank)"), "(blank)".into(), 4, 0.0, false)),
40        Value::Number(n) => {
41            if group != PivotDateGroup::None
42                && let Some(dt) = datetime_from_serial(wb.date_system, *n)
43            {
44                let mon = MONTHS.get(dt.month.saturating_sub(1) as usize).copied().unwrap_or("Jan");
45                let (label, key) = match group {
46                    PivotDateGroup::Years => (dt.year.to_string(), dt.year as f64),
47                    PivotDateGroup::Quarters => {
48                        let q = (dt.month.saturating_sub(1)) / 3 + 1;
49                        (format!("Qtr{q}"), q as f64)
50                    }
51                    PivotDateGroup::Months => (mon.to_string(), dt.month as f64),
52                    _ => (format!("{}-{mon}", dt.day), (dt.month * 100 + dt.day) as f64),
53                };
54                return (format!("g{key}"), it(Value::text(label.as_str()), label, 0, key, false));
55            }
56            let label = crate::engine::display::format(v, fmt, wb).text;
57            (format!("n{}", n.to_bits()), it(v.clone(), label, 0, *n, true))
58        }
59        Value::Text(t) => (format!("t{}", t.to_lowercase()), it(v.clone(), t.to_string(), 1, 0.0, false)),
60        Value::Bool(b) => {
61            let l = if *b { "TRUE" } else { "FALSE" };
62            (format!("l{l}"), it(Value::Bool(*b), l.into(), 2, *b as u8 as f64, false))
63        }
64        Value::Error(e) => (format!("e{}", e.as_str()), it(v.clone(), e.as_str().into(), 3, 0.0, false)),
65        Value::Array(_) => make_item(wb, &v.scalar(), group, fmt, first),
66    }
67}
68
69impl FieldItems {
70    pub(super) fn intern(&mut self, wb: &Workbook, v: &Value, group: PivotDateGroup, fmt: &str, first: usize) -> u32 {
71        let (key, item) = make_item(wb, v, group, fmt, first);
72        if let Some(i) = self.index.get(&key) {
73            return *i;
74        }
75        let i = self.items.len() as u32;
76        self.items.push(item);
77        self.index.insert(key, i);
78        i
79    }
80    pub(super) fn get(&self, i: u32) -> Option<&Item> {
81        self.items.get(i as usize)
82    }
83    pub(super) fn label(&self, i: u32) -> String {
84        self.get(i).map(|x| x.label.clone()).unwrap_or_default()
85    }
86}
87
88/// The label of a source value under a date grouping (for filters and field lists).
89pub fn item_label(wb: &Workbook, v: &Value, group: PivotDateGroup, fmt: &str) -> String {
90    make_item(wb, v, group, fmt, 0).1.label
91}
92
93/// Distinct item labels of a source column, sorted like the field list shows them.
94pub fn distinct_labels(wb: &Workbook, src: &Source, col: usize, group: PivotDateGroup, limit: usize) -> Vec<String> {
95    let mut fi = FieldItems::default();
96    let fmt = src.formats.get(col).map(String::as_str).unwrap_or("General");
97    for (i, r) in src.rows.iter().enumerate() {
98        if let Some(v) = r.get(col) {
99            fi.intern(wb, v, group, fmt, i);
100        }
101        if fi.items.len() > 100_000 {
102            break;
103        }
104    }
105    let mut items = fi.items;
106    items.sort_by(cmp_items);
107    items.into_iter().take(limit).map(|i| i.label).collect()
108}
109
110// ---------------------------------------------------------------- aggregation
111
112#[derive(Clone, Debug, Default)]
113pub(super) struct Acc {
114    count: u64,
115    nums: u64,
116    sum: f64,
117    prod: f64,
118    min: f64,
119    max: f64,
120    mean: f64,
121    m2: f64,
122    err: Option<CellError>,
123}
124
125impl Acc {
126    pub(super) fn add(&mut self, v: &Value) {
127        match v {
128            Value::Empty => {}
129            Value::Number(n) => {
130                let n = *n;
131                self.count += 1;
132                self.nums += 1;
133                self.sum += n;
134                if self.nums == 1 {
135                    self.prod = n;
136                    self.min = n;
137                    self.max = n;
138                } else {
139                    self.prod *= n;
140                    self.min = self.min.min(n);
141                    self.max = self.max.max(n);
142                }
143                let d = n - self.mean;
144                self.mean += d / self.nums as f64;
145                self.m2 += d * (n - self.mean);
146            }
147            Value::Error(e) => {
148                self.count += 1;
149                if self.err.is_none() {
150                    self.err = Some(*e);
151                }
152            }
153            _ => self.count += 1,
154        }
155    }
156    pub(super) fn result(&self, f: PivotFunc) -> Value {
157        let n = self.nums as f64;
158        match f {
159            PivotFunc::Count => return Value::number(self.count as f64),
160            PivotFunc::CountNumbers => return Value::number(n),
161            _ => {}
162        }
163        if let Some(e) = self.err {
164            return Value::Error(e);
165        }
166        let div0 = Value::Error(CellError::Div0);
167        match f {
168            PivotFunc::Sum => Value::number(self.sum),
169            PivotFunc::Average => {
170                if self.nums == 0 {
171                    div0
172                } else {
173                    Value::number(self.sum / n)
174                }
175            }
176            PivotFunc::Max => Value::number(if self.nums == 0 { 0.0 } else { self.max }),
177            PivotFunc::Min => Value::number(if self.nums == 0 { 0.0 } else { self.min }),
178            PivotFunc::Product => Value::number(if self.nums == 0 { 0.0 } else { self.prod }),
179            PivotFunc::StdDev | PivotFunc::Var => {
180                if self.nums < 2 {
181                    div0
182                } else {
183                    let var = (self.m2 / (n - 1.0)).max(0.0);
184                    Value::number(if f == PivotFunc::Var { var } else { var.sqrt() })
185                }
186            }
187            PivotFunc::StdDevP | PivotFunc::VarP => {
188                if self.nums < 1 {
189                    div0
190                } else {
191                    let var = (self.m2 / n).max(0.0);
192                    Value::number(if f == PivotFunc::VarP { var } else { var.sqrt() })
193                }
194            }
195            PivotFunc::Count | PivotFunc::CountNumbers => Value::Empty,
196        }
197    }
198}
199
200/// Excel's caption verb for a function ("Sum of Sales"). Count Numbers is captioned "Count of
201/// Sales", as Excel names it and as the XLSX reader names a value field saved without a name.
202pub fn func_caption(f: PivotFunc) -> &'static str {
203    match f {
204        PivotFunc::Sum => "Sum",
205        PivotFunc::Count => "Count",
206        PivotFunc::Average => "Average",
207        PivotFunc::Max => "Max",
208        PivotFunc::Min => "Min",
209        PivotFunc::Product => "Product",
210        PivotFunc::CountNumbers => "Count",
211        PivotFunc::StdDev => "StdDev",
212        PivotFunc::StdDevP => "StdDevp",
213        PivotFunc::Var => "Var",
214        PivotFunc::VarP => "Varp",
215    }
216}
217
218// ---------------------------------------------------------------- trees
219
220#[derive(Clone, Debug)]
221pub(super) struct Node {
222    pub(super) item: u32,
223    pub(super) children: Vec<Node>,
224}
225
226pub(super) fn build_tree(paths: &[&[u32]], depth: usize, fields: &[&PivotField], items: &[FieldItems]) -> Vec<Node> {
227    let (Some(field), Some(fi)) = (fields.get(depth), items.get(depth)) else { return vec![] };
228    let mut order: Vec<u32> = Vec::new();
229    let mut groups: HashMap<u32, Vec<&[u32]>> = HashMap::new();
230    for p in paths {
231        if let Some(&it) = p.get(depth) {
232            let g = groups.entry(it).or_default();
233            if g.is_empty() {
234                order.push(it);
235            }
236            g.push(p);
237        }
238    }
239    let mut nodes: Vec<Node> = order
240        .into_iter()
241        .map(|it| {
242            let ps = groups.remove(&it).unwrap_or_default();
243            Node { item: it, children: build_tree(&ps, depth + 1, fields, items) }
244        })
245        .collect();
246    let by = |a: &Node, b: &Node| match (fi.get(a.item), fi.get(b.item)) {
247        (Some(x), Some(y)) => match field.sort {
248            PivotSort::Asc => cmp_items(x, y),
249            PivotSort::Desc => cmp_items(y, x),
250            PivotSort::None => x.first.cmp(&y.first),
251        },
252        _ => Ordering::Equal,
253    };
254    nodes.sort_by(by);
255    nodes
256}
257
258pub(super) fn is_collapsed(field: &PivotField, label: &str) -> bool {
259    field.collapsed_items.iter().any(|c| c.eq_ignore_ascii_case(label))
260}
261
262pub(super) fn collect_siblings(nodes: &[Node], prefix: &mut Vec<u32>, out: &mut HashMap<Vec<u32>, Vec<u32>>) {
263    out.insert(prefix.clone(), nodes.iter().map(|n| n.item).collect());
264    for n in nodes {
265        prefix.push(n.item);
266        collect_siblings(&n.children, prefix, out);
267        prefix.pop();
268    }
269}
ravenrs_sheet/engine/pivot/items.rs

ravenrs_sheet/engine/pivot/
items.rs
