use super::{ CellRef, Ctx, DateSystem, El, HashMap, IoError, MAX_FIELDS, PivotCalcField, PivotDateGroup, PivotField, PivotFilter, PivotFunc, PivotLayout, PivotShowAs, PivotSort, PivotTable, PivotValue, RangeRef, abs_range, func_caption, func_from, number_to_text, quote_sheet, same, }; // ================================================================ reading /// A cache field as read. #[derive(Clone, Debug, Default)] struct FieldIn { name: String, database: bool, /// Shared item captions. items: Vec, /// (base field, grouping, group item captions); grouping `None` = unsupported grouping. group: Option<(Option, Option, Vec)>, /// A calculated field's formula. formula: Option, } impl FieldIn { fn labels(&self) -> &[String] { match &self.group { Some((_, _, items)) if !items.is_empty() => items, _ => &self.items, } } } #[derive(Clone, Debug, Default)] struct CacheIn { source: String, fields: Vec, } /// Workbook-level pivot cache lookup, filled from ``; caches are parsed once. #[derive(Default)] pub struct CachesIn { by_id: HashMap, parsed: HashMap>, next_id: u32, } impl CachesIn { pub fn new(wb_xml: &El, wb_rels: &[crate::package::Rel]) -> CachesIn { let mut by_id = HashMap::new(); if let Some(pc) = wb_xml.child("pivotCaches") { for c in pc.kids("pivotCache") { if let (Some(id), Some(rid)) = (c.attr_u32("cacheId"), c.attr("id")) && let Some(r) = wb_rels.iter().find(|r| r.id == rid) { by_id.insert(id, r.target.clone()); } } } CachesIn { by_id, parsed: HashMap::new(), next_id: 1 } } } fn item_label_el(e: &El, sys: DateSystem) -> String { let v = e.attr("v").unwrap_or(""); match e.name.as_str() { "m" => "(blank)".into(), "n" => v.trim().parse::().ok().filter(|n| n.is_finite()).map(number_to_text).unwrap_or_else(|| v.to_string()), "b" => if matches!(v.trim(), "1" | "true") { "TRUE" } else { "FALSE" }.into(), "d" => crate::sheet_read::parse_iso_datetime(v, sys).map(number_to_text).unwrap_or_else(|| v.to_string()), _ => v.to_string(), } } fn read_cache(cx: &mut Ctx<'_>, part: &str, sheet_name: &str) -> Option { let x = cx.optional_xml(part).ok().flatten()?; if x.name != "pivotCacheDefinition" { cx.warn(format!("{part} is not a pivot cache definition; its PivotTable was kept as plain cells")); return None; } let src = x.child("cacheSource"); let ty = src.and_then(|s| s.attr("type")).unwrap_or("worksheet"); if ty != "worksheet" { cx.warn(format!("PivotTables with a {ty} data source are not supported; kept as plain cells")); return None; } let ws = src.and_then(|s| s.child("worksheetSource")); let source = match ws { Some(w) if w.attr("name").is_some_and(|n| !n.trim().is_empty()) => w.attr("name").unwrap_or("").trim().to_string(), Some(w) => { if w.attr("id").is_some() { cx.warn("a PivotTable's source is in another workbook; its source reference may not resolve"); } let r = w.attr("ref").map(|r| r.replace('$', "")).and_then(|r| RangeRef::parse(&r)); let Some(r) = r else { cx.warn("a PivotTable's source reference is missing or invalid; kept as plain cells"); return None; }; let sheet = w.attr("sheet").filter(|s| !s.is_empty()).unwrap_or(sheet_name); format!("{}!{}", quote_sheet(sheet), abs_range(&r)) } None => { cx.warn("a PivotTable's cache has no worksheet source; kept as plain cells"); return None; } }; let sys = cx.date_system; let mut fields = Vec::new(); if let Some(cf) = x.child("cacheFields") { for f in cf.kids("cacheField").take(MAX_FIELDS) { let items = f.child("sharedItems").map(|si| si.children.iter().map(|e| item_label_el(e, sys)).collect()).unwrap_or_default(); let group = f.child("fieldGroup").map(|g| { let base = g.attr_u32("base").map(|b| b as usize); let by = match g.child("rangePr").map(|r| r.attr("groupBy").unwrap_or("range")) { Some("years") => Some(PivotDateGroup::Years), Some("quarters") => Some(PivotDateGroup::Quarters), Some("months") => Some(PivotDateGroup::Months), Some("days") => Some(PivotDateGroup::Days), _ => None, }; let gi = g.child("groupItems").map(|gi| gi.children.iter().map(|e| item_label_el(e, sys)).collect()).unwrap_or_default(); (base, by, gi) }); fields.push(FieldIn { name: f.attr("name").unwrap_or("").to_string(), database: f.flag("databaseField", true), items, group, formula: f.attr("formula").map(str::to_string), }); } } if fields.is_empty() { cx.warn("a PivotTable's cache has no fields; kept as plain cells"); return None; } Some(CacheIn { source, fields }) } /// Reads the PivotTables related to a worksheet part. pub fn read_pivots(cx: &mut Ctx<'_>, caches: &mut CachesIn, sheet_part: &str, sheet_name: &str) -> Result, IoError> { let rels = cx.pkg.rels(sheet_part)?; let mut out = Vec::new(); for rel in rels.iter().filter(|r| r.kind == "pivotTable" && !r.external) { let Some(x) = cx.optional_xml(&rel.target)? else { continue }; if x.name != "pivotTableDefinition" { cx.warn(format!("{} is not a PivotTable definition; skipped", rel.target)); continue; } // The cache: the table's own relationship, else the workbook's cache id. let trels = cx.pkg.rels(&rel.target)?; let cache_part = trels .iter() .find(|r| r.kind == "pivotCacheDefinition" && !r.external) .map(|r| r.target.clone()) .or_else(|| x.attr_u32("cacheId").and_then(|id| caches.by_id.get(&id).cloned())); let Some(cache_part) = cache_part else { cx.warn(format!("PivotTable in {} has no cache; kept as plain cells", rel.target)); continue; }; let cache = match caches.parsed.get(&cache_part) { Some(c) => c.clone(), None => { let c = read_cache(cx, &cache_part, sheet_name); caches.parsed.insert(cache_part.clone(), c.clone()); c } }; let Some(cache) = cache else { continue }; if let Some(pt) = read_table(cx, &x, &cache, caches.next_id) { caches.next_id = caches.next_id.saturating_add(1); out.push(pt); } } Ok(out) } /// A pivot field's data items (not subtotal or grand-total items). fn items_of(e: &El) -> Vec<&El> { e.child("items").map(|it| it.kids("item").filter(|i| i.attr("t").is_none_or(|t| t == "data")).collect()).unwrap_or_default() } /// (source column name, date grouping) of a cache field used on an axis. fn field_ref(cx: &mut Ctx<'_>, cache: &CacheIn, i: usize) -> Option<(String, PivotDateGroup)> { let f = cache.fields.get(i)?; if f.formula.is_some() { cx.warn("calculated PivotTable fields are not supported"); return None; } match &f.group { Some((base, by, _)) => { let by = match by { Some(b) => *b, None => { cx.warn(format!("PivotTable grouping of \"{}\" is not supported; the field is ungrouped", f.name)); PivotDateGroup::None } }; let base_name = if f.database { f.name.clone() } else { base.and_then(|b| cache.fields.get(b))?.name.clone() }; Some((base_name, by)) } None if !f.database => { cx.warn(format!("PivotTable field \"{}\" (grouped items) is not supported", f.name)); None } None => Some((f.name.clone(), PivotDateGroup::None)), } } fn read_table(cx: &mut Ctx<'_>, x: &El, cache: &CacheIn, id: u32) -> Option { let mut pt = PivotTable { id, ..PivotTable::default() }; if let Some(n) = x.attr("name").filter(|n| !n.trim().is_empty()) { pt.name = n.to_string(); } else { pt.name = format!("PivotTable{id}"); } pt.source = cache.source.clone(); let Some(loc) = x.child("location").and_then(|l| l.attr("ref")).and_then(|r| RangeRef::parse(&r.replace('$', ""))) else { cx.warn(format!("PivotTable \"{}\" has no valid location; kept as plain cells", pt.name)); return None; }; // Field lists are capped, and a field is used once (Excel puts a field on one axis, once): // each use walks the field's items. // A list cut at the cap is reported, so an incomplete PivotTable can be told apart. let mut truncated = false; let pfs: Vec<&El> = x.child("pivotFields").map(|p| capped(p.kids("pivotField"), &mut truncated)).unwrap_or_default(); let pf = |i: usize| pfs.get(i).copied(); let mut used_fields = std::collections::HashSet::new(); let mut idx_list = |name: &str, attr: &str| -> Vec { x.child(name) .map(|e| capped(e.children.iter().filter_map(|f| f.attr_i64(attr)).filter(|&i| i < 0 || used_fields.insert(i)), &mut truncated)) .unwrap_or_default() }; let row_x = idx_list("rowFields", "x"); let col_x = idx_list("colFields", "x"); let mut page_fields = std::collections::HashSet::new(); let pages: Vec<&El> = x .child("pageFields") .map(|p| capped(p.kids("pageField").filter(|f| f.attr_i64("fld").is_none_or(|i| page_fields.insert(i))), &mut truncated)) .unwrap_or_default(); // Layout from the first axis field (pivotField defaults: compact and outline on). let first_axis = row_x.iter().chain(col_x.iter()).find(|i| **i >= 0).and_then(|i| pf(*i as usize)); let (compact, outline) = match first_axis { Some(f) => (f.flag("compact", true), f.flag("outline", true)), None => (x.flag("compact", true), x.flag("outline", false)), }; pt.layout = if compact { PivotLayout::Compact } else if outline { PivotLayout::Outline } else { PivotLayout::Tabular }; if let Some(f) = first_axis { pt.subtotals = f.flag("defaultSubtotal", true); pt.subtotals_top = f.flag("subtotalTop", true); } pt.grand_totals_rows = x.flag("colGrandTotals", true); pt.grand_totals_cols = x.flag("rowGrandTotals", true); pt.show_headers = x.flag("showHeaders", true); pt.enable_show_details = x.flag("enableDrill", true); pt.style = x.child("pivotTableStyleInfo").and_then(|s| s.attr("name")).unwrap_or("").to_string(); if x.child("calculatedItems").is_some() || x.path(&["filters", "filter"]).is_some() { cx.warn(format!("PivotTable \"{}\": calculated items and label/value filters are not supported", pt.name)); } let mut filters: Vec = Vec::new(); // (fields, position of Σ Values (`-2`) among them) let axis = |cx: &mut Ctx<'_>, list: &[i64], filters: &mut Vec| -> (Vec, Option) { let mut v = Vec::new(); let mut values_at = None; for &i in list { if i == -2 { values_at = Some(v.len()); continue; } if i < 0 { continue; } let i = i as usize; let Some((name, group)) = field_ref(cx, cache, i) else { continue }; let labels: &[String] = cache.fields.get(i).map(|f| f.labels()).unwrap_or(&[]); let mut f = PivotField { source_col: name.clone(), date_group: group, sort: PivotSort::None, collapsed_items: vec![] }; if let Some(e) = pf(i) { f.sort = match e.attr("sortType") { Some("ascending") => PivotSort::Asc, Some("descending") => PivotSort::Desc, _ => PivotSort::None, }; let items = items_of(e); let lab = |it: &El| it.attr_u32("x").and_then(|k| labels.get(k as usize)).cloned(); f.collapsed_items = items.iter().filter(|it| !it.flag("sd", true)).filter_map(|it| lab(it)).collect(); if items.iter().any(|it| it.flag("h", false)) { let shown: Vec = items.iter().filter(|it| !it.flag("h", false)).filter_map(|it| lab(it)).collect(); if !filters.iter().any(|ff| same(&ff.source_col, &name)) { filters.push(PivotFilter { source_col: name.clone(), selected: Some(shown) }); } } } v.push(f); } (v, values_at) }; let mut page_filters = Vec::new(); for p in &pages { let Some(i) = p.attr_i64("fld").filter(|i| *i >= 0).map(|i| i as usize) else { continue }; let Some((name, _)) = field_ref(cx, cache, i) else { continue }; let labels: &[String] = cache.fields.get(i).map(|f| f.labels()).unwrap_or(&[]); let selected = if let Some(k) = p.attr_u32("item") { labels.get(k as usize).map(|l| vec![l.clone()]) } else { pf(i).and_then(|e| { let items = items_of(e); items.iter().any(|it| it.flag("h", false)).then(|| { items .iter() .filter(|it| !it.flag("h", false)) .filter_map(|it| it.attr_u32("x").and_then(|k| labels.get(k as usize)).cloned()) .collect() }) }) }; page_filters.push(PivotFilter { source_col: name, selected }); } let mut axis_filters = Vec::new(); let (rows, rows_values) = axis(cx, &row_x, &mut axis_filters); let (columns, cols_values) = axis(cx, &col_x, &mut axis_filters); pt.rows = rows; pt.columns = columns; // Σ Values: where its `-2` entry is, else `dataOnRows` and `dataPosition`; after the last // field by default. In Columns it always comes last, as the column layout puts it there. pt.values_on_rows = rows_values.is_some() || (cols_values.is_none() && x.flag("dataOnRows", false)); pt.values_position = if pt.values_on_rows { rows_values.or_else(|| x.attr_u32("dataPosition").map(|p| p as usize)).filter(|p| *p < pt.rows.len()) } else { None }; filters.extend(page_filters); filters.extend(axis_filters); pt.filters = filters; // Values. if let Some(df) = x.child("dataFields") { truncated |= df.kids("dataField").nth(MAX_FIELDS).is_some(); for d in df.kids("dataField").take(MAX_FIELDS) { let Some(i) = d.attr_u32("fld").map(|i| i as usize) else { continue }; let Some(f) = cache.fields.get(i) else { continue }; let func = match d.attr("subtotal") { None => PivotFunc::Sum, Some(s) => func_from(s).unwrap_or_else(|| { cx.warn(format!("PivotTable function {s} is not supported; using Sum")); PivotFunc::Sum }), }; let ext = d .child("extLst") .into_iter() .flat_map(|l| l.kids("ext")) .filter_map(|e| e.child("dataField")) .find_map(|e| e.attr("pivotShowAs").map(str::to_string)); let show_as = match (ext.as_deref(), d.attr("showDataAs")) { (Some("rankDescending"), _) => PivotShowAs::Rank, (Some(other), _) => { cx.warn(format!("PivotTable show-values-as {other} is not supported")); PivotShowAs::Normal } (None, None | Some("normal")) => PivotShowAs::Normal, (None, Some("percentOfTotal")) => PivotShowAs::PercentOfGrandTotal, (None, Some("percentOfCol")) => PivotShowAs::PercentOfColumnTotal, (None, Some("percentOfRow")) => PivotShowAs::PercentOfRowTotal, (None, Some("runTotal")) => PivotShowAs::RunningTotal, (None, Some(other)) => { cx.warn(format!("PivotTable show-values-as {other} is not supported")); PivotShowAs::Normal } }; let number_format = d .attr_u32("numFmtId") .filter(|id| *id != 0) .and_then(|id| crate::tables::builtin_format(id).map(str::to_string).or_else(|| cx.num_fmts.get(&id).cloned())); let name = d.attr("name").map(str::to_string).unwrap_or_else(|| format!("{} of {}", func_caption(func), f.name)); pt.values.push(PivotValue { source_col: f.name.clone(), func, name, show_as, number_format }); } } // Calculated fields of the cache (each PivotTable on it can use them). for f in &cache.fields { if let Some(formula) = &f.formula && !f.name.trim().is_empty() && !pt.calculated_fields.iter().any(|c| same(&c.name, &f.name)) { let formula = formula.trim(); let formula = if formula.starts_with('=') { formula.to_string() } else { format!("={formula}") }; pt.calculated_fields.push(PivotCalcField { name: f.name.clone(), formula }); } } if truncated { cx.warn(format!("PivotTable \"{}\": field lists past {MAX_FIELDS} entries were dropped; the table is incomplete", pt.name)); } pt.anchor = loc.start; let above = if pages.is_empty() { 0 } else { pages.len() as u32 + 1 }; pt.last_range = Some(RangeRef::new(CellRef::new(loc.start.row.saturating_sub(above), loc.start.col), loc.end)); Some(pt) } /// The first [`MAX_FIELDS`] items; sets `truncated` when there were more. fn capped(items: impl Iterator, truncated: &mut bool) -> Vec { let mut v: Vec = items.take(MAX_FIELDS.saturating_add(1)).collect(); if v.len() > MAX_FIELDS { v.truncate(MAX_FIELDS); *truncated = true; } v }