diff --git a/crates/calc/src/eval.rs b/crates/calc/src/eval.rs --- a/crates/calc/src/eval.rs +++ b/crates/calc/src/eval.rs @@ -792,6 +792,21 @@ impl<'h> Evaluator<'h> { } } } + "FIELDVALUE" => { + if args.len() != 2 { + return err(CellError::Value); + } + let Some(Value::Text(field)) = self.arg_val(args, 1) else { return err(CellError::Value) }; + let areas = match self.eval(&args[0]) { + Ev::R(areas) => areas, + Ev::V(Value::Error(e)) => return err(e), + _ => return err(CellError::Value), + }; + let Some(a) = areas.first() else { return err(CellError::Ref) }; + let record = self.host.workbook().sheet(a.sheet).and_then(|s| s.linked_records.get(&a.range.start)); + // Excel's #FIELD! has no error value here yet; a missing record or field is #VALUE!. + record.and_then(|r| r.field(&field)).map_or(err(CellError::Value), Ev::V) + } "SHEET" | "SHEETS" => { let wb = self.host.workbook(); if args.is_empty() { @@ -1991,6 +2006,7 @@ fn is_special(n: &str) -> bool { | "ISREF" | "ISFORMULA" | "FORMULATEXT" + | "FIELDVALUE" | "SHEET" | "SHEETS" | "OFFSET" @@ -2029,6 +2045,7 @@ pub const SPECIAL_FUNCTIONS: &[&str] = &[ "ISREF", "ISFORMULA", "FORMULATEXT", + "FIELDVALUE", "SHEET", "SHEETS", "OFFSET", diff --git a/crates/calc/src/tests.rs b/crates/calc/src/tests.rs --- a/crates/calc/src/tests.rs +++ b/crates/calc/src/tests.rs @@ -2274,3 +2274,35 @@ fn loan_schedule_residue_keeps_excels_sign() { } assert_eq!(t.get("P7"), Value::Text("".into())); } + +#[test] +fn fieldvalue_reads_a_linked_record_and_the_dot_syntax_means_the_same() { + use gridcraft_model::{LinkedField, LinkedRecord}; + let field = |name: &str, kind: &str, value: &str| LinkedField { name: name.into(), kind: kind.into(), value: value.into() }; + let mut t = T::new(); + t.set("A1", "EUR/USD"); + let record = LinkedRecord { + display: "EUR/USD".into(), + fields: vec![field("Price", "d", "1.0842"), field("Name", "s", "Euro / US dollar"), field("Open", "b", "1"), field("Icon", "r", "0")], + }; + t.wb.sheet_mut(0).unwrap().linked_records.insert(c("A1"), std::sync::Arc::new(record)); + t.set("B1", "=A1.Price"); + t.set("B2", "=FIELDVALUE(A1,\"price\")*2"); + t.set("B3", "=FIELDVALUE(A1,\"Name\")"); + t.set("B4", "=A1.Open"); + assert_eq!((t.num("B1"), t.num("B2")), (1.0842, 2.1684)); + assert_eq!(t.get("B3"), Value::text("Euro / US dollar")); + assert_eq!(t.get("B4"), Value::Bool(true)); + // An unknown field, a field that is not a scalar and a cell without a record are errors. + for (at, input) in [("C1", "=A1.Volume"), ("C2", "=A1.Icon"), ("C3", "=A2.Price"), ("C4", "=FIELDVALUE(1,\"Price\")")] { + t.set(at, input); + assert!(matches!(t.get(at), Value::Error(_)), "{input}"); + } + // An error in the first argument (a deleted record cell) comes through as itself. + t.set("C5", "=FIELDVALUE(#REF!,\"Price\")"); + assert_eq!(t.get("C5"), Value::Error(CellError::Ref)); + // Overwriting the cell drops the record. + t.wb.sheet_mut(0).unwrap().set_value(c("A1"), Value::text("x")); + t.calc.cells_changed(&mut t.wb, &[(0, c("A1"))]); + assert!(matches!(t.get("B1"), Value::Error(_))); +} diff --git a/crates/engine/src/cmd/formulas.rs b/crates/engine/src/cmd/formulas.rs --- a/crates/engine/src/cmd/formulas.rs +++ b/crates/engine/src/cmd/formulas.rs @@ -277,6 +277,7 @@ pub fn function_list() -> Vec { ("ISREF", "Information", "ISREF(value)", "TRUE for references."), ("ISFORMULA", "Information", "ISFORMULA(reference)", "TRUE when the cell has a formula."), ("FORMULATEXT", "Lookup", "FORMULATEXT(reference)", "The formula of a cell as text."), + ("FIELDVALUE", "Lookup", "FIELDVALUE(value, field_name)", "A field of a linked data type record."), ("SHEET", "Information", "SHEET([value])", "Sheet number."), ("SHEETS", "Information", "SHEETS([reference])", "Number of sheets."), ("AREAS", "Lookup", "AREAS(reference)", "Number of areas in a reference."), diff --git a/crates/engine/src/cmd/sheet.rs b/crates/engine/src/cmd/sheet.rs --- a/crates/engine/src/cmd/sheet.rs +++ b/crates/engine/src/cmd/sheet.rs @@ -187,6 +187,8 @@ fn shift_sheet_features(sh: &mut Sheet, axis: Axis, at: u32, count: u32, insert: } sh.cell_pictures = std::mem::take(&mut sh.cell_pictures).into_iter().filter_map(|(c, v)| map_cell(c).filter(CellRef::is_valid).map(|c| (c, v))).collect(); + sh.linked_records = + std::mem::take(&mut sh.linked_records).into_iter().filter_map(|(c, v)| map_cell(c).filter(CellRef::is_valid).map(|c| (c, v))).collect(); sh.comments = std::mem::take(&mut sh.comments).into_iter().filter_map(|(c, v)| map_cell(c).map(|c| (c, v))).collect(); sh.hyperlinks = std::mem::take(&mut sh.hyperlinks).into_iter().filter_map(|(c, v)| map_cell(c).map(|c| (c, v))).collect(); // Floating objects follow their anchor lines per their [`AnchorMode`]. Corners were snapshotted @@ -445,6 +447,8 @@ fn move_columns(s: &mut Session, p: &Json) -> Result { let sh = cx.sheet_mut(sheet)?; let pictures: Vec<_> = sh.cell_pictures.iter().filter(|(cell, _)| source.contains(**cell)).map(|(cell, picture)| (*cell, picture.clone())).collect(); + let records: Vec<_> = + sh.linked_records.iter().filter(|(cell, _)| source.contains(**cell)).map(|(cell, record)| (*cell, record.clone())).collect(); let cells = sh.take_cells(source); let moved_lines: Vec<_> = (0..count).filter_map(|offset| sh.cols.remove(&(moved_from + offset)).map(|info| (offset, info))).collect(); let movement = Edit::Move { from: source, to_row: 0, to_col: destination }; @@ -469,6 +473,10 @@ fn move_columns(s: &mut Session, p: &Json) -> Result { let dest = CellRef::new(cell.row, destination + (cell.col - moved_from)); sh.cell_pictures.insert(dest, picture); } + for (cell, record) in records { + let dest = CellRef::new(cell.row, destination + (cell.col - moved_from)); + sh.linked_records.insert(dest, record); + } for (offset, info) in moved_lines { sh.cols.insert(destination + offset, info); } @@ -1062,6 +1070,8 @@ pub(super) fn shift_cell_features(sh: &mut Sheet, e: &Edit) { } sh.cell_pictures = std::mem::take(&mut sh.cell_pictures).into_iter().filter_map(|(c, v)| map_cell(c).filter(CellRef::is_valid).map(|c| (c, v))).collect(); + sh.linked_records = + std::mem::take(&mut sh.linked_records).into_iter().filter_map(|(c, v)| map_cell(c).filter(CellRef::is_valid).map(|c| (c, v))).collect(); sh.comments = std::mem::take(&mut sh.comments).into_iter().filter_map(|(c, v)| map_cell(c).map(|c| (c, v))).collect(); sh.hyperlinks = std::mem::take(&mut sh.hyperlinks).into_iter().filter_map(|(c, v)| map_cell(c).map(|c| (c, v))).collect(); for sp in sh.sparklines.iter_mut() { diff --git a/crates/engine/src/tables.rs b/crates/engine/src/tables.rs --- a/crates/engine/src/tables.rs +++ b/crates/engine/src/tables.rs @@ -210,7 +210,7 @@ pub(crate) fn sync_headers(cx: &mut crate::cmd::Ctx) { // Headers are text: a number typed there, or a generated name, is written back as text. if name != text || !is_text { let style = sh.style_id(c); - sh.cells.set(c, Cell { value: gridcraft_core::Value::text(name.as_str()), style, ..Default::default() }); + sh.set_cell(c, Cell { value: gridcraft_core::Value::text(name.as_str()), style, ..Default::default() }); } if !old.eq_ignore_ascii_case(&name) { renames.push((si, table, range, old, name)); diff --git a/crates/formula/src/parser.rs b/crates/formula/src/parser.rs --- a/crates/formula/src/parser.rs +++ b/crates/formula/src/parser.rs @@ -366,6 +366,15 @@ impl Parser { _ => r, } }; + // `A1.Price`: field access on a linked data type record. + if trim.is_none() + && let Some((head, field)) = w.split_once('.') + && !field.is_empty() + && let Some(a) = word_is_cell(head) + { + let cell = Expr::Ref(Reference { sheet, kind: RefKind::Cell(a) }); + return Ok(Expr::Call("FIELDVALUE".into(), vec![cell, Expr::Text(field.into())])); + } if let Some(a) = word_is_cell(w) { // Cell, possibly `A1:B2` (merged here so the sheet applies to both ends). if trim.is_some() diff --git a/crates/model/src/cell.rs b/crates/model/src/cell.rs --- a/crates/model/src/cell.rs +++ b/crates/model/src/cell.rs @@ -58,6 +58,36 @@ pub struct CellPicture { pub alt: String, } +/// One saved field of a linked data type record: the structure key, its type code and value text. +#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)] +pub struct LinkedField { + pub name: String, + /// The file's key type: `d` (number), `i` (integer), `s` (text) or `b` (boolean). + pub kind: String, + pub value: String, +} + +/// A linked data type record (Excel's Stocks, Currencies…) kept as saved: the cell shows `display`, +/// and `FIELDVALUE` reads `fields`. Nothing is refreshed from a provider. +#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)] +pub struct LinkedRecord { + pub display: String, + pub fields: Vec, +} + +impl LinkedRecord { + /// A field's value, by name (case-insensitive); `None` for an unknown field. + pub fn field(&self, name: &str) -> Option { + let f = self.fields.iter().find(|f| f.name.eq_ignore_ascii_case(name))?; + match f.kind.as_str() { + "d" | "i" => f.value.trim().parse::().ok().filter(|n| n.is_finite()).map(Value::Number), + "b" => Some(Value::Bool(matches!(f.value.trim(), "1" | "true"))), + "s" => Some(Value::text(f.value.as_str())), + _ => None, + } + } +} + #[derive(Clone, Debug, Default, PartialEq, Serialize, Deserialize)] pub struct Cell { /// Constant value, or the cached result of the formula. diff --git a/crates/model/src/lib.rs b/crates/model/src/lib.rs --- a/crates/model/src/lib.rs +++ b/crates/model/src/lib.rs @@ -15,7 +15,7 @@ pub mod store; pub mod style; pub mod workbook; -pub use cell::{Cell, CellPicture, Formula}; +pub use cell::{Cell, CellPicture, Formula, LinkedField, LinkedRecord}; pub use features::*; pub use password::PasswordHash; pub use preserved::{Preserved, PreservedPart, PreservedRel, PreservedSheet, PreservedXml}; diff --git a/crates/model/src/sheet.rs b/crates/model/src/sheet.rs --- a/crates/model/src/sheet.rs +++ b/crates/model/src/sheet.rs @@ -6,7 +6,7 @@ use std::sync::Arc; use gridcraft_core::{CellRef, RangeRef, Value}; use serde::{Deserialize, Serialize}; -use crate::cell::{Cell, CellPicture}; +use crate::cell::{Cell, CellPicture, LinkedRecord}; use crate::features::{ AutoFilter, Chart, Comment, CondFormat, Hyperlink, Image, LineInfo, PivotTable, PrintSettings, Shape, SheetProtection, Slicer, Sparkline, Table, Validation, @@ -63,6 +63,9 @@ pub struct Sheet { /// Static pictures are sparse rich content. Ordinary cells carry no picture pointer. #[serde(default, skip_serializing_if = "BTreeMap::is_empty", with = "picture_map")] pub cell_pictures: BTreeMap>, + /// Linked data type records (Stocks, Currencies…); the cell's value is the record's display text. + #[serde(default, skip_serializing_if = "BTreeMap::is_empty", with = "picture_map")] + pub linked_records: BTreeMap>, /// Values spilled by dynamic-array formulas (maintained by calc), keyed by cell. #[serde(skip)] pub spill: BTreeMap, @@ -152,6 +155,7 @@ impl Sheet { file_id: 0, cells: CellStore::new(), cell_pictures: BTreeMap::new(), + linked_records: BTreeMap::new(), spill: BTreeMap::new(), spill_ranges: BTreeMap::new(), cols: BTreeMap::new(), @@ -264,21 +268,29 @@ impl Sheet { /// Replaces content, removing any picture at this address. Style-only edits use set_style. pub fn set_cell(&mut self, c: CellRef, cell: Cell) { self.cell_pictures.remove(&c); + // A write that keeps the value (a format-only paste) keeps the record. + let same = cell.formula.is_none() && self.cells.get(c).is_some_and(|old| old.formula.is_none() && old.value == cell.value); + if !same { + self.linked_records.remove(&c); + } self.cells.set(c, cell); } pub fn remove_cell(&mut self, c: CellRef) -> Option { self.cell_pictures.remove(&c); + self.linked_records.remove(&c); self.cells.remove(c) } /// Removes content in a range; returns scalar cells, with pictures removed from the side map. pub fn take_cells(&mut self, range: RangeRef) -> Vec<(CellRef, Cell)> { self.cell_pictures.retain(|c, _| !range.contains(*c)); + self.linked_records.retain(|c, _| !range.contains(*c)); self.cells.take_range(range) } /// Pictures retain a scalar fallback for calculation and unsupported consumers. pub fn set_picture(&mut self, c: CellRef, picture: Arc) { let style = self.style_id(c); self.cells.set(c, Cell { value: Value::Error(gridcraft_core::CellError::Value), formula: None, style }); + self.linked_records.remove(&c); self.cell_pictures.insert(c, picture); } pub fn input_text(&self, c: CellRef) -> String { @@ -478,6 +490,19 @@ mod tests { use super::*; use crate::cell::Formula; + #[test] + fn a_format_only_write_keeps_a_linked_record_and_a_new_value_drops_it() { + let at = CellRef::new(0, 0); + let mut s = Sheet::new("S"); + s.set_value(at, Value::text("EUR/USD")); + s.linked_records.insert(at, Arc::new(LinkedRecord { display: "EUR/USD".into(), fields: vec![] })); + let style = StyleId(1); + s.set_cell(at, Cell { value: Value::text("EUR/USD"), formula: None, style }); + assert!(s.linked_records.contains_key(&at)); + s.set_cell(at, Cell::value(Value::text("other"))); + assert!(!s.linked_records.contains_key(&at)); + } + #[test] fn line_index_matches_linear() { let mut s = Sheet::new("S"); @@ -630,15 +655,14 @@ mod tests { // CellRef is a struct, so encode the sparse map as entries for JSON as well as other formats. mod picture_map { - use crate::cell::CellPicture; use gridcraft_core::CellRef; use serde::{Deserialize, Deserializer, Serialize, Serializer}; use std::collections::BTreeMap; use std::sync::Arc; - pub fn serialize(map: &BTreeMap>, serializer: S) -> Result { + pub fn serialize(map: &BTreeMap>, serializer: S) -> Result { map.iter().collect::>().serialize(serializer) } - pub fn deserialize<'de, D: Deserializer<'de>>(deserializer: D) -> Result>, D::Error> { - Ok(Vec::<(CellRef, Arc)>::deserialize(deserializer)?.into_iter().collect()) + pub fn deserialize<'de, D: Deserializer<'de>, T: Deserialize<'de>>(deserializer: D) -> Result>, D::Error> { + Ok(Vec::<(CellRef, Arc)>::deserialize(deserializer)?.into_iter().collect()) } } diff --git a/crates/xlsx/src/fmla.rs b/crates/xlsx/src/fmla.rs --- a/crates/xlsx/src/fmla.rs +++ b/crates/xlsx/src/fmla.rs @@ -86,6 +86,8 @@ fn prefix_params(e: Expr, scope: &[String]) -> Expr { fn expr_to_file(e: Expr) -> String { let e = prefix_params(e, &[]); let e = e.map(&mut |x| match x { + // Excel stores a field access (`A1.Price`) as `_FV(A1,"Price")`. + Expr::Call(name, args) if name == "FIELDVALUE" => Expr::Call("_FV".into(), args), Expr::Call(name, args) => match crate::tables::file_function_name(&name) { Some(n) => Expr::Call(n, args), None => Expr::Call(name, args), @@ -125,6 +127,7 @@ pub fn from_file(text: &str) -> String { match gridcraft_formula::parse(body) { Ok(e) => gridcraft_formula::print(&e.map(&mut |x| match x { Expr::Name(n) => Expr::Name(strip_param_prefix(n)), + Expr::Call(n, args) if n.eq_ignore_ascii_case("_FV") => Expr::Call("FIELDVALUE".into(), args), Expr::Call(n, args) => Expr::Call(strip_param_prefix(n), args), other => other, })), diff --git a/crates/xlsx/src/read.rs b/crates/xlsx/src/read.rs --- a/crates/xlsx/src/read.rs +++ b/crates/xlsx/src/read.rs @@ -3,7 +3,9 @@ use std::sync::Arc; use gridcraft_core::{DateSystem, RangeRef}; -use gridcraft_model::{CalcMode, CellPicture, DefinedName, PhoneticString, Sheet, ShowObjects, Style, StyleId, StyleTable, Visibility, Workbook}; +use gridcraft_model::{ + CalcMode, CellPicture, DefinedName, LinkedRecord, PhoneticString, Sheet, ShowObjects, Style, StyleId, StyleTable, Visibility, Workbook, +}; use crate::package::{Package, Rel}; use crate::styles::{StylesIn, read_styles}; @@ -30,6 +32,8 @@ pub struct Ctx<'a> { pub num_fmts: std::collections::HashMap, /// Value metadata blocks, indexed by the worksheet's one-based `vm` attribute. pub cell_pictures: Vec>>, + /// The same blocks, for linked data type records. + pub linked_records: Vec>>, /// x14 conditional-format ids of the current sheet's data bars, with the index of the rule in `cond_formats`. pub cf_ext_ids: Vec<(String, usize)>, } @@ -112,6 +116,7 @@ pub fn read_xlsx(bytes: &[u8]) -> Result<(Workbook, ReadReport), IoError> { next_id: 1, num_fmts: Default::default(), cell_pictures: vec![], + linked_records: vec![], cf_ext_ids: vec![], }; let root_rels = cx.pkg.rels("")?; @@ -140,6 +145,13 @@ pub fn read_xlsx(bytes: &[u8]) -> Result<(Workbook, ReadReport), IoError> { vec![] } }; + cx.linked_records = match crate::richdata::read_linked(&mut cx, &wb_rels) { + Ok(records) => records, + Err(error) => { + cx.warn(format!("linked data type records could not be read ({error}); their displayed text was retained")); + vec![] + } + }; let mut wb = Workbook::new(); wb.sheets.clear(); diff --git a/crates/xlsx/src/richdata.rs b/crates/xlsx/src/richdata.rs --- a/crates/xlsx/src/richdata.rs +++ b/crates/xlsx/src/richdata.rs @@ -5,13 +5,13 @@ use std::collections::HashMap; use std::fmt::Write as _; use std::sync::Arc; -use gridcraft_model::CellPicture; +use gridcraft_model::{CellPicture, LinkedField, LinkedRecord}; use crate::IoError; use crate::package::{Rel, normalize}; use crate::read::Ctx; use crate::write::{NS_MAIN, NS_REL, Out, Rels, XML_DECL}; -use crate::xml::{self, El, esc}; +use crate::xml::{self, El, esc, esc_attr}; const NS_RICH: &str = "http://schemas.microsoft.com/office/spreadsheetml/2017/richdata"; const NS_IMAGE_REL: &str = "http://schemas.microsoft.com/office/spreadsheetml/2022/richvaluerel"; @@ -133,15 +133,24 @@ pub fn read(cx: &mut Ctx<'_>, workbook_rels: &[Rel]) -> Result, workbook_rels: &[Rel]) -> Result, workbook_rels: &[Rel]) -> Result, metadata: &El, type_id: usize, future: &El) -> Result>, IoError> { let future: Vec<_> = entries(cx, future, "bk")? .into_iter() .map(|block| { @@ -181,13 +197,82 @@ pub fn read(cx: &mut Ctx<'_>, workbook_rels: &[Rel]) -> Result bool { + matches!(structure.attr("t"), Some("_linkedentity" | "_linkedentitycore")) +} + +/// Linked data type records (Excel's Stocks, Currencies…) by the worksheet's one-based `vm` +/// block. Fields of a type other than number, text or boolean (icons, hyperlinks, format bags) +/// are not kept. +pub fn read_linked(cx: &mut Ctx<'_>, workbook_rels: &[Rel]) -> Result>>, IoError> { + let Some(metadata_path) = part(cx, workbook_rels, "sheetMetadata") else { return Ok(vec![]) }; + let mut rels = workbook_rels.to_vec(); + rels.extend(cx.pkg.rels(&metadata_path)?); + let Some(data_path) = part(cx, &rels, "rdRichValue") else { return Ok(vec![]) }; + rels.extend(cx.pkg.rels(&data_path)?); + let Some(structure_path) = part(cx, &rels, "rdRichValueStructure") else { return Ok(vec![]) }; + let Some(structures) = read_xml(cx, &structure_path, "rvStructures")? else { return Ok(vec![]) }; + let structures = entries(cx, &structures, "s")?; + if !structures.iter().any(|s| is_linked(s)) { + return Ok(vec![]); + } + let Some(metadata) = read_xml(cx, &metadata_path, "metadata")? else { return Ok(vec![]) }; + let Some(types) = metadata.child("metadataTypes") else { return Ok(vec![]) }; + let types = entries(cx, types, "metadataType")?; + let Some(type_id) = types.iter().position(|el| el.attr("name") == Some("XLRICHVALUE")).map(|n| n + 1) else { return Ok(vec![]) }; + let Some(future) = metadata.kids("futureMetadata").find(|el| el.attr("name") == Some("XLRICHVALUE")) else { return Ok(vec![]) }; + let Some(data) = read_xml(cx, &data_path, "rvData")? else { return Ok(vec![]) }; + let values = entries(cx, &data, "rv")?; + let cores: Vec>> = values + .iter() + .map(|value| { + let structure = value.attr_u32("s").and_then(|n| structures.get(n as usize))?; + if structure.attr("t") == Some("_linkedentitycore") { linked_core(structure, value).map(Arc::new) } else { None } + }) + .collect(); + let resolved: Vec>> = values + .iter() + .zip(&cores) + .map(|(value, core)| { + let structure = value.attr_u32("s").and_then(|n| structures.get(n as usize))?; + match structure.attr("t") { + Some("_linkedentitycore") => core.clone(), + // The wrapper's `%cvi` points at the rich value holding the record. + Some("_linkedentity") => { + let at = structure.kids("k").position(|k| k.attr("n") == Some("%cvi"))?; + let n = value.kids("v").nth(at)?.text.trim().parse::().ok()?; + cores.get(n).cloned().flatten() + } + _ => None, + } + }) + .collect(); + let indices = block_indices(cx, &metadata, type_id, future)?; + let blocks = indices.into_iter().map(|index| index.and_then(|n| resolved.get(n)).cloned().flatten()).collect(); + Ok(blocks) +} + +fn linked_core(structure: &El, value: &El) -> Option { + let keys: Vec<_> = structure.kids("k").collect(); + let values: Vec<_> = value.kids("v").collect(); + if keys.len() != values.len() { + return None; + } + let mut fields = Vec::new(); + for (key, value) in keys.iter().zip(&values) { + let (Some(name), Some(kind)) = (key.attr("n"), key.attr("t")) else { continue }; + if matches!(kind, "d" | "i" | "s" | "b") { + fields.push(LinkedField { name: name.to_string(), kind: kind.to_string(), value: value.text.clone() }); + } + } + let display = fields.iter().find(|f| f.name == "_DisplayString" && f.kind == "s").map(|f| f.value.clone()).unwrap_or_default(); + Some(LinkedRecord { display, fields }) +} + fn read_value( cx: &mut Ctx<'_>, value: &El, @@ -254,59 +339,116 @@ fn read_value( Ok(Some(picture)) } +enum Item { + Picture(Arc), + Linked(Arc), +} + /// One rich value per shared model payload; copied cells can reuse a metadata block. #[derive(Default)] pub struct PicturesOut { - pictures: Vec>, - indices: HashMap<*const CellPicture, u32>, + items: Vec, + indices: HashMap<*const (), u32>, } impl PicturesOut { pub fn is_empty(&self) -> bool { - self.pictures.is_empty() + self.items.is_empty() } pub fn index(&mut self, picture: &Arc) -> u32 { - let key = Arc::as_ptr(picture); + self.push(Arc::as_ptr(picture).cast(), Item::Picture(picture.clone())) + } + pub fn index_linked(&mut self, record: &Arc) -> u32 { + self.push(Arc::as_ptr(record).cast(), Item::Linked(record.clone())) + } + fn push(&mut self, key: *const (), item: Item) -> u32 { if let Some(index) = self.indices.get(&key) { return *index; } - let index = self.pictures.len().saturating_add(1) as u32; - self.pictures.push(picture.clone()); + let index = self.items.len().saturating_add(1) as u32; + self.items.push(item); self.indices.insert(key, index); index } } +/// The structure element for a linked record's fields. +fn core_structure(record: &LinkedRecord) -> String { + let mut xml = String::from(""); + for field in &record.fields { + let _ = write!(xml, "", esc_attr(&field.name), esc_attr(&field.kind)); + } + xml.push_str(""); + xml +} + pub fn write(out: &mut Out, workbook_rels: &mut Rels) -> Result<(), IoError> { - let pictures = std::mem::take(&mut out.cell_pictures).pictures; - if pictures.len() > MAX_RECORDS { + let items = std::mem::take(&mut out.cell_pictures).items; + if items.len() > MAX_RECORDS { return Err(IoError::TooLarge("too many cell pictures".into())); } - let count = pictures.len(); - let mut data = format!("{XML_DECL}"); + let has_pictures = items.iter().any(|item| matches!(item, Item::Picture(_))); let mut rel_list = format!("{XML_DECL}"); let mut image_rels = Rels::default(); let mut total = 0usize; - for (i, picture) in pictures.iter().enumerate() { - total = total.saturating_add(picture.data.len()); - if picture.data.len() as u64 > MAX_IMAGE || total > MAX_IMAGES_TOTAL { - return Err(IoError::TooLarge("cell pictures exceed the 16 MiB image or 128 MiB workbook limit".into())); + let mut pictures = 0usize; + let mut structures: Vec = vec![]; + if has_pictures { + structures.push( + "".into(), + ); + } + // `_linkedentity` wraps the `_linkedentitycore` record, which has one structure per key set. + let mut wrapper: Option = None; + let mut cores: HashMap = HashMap::new(); + let mut values = String::new(); + let mut rv_count = 0usize; + // The rich value each item's cell points at (a linked record's wrapper, after its core). + let mut cell_values = Vec::with_capacity(items.len()); + for item in &items { + match item { + Item::Picture(picture) => { + total = total.saturating_add(picture.data.len()); + if picture.data.len() as u64 > MAX_IMAGE || total > MAX_IMAGES_TOTAL { + return Err(IoError::TooLarge("cell pictures exceed the 16 MiB image or 128 MiB workbook limit".into())); + } + let (mime, ext) = image_type(&picture.data).ok_or_else(|| { + IoError::Format("cell pictures must be PNG/JPEG with at most 8192 pixels per side and 16 million pixels".into()) + })?; + pictures += 1; + let image_name = format!("cellImage{pictures}.{ext}"); + out.default_type(ext, mime); + out.part(&format!("xl/media/{image_name}"), None, picture.data.clone()); + let rid = image_rels.add("image", &format!("../media/{image_name}")); + let _ = write!(rel_list, ""); + let _ = write!(values, "{}5{}", pictures - 1, esc(&picture.alt)); + cell_values.push(rv_count); + rv_count += 1; + } + Item::Linked(record) => { + let core = core_structure(record); + let core_index = *cores.entry(core.clone()).or_insert_with(|| { + structures.push(core); + structures.len() - 1 + }); + let wrapper_index = *wrapper.get_or_insert_with(|| { + structures.push("".into()); + structures.len() - 1 + }); + let _ = write!(values, ""); + for field in &record.fields { + let _ = write!(values, "{}", esc(&field.value)); + } + let _ = write!(values, "{rv_count}"); + cell_values.push(rv_count + 1); + rv_count += 2; + } } - let (mime, ext) = image_type(&picture.data) - .ok_or_else(|| IoError::Format("cell pictures must be PNG/JPEG with at most 8192 pixels per side and 16 million pixels".into()))?; - let image_name = format!("cellImage{}.{}", i + 1, ext); - out.default_type(ext, mime); - out.part(&format!("xl/media/{image_name}"), None, picture.data.clone()); - let rid = image_rels.add("image", &format!("../media/{image_name}")); - let _ = write!(rel_list, ""); - let _ = write!(data, "{i}5{}", esc(&picture.alt)); - } - data.push_str(""); + } + let data = format!("{XML_DECL}{values}"); rel_list.push_str(""); - let structures = format!( - "{XML_DECL}" - ); - for (name, content_type, relation, bytes) in [ + let structures = format!("{XML_DECL}{}", structures.len(), structures.concat()); + let mut parts = vec![ ( "rdrichvalue.xml", "application/vnd.ms-excel.rdRichValue+xml", @@ -319,27 +461,34 @@ pub fn write(out: &mut Out, workbook_rels: &mut Rels) -> Result<(), IoError> { "http://schemas.microsoft.com/office/2017/06/relationships/rdRichValueStructure", structures.into_bytes(), ), - ( + ]; + if has_pictures { + parts.push(( "richValueRel.xml", "application/vnd.ms-excel.richvaluerel+xml", "http://schemas.microsoft.com/office/2022/10/relationships/richValueRel", rel_list.into_bytes(), - ), - ] { + )); + } + for (name, content_type, relation, bytes) in parts { out.part(&format!("xl/richData/{name}"), Some(content_type), bytes); workbook_rels.add_uri(relation, &format!("richData/{name}")); } - out.part("xl/richData/_rels/richValueRel.xml.rels", None, image_rels.xml().into_bytes()); + if has_pictures { + out.part("xl/richData/_rels/richValueRel.xml.rels", None, image_rels.xml().into_bytes()); + } out.part( "xl/metadata.xml", Some("application/vnd.openxmlformats-officedocument.spreadsheetml.sheetMetadata+xml"), - metadata(count, out.dynamic_arrays).into_bytes(), + metadata(&cell_values, out.dynamic_arrays).into_bytes(), ); workbook_rels.add("sheetMetadata", "metadata.xml"); Ok(()) } -fn metadata(count: usize, dynamic: bool) -> String { +/// `rich_values[i]` is the rich value that value-metadata block `i` (the cell's `vm`, minus one) holds. +fn metadata(rich_values: &[usize], dynamic: bool) -> String { + let count = rich_values.len(); let type_id = if dynamic { 2 } else { 1 }; let mut xml = format!( "{XML_DECL}" @@ -349,11 +498,11 @@ fn metadata(count: usize, dynamic: bool) -> String { } xml.push_str(""); if dynamic { - xml.push_str(""); + xml.push_str(""); } let _ = write!(xml, ""); - for i in 0..count { - let _ = write!(xml, ""); + for rich_value in rich_values { + let _ = write!(xml, ""); } xml.push_str(""); if dynamic { diff --git a/crates/xlsx/src/sheet_read.rs b/crates/xlsx/src/sheet_read.rs --- a/crates/xlsx/src/sheet_read.rs +++ b/crates/xlsx/src/sheet_read.rs @@ -495,11 +495,13 @@ fn read_cell(cx: &mut Ctx<'_>, sheet: &mut Sheet, st: &mut RowState, c: &El, pos None => Value::Empty, }, }; - let picture = c.attr("vm").and_then(|vm| { + let block = c.attr("vm").and_then(|vm| vm.parse::().ok()).and_then(|n| n.checked_sub(1)); + let record = block.and_then(|n| cx.linked_records.get(n)).cloned().flatten(); + let picture = c.attr("vm").filter(|_| record.is_none()).and_then(|vm| { if vm == "0" { return None; } - let picture = vm.parse::().ok().and_then(|n| n.checked_sub(1)).and_then(|n| cx.cell_pictures.get(n)).cloned().flatten(); + let picture = block.and_then(|n| cx.cell_pictures.get(n)).cloned().flatten(); if picture.is_none() { cx.warn("some cells have unsupported or missing value metadata; kept their scalar fallback (cell pictures may be missing)"); } @@ -516,7 +518,16 @@ fn read_cell(cx: &mut Ctx<'_>, sheet: &mut Sheet, st: &mut RowState, c: &El, pos let dynamic = c.attr("cm").is_some(); let formula = c.child("f").and_then(|f| read_formula(cx, st, f, pos, dynamic)); let value = if formula.is_none() && in_dynamic(st, pos) { Value::Empty } else { value }; + // A linked record's cell holds an error for other apps; GridCraft shows its display text. + let value = match &record { + Some(record) if formula.is_none() && !record.display.is_empty() => Value::text(record.display.as_str()), + _ => value, + }; + let has_formula = formula.is_some(); sheet.cells.set(pos, Cell { value, formula: formula.map(Arc::new), style }); + if let Some(record) = record.filter(|_| !has_formula) { + sheet.linked_records.insert(pos, record); + } } fn read_formula(cx: &mut Ctx<'_>, st: &mut RowState, f: &El, pos: CellRef, dynamic: bool) -> Option { diff --git a/crates/xlsx/src/sheet_write.rs b/crates/xlsx/src/sheet_write.rs --- a/crates/xlsx/src/sheet_write.rs +++ b/crates/xlsx/src/sheet_write.rs @@ -296,6 +296,7 @@ pub fn write_sheet(wb: &Workbook, si: usize, selected: bool, out: &mut Out) -> ( // sheetData let mut keys: BTreeSet = sheet.cells.iter().map(|(c, _)| c).collect(); keys.extend(sheet.cell_pictures.keys().copied()); + keys.extend(sheet.linked_records.keys().copied()); keys.extend(extra.keys().copied()); keys.extend(overrides.keys().copied()); let mut rows: BTreeMap> = BTreeMap::new(); @@ -340,7 +341,9 @@ pub fn write_sheet(wb: &Workbook, si: usize, selected: bool, out: &mut Out) -> ( let f = cell?.formula.as_ref()?; (f.array.is_none() && 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); + let linked = sheet.linked_records.get(c).filter(|_| cell.is_some_and(|x| x.formula.is_none())); + let rich = sheet.cell_pictures.get(c).map(Rich::Picture).or(linked.map(Rich::Linked)); + write_cell(&mut s, *c, cell, rich, ov, ex, array.as_ref(), &sid, out); } s.push_str(""); } @@ -664,11 +667,17 @@ pub fn write_sheet(wb: &Workbook, si: usize, selected: bool, out: &mut Out) -> ( (s, rels) } +/// A cell whose content is a rich value, not a scalar. +enum Rich<'a> { + Picture(&'a std::sync::Arc), + Linked(&'a std::sync::Arc), +} + fn write_cell( s: &mut String, c: CellRef, cell: Option<&Cell>, - picture: Option<&std::sync::Arc>, + rich: Option>, ov: Option<&String>, extra: Option<&Value>, dynamic: Option<&RangeRef>, @@ -680,8 +689,11 @@ fn write_cell( if style != 0 { let _ = write!(s, " s=\"{style}\""); } - if let Some(picture) = picture { - let vm = out.cell_pictures.index(picture); + if let Some(rich) = rich { + let vm = match rich { + Rich::Picture(picture) => out.cell_pictures.index(picture), + Rich::Linked(record) => out.cell_pictures.index_linked(record), + }; let _ = write!(s, " t=\"e\" vm=\"{vm}\">#VALUE!"); return; } diff --git a/crates/xlsx/src/tests/linked_tests.rs b/crates/xlsx/src/tests/linked_tests.rs new file mode 100644 --- /dev/null +++ b/crates/xlsx/src/tests/linked_tests.rs @@ -0,0 +1,110 @@ +//! Linked data type records (Stocks, Currencies) in a hand-written package, not an Excel file. + +use gridcraft_core::{CellRef, Value}; + +use super::{CT, ROOT_RELS, make_zip}; +use crate::{read_xlsx, write_xlsx}; + +fn at(value: &str) -> CellRef { + CellRef::parse(value).unwrap() +} + +const NS: &str = "http://schemas.microsoft.com/office/spreadsheetml/2017/richdata"; + +/// Two records (a currency pair and a stock with an extra field) and `=A1.Price` in B1. +fn package() -> Vec { + let rich_rels = [ + ("rdRichValue", "rdrichvalue.xml", "http://schemas.microsoft.com/office/2017/06/relationships/rdRichValue"), + ("rdRichValueStructure", "rdrichvaluestructure.xml", "http://schemas.microsoft.com/office/2017/06/relationships/rdRichValueStructure"), + ] + .iter() + .enumerate() + .map(|(i, (_, file, ty))| format!(r#""#)) + .collect::(); + let workbook = r#""#; + let workbook_rels = format!( + r#"{rich_rels}"# + ); + let sheet = r#"#VALUE!_FV(A1,"Price")1.0842#VALUE!"#; + let metadata = format!( + r#""# + ); + let structures = format!( + r#""# + ); + // Rich value 0 is the currency record and 1 its wrapper; 2 the stock record and 3 its wrapper. + let values = format!( + r#"EUR/USD1.0842Bank00Contoso & Co25.51.5e92"# + ); + make_zip(&[ + ("[Content_Types].xml", CT.as_bytes()), + ("_rels/.rels", ROOT_RELS.as_bytes()), + ("xl/workbook.xml", workbook.as_bytes()), + ("xl/_rels/workbook.xml.rels", workbook_rels.as_bytes()), + ("xl/worksheets/sheet1.xml", sheet.as_bytes()), + ("xl/metadata.xml", metadata.as_bytes()), + ("xl/richData/rdrichvalue.xml", values.as_bytes()), + ("xl/richData/rdrichvaluestructure.xml", structures.as_bytes()), + ]) +} + +fn check(wb: &gridcraft_model::Workbook) { + let sheet = wb.sheet(0).unwrap(); + assert_eq!(sheet.value(at("A1")), Value::text("EUR/USD")); + assert_eq!(sheet.value(at("A2")), Value::text("Contoso & Co")); + let currency = sheet.linked_records.get(&at("A1")).unwrap(); + assert_eq!(currency.field("Price"), Some(Value::Number(1.0842))); + // Only number, text and boolean fields are kept. + assert_eq!(currency.fields.len(), 3); + assert_eq!(sheet.linked_records.get(&at("A2")).unwrap().field("market cap"), Some(Value::Number(1.5e9))); + let b1 = sheet.cells.get(at("B1")).unwrap(); + assert_eq!(b1.formula.as_ref().unwrap().text, "FIELDVALUE(A1,\"Price\")"); + assert_eq!(b1.value, Value::Number(1.0842)); +} + +#[test] +fn reads_linked_records_through_wrappers_and_keeps_the_field_formula() { + let (wb, report) = read_xlsx(&package()).unwrap(); + // The hand-written package has no styles part; nothing else may be reported. + assert!(report.warnings.iter().all(|w| w == "no styles part"), "{:?}", report.warnings); + check(&wb); +} + +#[test] +fn linked_records_survive_a_save_with_their_field_formula() { + let (wb, _) = read_xlsx(&package()).unwrap(); + let saved = write_xlsx(&wb).unwrap(); + let (again, report) = read_xlsx(&saved).unwrap(); + assert!(report.warnings.is_empty(), "{:?}", report.warnings); + check(&again); + let text = |name: &str| { + let mut zip = zip::ZipArchive::new(std::io::Cursor::new(&saved)).unwrap(); + let mut out = String::new(); + std::io::Read::read_to_string(&mut zip.by_name(name).unwrap(), &mut out).unwrap(); + out + }; + assert!(text("xl/worksheets/sheet1.xml").contains("_FV(A1,"Price")") || text("xl/worksheets/sheet1.xml").contains("_FV(A1,\"Price\")")); + assert!(text("xl/richData/rdrichvaluestructure.xml").contains("_linkedentity")); +} + +#[test] +fn a_damaged_linked_reference_keeps_the_display_fallback_without_a_record() { + let original = package(); + let mut archive = zip::ZipArchive::new(std::io::Cursor::new(&original)).unwrap(); + let mut parts: Vec<(String, Vec)> = vec![]; + for i in 0..archive.len() { + let mut file = archive.by_index(i).unwrap(); + let mut bytes = vec![]; + std::io::Read::read_to_end(&mut file, &mut bytes).unwrap(); + if file.name() == "xl/richData/rdrichvalue.xml" { + // The second wrapper points past the last rich value. + bytes = String::from_utf8(bytes).unwrap().replace("2", "99").into_bytes(); + } + parts.push((file.name().to_string(), bytes)); + } + let broken = make_zip(&parts.iter().map(|(n, b)| (n.as_str(), b.as_slice())).collect::>()); + let (wb, _) = read_xlsx(&broken).unwrap(); + let sheet = wb.sheet(0).unwrap(); + assert!(sheet.linked_records.contains_key(&at("A1"))); + assert!(!sheet.linked_records.contains_key(&at("A2"))); +} diff --git a/crates/xlsx/src/tests/mod.rs b/crates/xlsx/src/tests/mod.rs --- a/crates/xlsx/src/tests/mod.rs +++ b/crates/xlsx/src/tests/mod.rs @@ -10,6 +10,7 @@ mod csv_tests; mod encrypted_tests; mod fixtures; mod ink_roundtrip; +mod linked_tests; mod literal_category_roundtrip; mod locale_numfmt; mod malformed; diff --git a/docs/file-format-parity.md b/docs/file-format-parity.md --- a/docs/file-format-parity.md +++ b/docs/file-format-parity.md @@ -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-27 · **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 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** @@ -36,8 +36,12 @@ array evaluation. Dropping the mark turned such formulas into legacy ones, which with implicit intersection: `SUMPRODUCT(IFERROR(B1:B3/C1:C3,0))` recalculated to a different value in Excel. Moving the formula, inserting or deleting rows and columns, and renaming sheets or names keep the mark; retyping the formula drops it, and the writer then decides from the -formula's shape. Rich-value metadata other than cell pictures (data types, `#SPILL!` error -values) is still dropped. +formula's shape. Linked data type records (Stocks, Currencies…) are kept as saved: the cell shows +the record's display text, `=A1.Price` (stored as `_FV(A1,"Price")`) reads its number, text and +boolean fields, and save writes the record back. They are not refreshed from a provider, fields +of other kinds (icons, hyperlinks, format property bags) are dropped, and cut, copy, sort and +fill do not carry a record along yet. Other rich-value metadata (`#SPILL!` error values) is still +dropped. | Format | Excel | GridCraft read | GridCraft write | Fidelity / notes | Tests | |---|---|---|---|---|---| @@ -110,6 +114,7 @@ refresh. Counted under features (Get & Transform, ~10% ready), not here. | Date | Change | Summary | |---|---|---| +| 2026-10-27 | minor | XLSX linked data type records (Stocks, Currencies) are read, shown as their display text instead of `#VALUE!`, and written back; `=A1.Price` / `FIELDVALUE` returns the saved fields | | 2026-10-21 | minor | HTML (Save as Web Page) writes pictures (`data:` URI ``) 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 (ふりがな, `` with ``) 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 `` without runs is kept on its own, and when duplicate `` 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 (``, also Hebr, Jpan and the rest) are read and written back in file order after ``; they were dropped before. A hostile file is held to 64 entries of 256 characters |