diff --git a/crates/core/src/parse.rs b/crates/core/src/parse.rs --- a/crates/core/src/parse.rs +++ b/crates/core/src/parse.rs @@ -56,7 +56,7 @@ pub fn number_stored_as_text(s: &str) -> Option { let t = s.trim(); // The same parentheses typed entry reads, accounting forms included: `$(5)`, `(5)%`. match parenthesized(t) { - Some(inner) if inner.strip_prefix('$').map_or(inner.as_str(), str::trim_start).starts_with(['-', '+', '(']) => None, + Some(inner) if inner.strip_prefix(['$', '€']).map_or(inner.as_str(), str::trim_start).starts_with(['-', '+', '(']) => None, Some(inner) => parse_plain(&inner).map(|(n, _)| -n), None => parse_plain(t).map(|(n, _)| n), } @@ -66,7 +66,7 @@ fn parse_numeric(t: &str, sys: DateSystem) -> Option<(f64, Option<&'static str>) // Parenthesised negatives: (100) = -100, and the accounting forms `$(100)` and `(5)%`. if let Some(inner) = parenthesized(t) { // Excel reads no sign and no nested parentheses inside: `(-5)`, `($-5)`, `((5))`. - if inner.strip_prefix('$').map_or(inner.as_str(), str::trim_start).starts_with(['-', '+', '(']) { + if inner.strip_prefix(['$', '€']).map_or(inner.as_str(), str::trim_start).starts_with(['-', '+', '(']) { return None; } let r = parse_plain(&inner).or_else(|| parse_fraction(&inner).map(|n| (n, Some("# ?/?")))); @@ -112,6 +112,7 @@ fn parse_plain(t: &str) -> Option<(f64, Option<&'static str>)> { let mut s = t; let mut neg = false; let mut currency = false; + let mut euro = false; let mut percent = false; if let Some(r) = s.strip_prefix('-') { neg = true; @@ -127,12 +128,30 @@ fn parse_plain(t: &str) -> Option<(f64, Option<&'static str>)> { s = r2; } } + // The euro sign stands before or after the digits, with an optional space, and leaves the + // cell in General (observed in Excel with US settings: `65.00€` and `€ 65` are 65; `£5` is + // text). + if !currency && let Some(r) = s.strip_prefix('€') { + euro = true; + s = r.trim_start(); + if !neg && let Some(r2) = s.strip_prefix('-') { + neg = true; + s = r2; + } + } if let Some(r) = s.strip_suffix('%') { percent = true; s = r.trim_end(); } - // Excel reads `$5` and `5%` but not `$5%`. - if s.is_empty() || currency && percent { + if !currency + && !euro + && let Some(r) = s.strip_suffix('€') + { + euro = true; + s = r.trim_end(); + } + // Excel reads `$5` and `5%` but not `$5%`, and no euro sign with a percent sign. + if s.is_empty() || (currency || euro) && percent { return None; } let has_sep = s.contains(','); @@ -177,6 +196,8 @@ fn parse_plain(t: &str) -> Option<(f64, Option<&'static str>)> { Some(if digits.contains('.') { "0.00%" } else { "0%" }) } else if sci { Some("0.00E+00") + } else if euro { + None } else if has_sep { Some(if digits.contains('.') { "#,##0.00" } else { "#,##0" }) } else { @@ -613,6 +634,24 @@ mod tests { assert_eq!(parse_input("2/29/1904", DateSystem::D1904).value, Value::Number(59.0)); } + /// Typed into a General cell, Excel (US settings) reads a euro sign before or after the digits + /// and keeps the cell General; other currency signs and a decimal comma make text. + #[test] + fn euro_sign_is_read_as_a_number() { + for (text, n) in [("65.00€", 65.0), ("€65", 65.0), ("65 €", 65.0), ("€ 65", 65.0), ("-€5", -5.0), ("(€5)", -5.0), ("1,000€", 1000.0)] + { + let r = p(text); + assert_eq!(r.value, Value::Number(n), "{text}"); + assert_eq!(r.format, None, "{text}"); + } + // Excel keeps the scientific format a typed exponent gets, euro sign or not. + assert_eq!(p("1e3€").value, Value::Number(1000.0)); + assert_eq!(p("1e3€").format, Some("0.00E+00")); + for text in ["65,00€", "£5", "¥5", "65.00€%", "5 $", "12€3"] { + assert_eq!(p(text).value, Value::text(text), "{text}"); + } + } + #[test] fn numbers_stored_as_text() { for (text, n) in [ @@ -625,11 +664,46 @@ mod tests { ("(100)", -100.0), ("$(5)", -5.0), ("(5)%", -0.05), + ("65.00€", 65.0), + ("€65", 65.0), + ("65 €", 65.0), + ("€ 65", 65.0), + ("-€5", -5.0), + ("€-5", -5.0), + ("(€5)", -5.0), + ("1,000€", 1000.0), + ("1e3€", 1000.0), ] { assert_eq!(number_stored_as_text(text), Some(n), "{text}"); } // Dates, times, fractions, words and blanks are not flagged. - for text in ["3/4/2026", "10:30", "1 1/2", "abc", "12abc", "", " ", "TRUE", "#N/A", "((1))", "(-5)", "$(5)%", "(1/2)"] { + for text in [ + "3/4/2026", + "10:30", + "1 1/2", + "abc", + "12abc", + "", + " ", + "TRUE", + "#N/A", + "((1))", + "(-5)", + "$(5)%", + "(1/2)", + "65,00€", + "£5", + "5£", + "¥5", + "65.00€%", + "€5%", + "5 $", + "12€3", + "$5€", + "€$5", + "(€-5)", + "€", + ] { assert_eq!(number_stored_as_text(text), None, "{text}"); } } diff --git a/crates/engine/src/cmd.rs b/crates/engine/src/cmd.rs --- a/crates/engine/src/cmd.rs +++ b/crates/engine/src/cmd.rs @@ -363,6 +363,7 @@ pub(crate) fn commit(d: &mut DocState, f: impl FnOnce(&mut Ctx) -> Result) } if !structural { check_arrays(&d.wb, &wb)?; + forget_ignored_errors(&d.wb, &mut wb, &changed); } if changed.len() <= 200_000 { fit_rows.extend(changed.iter().map(|(s, c)| (*s, c.row))); @@ -391,6 +392,22 @@ pub(crate) fn commit(d: &mut DocState, f: impl FnOnce(&mut Ctx) -> Result) Ok(r) } +/// Editing a cell drops the error checks it had ignored (Excel does the same): a cell whose +/// content differs from before stops ignoring them. +fn forget_ignored_errors(old: &Workbook, new: &mut Workbook, changed: &[Key]) { + if new.sheets.iter().all(|s| s.ignored_errors.is_empty()) { + return; + } + for (si, c) in changed { + let before = old.sheet(*si).and_then(|s| s.cell(*c)); + let after = new.sheet(*si).and_then(|s| s.cell(*c)); + let same = same_content(before, after); + if !same && let Some(sh) = new.sheet_mut(*si) { + gridcraft_model::unignore_errors(&mut sh.ignored_errors, RangeRef::cell(*c), gridcraft_model::error_rule::ALL); + } + } +} + /// Excel's message for an edit of a locked cell on a protected sheet. pub(crate) const PROTECTED: &str = "The cell or chart you're trying to change is on a protected sheet."; diff --git a/crates/engine/src/cmd/edit/paste_special.rs b/crates/engine/src/cmd/edit/paste_special.rs --- a/crates/engine/src/cmd/edit/paste_special.rs +++ b/crates/engine/src/cmd/edit/paste_special.rs @@ -69,6 +69,7 @@ pub(super) fn paste_special(s: &mut Session, p: &Json) -> Result { source.comments.retain(|c, _| !src.contains(*c)); source.hyperlinks.retain(|c, _| !src.contains(*c)); source.merges.retain(|m| !src.contains_range(m)); + gridcraft_model::unignore_errors(&mut source.ignored_errors, src, gridcraft_model::error_rule::ALL); for (c, _) in &taken { cx.touch(clip.sheet, *c); } @@ -259,6 +260,14 @@ pub(super) fn paste_special(s: &mut Session, p: &Json) -> Result { cx.sheet_mut(dest_sheet)?.cell_pictures.insert(dest, picture); } cx.touch(dest_sheet, dest); + if cut_move { + // A structural commit skips the ignored-error cleanup, so each overwritten cell drops its rules here. + gridcraft_model::unignore_errors( + &mut cx.sheet_mut(dest_sheet)?.ignored_errors, + RangeRef::cell(dest), + gridcraft_model::error_rule::ALL, + ); + } } } } 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 @@ -79,6 +79,25 @@ pub fn specs() -> Vec { has_doc, convert_to_number ), + cmd!( + "formulas.ignoreError", + "Ignore Error", + [], + None, + "{range?, kinds?: [\"numberStoredAsText\"] (default; or evalError, twoDigitTextYear, formula, formulaRange, unlockedFormula, emptyCellReference, listDataValidation, calculatedColumn, all)} → {ranges}: the cells stop being flagged for those error-checking rules (saved as the sheet's ignoredErrors). Editing a cell's content forgets what it ignored.", + has_doc, + ignore_error + ), + cmd!( + "formulas.clearIgnoredErrors", + "Reset Ignored Errors", + [], + None, + "{range?, kinds? (default: all rules), all?: true = every sheet (File ▸ Options ▸ Formulas ▸ Reset Ignored Errors)} → {ranges}: the cells are checked again", + has_doc, + clear_ignored_errors + ), + cmd!(query "formulas.ignoredErrors", "Ignored Errors", [], None, "{} → [{sheet, ranges: [\"A1:B2\"], kinds: [\"numberStoredAsText\"]}]", has_doc, ignored_errors), cmd!(query "formulas.errorChecking", "Error Checking", ["Formulas", "Formula Auditing"], None, "{} → cells with errors", has_doc, error_checking), cmd!(query "formulas.evaluateFormula", "Evaluate Formula", ["Formulas", "Formula Auditing"], None, "{cell?, formula?} → steps", has_doc, evaluate_formula), cmd!(query "formulas.evaluate", "Evaluate", [], None, "{formula: \"=SUM(A1:A3)\", cell?} → value without changing the sheet; a formula that couldn't be entered in a cell is an error", has_doc, evaluate), @@ -440,6 +459,8 @@ fn rename_name_in_formulas(wb: &mut gridcraft_model::Workbook, old: &str, old_sc if let Some(cell) = sh.cells.get_mut(c) { cell.formula = Some(std::sync::Arc::new(nf)); } + // A structural commit skips the ignored-error cleanup, so the rewritten cell drops its rules here. + gridcraft_model::unignore_errors(&mut sh.ignored_errors, RangeRef::cell(c), gridcraft_model::error_rule::ALL); } } for n in wb.names.iter_mut() { @@ -770,6 +791,78 @@ fn convert_to_number(s: &mut Session, p: &Json) -> Result { }) } +/// `kinds`: one rule name, or a list of them (`all` stands for every rule). +fn kinds_param(p: &Json, default: u16) -> Result { + let names: Vec<&str> = match p.get("kinds") { + None | Some(Json::Null) => return Ok(default), + Some(Json::String(n)) => vec![n.as_str()], + Some(Json::Array(a)) => a.iter().map(|n| n.as_str().ok_or_else(|| bad("kinds", "expected rule names (text)"))).collect::>()?, + Some(_) => return Err(bad("kinds", "expected a rule name or a list of rule names")), + }; + let mut mask = 0; + for n in names { + mask |= if n.eq_ignore_ascii_case("all") { + gridcraft_model::error_rule::ALL + } else { + gridcraft_model::error_rule::parse(n).ok_or_else(|| bad("kinds", format!("unknown error-checking rule `{n}`")))? + }; + } + Ok(mask) +} + +/// Ignore Error: the range's cells stop being flagged for the chosen rules. +fn ignore_error(s: &mut Session, p: &Json) -> Result { + let sheet = target_sheet(s, p)?; + let ranges = target_ranges(s, p)?; + let kinds = kinds_param(p, gridcraft_model::error_rule::NUMBER_STORED_AS_TEXT)?; + edit(s, |cx| { + let sh = cx.sheet_mut(sheet)?; + for r in &ranges { + gridcraft_model::ignore_errors(&mut sh.ignored_errors, *r, kinds); + } + Ok(json!({"ranges": ranges.len()})) + }) +} + +/// Reset Ignored Errors: the range's cells (or every sheet's, with `all`) are checked again. +fn clear_ignored_errors(s: &mut Session, p: &Json) -> Result { + let all = bool_param(p, "all").unwrap_or(false); + let kinds = kinds_param(p, gridcraft_model::error_rule::ALL)?; + let (sheet, ranges) = if all { + (0, vec![RangeRef::new(CellRef::new(0, 0), CellRef::new(u32::MAX, u32::MAX))]) + } else { + (target_sheet(s, p)?, target_ranges(s, p)?) + }; + edit(s, |cx| { + if all { + for sh in cx.wb.sheets.iter_mut() { + let sh = std::sync::Arc::make_mut(sh); + for r in &ranges { + gridcraft_model::unignore_errors(&mut sh.ignored_errors, *r, kinds); + } + } + } else { + let sh = cx.sheet_mut(sheet)?; + for r in &ranges { + gridcraft_model::unignore_errors(&mut sh.ignored_errors, *r, kinds); + } + } + Ok(json!({"ranges": ranges.len()})) + }) +} + +fn ignored_errors(s: &mut Session, _: &Json) -> Result { + let d = s.doc()?; + let mut out = Vec::new(); + for sh in &d.wb.sheets { + for ie in &sh.ignored_errors { + let kinds: Vec<&str> = gridcraft_model::error_rule::NAMES.iter().filter(|(_, bit)| ie.kinds & bit != 0).map(|(n, _)| *n).collect(); + out.push(json!({"sheet": sh.name, "ranges": ie.ranges.iter().map(|r| r.a1()).collect::>(), "kinds": kinds})); + } + } + Ok(Json::Array(out)) +} + fn error_checking(s: &mut Session, _: &Json) -> Result { let d = s.doc()?; let mut out = Vec::new(); 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 @@ -171,6 +171,10 @@ fn shift_sheet_features(sh: &mut Sheet, axis: Axis, at: u32, count: u32, insert: for dv in sh.validations.iter_mut() { dv.ranges = dv.ranges.iter().filter_map(|r| map_range(*r)).collect(); } + for ie in sh.ignored_errors.iter_mut() { + ie.ranges = ie.ranges.iter().filter_map(|r| map_range(*r)).collect(); + } + sh.ignored_errors.retain(|ie| !ie.ranges.is_empty()); sh.validations.retain(|dv| !dv.ranges.is_empty()); for t in sh.tables.iter_mut() { if let Some(r) = map_range(t.range) { @@ -762,6 +766,10 @@ pub(super) fn shift_cell_features(sh: &mut Sheet, e: &Edit) { for dv in sh.validations.iter_mut() { dv.ranges = dv.ranges.iter().filter_map(|r| map_range(*r, e)).collect(); } + for ie in sh.ignored_errors.iter_mut() { + ie.ranges = ie.ranges.iter().filter_map(|r| map_range(*r, e)).collect(); + } + sh.ignored_errors.retain(|ie| !ie.ranges.is_empty()); sh.validations.retain(|dv| !dv.ranges.is_empty()); for t in sh.tables.iter_mut() { // Cells inserted or deleted across a table's full height add or remove table columns. diff --git a/crates/engine/src/tests.rs b/crates/engine/src/tests.rs --- a/crates/engine/src/tests.rs +++ b/crates/engine/src/tests.rs @@ -4708,6 +4708,77 @@ fn convert_to_number_turns_numbers_stored_as_text_into_numbers() { assert_eq!(value(&mut s, "A1"), json!("123")); } +/// Ignore Error, as observed in Excel: per cell and rule, kept as Excel's multi-area ranges, +/// forgotten when the cell's content is edited, moved by inserted rows, never carried by paste. +#[test] +fn ignore_error_is_per_cell_and_rule_and_forgotten_on_edit() { + let mut s = s(); + let ignored = |s: &mut Session| s.execute("formulas.ignoredErrors", json!({})).unwrap(); + for (cell, input) in [("A1", "'1"), ("A2", "'2"), ("A3", "'3"), ("A4", "'4")] { + s.execute("cell.set", json!({"cell": cell, "input": input})).unwrap(); + } + // The default rule is number stored as text; the selection is the default range. + s.execute("selection.set", json!({"range": "A1:A2"})).unwrap(); + assert_eq!(s.execute("formulas.ignoreError", json!({})).unwrap()["ranges"], 1); + s.execute("formulas.ignoreError", json!({"range": "A4", "kinds": ["evalError", "formula"]})).unwrap(); + assert_eq!( + ignored(&mut s), + json!([ + {"sheet": "Sheet1", "ranges": ["A1:A2"], "kinds": ["numberStoredAsText"]}, + {"sheet": "Sheet1", "ranges": ["A4"], "kinds": ["evalError", "formula"]}, + ]) + ); + assert!(s.execute("formulas.ignoreError", json!({"range": "A1", "kinds": ["nonsense"]})).is_err()); + assert!(s.execute("formulas.ignoreError", json!({"range": "A1", "kinds": ["evalError", 4]})).is_err()); + assert!(s.execute("formulas.ignoreError", json!({"range": "A1", "kinds": 4})).is_err()); + // Another rule on the same cells is its own record; the same rule on more cells joins one. + s.execute("formulas.ignoreError", json!({"range": "A3"})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["A1:A2", "A3"])); + // Undo takes the last ignore back. + s.execute("edit.undo", json!({})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["A1:A2"])); + // Retyping a cell forgets what it ignored; its neighbours keep theirs. + s.execute("cell.set", json!({"cell": "A1", "input": "'7"})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["A2"])); + // Formatting is not an edit. + s.execute("home.bold", json!({"range": "A2", "on": true})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["A2"])); + // Inserted rows move the record with its cells. + s.execute("home.insertRows", json!({"rows": "2:3"})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["A4"])); + assert_eq!(ignored(&mut s)[1]["ranges"], json!(["A6"])); + // Reset one rule in one cell, then everything on every sheet. + s.execute("formulas.clearIgnoredErrors", json!({"range": "A6", "kinds": "evalError"})).unwrap(); + assert_eq!(ignored(&mut s)[1]["kinds"], json!(["formula"])); + s.execute("formulas.clearIgnoredErrors", json!({"all": true})).unwrap(); + assert_eq!(ignored(&mut s), json!([])); +} + +/// Structural commits skip the generic cleanup, so a renamed name and a cut-paste forget ignored +/// rules themselves; a format-only edit of an empty cell keeps them. +#[test] +fn ignore_error_survives_format_only_edits_and_clears_on_structural_rewrites() { + let mut s = s(); + let ignored = |s: &mut Session| s.execute("formulas.ignoredErrors", json!({})).unwrap(); + s.execute("formulas.defineName", json!({"name": "Rate", "refersTo": "=0.2"})).unwrap(); + s.execute("cell.set", json!({"cell": "A1", "input": "=Rate"})).unwrap(); + s.execute("cell.set", json!({"cell": "B1", "input": "'5"})).unwrap(); + s.execute("cell.set", json!({"cell": "C1", "input": "'6"})).unwrap(); + s.execute("formulas.ignoreError", json!({"range": "A1:C1"})).unwrap(); + s.execute("home.bold", json!({"range": "D1", "on": true})).unwrap(); + s.execute("formulas.ignoreError", json!({"range": "D1"})).unwrap(); + s.execute("home.bold", json!({"range": "D1", "on": false})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["A1:C1", "D1"])); + // Renaming the name rewrites A1's formula: A1 stops ignoring. + s.execute("formulas.defineName", json!({"name": "Tax", "refersTo": "=0.2", "oldName": "Rate"})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["B1:C1", "D1"])); + // Cutting B1 onto C1 overwrites C1 and empties B1: both records go. + s.execute("edit.cut", json!({"range": "B1"})).unwrap(); + s.execute("selection.set", json!({"range": "C1"})).unwrap(); + s.execute("edit.paste", json!({})).unwrap(); + assert_eq!(ignored(&mut s)[0]["ranges"], json!(["D1"])); +} + #[test] fn saving_csv_keeps_leading_empty_columns_and_rows() { let wb = gridcraft_xlsx::read_csv(b",x\r\n\r\n,,y\r\n", &gridcraft_xlsx::CsvOptions::default()).unwrap(); diff --git a/crates/model/src/features.rs b/crates/model/src/features.rs --- a/crates/model/src/features.rs +++ b/crates/model/src/features.rs @@ -273,6 +273,115 @@ pub enum ErrorStyle { Information, } +// ---------------------------------------------------------------- ignored errors + +/// Error-checking rules (File ▸ Options ▸ Formulas) a user can ignore per cell, saved as the +/// attributes of `` (ISO/IEC 29500-1 §18.3.1.51). A set of rules is a bit mask. +pub mod error_rule { + pub const EVAL_ERROR: u16 = 1; + pub const TWO_DIGIT_TEXT_YEAR: u16 = 1 << 1; + pub const NUMBER_STORED_AS_TEXT: u16 = 1 << 2; + pub const FORMULA: u16 = 1 << 3; + pub const FORMULA_RANGE: u16 = 1 << 4; + pub const UNLOCKED_FORMULA: u16 = 1 << 5; + pub const EMPTY_CELL_REFERENCE: u16 = 1 << 6; + pub const LIST_DATA_VALIDATION: u16 = 1 << 7; + pub const CALCULATED_COLUMN: u16 = 1 << 8; + pub const ALL: u16 = (1 << 9) - 1; + /// The file's attribute name for each rule, in the schema's order. + pub const NAMES: [(&str, u16); 9] = [ + ("evalError", EVAL_ERROR), + ("twoDigitTextYear", TWO_DIGIT_TEXT_YEAR), + ("numberStoredAsText", NUMBER_STORED_AS_TEXT), + ("formula", FORMULA), + ("formulaRange", FORMULA_RANGE), + ("unlockedFormula", UNLOCKED_FORMULA), + ("emptyCellReference", EMPTY_CELL_REFERENCE), + ("listDataValidation", LIST_DATA_VALIDATION), + ("calculatedColumn", CALCULATED_COLUMN), + ]; + + /// The rule a name stands for (the file's attribute name, any case). + pub fn parse(name: &str) -> Option { + NAMES.iter().find(|(n, _)| n.eq_ignore_ascii_case(name)).map(|(_, bit)| *bit) + } +} + +/// Cells whose error-checking `kinds` (a mask of [`error_rule`] bits) the user chose to ignore. +#[derive(Clone, Debug, PartialEq, Eq, Serialize, Deserialize)] +pub struct IgnoredError { + pub ranges: Vec, + pub kinds: u16, +} + +/// What is left of `r` outside `cut`: up to four rectangles. +fn range_minus(r: RangeRef, cut: RangeRef) -> Vec { + let Some(i) = r.intersection(&cut) else { return vec![r] }; + let mut out = Vec::new(); + if r.start.row < i.start.row { + out.push(RangeRef::new(r.start, CellRef::new(i.start.row - 1, r.end.col))); + } + if i.end.row < r.end.row { + out.push(RangeRef::new(CellRef::new(i.end.row + 1, r.start.col), r.end)); + } + if r.start.col < i.start.col { + out.push(RangeRef::new(CellRef::new(i.start.row, r.start.col), CellRef::new(i.end.row, i.start.col - 1))); + } + if i.end.col < r.end.col { + out.push(RangeRef::new(CellRef::new(i.start.row, i.end.col + 1), CellRef::new(i.end.row, r.end.col))); + } + out +} + +/// Ignores `kinds` in `range`. +pub fn ignore_errors(list: &mut Vec, range: RangeRef, kinds: u16) { + let kinds = kinds & error_rule::ALL; + if kinds == 0 { + return; + } + if !list.iter().any(|e| e.kinds == kinds && e.ranges.iter().any(|r| r.contains_range(&range))) { + add_ranges(list, kinds, [range]); + } +} + +/// Adds `ranges` to the record for `kinds` without checking for duplicates, so reading a file +/// with thousands of ranges stays linear. +pub fn add_ranges(list: &mut Vec, kinds: u16, ranges: impl IntoIterator) { + let kinds = kinds & error_rule::ALL; + let mut ranges = ranges.into_iter().peekable(); + if kinds == 0 || ranges.peek().is_none() { + return; + } + match list.iter_mut().find(|e| e.kinds == kinds) { + Some(e) => e.ranges.extend(ranges), + None => list.push(IgnoredError { ranges: ranges.collect(), kinds }), + } +} + +/// Stops ignoring `kinds` in `range` (the cells outside it keep what they ignore). +pub fn unignore_errors(list: &mut Vec, range: RangeRef, kinds: u16) { + let kinds = kinds & error_rule::ALL; + let mut out: Vec = Vec::new(); + for e in std::mem::take(list) { + if e.kinds & kinds == 0 || !e.ranges.iter().any(|r| r.intersects(&range)) { + out.push(e); + continue; + } + let kept = e.kinds & !kinds; + for r in &e.ranges { + // Outside `range` the rules stay as they were; inside, only those not being cleared. + add_ranges(&mut out, e.kinds, range_minus(*r, range)); + add_ranges(&mut out, kept, r.intersection(&range)); + } + } + *list = out; +} + +/// Whether `kind` (one [`error_rule`] bit) is ignored in `cell`. +pub fn is_error_ignored(list: &[IgnoredError], cell: CellRef, kind: u16) -> bool { + list.iter().any(|e| e.kinds & kind != 0 && e.ranges.iter().any(|r| r.contains(cell))) +} + #[derive(Clone, Debug, PartialEq, Serialize, Deserialize)] pub struct Validation { pub ranges: Vec, @@ -737,3 +846,52 @@ mod trendline_forecast_tests { assert_eq!(Trendline::clamp_forecast(1e300), Trendline::MAX_FORECAST); } } + +#[cfg(test)] +mod ignored_error_tests { + use super::*; + + fn range(r0: u32, c0: u32, r1: u32, c1: u32) -> RangeRef { + RangeRef::new(CellRef::new(r0, c0), CellRef::new(r1, c1)) + } + + fn ignored(list: &[IgnoredError], r: u32, c: u32, kind: u16) -> bool { + is_error_ignored(list, CellRef::new(r, c), kind) + } + + #[test] + fn clearing_the_middle_of_a_block_leaves_the_ring_around_it() { + let mut list = Vec::new(); + ignore_errors(&mut list, range(0, 0, 4, 4), error_rule::NUMBER_STORED_AS_TEXT | error_rule::EVAL_ERROR); + unignore_errors(&mut list, range(1, 1, 3, 3), error_rule::NUMBER_STORED_AS_TEXT); + for r in 0..5 { + for c in 0..5 { + let inside = (1..4).contains(&r) && (1..4).contains(&c); + assert_eq!(ignored(&list, r, c, error_rule::NUMBER_STORED_AS_TEXT), !inside, "({r},{c})"); + assert!(ignored(&list, r, c, error_rule::EVAL_ERROR), "({r},{c}) keeps the other rule"); + } + } + } + + #[test] + fn clearing_a_range_that_misses_or_covers_a_record() { + let mut list = Vec::new(); + ignore_errors(&mut list, range(2, 2, 3, 3), error_rule::FORMULA); + unignore_errors(&mut list, range(0, 0, 1, 1), error_rule::ALL); + assert_eq!(list.len(), 1); + unignore_errors(&mut list, range(0, 0, 9, 9), error_rule::FORMULA); + assert!(list.is_empty()); + // No rule, or an unknown bit, ignores nothing. + ignore_errors(&mut list, range(0, 0, 0, 0), 0); + ignore_errors(&mut list, range(0, 0, 0, 0), 1 << 12); + assert!(list.is_empty()); + } + + #[test] + fn rule_names_are_the_files_attribute_names() { + assert_eq!(error_rule::parse("numberStoredAsText"), Some(error_rule::NUMBER_STORED_AS_TEXT)); + assert_eq!(error_rule::parse("EVALERROR"), Some(error_rule::EVAL_ERROR)); + assert_eq!(error_rule::parse("bogus"), None); + assert_eq!(error_rule::NAMES.iter().fold(0, |m, (_, bit)| m | bit), error_rule::ALL); + } +} 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 @@ -118,6 +118,9 @@ pub struct Sheet { /// Allow Edit Ranges (meaningful while the sheet is protected). #[serde(default, skip_serializing_if = "Vec::is_empty")] pub edit_ranges: Vec, + /// Error-checking rules the user ignored for cells (Ignore Error), saved as `ignoredErrors`. + #[serde(default, skip_serializing_if = "Vec::is_empty")] + pub ignored_errors: Vec, pub print: PrintSettings, /// Last selection and scroll position, saved with the file. pub view_active: CellRef, @@ -185,6 +188,7 @@ impl Sheet { pivots: vec![], protection: None, edit_ranges: vec![], + ignored_errors: vec![], print: PrintSettings::default(), view_active: CellRef::default(), view_top_left: CellRef::default(), diff --git a/crates/ui-egui/src/grid.rs b/crates/ui-egui/src/grid.rs --- a/crates/ui-egui/src/grid.rs +++ b/crates/ui-egui/src/grid.rs @@ -275,5 +275,12 @@ pub(crate) fn heading_font_size(zoom: f32) -> f32 { (11.5 * (zoom / DISPLAY_SCALE)).max(8.5) } +/// Whether background error checking flags the cell: a number stored as text the user has not +/// chosen to ignore. +fn is_number_stored_as_text(sh: &Sheet, c: CellRef) -> bool { + sh.cell(c).and_then(gridcraft_engine::cmd::formulas::number_stored_as_text).is_some() + && !gridcraft_engine::model::is_error_ignored(&sh.ignored_errors, c, gridcraft_engine::model::error_rule::NUMBER_STORED_AS_TEXT) +} + #[cfg(test)] mod tests; diff --git a/crates/ui-egui/src/grid/menus.rs b/crates/ui-egui/src/grid/menus.rs --- a/crates/ui-egui/src/grid/menus.rs +++ b/crates/ui-egui/src/grid/menus.rs @@ -418,7 +418,7 @@ pub(super) fn error_check_tag(app: &mut SheetApp, ui: &mut egui::Ui, geo: &Geo, return; } let Some(active) = app.session.active().map(|d| d.selection.active) else { return }; - if sh.cell(active).and_then(gridcraft_engine::cmd::formulas::number_stored_as_text).is_none() { + if !is_number_stored_as_text(sh, active) { return; } let cell = geo.cell_rect(sh, active); @@ -452,6 +452,10 @@ pub(super) fn error_check_tag(app: &mut SheetApp, ui: &mut egui::Ui, geo: &Geo, app.run_or_alert("formulas.convertToNumber", json!({})); ui.close(); } + if ui.button(lang.tr("Ignore Error")).clicked() { + app.run_or_alert("formulas.ignoreError", json!({})); + ui.close(); + } }); } diff --git a/crates/ui-egui/src/grid/quadrant.rs b/crates/ui-egui/src/grid/quadrant.rs --- a/crates/ui-egui/src/grid/quadrant.rs +++ b/crates/ui-egui/src/grid/quadrant.rs @@ -427,7 +427,7 @@ pub(crate) fn paint_quadrant( )); } // Background error checking: a number stored as text gets Excel's green corner (#1994). - if !sh.show_formulas && sh.cell(*c).and_then(gridcraft_engine::cmd::formulas::number_stored_as_text).is_some() { + if !sh.show_formulas && is_number_stored_as_text(sh, *c) { let s = 6.0 * z.max(0.7); p.add(egui::Shape::convex_polygon( vec![rect.left_top(), pos2(rect.left() + s, rect.top()), pos2(rect.left(), rect.top() + s)], diff --git a/crates/ui-egui/src/i18n/de.rs b/crates/ui-egui/src/i18n/de.rs --- a/crates/ui-egui/src/i18n/de.rs +++ b/crates/ui-egui/src/i18n/de.rs @@ -63,6 +63,7 @@ pub(super) fn translate(text: &str) -> Option<&'static str> { "Format Chart Area…" => "Diagrammbereich formatieren…", "Number Stored as Text" => "Als Text gespeicherte Zahl", "Convert to Number" => "In eine Zahl umwandeln", + "Ignore Error" => "Fehler ignorieren", "Delete Cells…" => "Zellen löschen…", "Insert Sheet Rows" => "Blattzeilen einfügen", "Insert Sheet Columns" => "Blattspalten einfügen", diff --git a/crates/ui-egui/src/i18n/pt.rs b/crates/ui-egui/src/i18n/pt.rs --- a/crates/ui-egui/src/i18n/pt.rs +++ b/crates/ui-egui/src/i18n/pt.rs @@ -443,6 +443,7 @@ pub(super) fn translate(text: &str) -> Option<&'static str> { "Format Chart Area…" => "Formatar Área do Gráfico…", "Number Stored as Text" => "Número Armazenado como Texto", "Convert to Number" => "Converter em Número", + "Ignore Error" => "Ignorar Erro", "Error checking options" => "Opções de verificação de erros", "Paste formulas that refer to the copied cells" => "Colar fórmulas que se referem às células copiadas", "Row Height" => "Altura da Linha", 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 @@ -9,7 +9,7 @@ use gridcraft_formula::Expr; use gridcraft_model::{ AutoFilter, Cell, CfOperator, CfRule, CfValueKind, Comment, CondFormat, EditRange, ErrorStyle, FilterCriterion, Formula, Hyperlink, LineInfo, NoteBox, NoteCorner, Orientation, PasswordHash, Sheet, SheetProtection, Sparkline, SparklineKind, Table, TableColumn, TotalsFn, Validation, - ValidationKind, XmlColumnPr, + ValidationKind, XmlColumnPr, add_ranges, error_rule, }; use crate::IoError; @@ -162,6 +162,13 @@ pub fn read_sheet(cx: &mut Ctx<'_>, part: &str, name: &str) -> Result read_cf(cx, e, &mut sheet), + "ignoredErrors" => { + for ie in e.kids("ignoredError") { + let kinds = error_rule::NAMES.iter().filter(|(n, _)| ie.flag(n, false)).fold(0, |m, (_, bit)| m | bit); + // An element with no rule on (Excel leaves one behind after Ignore is turned off) ignores nothing. + add_ranges(&mut sheet.ignored_errors, kinds, parse_sqref(ie.attr("sqref"))); + } + } "dataValidations" => { for v in e.kids("dataValidation") { if let Some(v) = read_validation(v) { 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 @@ -5,8 +5,8 @@ use std::fmt::Write as _; use gridcraft_core::{CellError, CellRef, MAX_COLS, MAX_ROWS, RangeRef, Value}; use gridcraft_model::{ - Cell, CfOperator, CfRule, CfValueKind, Color, CondFormat, ErrorStyle, FilterCriterion, LineInfo, NoteBox, NoteCorner, Orientation, Sheet, - SparklineKind, Style, Table, TotalsFn, Validation, ValidationKind, Workbook, default_icon_thresholds, icon_count, + Cell, CfOperator, CfRule, CfValueKind, Color, CondFormat, ErrorStyle, FilterCriterion, IgnoredError, LineInfo, NoteBox, NoteCorner, Orientation, + Sheet, SparklineKind, Style, Table, TotalsFn, Validation, ValidationKind, Workbook, default_icon_thresholds, error_rule, icon_count, }; use crate::drawing::{Obj, image_content_type, image_ext}; @@ -505,6 +505,21 @@ pub fn write_sheet(wb: &Workbook, si: usize, selected: bool, out: &mut Out) -> ( } } + let ignored: Vec<&IgnoredError> = sheet.ignored_errors.iter().filter(|e| e.kinds & error_rule::ALL != 0 && !e.ranges.is_empty()).collect(); + if !ignored.is_empty() { + s.push_str(""); + for e in ignored { + let _ = write!(s, ""); + } + s.push_str(""); + } + // Drawing: pictures, charts, shapes. if !sheet.images.is_empty() || !sheet.charts.is_empty() || !sheet.shapes.is_empty() { out.drawings += 1; 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 @@ -9,6 +9,7 @@ mod chart_ref_offsets; mod csv_tests; mod encrypted_tests; mod fixtures; +mod ignored_errors; mod ink_roundtrip; mod literal_category_roundtrip; mod locale_numfmt; diff --git a/crates/xlsx/src/tests/ignored_errors.rs b/crates/xlsx/src/tests/ignored_errors.rs new file mode 100644 --- /dev/null +++ b/crates/xlsx/src/tests/ignored_errors.rs @@ -0,0 +1,93 @@ +//! Ignore Error: the sheet's `ignoredErrors` are read and written back. The element bodies below +//! are what Excel saved after `Range.Errors(n).Ignore = True` on synthetic workbooks. + +use std::io::Read; + +use gridcraft_core::{CellRef, RangeRef}; +use gridcraft_model::{error_rule, is_error_ignored}; + +use super::minimal; +use crate::{read_xlsx, write_xlsx}; + +/// One record per rule, as Excel wrote it for B1 (evalError), C1 (text date), E3 (formula), A6 +/// (number as text), D1 (formulaRange), E1 (unlockedFormula), F10 (emptyCellReference) and G1 +/// (listDataValidation). +const EXCEL_EACH_RULE: &str = r#""#; + +/// Cells ignored in separate calls come back as one multi-area sqref; turning Ignore off on A1 +/// leaves a record with no rule on it. +const EXCEL_MERGED: &str = + r#""#; + +fn cell(a1: &str) -> CellRef { + CellRef::parse(a1).unwrap() +} + +fn written_sheet(bytes: &[u8]) -> String { + let mut z = zip::ZipArchive::new(std::io::Cursor::new(bytes)).unwrap(); + let mut s = String::new(); + z.by_name("xl/worksheets/sheet1.xml").unwrap().read_to_string(&mut s).unwrap(); + s +} + +#[test] +fn each_rule_excel_saved_is_read_and_written_back() { + let (wb, _) = read_xlsx(&minimal(EXCEL_EACH_RULE, &[], "", "")).unwrap(); + let sh = &wb.sheets[0]; + for (a1, rule) in [ + ("B1", error_rule::EVAL_ERROR), + ("C1", error_rule::TWO_DIGIT_TEXT_YEAR), + ("E3", error_rule::FORMULA), + ("A6", error_rule::NUMBER_STORED_AS_TEXT), + ("D1", error_rule::FORMULA_RANGE), + ("E1", error_rule::UNLOCKED_FORMULA), + ("F10", error_rule::EMPTY_CELL_REFERENCE), + ("G1", error_rule::LIST_DATA_VALIDATION), + ] { + assert!(is_error_ignored(&sh.ignored_errors, cell(a1), rule), "{a1}"); + // Only that rule, and only that cell. + assert!(!is_error_ignored(&sh.ignored_errors, cell(a1), error_rule::ALL & !rule), "{a1}"); + } + assert!(!is_error_ignored(&sh.ignored_errors, cell("B2"), error_rule::ALL)); + + let bytes = write_xlsx(&wb).unwrap(); + let xml = written_sheet(&bytes); + for expected in [ + r#""#, + r#""#, + r#""#, + r#""#, + r#""#, + r#""#, + r#""#, + r#""#, + ] { + assert!(xml.contains(expected), "{expected} in {xml}"); + } + let (back, _) = read_xlsx(&bytes).unwrap(); + assert_eq!(back.sheets[0].ignored_errors, wb.sheets[0].ignored_errors); +} + +#[test] +fn a_multi_area_sqref_is_kept_and_a_record_with_no_rule_ignores_nothing() { + let (wb, _) = read_xlsx(&minimal(EXCEL_MERGED, &[], "", "")).unwrap(); + let sh = &wb.sheets[0]; + let rule = error_rule::NUMBER_STORED_AS_TEXT; + for a1 in ["A2", "A3", "C1", "D1"] { + assert!(is_error_ignored(&sh.ignored_errors, cell(a1), rule), "{a1}"); + } + for a1 in ["A1", "A4", "C2", "E1"] { + assert!(!is_error_ignored(&sh.ignored_errors, cell(a1), rule), "{a1}"); + } + assert_eq!(sh.ignored_errors.len(), 1); + assert_eq!(sh.ignored_errors[0].ranges, [RangeRef::parse("A2:A3").unwrap(), RangeRef::parse("C1:D1").unwrap()]); + let xml = written_sheet(&write_xlsx(&wb).unwrap()); + assert!(xml.contains(r#""#), "{xml}"); + assert!(!xml.contains(r#"", &[], "", "")).unwrap(); + assert!(!written_sheet(&write_xlsx(&wb).unwrap()).contains("ignoredErrors")); +} diff --git a/docs/ui-parity.md b/docs/ui-parity.md --- a/docs/ui-parity.md +++ b/docs/ui-parity.md @@ -1,6 +1,6 @@ # UI parity -> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 +> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-26 · **Change:** minor (Ignore Error, Reset Ignored Errors and the file's ignoredErrors; a euro sign is read as part of a number) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Interaction, controls and feel, against Excel. Part of [target-app-parity.md](target-app-parity.md). **UI/UX fidelity: ~55% ready, 50–80 h** (estimated). Ribbon command coverage is measured @@ -187,6 +187,7 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | Ignore Error (#1994): the warning button's menu gets Ignore Error, which stops flagging the cell (`formulas.ignoreError {range?, kinds?}`; `formulas.clearIgnoredErrors` is Reset Ignored Errors, `formulas.ignoredErrors` lists them). The sheet's `ignoredErrors` (all nine rules) are read and written; editing a cell forgets what it ignored and inserted or deleted rows and columns move it, as observed in Excel. A euro sign before or after the digits (`65.00€`, `€ 65`) is read as a number, as Excel with US settings does. Not done: the other error-check rules are not detected, so only Number Stored as Text and (through the command) the saved flags exist; Excel's per-rule on/off options and Error Checking Options dialog are missing; Convert to Number does not skip ignored cells (not observed) | | 2026-10-25 | minor | AutoSum (Alt+=, Home › AutoSum) over a selected row puts its total to the right of the row, or in its last cell when that is empty, as in Excel; it wrote a one-cell total under each cell. A selection's empty last column takes each row's total and its empty last row each column's, with the block's total in the corner when both are empty | | 2026-10-25 | minor | Paste Name lays out only the names in view, so a workbook with thousands of names opens it at once; the arrow keys still scroll the chosen name into view. Go To matches a `Sheet2!Name` reference and hides a workbook name behind the active sheet's own name in any case and script (`Größe` = `GRÖSSE`), as formulas do. The two Page Layout view rows are one; the 0.7 in side margin used when Align with Page Margins is off is marked unverified | | 2026-10-24 | minor | Go To lists the named ranges on every sheet, as Excel's does ("The Go to popup window shows named ranges on every worksheet in your workbook"): a name scoped to another sheet appears as `Sheet2!Name`, and hidden names, constants and formulas are left out. It keeps the references entered (newest first, ten at most), and a double-click on an entry goes there. The Reference box and `edit.goTo` take several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30,H21:H29`) and select them all; `edit.goTo {list: true}` returns the list |