diff --git a/crates/calc/src/lib.rs b/crates/calc/src/lib.rs --- a/crates/calc/src/lib.rs +++ b/crates/calc/src/lib.rs @@ -8,7 +8,7 @@ pub mod recalc; mod trimrange; pub use eval::{Area, SPECIAL_FUNCTIONS, is_known_function}; -pub use recalc::{Calc, Key, evaluate, now_serial}; +pub use recalc::{Calc, Key, evaluate, evaluate_area, now_serial}; #[cfg(test)] mod tests; diff --git a/crates/calc/src/recalc.rs b/crates/calc/src/recalc.rs --- a/crates/calc/src/recalc.rs +++ b/crates/calc/src/recalc.rs @@ -17,7 +17,7 @@ use gridcraft_core::{Array, CellError, CellRef, RangeRef, Value}; use gridcraft_formula::Expr; use gridcraft_model::{CalcMode, Workbook}; -use crate::eval::{Area, Evaluator, Host, precedents, stack_position}; +use crate::eval::{Area, Ev, Evaluator, Host, precedents, stack_position}; mod graph; mod pass_host; @@ -513,6 +513,22 @@ pub fn evaluate_expr(wb: &Workbook, sheet: usize, at: CellRef, expr: &Expr) -> V ev.value(expr) } +/// The sheet and range a formula evaluates to when it is a single reference: `Sheet1!A1:B2`, a +/// defined name for one, an `OFFSET` or `INDEX` result. Chart series use it to find the cells +/// behind a name (for number formats); `None` for values and for unions of areas. +pub fn evaluate_area(wb: &Workbook, sheet: usize, formula: &str) -> Option { + let body = formula.trim().strip_prefix('=').unwrap_or(formula.trim()); + let expr = gridcraft_formula::parse(body).ok()?; + let exprs = HashMap::default(); + let mut rng = 0x1234_5678_9ABC_DEF0u64; + let mut host = PassHost::new(wb, &exprs, &mut rng, now_serial()); + let mut ev = Evaluator::new(&mut host, sheet, CellRef::default()); + match ev.eval(&expr) { + Ev::R(areas) if areas.len() == 1 => areas.first().copied(), + _ => None, + } +} + #[allow(clippy::disallowed_methods)] // the clock is read only off wasm fn prof_now() -> f64 { #[cfg(not(target_arch = "wasm32"))] diff --git a/crates/chart/src/resolve.rs b/crates/chart/src/resolve.rs --- a/crates/chart/src/resolve.rs +++ b/crates/chart/src/resolve.rs @@ -50,6 +50,12 @@ fn parse_ref(wb: &Workbook, sheet: usize, formula: &str) -> Option<(usize, Range Some((sh, r)) } +/// The cells a series formula stands for: a reference, or a defined name (static, or `OFFSET` +/// and the like) that evaluates to one. Number formats come from these cells. +fn target(wb: &Workbook, sheet: usize, formula: &str) -> Option<(usize, RangeRef)> { + parse_ref(wb, sheet, formula).or_else(|| gridcraft_calc::evaluate_area(wb, sheet, formula).map(|a| (a.sheet, a.range))) +} + /// The parts of a union reference `(Sheet1!$A$1:$A$3,Sheet1!$A$5)`, or `None`. fn union_parts(f: &str) -> Option> { let inner = f.strip_prefix('(')?.strip_suffix(')')?; @@ -115,9 +121,9 @@ fn nums(wb: &Workbook, sheet: usize, formula: &str) -> Vec> { eval_list(wb, sheet, formula).iter().map(to_num).collect() } -/// Number format code of the first cell of a reference ("General" when not a reference). +/// Number format code of the first cell of a reference or a name for one ("General" otherwise). fn ref_format(wb: &Workbook, sheet: usize, formula: &str) -> String { - parse_ref(wb, sheet, formula) + target(wb, sheet, formula) .and_then(|(sh, r)| wb.sheet(sh).map(|s| wb.styles.get(s.style_id(r.start)).num_fmt.0.to_string())) .unwrap_or_else(|| "General".into()) } @@ -139,7 +145,7 @@ fn value_text(v: &Value) -> String { /// has one entry per category, non-empty where a group starts, as the cells are. fn category_table(wb: &Workbook, sheet: usize, formula: &str, values: &str) -> (Vec, Vec>) { let vals = eval_list(wb, sheet, formula); - let cells: Option<(usize, RangeRef)> = parse_ref(wb, sheet, formula); + let cells: Option<(usize, RangeRef)> = target(wb, sheet, formula); let src = cells.and_then(|(sh, r)| wb.sheet(sh).map(|s| (s, r))); let texts: Vec = vals .iter() @@ -159,7 +165,7 @@ fn category_table(wb: &Workbook, sheet: usize, formula: &str, values: &str) -> ( }) .collect(); let Some((_, r)) = src else { return (texts, vec![]) }; - let value_range = parse_ref(wb, sheet, values).map(|(_, r)| r); + let value_range = target(wb, sheet, values).map(|(_, r)| r); let Some(levels) = category_levels(&r, value_range.as_ref()) else { return (texts, vec![]) }; let (w, h) = (r.width().max(1) as usize, r.height().max(1) as usize); if texts.len() != w.saturating_mul(h) { diff --git a/crates/chart/src/tests.rs b/crates/chart/src/tests.rs --- a/crates/chart/src/tests.rs +++ b/crates/chart/src/tests.rs @@ -1255,3 +1255,55 @@ fn box_whisker_draws_each_series_quartile_method() { // The boxes span 3.5 and 2.5 data units on the same value axis. assert!((heights[0] / heights[1] - 1.4).abs() < 0.001); } + +/// A series through a defined name takes its number format (and its date categories) from the +/// cells the name stands for, as the same series over the plain reference does. +#[test] +fn names_resolve_formats_like_references() { + let mut wb = Workbook::new(); + let date = wb.styles.intern(Style { num_fmt: gridcraft_model::NumFmt(Arc::from("m/d/yyyy")), ..Style::default() }); + let money = wb.styles.intern(Style { num_fmt: gridcraft_model::NumFmt(Arc::from("0.00")), ..Style::default() }); + { + let s = wb.sheet_mut(0).unwrap(); + for (i, (d, v)) in [(45296.0, 10.0), (45327.0, 20.0), (45356.0, 30.0)].iter().enumerate() { + let r = i as u32 + 1; + s.set_value(CellRef::new(r, 0), Value::Number(*d)); + s.set_style(CellRef::new(r, 0), date); + s.set_value(CellRef::new(r, 1), Value::Number(*v)); + s.set_style(CellRef::new(r, 1), money); + } + } + let name = + |n: &str, f: &str| gridcraft_model::DefinedName { name: n.into(), scope: None, formula: f.into(), comment: String::new(), hidden: false }; + wb.names = vec![ + name("Cats", "Sheet1!$A$2:$A$4"), + name("Vals", "Sheet1!$B$2:$B$4"), + name("DynC", "OFFSET(Sheet1!$A$2,0,0,3,1)"), + name("DynV", "OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)"), + name("Dates", "Sheet1!$A$2:$A$4"), + ]; + let resolved = |cats: &str, vals: &str| { + let mut c = chart(ChartKind::ColumnClustered); + c.series = vec![Series { + name: None, + categories: Some(cats.into()), + values: vals.into(), + bubble_sizes: None, + color: None, + secondary: false, + kind: None, + quartiles: Default::default(), + smooth: false, + trendlines: Vec::new(), + }]; + let d = resolve(&wb, 0, &c); + (d.categories, d.series[0].number_format.clone()) + }; + let direct = resolved("Sheet1!$A$2:$A$4", "Sheet1!$B$2:$B$4"); + assert_eq!(direct, (vec!["1/5/2024".to_string(), "2/5/2024".to_string(), "3/5/2024".to_string()], "0.00".to_string())); + assert_eq!(resolved("Cats", "Vals"), direct); + // Dynamic names: an OFFSET with a calculated height is followed through the calculation. + assert_eq!(resolved("DynC", "DynV"), direct); + // The axis of a series whose values are date cells reads the date format through a name. + assert_eq!(resolved("Cats", "Dates").1, "m/d/yyyy"); +} diff --git a/crates/xlsx/src/chart.rs b/crates/xlsx/src/chart.rs --- a/crates/xlsx/src/chart.rs +++ b/crates/xlsx/src/chart.rs @@ -4,7 +4,7 @@ use std::fmt::Write as _; use gridcraft_core::{RangeRef, Value, category_levels}; -use gridcraft_formula::{Expr, SheetSel}; +use gridcraft_formula::{Expr, SheetSel, UnOp}; use gridcraft_model::{Chart, ChartKind, Color, DefinedName, LegendPos, LineDash, Series, Trendline, TrendlineKind, Workbook}; use crate::drawing::{dml_color, srgb}; @@ -320,16 +320,65 @@ fn group_for(k: ChartKind) -> G { const MAX_CACHE: u64 = 100_000; -/// The sheet and range a same-workbook reference (`Sheet1!$B$2:$B$9`) names. +/// How many names may refer to names (or OFFSETs nest) before a series stops resolving. +const MAX_NAME_DEPTH: u32 = 8; + +/// The sheet and range a series formula names: a same-workbook reference (`Sheet1!$B$2:$B$9`), a +/// defined name that stands for one (names may refer to names), or an `OFFSET` of one with +/// literal arguments. A name that has to be calculated (`OFFSET(…,COUNTA(…))`) has no target +/// here: this crate doesn't evaluate formulas. fn ref_target(wb: &Workbook, sheet_idx: usize, f: &str) -> Option<(usize, RangeRef)> { - let e = gridcraft_formula::parse(f).ok()?; - let Expr::Ref(r) = e else { return None }; - let si = match &r.sheet { - SheetSel::Current => sheet_idx, - SheetSel::Named(n) => wb.sheet_index(n)?, - SheetSel::Span(..) => return None, - }; - Some((si, r.range())) + let e = gridcraft_formula::parse(f.trim()).ok()?; + expr_target(wb, sheet_idx, &e, 0) +} + +fn expr_target(wb: &Workbook, sheet_idx: usize, e: &Expr, depth: u32) -> Option<(usize, RangeRef)> { + if depth > MAX_NAME_DEPTH { + return None; + } + match e { + Expr::Paren(inner) => expr_target(wb, sheet_idx, inner, depth + 1), + Expr::Ref(r) => { + let si = match &r.sheet { + SheetSel::Current => sheet_idx, + SheetSel::Named(n) => wb.sheet_index(n)?, + SheetSel::Span(..) => return None, + }; + Some((si, r.range())) + } + Expr::Name(n) => { + let (_, _, def) = chart_name(wb, sheet_idx, n)?; + let def = def?; + let body = gridcraft_formula::parse_name(&def.formula).ok()?; + expr_target(wb, def.scope.unwrap_or(sheet_idx), &body, depth + 1) + } + Expr::Call(func, args) if func == "OFFSET" => { + let (si, r) = expr_target(wb, sheet_idx, args.first()?, depth + 1)?; + let int = |i: usize| match args.get(i) { + None | Some(Expr::Missing) => Some(None), + Some(Expr::Number(n)) if n.fract() == 0.0 && n.abs() < 2e6 => Some(Some(*n as i64)), + Some(Expr::Unary(UnOp::Neg, n)) => match **n { + Expr::Number(n) if n.fract() == 0.0 && n < 2e6 => Some(Some(-(n as i64))), + _ => None, + }, + _ => None, + }; + let (dr, dc) = (int(1)?.unwrap_or(0), int(2)?.unwrap_or(0)); + let (h, w) = (int(3)?.unwrap_or(i64::from(r.height())), int(4)?.unwrap_or(i64::from(r.width()))); + let start = r.start.offset(dr, dc)?; + let end = start.offset(h.checked_sub(1).filter(|v| *v >= 0)?, w.checked_sub(1).filter(|v| *v >= 0)?)?; + Some((si, RangeRef::new(start, end))) + } + _ => None, + } +} + +/// The number format code of the first cell a series formula refers to, as Excel writes it in +/// `` (`General` when the formula isn't a reference or a name for one). +fn source_format(wb: &Workbook, sheet_idx: usize, f: &str) -> String { + ref_target(wb, sheet_idx, f) + .and_then(|(si, r)| Some(wb.styles.get(wb.sheet(si)?.style_id(r.start)).num_fmt.0.to_string())) + .unwrap_or_else(|| "General".into()) } /// Values of a same-workbook reference (`Sheet1!$B$2:$B$9`), row-major. @@ -408,7 +457,7 @@ fn num_data(tag: &str, f: Option<&str>, wb: &Workbook, si: usize) -> String { } let mut s = format!("{}", esc(&chart_ref_to_file(wb, si, f))); if let Some(vals) = ref_values(wb, si, f) { - let _ = write!(s, "General", vals.len()); + let _ = write!(s, "{}", esc(&source_format(wb, si, f)), vals.len()); for (i, v) in vals.iter().enumerate() { if let Value::Number(n) = v { let _ = write!(s, "{}", num(*n)); @@ -486,7 +535,16 @@ fn str_data(tag: &str, f: &str, wb: &Workbook, si: usize) -> String { /// year beside month). Its cache lists the innermost level first, then each outer level with /// only the cells that start a group, as Excel writes it. fn cat_data(f: &str, values: &str, wb: &Workbook, si: usize) -> String { - multi_level_cat(f.trim(), values, wb, si).unwrap_or_else(|| str_data("cat", f, wb, si)) + multi_level_cat(f.trim(), values, wb, si).or_else(|| numeric_cat(f, wb, si)).unwrap_or_else(|| str_data("cat", f, wb, si)) +} + +/// Categories that are all numbers (dates, years) are a number reference with a number cache, as +/// Excel writes them; the cache's format code makes a viewer show dates rather than serials. +fn numeric_cat(f: &str, wb: &Workbook, si: usize) -> Option { + let vals = ref_values(wb, si, f)?; + let mut filled = vals.iter().filter(|v| !v.is_empty()).peekable(); + filled.peek()?; + filled.all(|v| matches!(v, Value::Number(_))).then(|| num_data("cat", Some(f), wb, si)) } fn multi_level_cat(f: &str, values: &str, wb: &Workbook, si: usize) -> Option { @@ -508,7 +566,7 @@ fn multi_level_cat(f: &str, values: &str, wb: &Workbook, si: usize) -> Option{}", esc(&crate::fmla::text_to_file(f))); + let mut s = format!("{}", esc(&chart_ref_to_file(wb, si, f))); for lvl in &lvls { s.push_str(""); for (i, v) in lvl.iter().enumerate() { diff --git a/crates/xlsx/src/tests.rs b/crates/xlsx/src/tests.rs --- a/crates/xlsx/src/tests.rs +++ b/crates/xlsx/src/tests.rs @@ -5,6 +5,7 @@ mod cellmetadata; mod chart_book_names; mod chart_bool_default; mod chart_combo_kinds; +mod chart_name_caches; mod chart_ref_offsets; mod csv_tests; mod encrypted_tests; diff --git a/crates/xlsx/src/tests/chart_name_caches.rs b/crates/xlsx/src/tests/chart_name_caches.rs new file mode 100644 --- /dev/null +++ b/crates/xlsx/src/tests/chart_name_caches.rs @@ -0,0 +1,124 @@ +//! A series that goes through a defined name is saved with the cache Excel writes for it: the +//! values of the cells the name stands for and their number format (observed in Excel for +//! Windows, Microsoft 365: charts over `[0]!Name` carry the same `numCache`/`strCache` as charts +//! over the plain reference, dynamic `OFFSET` names included). + +use std::io::Read; +use std::sync::Arc; + +use gridcraft_core::{CellRef, Value}; +use gridcraft_model::{Anchor, Chart, ChartKind, DefinedName, LegendPos, NumFmt, Series, Sheet, Style, Workbook}; + +use crate::write_xlsx; + +fn name(n: &str, formula: &str) -> DefinedName { + DefinedName { name: n.into(), scope: None, formula: formula.into(), comment: String::new(), hidden: false } +} + +fn chart_xml(cats: &str, vals: &str, names: Vec) -> String { + let mut wb = Workbook::default(); + let date = wb.styles.intern(Style { num_fmt: NumFmt(Arc::from("m/d/yyyy")), ..Style::default() }); + let money = wb.styles.intern(Style { num_fmt: NumFmt(Arc::from("0.00")), ..Style::default() }); + let mut s = Sheet::new("Sheet1"); + for (i, (d, v)) in [(45296.0, 10.0), (45327.0, 20.0), (45356.0, 30.0)].iter().enumerate() { + let r = i as u32 + 1; + s.set_value(CellRef::new(r, 0), Value::Number(*d)); + s.set_style(CellRef::new(r, 0), date); + s.set_value(CellRef::new(r, 1), Value::Number(*v)); + s.set_style(CellRef::new(r, 1), money); + s.set_value(CellRef::new(r + 5, 0), Value::from(["a", "b", "c"][i])); + } + s.charts.push(Chart { + id: 1, + kind: ChartKind::ColumnClustered, + anchor: Anchor { cell: CellRef::new(0, 4), width: 480.0, height: 300.0, ..Anchor::default() }, + title: None, + series: vec![Series { + name: None, + categories: Some(cats.into()), + values: vals.into(), + bubble_sizes: None, + color: None, + secondary: false, + kind: None, + smooth: false, + trendlines: Vec::new(), + quartiles: Default::default(), + }], + legend: LegendPos::Bottom, + data_labels: false, + gridlines: true, + style: 2, + x_title: None, + y_title: None, + source: None, + by_rows: false, + pivot: None, + }); + wb.sheets = vec![Arc::new(s)]; + wb.names = names; + let bytes = write_xlsx(&wb).unwrap(); + let mut xml = String::new(); + zip::ZipArchive::new(std::io::Cursor::new(&bytes)).unwrap().by_name("xl/charts/chart1.xml").unwrap().read_to_string(&mut xml).unwrap(); + xml +} + +fn part<'a>(xml: &'a str, tag: &str) -> &'a str { + let open = format!(""); + let start = xml.find(&open).unwrap() + open.len(); + let end = xml[start..].find(&format!("")).unwrap() + start; + &xml[start..end] +} + +const DATE_CACHE: &str = "m/d/yyyy452964532745356"; +const VAL_CACHE: &str = "0.00102030"; + +#[test] +fn plain_references_carry_the_source_format() { + let xml = chart_xml("Sheet1!$A$2:$A$4", "Sheet1!$B$2:$B$4", vec![]); + assert_eq!(part(&xml, "cat"), format!("Sheet1!$A$2:$A$4{DATE_CACHE}")); + assert_eq!(part(&xml, "val"), format!("Sheet1!$B$2:$B$4{VAL_CACHE}")); +} + +#[test] +fn static_names_are_cached_like_the_plain_reference() { + let names = vec![name("Cats", "Sheet1!$A$2:$A$4"), name("Vals", "Sheet1!$B$2:$B$4")]; + let xml = chart_xml("Cats", "Vals", names); + assert_eq!(part(&xml, "cat"), format!("[0]!Cats{DATE_CACHE}")); + assert_eq!(part(&xml, "val"), format!("[0]!Vals{VAL_CACHE}")); +} + +#[test] +fn names_of_names_and_offsets_are_cached() { + let names = vec![ + name("Base", "Sheet1!$A$2:$A$4"), + name("Cats", "Base"), + name("DynV", "OFFSET(Sheet1!$B$2,0,0,3,1)"), + name("Shifted", "OFFSET(Cats,0,1)"), + ]; + let xml = chart_xml("Cats", "DynV", names.clone()); + assert_eq!(part(&xml, "cat"), format!("[0]!Cats{DATE_CACHE}")); + assert_eq!(part(&xml, "val"), format!("[0]!DynV{VAL_CACHE}")); + // OFFSET of a name: the cells one column over. + assert_eq!(part(&chart_xml("Cats", "Shifted", names), "val"), format!("[0]!Shifted{VAL_CACHE}")); +} + +#[test] +fn text_categories_through_a_name_get_a_string_cache() { + let xml = chart_xml("Txt", "Sheet1!$B$2:$B$4", vec![name("Txt", "Sheet1!$A$7:$A$9")]); + assert_eq!( + part(&xml, "cat"), + "[0]!Txtabc" + ); +} + +#[test] +fn names_that_cannot_be_resolved_stay_without_a_cache() { + // Name chains that loop, calculated sizes and undefined names: no cache, and no hang. + let names = vec![name("A", "B"), name("B", "A"), name("Calc", "OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)")]; + for n in ["A", "Calc", "Missing"] { + let xml = chart_xml(n, n, names.clone()); + assert!(!xml.contains("") && !xml.contains(""), "{n}: {xml}"); + assert!(xml.contains(&format!("[0]!{n}")), "{n}: {xml}"); + } +} diff --git a/docs/chart-parity.md b/docs/chart-parity.md --- a/docs/chart-parity.md +++ b/docs/chart-parity.md @@ -1,6 +1,6 @@ # Chart parity -> **Last reviewed:** 2026-10-18 · **Last updated:** 2026-10-24 · **Change:** minor (100% stacked area charts) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 +> **Last reviewed:** 2026-10-18 · **Last updated:** 2026-10-26 · **Change:** minor (chart caches and number formats through defined names) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Excel's chart types and chart elements against GridCraft. Part of [target-app-parity.md](target-app-parity.md). **Breadth ~50%, ready ~30%, 40–60 h** (estimated). @@ -74,7 +74,10 @@ Excel writes a workbook-scoped name as `[0]!Name` in ``. GridCraft reads it name and writes it back with `[0]!`; a sheet-scoped name is written `Sheet!Name`. Excel needs the qualifier: with a bare name in `` it draws the whole chart blank, without a repair prompt (observed in Excel for Windows, Microsoft 365). External books (`[1]Sheet1!…`) are kept as they -are (#1686). Such series get no `numCache`/`strCache` on save (#1690). +are (#1686). Series through a name are saved with the same `numCache`/`strCache` as the plain +reference, `formatCode` from the first cell (#1690), and the chart reads number formats and date +categories through the name (#1689). The file writer can't calculate, so a name whose size comes +from a calculation (`OFFSET(…,COUNTA(…))`) is saved without a cache; Excel recalculates it on open. ## Work (40–60 h) @@ -90,6 +93,7 @@ are (#1686). Such series get no `numCache`/`strCache` on save (#1690). | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Series through defined names (static and `OFFSET` with literal arguments) are saved with `numCache`/`strCache` and the first cell's `formatCode`; the chart reads number formats and date categories through names; all-number categories are written as `numRef` (#1689, #1690) | | 2026-10-24 | minor | 100% Stacked Area: inserted from the ribbon and All Charts, drawn as shares of each category's total, read and written as `percentStacked` | | 2026-10-18 | trivial | Stale notes: stock/combo, treemap/sunburst, box and waterfall kinds survive XLSX round-trip (PRs #158, #161) | | 2026-10-16 | trivial | Series that refer to defined names (`[0]!Name`, `Sheet!Name`) resolve instead of showing `#NAME?`, and are saved qualified, as Excel needs (#1686) |