collective
Browse / github.com/storytold/gridcraft / patch
Calc: legacy implicit intersection for formulas imported without dynamic arrays
Calc: legacy implicit intersection for formulas imported without dynamic arrays Formulas read from files without dynamic-array metadata (cm), and every .xls formula, are marked implicit and calculated with implicit intersection, so legacy workbooks show their values instead of #SPILL./deploy/publish-floodiis.sh The formula text and saved file stay plain; typing a formula still creates a dynamic array. Verified against a real legacy workbook (4179/4179 formulas match Excel's cached values) and covered by calc, XLSX/BIFF and engine tests. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> <!-- coderabbit-ai-generated:v1 --> ## Summary by CodeRabbi
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- robalexclark (imported from pull request #2677, open)
- Base
d8c5f5885fc5320f8f1023d5fdc9f484a185ba86- Size
- +558 −22 in 25 files
- Review
- pending
- Shared
- 2026-10-28
- Id
5fa03f48083ffcceb09322f3
How it went
No other operator has reported using it yet.
Diff
raw · crates/calc/src/eval.rs crates/calc/src/implicit.rs crates/calc/src/lib.rs crates/calc/src/recalc.rs crates/calc/src/tests.rs crates/engine/src/cmd/data.rs crates/engine/src/cmd/edit.rs crates/engine/src/cmd/formulas.rs crates/engine/src/cmd/insert.rs crates/engine/src/cmd/sheet.rs crates/engine/src/fill.rs crates/engine/src/tables.rs crates/engine/tests/it/legacyimplicit.rs crates/engine/tests/it/main.rs crates/model/src/cell.rs crates/model/src/workbook.rs crates/xlsx/src/array_formula.rs crates/xlsx/src/sheet_read.rs crates/xlsx/src/sheet_write.rs crates/xlsx/src/tests/fixtures.rs crates/xlsx/src/tests/xls_tests.rs crates/xlsx/src/xls/mod.rs docs/decisions.md docs/file-format-parity.md docs/function-parity.md
@@ -156,8 +156,12 @@ impl Ctx for FnCtx<'_, '_> { fn computed_criterion(&mut self, row: usize, col: usize, db: &Array, record: usize) -> Option<bool> { let area = self.criteria?; let at = area.range.start.offset(row as i64, col as i64).filter(|c| area.range.contains(*c))?;- let expr = self.ev.host.workbook().sheet(area.sheet)?.cell(at)?.formula.as_ref()?.expr_arc()?;+ let formula = self.ev.host.workbook().sheet(area.sheet)?.cell(at)?.formula.as_ref()?;+ let expr = formula.expr_arc()?;+ let implicit = formula.implicit && formula.array.is_none(); let shifted = gridcraft_formula::adjust::shift_relative((*expr).clone(), record as i64 - 1, 0);+ // A legacy formula intersects its references, as it does in its own cell.+ let shifted = if implicit { crate::implicit::scalarize(&shifted) } else { shifted }; let env = (0..db.cols) .filter_map(|c| match db.get(0, c) { Some(Value::Text(t)) if !t.trim().is_empty() => {new file mode 100644@@ -0,0 +1,256 @@+//! Legacy implicit intersection.+//!+//! A formula read from a file that predates dynamic arrays (no `cm` cell metadata, not a legacy+//! CSE array) is calculated by Excel with *implicit intersection*: where the formula expects one+//! value, a multi-cell reference is reduced to the cell on the formula's row or column, and an+//! array value to its top-left element. Excel 365 applies the same rule, and shows the+//! intersection as `@`.+//!+//! [`scalarize`] rewrites the parsed formula the same way: every reference in a scalar position+//! is wrapped in `@`, which the evaluator already understands. The calculation engine uses it+//! for formulas marked [`Formula::implicit`](gridcraft_model::Formula::implicit); the formula+//! text, its references and the saved file stay unchanged.+//!+//! Rules, from Microsoft's *Implicit intersection operator: @*:+//! - a reference in a scalar position is intersected: `=A1:A10` is `=@A1:A10`, and so is+//! `=INDEX(A1:A10,B1)`, a call that may return a range;+//! - a reference that is the whole argument of a function taking ranges or arrays is not+//! intersected: `SUM(A1:A10)` is unchanged, and so is `SUM(INDEX(A1:A10,B1))`;+//! - an expression inside such an argument is a scalar position: `SUM(A1:A3*B1:B3)` intersects+//! both references, as `@A1:A3*@B1:B3` (older Excel could not mix the two modes);+//! - `SUMPRODUCT`, `MMULT`, `TRANSPOSE` and the other functions that always took arrays keep+//! their arguments in array mode, and `IF`/`IFERROR`/`CHOOSE`/`SWITCH` pass the position on,+//! so a legacy formula can still contain an array computation there;+//! - an explicit `@` (read from `_xlfn.SINGLE`) is left as it is.++use gridcraft_formula::{BinOp, Expr, UnOp};++/// Functions that always evaluate their arguments as arrays in a legacy formula (they never+/// needed Ctrl+Shift+Enter).+const ARRAY_FUNCTIONS: &[&str] = &["SUMPRODUCT", "MMULT", "MINVERSE", "MDETERM", "TRANSPOSE", "FREQUENCY", "LINEST", "LOGEST", "TREND", "GROWTH"];++/// Functions that return one of their arguments: they keep the position they are used in.+const PASS_THROUGH: &[&str] = &["IF", "IFS", "IFERROR", "IFNA", "CHOOSE", "SWITCH"];++/// Functions that may return a range or an array, so a scalar position intersects their result.+/// Any other function returns a single value (a scalar function, or one that aggregates).+const RANGE_FUNCTIONS: &[&str] = &["INDEX", "OFFSET", "INDIRECT", "CHOOSE"];++/// Deepest nesting the rewrite follows; a formula may nest about 4,000 levels (the 8,192+/// character limit), so this only stops hostile input, which is then left as it is.+const MAX_DEPTH: usize = 512;++/// The formula rewritten for legacy implicit intersection: the same expression with `@` around+/// every reference used where Excel expects one value.+pub fn scalarize(e: &Expr) -> Expr {+ scalarize_in(e, Position::Scalar, 0)+}++#[derive(Clone, Copy, PartialEq, Eq)]+enum Position {+ /// One value is expected: references are intersected.+ Scalar,+ /// An array or range is expected: references keep their shape.+ Array,+}++fn scalarize_in(e: &Expr, at: Position, depth: usize) -> Expr {+ if depth >= MAX_DEPTH {+ return e.clone();+ }+ let d = depth + 1;+ match e {+ Expr::Ref(_) | Expr::Name(_) | Expr::Struct(_) => match at {+ Position::Array => e.clone(),+ Position::Scalar => Expr::Unary(UnOp::At, Box::new(e.clone())),+ },+ Expr::Paren(x) => Expr::Paren(Box::new(scalarize_in(x, at, d))),+ // An array constant's items are values, not references in the formula's position.+ Expr::Array(rows) => Expr::Array(rows.iter().map(|r| r.iter().map(|x| scalarize_in(x, Position::Array, d)).collect()).collect()),+ // An explicit `@` already does the intersection: its operand keeps its shape.+ Expr::Unary(UnOp::At, x) => Expr::Unary(UnOp::At, Box::new(scalarize_in(x, Position::Array, d))),+ Expr::Unary(op, x) => {+ // `A1#` and trim references are reference operators: their operand stays a range.+ let inner = if matches!(op, UnOp::Spill | UnOp::Trim(_)) { Position::Array } else { at };+ Expr::Unary(*op, Box::new(scalarize_in(x, inner, d)))+ }+ Expr::Binary(op, a, b) => binary(*op, a, b, at, d),+ Expr::Call(name, args) => call(name, args, at, d),+ Expr::Invoke(callee, args) => Expr::Invoke(Box::new(scalarize_in(callee, at, d)), args.iter().map(|a| scalarize_in(a, at, d)).collect()),+ other => other.clone(),+ }+}++fn binary(op: BinOp, a: &Expr, b: &Expr, at: Position, depth: usize) -> Expr {+ if matches!(op, BinOp::Range | BinOp::Union | BinOp::Intersect) {+ // A reference: both ends stay references, and the result is intersected if one value+ // is expected (`=A1:B3`, `=A1 A2:B3`).+ let node = Expr::Binary(op, Box::new(scalarize_in(a, Position::Array, depth)), Box::new(scalarize_in(b, Position::Array, depth)));+ return match at {+ Position::Array => node,+ Position::Scalar => Expr::Unary(UnOp::At, Box::new(node)),+ };+ }+ // A flat chain nests on its left, so follow that spine in a loop rather than one call per+ // operator (a formula as long as Excel allows is about 4,000 levels deep).+ let mut spine = vec![(op, b)];+ let mut left = a;+ while let Expr::Binary(op, l, r) = left {+ if matches!(op, BinOp::Range | BinOp::Union | BinOp::Intersect) {+ break;+ }+ spine.push((*op, r.as_ref()));+ left = l.as_ref();+ }+ let mut acc = scalarize_in(left, at, depth);+ while let Some((op, right)) = spine.pop() {+ acc = Expr::Binary(op, Box::new(acc), Box::new(scalarize_in(right, at, depth)));+ }+ acc+}++fn call(name: &str, args: &[Expr], at: Position, depth: usize) -> Expr {+ let rewritten: Vec<Expr> = args.iter().enumerate().map(|(i, a)| scalarize_in(a, argument_position(name, i, a, at), depth)).collect();+ let node = Expr::Call(name.to_string(), rewritten);+ // Where one value is expected, the result is intersected: a call that returns a range+ // (`INDEX`, `OFFSET`, `INDIRECT`, a user function) gives the cell on the formula's row or+ // column, and one that returns an array (`ROW(A1:A3)`) its top-left value. A call that+ // returns one value is unaffected by `@`.+ match at {+ Position::Scalar => Expr::Unary(UnOp::At, Box::new(node)),+ Position::Array => node,+ }+}++/// The position argument `i` of `name` is in: an array where the function takes ranges or+/// arrays as values, one value everywhere else.+fn argument_position(name: &str, i: usize, arg: &Expr, at: Position) -> Position {+ if ARRAY_FUNCTIONS.iter().any(|f| f.eq_ignore_ascii_case(name)) {+ return Position::Array;+ }+ if PASS_THROUGH.iter().any(|f| f.eq_ignore_ascii_case(name)) {+ return at;+ }+ match gridcraft_functions::lookup(name) {+ // A value parameter rejects a multi-cell reference with #VALUE! instead of intersecting+ // it, in legacy formulas too: its references keep their shape so the function can.+ Some(spec) if spec.is_value_arg(i) => Position::Array,+ // A whole reference (or array constant) is the function's argument: `SUM(A1:A3)` keeps+ // its three cells. Any other expression is calculated as one value: `SUM(A1:A3*B1:B3)`.+ Some(spec) if !spec.is_scalar_arg(i) => {+ if whole_argument(arg) {+ Position::Array+ } else {+ Position::Scalar+ }+ }+ // A single-value parameter is one value, which an old formula intersects.+ Some(_) => Position::Scalar,+ // A function GridCraft does not know: its arguments may be anything, so leave them.+ None => Position::Array,+ }+}++/// Whether `e` is a reference or an array constant in itself, rather than a calculation over+/// one: a range argument used whole by a function that takes ranges.+fn whole_argument(e: &Expr) -> bool {+ match e {+ Expr::Ref(_) | Expr::Name(_) | Expr::Struct(_) | Expr::Array(_) => true,+ Expr::Paren(x) => whole_argument(x),+ Expr::Unary(UnOp::Spill, _) => true,+ Expr::Binary(op, a, b) => matches!(op, BinOp::Range | BinOp::Union | BinOp::Intersect) && whole_argument(a) && whole_argument(b),+ Expr::Call(name, _) => RANGE_FUNCTIONS.iter().any(|f| f.eq_ignore_ascii_case(name)),+ _ => false,+ }+}++#[cfg(test)]+mod tests {+ use gridcraft_formula::print;++ use super::scalarize;++ /// The formula after the rewrite, as a person would type it.+ fn rewritten(text: &str) -> String {+ let e = gridcraft_formula::parse(text).expect("test formula parses");+ print(&scalarize(&e))+ }++ #[test]+ fn references_used_as_one_value_are_intersected() {+ assert_eq!(rewritten("A1:A10"), "@A1:A10");+ assert_eq!(rewritten("A1:A10*2"), "@A1:A10*2");+ assert_eq!(rewritten("A1:A10&B1"), "@A1:A10&@B1");+ assert_eq!(rewritten("IF(Pay_Num<>\"\",Beg_Bal*Rate/12,\"\")"), "@IF(@Pay_Num<>\"\",@Beg_Bal*@Rate/12,\"\")");+ assert_eq!(rewritten("SQRT(A1:A3)"), "@SQRT(@A1:A3)");+ assert_eq!(rewritten("Sheet2!A1:B3"), "@Sheet2!A1:B3");+ assert_eq!(rewritten("Data2[[#This Row],[Input]]*2"), "@Data2[@Input]*2");+ }++ #[test]+ fn a_reference_used_whole_is_not_intersected() {+ assert_eq!(rewritten("SUM(A1:A10)"), "@SUM(A1:A10)");+ assert_eq!(rewritten("AVERAGE((A1:A10))"), "@AVERAGE((A1:A10))");+ assert_eq!(rewritten("SUM(Sheet2!A1:B3)"), "@SUM(Sheet2!A1:B3)");+ assert_eq!(rewritten("SUM(Pay_Num)"), "@SUM(Pay_Num)");+ assert_eq!(rewritten("SUM({1,2,3})"), "@SUM({1,2,3})");+ assert_eq!(rewritten("SUM(INDEX(A1:B3,0,1))"), "@SUM(INDEX(A1:B3,0,1))");+ assert_eq!(rewritten("COUNTIF(A1:A10,\">0\")"), "@COUNTIF(A1:A10,\">0\")");+ }++ #[test]+ fn an_expression_in_a_range_argument_is_one_value() {+ assert_eq!(rewritten("SUM(A1:A3*B1:B3)"), "@SUM(@A1:A3*@B1:B3)");+ assert_eq!(rewritten("SUM(A1:A3+0)"), "@SUM(@A1:A3+0)");+ }++ #[test]+ fn array_functions_and_pass_through_keep_array_positions() {+ assert_eq!(rewritten("SUMPRODUCT(A1:A3*B1:B3)"), "@SUMPRODUCT(A1:A3*B1:B3)");+ assert_eq!(rewritten("SUMPRODUCT(--(A1:A3=\"x\"))"), "@SUMPRODUCT(--(A1:A3=\"x\"))");+ assert_eq!(rewritten("MMULT(A1:B3,D1:E2)"), "@MMULT(A1:B3,D1:E2)");+ assert_eq!(rewritten("IF(A1:A3>0,B1:B3,0)"), "@IF(@A1:A3>0,@B1:B3,0)");+ assert_eq!(rewritten("SUMPRODUCT(IF(A1:A3>0,B1:B3,0))"), "@SUMPRODUCT(IF(A1:A3>0,B1:B3,0))");+ assert_eq!(rewritten("IFERROR(ROW(A1:A3),0)"), "@IFERROR(@ROW(A1:A3),0)");+ }++ #[test]+ fn calls_that_return_a_range_are_intersected() {+ assert_eq!(rewritten("INDEX(A1:A10,B1)"), "@INDEX(A1:A10,@B1)");+ assert_eq!(rewritten("INDEX(A1:A10,B1)*2"), "@INDEX(A1:A10,@B1)*2");+ assert_eq!(rewritten("OFFSET(A1,0,0,3,1)"), "@OFFSET(A1,0,0,3,1)");+ assert_eq!(rewritten("MYUDF(A1)"), "@MYUDF(A1)");+ }++ #[test]+ fn value_parameters_keep_their_reference_so_it_is_rejected() {+ // `EOMONTH` and its peers give #VALUE! for a multi-cell reference in a legacy formula+ // too, instead of intersecting it (`functions::lift`), so the reference stays whole.+ assert_eq!(rewritten("EOMONTH(A1:A3,0)"), "@EOMONTH(A1:A3,0)");+ assert_eq!(rewritten("EOMONTH(INDEX(A1:A3,0,1),0)"), "@EOMONTH(INDEX(A1:A3,0,1),0)");+ assert_eq!(rewritten("ISEVEN(B1:B3)"), "@ISEVEN(B1:B3)");+ assert_eq!(rewritten("SUM(EOMONTH(A1:A3,0))"), "@SUM(@EOMONTH(A1:A3,0))");+ }++ #[test]+ fn scalar_functions_are_not_intersected() {+ assert_eq!(rewritten("SUM(A1:A3)+1"), "@SUM(A1:A3)+1");+ assert_eq!(rewritten("ROUND(A1*2,0)"), "@ROUND(@A1*2,0)");+ }++ #[test]+ fn an_explicit_intersection_is_kept() {+ assert_eq!(rewritten("@A1:A3*2"), "@A1:A3*2");+ assert_eq!(rewritten("SUM(@A1:A3)"), "@SUM(@A1:A3)");+ assert_eq!(rewritten("A1#"), "A1#");+ }++ #[test]+ fn a_long_chain_does_not_recurse_per_operator() {+ let text: String = (1..=1_000).map(|i| format!("+A{i}")).collect();+ let e = gridcraft_formula::parse(&format!("1{text}")).expect("parses");+ let out = scalarize(&e);+ // Every reference is intersected, and the chain is still one flat formula.+ assert_eq!(gridcraft_formula::print(&out), format!("1{}", (1..=1_000).map(|i| format!("+@A{i}")).collect::<String>()));+ }+}@@ -4,6 +4,7 @@ #![forbid(unsafe_code)] pub mod eval;+pub mod implicit; pub mod recalc; mod trimrange; @@ -1216,9 +1216,12 @@ impl Calc { dirty.sort_by_key(|(s, c)| (*s, c.row, c.col)); let mut exprs: HashMap<Key, std::sync::Arc<Expr>> = HashMap::with_capacity_and_hasher(dirty.len(), Default::default()); for k in &dirty {- if let Some(e) = wb.sheet(k.0).and_then(|s| s.cell(k.1)).and_then(|c| c.formula.as_ref()).and_then(|f| f.expr_arc()) {- exprs.insert(*k, e);- }+ let Some(f) = wb.sheet(k.0).and_then(|s| s.cell(k.1)).and_then(|c| c.formula.as_ref()) else { continue };+ let Some(e) = f.expr_arc() else { continue };+ // A formula from a file that predates dynamic arrays is calculated with implicit+ // intersection, as Excel does: its references in scalar positions become `@`.+ let e = if f.implicit && f.array.is_none() { std::sync::Arc::new(crate::implicit::scalarize(&e)) } else { e };+ exprs.insert(*k, e); } let now = self.now(); let mut rng = self.rng;@@ -25,6 +25,14 @@ impl T { self.wb.sheet_mut(0).unwrap().cells.set(c(at), cell); self.calc.cells_changed(&mut self.wb, &[(0, c(at))]); }+ /// A formula read from a file without dynamic-array metadata: Excel calculates it with+ /// implicit intersection.+ fn set_legacy(&mut self, at: &str, input: &str) {+ let mut f = Formula::new(input);+ f.implicit = true;+ self.wb.sheet_mut(0).unwrap().cells.set(c(at), Cell::formula(f));+ self.calc.cells_changed(&mut self.wb, &[(0, c(at))]);+ } fn get(&self, at: &str) -> Value { self.wb.sheet(0).unwrap().value(c(at)) }@@ -2274,3 +2282,35 @@ fn loan_schedule_residue_keeps_excels_sign() { } assert_eq!(t.get("P7"), Value::Text("".into())); }++/// A formula imported from a file that predates dynamic arrays is calculated with implicit+/// intersection: a reference used as one value gives the cell on the formula's row, not a spill.+#[test]+fn legacy_formulas_intersect_instead_of_spilling() {+ let mut t = T::new();+ for (at, n) in [("A1", 1.0), ("A2", 2.0), ("A3", 3.0), ("B1", 10.0), ("B2", 20.0), ("B3", 30.0)] {+ t.set(at, &n.to_string());+ }+ // One reference: the cell on the formula's row (`A2`). Two: each intersects, then multiply.+ t.set_legacy("D2", "=A1:A3");+ t.set_legacy("E2", "=A1:A3*B1:B3");+ assert_eq!((t.get("D2"), t.get("E2")), (Value::Number(2.0), Value::Number(40.0)));+ // Nothing spilled: the cells below and to the right are untouched.+ for at in ["D3", "D4", "E3", "E4"] {+ assert_eq!(t.get(at), Value::Empty, "{at}");+ }+ // A reference used whole by an aggregating function keeps its cells.+ t.set_legacy("F2", "=SUM(A1:A3)");+ t.set_legacy("G2", "=SUM(A1:A3*B1:B3)");+ assert_eq!((t.num("F2"), t.num("G2")), (6.0, 40.0));+ // A function returning a range intersects its result (`INDEX` here picks A2 anyway).+ t.set_legacy("H2", "=INDEX(A1:A3,2)");+ assert_eq!(t.num("H2"), 2.0);+ // A value parameter rejects a multi-cell reference in a legacy formula too, instead of+ // intersecting it.+ t.set_legacy("I2", "=EOMONTH(A1:A3,0)");+ assert_eq!(t.get("I2"), Value::Error(CellError::Value));+ // The same formula typed in GridCraft is a dynamic array and spills.+ t.set("J2", "=A1:A3*B1:B3");+ assert_eq!((t.num("J2"), t.num("J3"), t.num("J4")), (10.0, 40.0, 90.0));+}@@ -366,7 +366,9 @@ fn do_sort(s: &mut Session, sheet: usize, r: RangeRef, header: bool, keys: Vec<S && let Some(e) = f.expr() { let shifted = gridcraft_formula::adjust::shift_relative(e, dst.row as i64 - src.row as i64, dst.col as i64 - src.col as i64);- c.formula = Some(Arc::new(Formula::from_expr(shifted)));+ let mut nf = Formula::from_expr(shifted);+ nf.implicit = f.implicit;+ c.formula = Some(Arc::new(nf)); } if let Some(picture) = sh.cell_pictures.get(&src) { pictures.push((dst, picture.clone()));@@ -1212,11 +1212,12 @@ fn paste_special(s: &mut Session, p: &Json) -> Result<Json> { let (dr, dc) = (dest.row as i64 - sc.row as i64, dest.col as i64 - sc.col as i64); moved.array = f.array.and_then(|a| Some(RangeRef::new(a.start.offset(dr, dc)?, a.end.offset(dr, dc)?))); moved.dynamic = f.dynamic;+ moved.implicit = f.implicit; return Some(std::sync::Arc::new(moved)); } let moved = gridcraft_formula::adjust::shift_relative(e, dest.row as i64 - sc.row as i64, dest.col as i64 - sc.col as i64);- Some(std::sync::Arc::new(Formula { dynamic: f.dynamic, ..Formula::from_expr(moved) }))+ Some(std::sync::Arc::new(Formula { dynamic: f.dynamic, implicit: f.implicit, ..Formula::from_expr(moved) })) }; if link { let sheet_prefix = if dest_sheet == clip.sheet && same_doc {@@ -1432,6 +1433,7 @@ pub(crate) fn rewrite_all_formulas(wb: &mut gridcraft_model::Workbook, target: & let mut nf = Formula::from_expr(new); nf.array = array; nf.dynamic = f.dynamic;+ nf.implicit = f.implicit; cell.formula = Some(std::sync::Arc::new(nf)); } }@@ -437,6 +437,7 @@ fn rename_name_in_formulas(wb: &mut gridcraft_model::Workbook, old: &str, old_sc let mut nf = Formula::from_expr(e); nf.array = f.array; nf.dynamic = f.dynamic;+ nf.implicit = f.implicit; if let Some(cell) = sh.cells.get_mut(c) { cell.formula = Some(std::sync::Arc::new(nf)); }@@ -537,7 +537,9 @@ fn table_rename(s: &mut Session, p: &Json) -> Result<Json> { o => o, }); if let Some(cell) = sh.cells.get_mut(c) {- cell.formula = Some(std::sync::Arc::new(Formula::from_expr(ne)));+ let mut nf = Formula::from_expr(ne);+ nf.implicit = f.implicit;+ cell.formula = Some(std::sync::Arc::new(nf)); } } }@@ -785,7 +785,9 @@ fn delete_sheet(s: &mut Session, p: &Json) -> Result<Json> { if let Some(e) = f.expr() { let ne = gridcraft_formula::adjust::delete_sheet(e, &name, &order); if let Some(cell) = sh.cells.get_mut(c) {- cell.formula = Some(Arc::new(gridcraft_model::Formula::from_expr(ne)));+ let mut nf = gridcraft_model::Formula::from_expr(ne);+ nf.implicit = f.implicit;+ cell.formula = Some(Arc::new(nf)); } } }@@ -839,7 +841,9 @@ fn rename_sheet(s: &mut Session, p: &Json) -> Result<Json> { if text != f.text && let Some(cell) = sh.cells.get_mut(c) {- cell.formula = Some(Arc::new(gridcraft_model::Formula::from_expr(ne)));+ let mut nf = gridcraft_model::Formula::from_expr(ne);+ nf.implicit = f.implicit;+ cell.formula = Some(Arc::new(nf)); } } }@@ -168,7 +168,9 @@ pub fn fill(cx: &mut Ctx, sheet: usize, src: RangeRef, target: RangeRef, mode: F if let Some(f) = &cell.formula { if let Some(e) = f.expr() { let shifted = gridcraft_formula::adjust::shift_relative(e, t.row as i64 - sc.row as i64, t.col as i64 - sc.col as i64);- cell.formula = Some(Arc::new(Formula::from_expr(shifted)));+ let mut nf = Formula::from_expr(shifted);+ nf.implicit = f.implicit;+ cell.formula = Some(Arc::new(nf)); cell.value = Value::Empty; } } else if let Some(v) = match per_cell.get(src_i) {@@ -244,7 +244,9 @@ pub(crate) fn sync_headers(cx: &mut crate::cmd::Ctx) { o => o, }); if hit && let Some(cell) = cx.wb.sheet_mut(i).and_then(|s| s.cells.get_mut(c)) {- cell.formula = Some(std::sync::Arc::new(Formula::from_expr(ne)));+ let mut nf = Formula::from_expr(ne);+ nf.implicit = f.implicit;+ cell.formula = Some(std::sync::Arc::new(nf)); } } }new file mode 100644@@ -0,0 +1,100 @@+//! A plain formula from a file that predates dynamic arrays is calculated with implicit+//! intersection; edits that only move or adjust it keep that, so it never starts spilling.++use std::sync::Arc;++use gridcraft_engine::core::{CellRef, Value};+use gridcraft_engine::model::{Cell, Formula, Workbook};+use gridcraft_engine::{DocState, Session, io};+use serde_json::json;++fn at(s: &str) -> CellRef {+ CellRef::parse(s).unwrap()+}++/// A workbook whose D2 formula stands in for one read from a legacy file (the save below takes+/// the plain-formula path the writer uses for those).+fn opened() -> Session {+ let mut wb = Workbook::new();+ let sh = wb.sheet_mut(0).unwrap();+ for (r, (a, b)) in [(1.0, 10.0), (2.0, 20.0), (3.0, 30.0)].into_iter().enumerate() {+ sh.set_value(CellRef::new(r as u32, 0), Value::Number(a));+ sh.set_value(CellRef::new(r as u32, 1), Value::Number(b));+ }+ let mut f = Formula::new("Sheet1!A1:A3*Sheet1!B1:B3");+ f.implicit = true;+ sh.cells.set(at("D2"), Cell::formula(f));+ let bytes = io::save_bytes(&wb, "legacy.xlsx").unwrap();+ let (back, _) = io::open_bytes("legacy.xlsx", &bytes).unwrap();+ let mut s = Session::new();+ s.add_document(DocState::new(back, None, "legacy".into(), gridcraft_engine::Trust::FromFile));+ s+}++fn formula(s: &Session, cell: &str) -> Arc<Formula> {+ s.doc().unwrap().wb.sheet(0).unwrap().cell(at(cell)).unwrap().formula.clone().unwrap()+}++fn value(s: &Session, cell: &str) -> Value {+ s.doc().unwrap().wb.sheet(0).unwrap().value(at(cell))+}++/// The formula as a fresh open of the saved file reads it: the file's metadata decides whether+/// it is a legacy formula or a dynamic array.+fn saved(s: &Session, cell: &str) -> Arc<Formula> {+ let bytes = io::save_bytes(&s.doc().unwrap().wb, "legacy.xlsx").unwrap();+ let (back, _) = io::open_bytes("legacy.xlsx", &bytes).unwrap();+ back.sheet(0).unwrap().cell(at(cell)).unwrap().formula.clone().unwrap()+}++#[test]+fn opened_legacy_formula_intersects_and_stays_plain() {+ let s = opened();+ let f = formula(&s, "D2");+ assert!(f.implicit && !f.dynamic && f.array.is_none(), "the file's plain formula is legacy");+ // Row 2: A2 * B2, and nothing spills into the empty cells below.+ assert_eq!(value(&s, "D2"), Value::Number(40.0));+ assert_eq!(value(&s, "D3"), Value::Empty);+ let back = saved(&s, "D2");+ assert!(back.implicit && !back.dynamic, "saving must not turn it into a dynamic array");+}++#[test]+fn inserting_rows_and_moving_keep_implicit_intersection() {+ let mut s = opened();+ s.execute("home.insertRows", json!({"rows": "1:1"})).unwrap();+ let f = formula(&s, "D3");+ assert!(f.implicit, "a structural edit only moves the formula");+ assert_eq!(f.text, "Sheet1!A2:A4*Sheet1!B2:B4");+ assert_eq!(value(&s, "D3"), Value::Number(40.0));+ assert!(saved(&s, "D3").implicit);++ s.execute("selection.set", json!({"range": "D3"})).unwrap();+ s.execute("edit.cut", json!({})).unwrap();+ s.execute("selection.set", json!({"range": "F3"})).unwrap();+ s.execute("edit.paste", json!({})).unwrap();+ assert!(formula(&s, "F3").implicit, "a cut keeps the formula");+ assert_eq!(value(&s, "F3"), Value::Number(40.0));+ assert!(saved(&s, "F3").implicit);+}++#[test]+fn renaming_the_sheet_keeps_implicit_intersection() {+ let mut s = opened();+ s.execute("sheet.rename", json!({"sheet": 0, "name": "Data"})).unwrap();+ let f = formula(&s, "D2");+ assert!(f.implicit);+ assert_eq!(f.text, "Data!A1:A3*Data!B1:B3");+ assert_eq!(value(&s, "D2"), Value::Number(40.0));+ assert!(saved(&s, "D2").implicit);+}++#[test]+fn a_retyped_formula_is_a_dynamic_array_again() {+ let mut s = opened();+ s.execute("cell.set", json!({"cell": "D2", "input": "=Sheet1!A1:A3*Sheet1!B1:B3"})).unwrap();+ assert!(!formula(&s, "D2").implicit, "typing a formula makes it dynamic");+ assert_eq!((value(&s, "D2"), value(&s, "D3"), value(&s, "D4")), (Value::Number(10.0), Value::Number(40.0), Value::Number(90.0)));+ let back = saved(&s, "D2");+ assert!(back.dynamic && !back.implicit);+}@@ -15,6 +15,7 @@ mod font_scheme_minor; mod format_as_table_restyle; mod hidden_sheet_navigation; mod ink_roundtrip;+mod legacyimplicit; mod line_count_overflow; mod long_operator_chains; mod offset_overflow;@@ -23,6 +23,13 @@ pub struct Formula { /// rewritten in GridCraft start without it, and the writer decides from their shape. #[serde(default, skip_serializing_if = "std::ops::Not::not")] pub dynamic: bool,+ /// Read from a file that does not mark this formula as a dynamic-array formula (and it is+ /// not a legacy CSE array): Excel has calculated it with implicit intersection since it was+ /// written, and still does. GridCraft calculates it the same way (`gridcraft_calc::implicit`)+ /// and leaves it as a plain formula when saving, so the file keeps behaving as it did.+ /// Formulas entered or rewritten in GridCraft start without it, and are dynamic arrays.+ #[serde(default, skip_serializing_if = "std::ops::Not::not")]+ pub implicit: bool, } impl Formula {@@ -31,12 +38,12 @@ impl Formula { pub fn new(text: &str) -> Formula { let body = text.strip_prefix('=').unwrap_or(text); match gridcraft_formula::parse(body) {- Ok(e) => Formula { text: gridcraft_formula::print(&e), expr: Some(Arc::new(e)), array: None, dynamic: false },- Err(_) => Formula { text: body.to_string(), expr: None, array: None, dynamic: false },+ Ok(e) => Formula { text: gridcraft_formula::print(&e), expr: Some(Arc::new(e)), array: None, dynamic: false, implicit: false },+ Err(_) => Formula { text: body.to_string(), expr: None, array: None, dynamic: false, implicit: false }, } } pub fn from_expr(e: Expr) -> Formula {- Formula { text: gridcraft_formula::print(&e), expr: Some(Arc::new(e)), array: None, dynamic: false }+ Formula { text: gridcraft_formula::print(&e), expr: Some(Arc::new(e)), array: None, dynamic: false, implicit: false } } /// The parsed expression, re-parsing after deserialization. pub fn expr(&self) -> Option<Expr> {@@ -361,7 +361,7 @@ impl Workbook { let f = cell.formula.as_ref()?; let e = f.expr()?; let ne = spell_calls(e.clone(), &lambdas);- (ne != e).then(|| (c, Formula { array: f.array, dynamic: f.dynamic, ..Formula::from_expr(ne) }))+ (ne != e).then(|| (c, Formula { array: f.array, dynamic: f.dynamic, implicit: f.implicit, ..Formula::from_expr(ne) })) }) .collect(); if fixed.is_empty() {@@ -20,6 +20,52 @@ impl Shape { } } +/// Functions and operators that only exist with dynamic arrays. A formula that uses one is not a+/// legacy formula even when the file does not mark it (cell metadata `cm`): a dynamic-array+/// formula written by Excel carries the mark, so such a file came from another tool, and the+/// formula means what GridCraft's array evaluation gives.+pub(crate) fn uses_dynamic_arrays(formula: &Formula) -> bool {+ const FUNCTIONS: &[&str] = &[+ "FILTER",+ "SORT",+ "SORTBY",+ "UNIQUE",+ "SEQUENCE",+ "RANDARRAY",+ "VSTACK",+ "HSTACK",+ "TOROW",+ "TOCOL",+ "WRAPROWS",+ "WRAPCOLS",+ "TAKE",+ "DROP",+ "CHOOSEROWS",+ "CHOOSECOLS",+ "EXPAND",+ "GROUPBY",+ "PIVOTBY",+ "LET",+ "LAMBDA",+ "MAP",+ "SCAN",+ "BYROW",+ "BYCOL",+ "MAKEARRAY",+ "ISOMITTED",+ "ANCHORARRAY",+ "SINGLE",+ ];+ let Some(expr) = formula.expr_arc() else { return false };+ let mut found = false;+ expr.walk(&mut |e| match e {+ Expr::Unary(UnOp::At | UnOp::Spill, _) => found = true,+ Expr::Call(name, _) => found |= FUNCTIONS.iter().any(|f| f.eq_ignore_ascii_case(name)),+ _ => {}+ });+ found+}+ pub(crate) fn needs_array(wb: &Workbook, sheet: usize, at: CellRef, formula: &Formula) -> bool { let Some(expr) = formula.expr_arc() else { return false }; // The budget covers every node of a formula as long as Excel allows (8,192 characters make@@ -521,7 +521,12 @@ fn read_cell(cx: &mut Ctx<'_>, sheet: &mut Sheet, st: &mut RowState, c: &El, pos fn read_formula(cx: &mut Ctx<'_>, st: &mut RowState, f: &El, pos: CellRef, dynamic: bool) -> Option<Formula> { // Keep the file's dynamic-array mark, so saving never turns the formula into a legacy one.- read_formula_text(cx, st, f, pos, dynamic).map(|fm| Formula { dynamic, ..fm })+ // A formula without it that is not a legacy CSE array is one Excel calculated with implicit+ // intersection, and goes on doing so: the calculation and the writer both honour that.+ read_formula_text(cx, st, f, pos, dynamic).map(|fm| {+ let implicit = !dynamic && fm.array.is_none() && !crate::array_formula::uses_dynamic_arrays(&fm);+ Formula { dynamic, implicit, ..fm }+ }) } fn read_formula_text(cx: &mut Ctx<'_>, st: &mut RowState, f: &El, pos: CellRef, dynamic: bool) -> Option<Formula> {@@ -338,7 +338,9 @@ pub fn write_sheet(wb: &Workbook, si: usize, selected: bool, out: &mut Out) -> ( let ex = extra.get(c); let array = dynamic.get(c).copied().or_else(|| { let f = cell?.formula.as_ref()?;- (f.array.is_none() && crate::array_formula::needs_array(wb, si, *c, f)).then_some(RangeRef::cell(*c))+ // A formula read from a legacy file keeps its implicit intersection: Excel+ // calculates a plain formula that way, so marking it dynamic would change it.+ (f.array.is_none() && !f.implicit && crate::array_formula::needs_array(wb, si, *c, f)).then_some(RangeRef::cell(*c)) }); write_cell(&mut s, *c, cell, sheet.cell_pictures.get(c), ov, ex, array.as_ref(), &sid, out); }@@ -724,3 +724,42 @@ fn every_skipped_drawing_object_is_reported() { assert_eq!(hits, 1, "object {n}: {:?}", rep.warnings); } }++/// A plain formula from a file without dynamic-array metadata is one Excel calculated with+/// implicit intersection, so GridCraft imports it that way and saves it plain. A legacy CSE+/// array, a dynamic-array formula and a third-party formula that uses dynamic-array-only+/// functions keep array evaluation.+#[test]+fn legacy_formulas_keep_implicit_intersection_through_a_save() {+ let body = r#"<sheetData>+ <row r="1"><c r="A1"><v>1</v></c><c r="B1"><f>A1:A3*2</f><v>2</v></c><c r="C1"><f t="array" ref="C1:C3">A1:A3*2</f><v>2</v></c><c r="D1" cm="1"><f t="array" ref="D1">_xlfn.SEQUENCE(1)</f><v>1</v></c><c r="E1"><f>FILTER(A1:A3,A1:A3>0)</f><v>1</v></c><c r="F1"><f>SUM(A1:A3)</f><v>6</v></c></row>+ <row r="2"><c r="A2"><v>2</v></c><c r="C2"><v>4</v></c></row>+ <row r="3"><c r="A3"><v>3</v></c><c r="C3"><v>6</v></c></row>+ </sheetData>"#;+ let (wb, _) = read_xlsx(&super::minimal(body, &[], "", "")).unwrap();+ let s = wb.sheet(0).unwrap();+ let formula = |address: &str| s.cell(at(address)).and_then(|c| c.formula.clone()).unwrap();+ let plain = formula("B1");+ assert!(plain.implicit && !plain.dynamic && plain.array.is_none(), "a plain formula is legacy");+ assert!(formula("F1").implicit, "SUM keeps its range argument, but the formula is still legacy");+ assert!(!formula("C1").implicit, "a legacy CSE array is array-evaluated");+ assert!(!formula("D1").implicit, "a formula with dynamic-array metadata is not legacy");+ assert!(!formula("E1").implicit, "a formula using FILTER is dynamic even without the metadata");++ let bytes = crate::write_xlsx(&wb).unwrap();+ let mut pkg = crate::package::Package::open(&bytes).unwrap();+ let sheet = pkg.read_xml("xl/worksheets/sheet1.xml").unwrap().unwrap();+ let cell =+ |address: &str| sheet.child("sheetData").unwrap().kids("row").flat_map(|r| r.kids("c")).find(|c| c.attr("r") == Some(address)).unwrap();+ for address in ["B1", "F1"] {+ assert_eq!(cell(address).attr("cm"), None, "{address} stays plain");+ let f = cell(address).child("f").unwrap();+ assert_eq!((f.attr("t"), f.attr("ref")), (None, None), "{address} stays a scalar formula");+ assert!(!f.text.contains('@') && !f.text.contains("SINGLE"), "{address}: {}", f.text);+ }+ assert_eq!(cell("B1").child("f").unwrap().text, "A1:A3*2");+ assert_eq!(cell("F1").child("f").unwrap().text, "SUM(A1:A3)");+ let cse = cell("C1").child("f").unwrap();+ assert_eq!((cse.attr("t"), cse.attr("ref")), (Some("array"), Some("C1:C3")));+ assert_eq!(cell("D1").attr("cm"), Some("1"));+}@@ -318,6 +318,8 @@ fn reads_values_formulas_formats_and_layout() { // Formulas: text plus the cached result. assert_eq!(ftext(&wb, "A3").as_deref(), Some("B1*2")); assert_eq!(v(&wb, 0, "A3"), Value::Number(3.0));+ assert!(wb.sheets[0].cell(CellRef::parse("A3").unwrap()).unwrap().formula.as_ref().unwrap().implicit, "BIFF has no dynamic arrays");+ assert_eq!(ftext(&wb, "B3").as_deref(), Some("SUM(B1:C1)")); assert_eq!(ftext(&wb, "C3").as_deref(), Some("A1&\"!\"")); assert_eq!(v(&wb, 0, "C3"), Value::from("Hello!"));@@ -334,6 +336,7 @@ fn reads_values_formulas_formats_and_layout() { // the formula fills the range on recalculation (as when an .xlsx is read, #156). let c5 = wb.sheets[0].cell(CellRef::parse("C5").unwrap()).unwrap().formula.clone().unwrap(); assert_eq!((c5.text.as_str(), c5.array), ("A5:A6*2", Some(RangeRef::parse("C5:C6").unwrap())));+ assert!(!c5.implicit, "a legacy CSE array keeps array evaluation"); assert_eq!(ftext(&wb, "C6"), None); assert_eq!(v(&wb, 0, "C6"), Value::Empty); // Number formats, merges, sizes, names, print area; embedded chart records ignored.@@ -469,6 +469,9 @@ impl SheetState<'_> { return; } f.array = array.filter(|r| !r.is_single());+ // BIFF predates dynamic arrays: every other formula is a legacy one, calculated+ // with implicit intersection, and is saved as a plain formula.+ f.implicit = f.array.is_none(); if let Some(cell) = self.sheet.cells.get_mut(pos) { cell.formula = Some(Arc::new(f)); } else {@@ -28,7 +28,7 @@ The exporter inspects the parsed formula without evaluating it. It distinguishes Names are resolved using workbook/sheet scope, and structured references distinguish one table-row cell from a column. Literal `INDIRECT` and `INDEX` arguments permit tighter classification; unresolved reference dimensions are conservatively treated as potentially multiple cells. Name/AST traversal is bounded, with array preservation as the fallback. This is an export classifier, not a claim of exact Excel metadata parity for every function or user-defined function. It stays within the format crate's existing dependencies and does not change calculation or import behavior. -This is a writer policy; interpreting implicit intersection in imported legacy formulas remains separate work. See Microsoft's [Formula vs. Formula2 documentation](https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2).+Imported formulas are the other half. A formula read from a file without dynamic-array metadata (`cm`) that is not a legacy CSE array is one Excel has always calculated with implicit intersection, and every `.xls` (BIFF) formula is such a formula, so the model marks it `implicit`. At calculation time `gridcraft_calc::implicit` rewrites a copy of the expression, wrapping every reference used as one value in `@`; the formula text, its references and the saved file stay plain. Edits that only move or adjust the formula (inserting rows, moving or copying it, renaming a sheet, table or name) keep the mark, while typing a formula makes it a dynamic array, as in Excel. A formula that uses a dynamic-array-only function (`FILTER`, `SEQUENCE`, `LET`, …) is treated as dynamic even without the mark, for files written by other tools. Names used by an implicit formula are intersected; a name whose own definition is a legacy calculation is not rewritten (a known gap). See Microsoft's [Formula vs. Formula2 documentation](https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2). ## Static cell pictures @@ -1,6 +1,6 @@ # File-format parity -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-21 · **Change:** minor (.xls dialog sheets are skipped instead of read as worksheets; `.xls` import reads each sheet's display options from WINDOW2: right-to-left, gridlines, headings, zeros, formulas; XLSX text boxes and shapes keep their paragraphs' right-to-left direction on save; CSV/TSV/TXT can open with every field kept as text via `file.open {csvAsText}`; formulas over Excel's 8,192 characters, and `%` or call chains past the nesting limit, are parse errors instead of stack overflows when a file is opened; XLSX PivotTables read and write `rowGrandTotals`/`colGrandTotals` the way Excel does, so a table saved with only one grand total opens with the right one; HTML export keeps right-to-left; CSV and text import detects likely Windows-1256 Arabic when invalid UTF-8 has Arabic byte runs; other invalid UTF-8 remains Windows-1252; HTML export places pictures and charts, as inline SVG, at their anchors) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4+> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (XLSX formulas read without dynamic-array metadata calculate with implicit intersection and are saved plain, so legacy workbooks no longer show `#SPILL!`; earlier: .xls dialog sheets are skipped instead of read as worksheets; `.xls` import reads each sheet's display options from WINDOW2: right-to-left, gridlines, headings, zeros, formulas; XLSX text boxes and shapes keep their paragraphs' right-to-left direction on save; CSV/TSV/TXT can open with every field kept as text via `file.open {csvAsText}`; formulas over Excel's 8,192 characters, and `%` or call chains past the nesting limit, are parse errors instead of stack overflows when a file is opened; XLSX PivotTables read and write `rowGrandTotals`/`colGrandTotals` the way Excel does, so a table saved with only one grand total opens with the right one; HTML export keeps right-to-left; CSV and text import detects likely Windows-1256 Arabic when invalid UTF-8 has Arabic byte runs; other invalid UTF-8 remains Windows-1252; HTML export places pictures and charts, as inline SVG, at their anchors) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Every format Excel opens or saves, with GridCraft's read and write support, fidelity and tests. Part of [target-app-parity.md](target-app-parity.md). **File formats: ~50% ready, 80–120 h**@@ -39,6 +39,17 @@ names keep the mark; retyping the formula drops it, and the writer then decides formula's shape. Rich-value metadata other than cell pictures (data types, `#SPILL!` error values) is still dropped. +A plain formula read without that metadata, and not a legacy CSE array, is calculated the way+Excel calculates it: with implicit intersection. The model marks it `implicit`, the evaluator+rewrites its scalar positions with `@` when it recalculates, and the writer saves it plain, so a+file from Excel 2019 (or any writer that leaves out `cm`) opens with the values it had instead of+`#SPILL!` (`fixtures.rs`, engine `legacyimplicit.rs`). `.xls` (BIFF) has no dynamic arrays, so+every non-CSE `.xls` formula is marked the same way (`xls_tests.rs`). Formulas using dynamic-array-only+functions (`FILTER`, `SEQUENCE`, `LET`, …) are treated as dynamic even without the mark, and a+formula typed in GridCraft is always a dynamic array. Undo/redo, row and column insert/delete,+cut/copy/paste, fills and sheet, table or name renames keep the mark; a defined name whose own+definition is a legacy calculation is not rewritten yet.+ | Format | Excel | GridCraft read | GridCraft write | Fidelity / notes | Tests | |---|---|---|---|---|---| | `.xlsx` Excel Workbook (transitional) | R/W, native | yes | yes | Cells, shared/array/dynamic formulas, shared strings (with their Japanese phonetic runs — ふりがな — kept through open + save: `<rPh>` offsets and readings and `<phoneticPr>` are written back next to the same string; the table holds each text once, so duplicate `<si>` items whose text is the same but whose readings differ keep only the first reading, with a warning on open), styles, themes, merges, CF, validation, tables, comments and notes, hyperlinks, names, freeze panes, outline, print settings, pictures, shapes, charts (simplified, see [chart-parity.md](chart-parity.md)), sparklines, PivotTables (caches and tables), protection, calc settings, show objects (`workbookPr showObjects`). **Kept unchanged on save (pass-through, `passthrough.rs`):** custom document properties, ribbon customisation and add-in task panes, custom XML, links to external workbooks (never followed or refreshed), data connections, sheet query tables, printer settings (#324), form controls, ActiveX controls, OLE objects and sheet backgrounds (with their VML shapes), and chart sheets (#727: the sheet, drawing, chart, style and colour parts are written back unchanged in their place, and left out when a sheet they plot was deleted or renamed or a worksheet took their name; they can't be edited or created); dropped when their sheet is deleted or their parts point at content GridCraft rewrites. **Not preserved:** slicers and slicer caches, timelines, data model, query tables attached to tables, sheet and workbook `extLst` entries we don't model, chart details; Strict Open XML files keep nothing. Never checked in Excel across a corpus: repair prompts unknown. 100 MB files fail (#175) A picture used on several sheets is written once (#985). | `roundtrip.rs`, `fixtures.rs`, `malformed.rs`, `pivot_tests.rs` |@@ -110,6 +121,7 @@ refresh. Counted under features (Get & Transform, ~10% ready), not here. | Date | Change | Summary | |---|---|---|+| 2026-10-26 | minor | XLSX formulas read without dynamic-array metadata (`cm`), and every `.xls` formula, calculate with implicit intersection as Excel does and are saved plain: a legacy workbook's formulas show their values instead of `#SPILL!`. Dynamic-array-only functions still mark a formula dynamic, and typing a formula makes it one; moves, fills and renames keep the legacy mark (`fixtures.rs`, `xls_tests.rs`, engine `legacyimplicit.rs`) | | 2026-10-21 | minor | HTML (Save as Web Page) writes pictures (`data:` URI `<img>`) and charts (inline SVG from the chart crate's drawing primitives, text included) at their anchors; the table uses the sheet's column widths and row heights when the sheet has either, and output for sheets without pictures or charts is unchanged | | 2026-10-20 | trivial | XLSX shared strings: Japanese phonetic runs (ふりがな, `<rPh sb="…" eb="…">` with `<phoneticPr>`) are read into the workbook and written back next to the string with the same text, so an open + save no longer deletes them; bad offsets and readings are dropped with a warning, a `<phoneticPr>` without runs is kept on its own, and when duplicate `<si>` items share a text but not a reading only the first reading is written (reported on open) (`phonetic_tests.rs`) | | 2026-10-20 | minor | A workbook theme's major/minor per-script typefaces (`<a:font script="Arab" typeface="…"/>`, also Hebr, Jpan and the rest) are read and written back in file order after `<a:cs>`; they were dropped before. A hostile file is held to 64 entries of 256 characters |@@ -1,6 +1,6 @@ # Function parity: worksheet functions and formulas -> **Last reviewed:** 2026-10-15 · **Last updated:** 2026-10-25 · **Change:** minor (the LAMBDA row lists the recursion limit and its `#NUM!`, and the error a defined name holding an error gives when called; earlier: number text with accounting parentheses coerces like Excel 365; INDEX with one index on a 2-D reference is #REF! as in Excel; earlier: SUBTOTAL and AGGREGATE cap the cells they visit, #2175) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4+> **Last reviewed:** 2026-10-15 · **Last updated:** 2026-10-26 · **Change:** minor (legacy formulas read without dynamic-array metadata calculate with implicit intersection; earlier: the LAMBDA row lists the recursion limit and its `#NUM!`, and the error a defined name holding an error gives when called; earlier: number text with accounting parentheses coerces like Excel 365; INDEX with one index on a 2-D reference is #REF! as in Excel; earlier: SUBTOTAL and AGGREGATE cap the cells they visit, #2175) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Excel's worksheet functions, category by category, against GridCraft's function library. Part of [target-app-parity.md](target-app-parity.md); gaps feed [gaps.md](gaps.md).@@ -83,8 +83,7 @@ This improves series preparation; it does not change the model-fitting or season | Dynamic arrays, spill, `#SPILL!`, spill operator `A1#` | done; stale spills after a structural edit or a cleared blocker fixed (#133, #139; PRs #148, #157) | | Recalculation after clearing large sparse ranges | every stored cell changed by Clear is reported to recalculation, without a million-coordinate cutoff; removed formula spills are cleared, and removing empty merges still unblocks spills (#1032) | | SUBTOTAL / AGGREGATE input size | references trim to the used range; all areas, arrays and values of one call are charged against the 16-million-cell limit before any cell is read (skipped hidden, nested and error cells count), and `#NUM!` past it (#2175) |-| Implicit intersection `@`, legacy CSE array formulas | done; legacy array range fill (PR #156) |-| Implicit intersection `@`, legacy CSE array formulas | done, the entered range filled (PR #156) |+| Implicit intersection `@`, legacy CSE array formulas | done; legacy array range fill (PR #156); formulas read from files without dynamic-array metadata calculate with implicit intersection and are saved plain, while formulas typed here are dynamic arrays | | Array lifting: an array in a scalar argument evaluates the function per element | done. The Analysis ToolPak functions (EOMONTH, EDATE, WORKDAY(.INTL), NETWORKDAYS(.INTL), YEARFRAC, WEEKNUM, the COUP*, PRICE*, YIELD*, ODD*, TBILL*, ACCRINT*, AMOR*, DISC, DURATION, CUMIPMT/CUMPRINC, EFFECT, NOMINAL, DOLLARDE/FR, XNPV/XIRR rate and guess, FVSCHEDULE principal, ISEVEN/ISODD, QUOTIENT, MROUND, FACTDOUBLE, SQRTPI, RANDBETWEEN, SERIESSUM, the base conversions, CONVERT, DELTA, GESTEP, ERF/ERFC, BESSEL*, COMPLEX and IM* except IMSUM/IMPRODUCT) give `#VALUE!` for a reference to more than one cell in a value argument, as Excel 365 does in both dynamic-array and legacy formulas; array constants and computed arrays (`A1:A3+0`) still lift, and range arguments (holidays, schedule, cash flows) and the .INTL weekend accept ranges. Difference: BYROW/BYCOL pass their row or column to the LAMBDA as values, so `BYROW(A1:B3,LAMBDA(r,EOMONTH(r,0)))` lifts where Excel gives `#VALUE!` | | Trim references `A1:.E10` (trailing), `A1.:E10` (leading), `A1.:.E10` (both), also on whole rows/columns (`A:.A`) | done; evaluated as `TRIMRANGE(range, code, code)`, adjusted on insert/delete; files store `_xlfn.TRIMRANGE(range,code,code)` (Excel's own file spelling unconfirmed in public docs), `_xlfn._TRO_ALL/_TRO_LEADING/_TRO_TRAILING(...)` read too | | LET, LAMBDA, named LAMBDAs, ISOMITTED, MAP/REDUCE/SCAN/BYROW/BYCOL/MAKEARRAY | done. A LAMBDA that calls itself more than 140 times, or a formula nested past the depth limit, gives `#NUM!` (the target app allows deeper recursion, #1581). Calling a defined name that holds an error value gives that error, not `#NAME?` |