ravenrs_sheet/base/
value.rs

1//! Cell values and Excel's coercion and comparison rules.
2
3use std::cmp::Ordering;
4use std::fmt;
5use std::sync::Arc;
6
7use serde::{Deserialize, Serialize};
8
9/// Excel's error values.
10#[derive(Clone, Copy, Debug, PartialEq, Eq, Hash, Serialize, Deserialize)]
11pub enum CellError {
12    Null,
13    Div0,
14    Value,
15    Ref,
16    Name,
17    Num,
18    NA,
19    GettingData,
20    Spill,
21    Calc,
22    /// Circular reference that could not be resolved (shown as 0 by Excel; we keep it explicit).
23    Circ,
24    /// A Python cell's code raised an error, or Python stopped.
25    Python,
26    /// A Python cell is waiting to run.
27    Busy,
28    /// A Python cell ran longer than the time limit.
29    Timeout,
30    /// Python can't run here: the workbook's content isn't enabled, or this build has no Python.
31    Blocked,
32}
33
34impl CellError {
35    pub const ALL: [CellError; 14] = [
36        CellError::Null,
37        CellError::Div0,
38        CellError::Value,
39        CellError::Ref,
40        CellError::Name,
41        CellError::Num,
42        CellError::NA,
43        CellError::GettingData,
44        CellError::Spill,
45        CellError::Calc,
46        CellError::Python,
47        CellError::Busy,
48        CellError::Timeout,
49        CellError::Blocked,
50    ];
51    pub fn as_str(&self) -> &'static str {
52        match self {
53            CellError::Null => "#NULL!",
54            CellError::Div0 => "#DIV/0!",
55            CellError::Value => "#VALUE!",
56            CellError::Ref => "#REF!",
57            CellError::Name => "#NAME?",
58            CellError::Num => "#NUM!",
59            CellError::NA => "#N/A",
60            CellError::GettingData => "#GETTING_DATA",
61            CellError::Spill => "#SPILL!",
62            CellError::Calc => "#CALC!",
63            CellError::Circ => "#CIRC!",
64            CellError::Python => "#PYTHON!",
65            CellError::Busy => "#BUSY!",
66            CellError::Timeout => "#TIMEOUT!",
67            CellError::Blocked => "#BLOCKED!",
68        }
69    }
70    /// `ERROR.TYPE` number. Microsoft documents 1 to 8; 9 (#SPILL!), 11 (#BLOCKED!) and 14 (#CALC!)
71    /// are the numbers Excel returns. Microsoft documents no number for #PYTHON!, #BUSY! or
72    /// #TIMEOUT!: 19, 20 and 21 are GridCraft's own choice, after the numbers Excel is known to use,
73    /// and may change if Excel's numbers become known.
74    pub fn code(&self) -> u32 {
75        match self {
76            CellError::Null => 1,
77            CellError::Div0 => 2,
78            CellError::Value => 3,
79            CellError::Ref => 4,
80            CellError::Name => 5,
81            CellError::Num => 6,
82            CellError::NA => 7,
83            CellError::GettingData => 8,
84            CellError::Spill => 9,
85            CellError::Calc => 14,
86            CellError::Circ => 15,
87            CellError::Blocked => 11,
88            CellError::Python => 19,
89            CellError::Busy => 20,
90            CellError::Timeout => 21,
91        }
92    }
93    pub fn parse(s: &str) -> Option<CellError> {
94        let u = s.to_ascii_uppercase();
95        CellError::ALL.iter().chain([CellError::Circ].iter()).find(|e| e.as_str() == u).copied()
96    }
97}
98
99impl fmt::Display for CellError {
100    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
101        f.write_str(self.as_str())
102    }
103}
104
105/// A 2-D array of values (row-major). Always at least 1×1.
106#[derive(Clone, Debug, PartialEq, Serialize, Deserialize)]
107pub struct Array {
108    pub rows: usize,
109    pub cols: usize,
110    pub data: Vec<Value>,
111}
112
113impl Array {
114    pub fn new(rows: usize, cols: usize, data: Vec<Value>) -> Option<Array> {
115        if rows == 0 || cols == 0 || rows.checked_mul(cols)? != data.len() {
116            return None;
117        }
118        Some(Array { rows, cols, data })
119    }
120    pub fn filled(rows: usize, cols: usize, v: Value) -> Array {
121        let (rows, cols) = (rows.max(1), cols.max(1));
122        Array { rows, cols, data: vec![v; rows.saturating_mul(cols)] }
123    }
124    pub fn scalar(v: Value) -> Array {
125        Array { rows: 1, cols: 1, data: vec![v] }
126    }
127    pub fn column(v: Vec<Value>) -> Array {
128        if v.is_empty() {
129            return Array::scalar(Value::Error(CellError::Calc));
130        }
131        Array { rows: v.len(), cols: 1, data: v }
132    }
133    pub fn row(v: Vec<Value>) -> Array {
134        if v.is_empty() {
135            return Array::scalar(Value::Error(CellError::Calc));
136        }
137        Array { rows: 1, cols: v.len(), data: v }
138    }
139    pub fn get(&self, r: usize, c: usize) -> Option<&Value> {
140        if r >= self.rows || c >= self.cols {
141            return None;
142        }
143        self.data.get(r * self.cols + c)
144    }
145    /// Excel's broadcasting read: a 1-row or 1-column array repeats along that axis; outside the
146    /// array is `#N/A`.
147    pub fn get_broadcast(&self, r: usize, c: usize) -> Value {
148        let rr = if self.rows == 1 { 0 } else { r };
149        let cc = if self.cols == 1 { 0 } else { c };
150        self.get(rr, cc).cloned().unwrap_or(Value::Error(CellError::NA))
151    }
152    pub fn transpose(&self) -> Array {
153        let mut data = Vec::with_capacity(self.data.len());
154        for c in 0..self.cols {
155            for r in 0..self.rows {
156                data.push(self.get(r, c).cloned().unwrap_or(Value::Empty));
157            }
158        }
159        Array { rows: self.cols, cols: self.rows, data }
160    }
161    pub fn iter(&self) -> impl Iterator<Item = &Value> {
162        self.data.iter()
163    }
164}
165
166/// A cell value or an intermediate formula result.
167#[derive(Clone, Debug, PartialEq, Serialize, Deserialize, Default)]
168#[serde(tag = "t", content = "v")]
169pub enum Value {
170    #[default]
171    Empty,
172    Number(f64),
173    Text(Arc<str>),
174    Bool(bool),
175    Error(CellError),
176    Array(Arc<Array>),
177}
178
179impl From<f64> for Value {
180    fn from(v: f64) -> Self {
181        Value::number(v)
182    }
183}
184impl From<bool> for Value {
185    fn from(v: bool) -> Self {
186        Value::Bool(v)
187    }
188}
189impl From<&str> for Value {
190    fn from(v: &str) -> Self {
191        Value::Text(Arc::from(v))
192    }
193}
194impl From<String> for Value {
195    fn from(v: String) -> Self {
196        Value::Text(Arc::from(v))
197    }
198}
199impl From<CellError> for Value {
200    fn from(v: CellError) -> Self {
201        Value::Error(v)
202    }
203}
204impl From<Array> for Value {
205    fn from(a: Array) -> Self {
206        Value::Array(Arc::new(a))
207    }
208}
209
210impl Value {
211    /// A number, or `#NUM!` when it is NaN or infinite (Excel never stores those).
212    pub fn number(v: f64) -> Value {
213        if v.is_finite() { Value::Number(if v == 0.0 { 0.0 } else { v }) } else { Value::Error(CellError::Num) }
214    }
215    pub fn text(s: impl Into<Arc<str>>) -> Value {
216        Value::Text(s.into())
217    }
218    pub fn is_empty(&self) -> bool {
219        matches!(self, Value::Empty)
220    }
221    pub fn is_error(&self) -> bool {
222        matches!(self, Value::Error(_))
223    }
224    pub fn is_number(&self) -> bool {
225        matches!(self, Value::Number(_))
226    }
227    pub fn is_text(&self) -> bool {
228        matches!(self, Value::Text(_))
229    }
230    pub fn as_error(&self) -> Option<CellError> {
231        if let Value::Error(e) = self { Some(*e) } else { None }
232    }
233    pub fn as_f64(&self) -> Option<f64> {
234        if let Value::Number(n) = self { Some(*n) } else { None }
235    }
236    pub fn as_text(&self) -> Option<&str> {
237        if let Value::Text(t) = self { Some(t) } else { None }
238    }
239    /// Top-left element of an array, the value itself otherwise.
240    pub fn scalar(&self) -> Value {
241        match self {
242            Value::Array(a) => a.data.first().cloned().unwrap_or(Value::Empty),
243            v => v.clone(),
244        }
245    }
246
247    /// Coercion to a number as arithmetic does it: empty = 0, TRUE = 1, numeric text parses
248    /// (including dates, times, percentages and currency), other text = `#VALUE!`.
249    pub fn to_number(&self) -> Result<f64, CellError> {
250        self.to_number_in(crate::base::DateSystem::D1900)
251    }
252    /// [`Value::to_number`] with date text read in the given workbook date system.
253    pub fn to_number_in(&self, sys: crate::base::DateSystem) -> Result<f64, CellError> {
254        match self {
255            Value::Empty => Ok(0.0),
256            Value::Number(n) => Ok(*n),
257            Value::Bool(b) => Ok(if *b { 1.0 } else { 0.0 }),
258            Value::Text(t) => crate::base::parse::parse_number_text_in(t, sys).ok_or(CellError::Value),
259            Value::Error(e) => Err(*e),
260            Value::Array(a) => a.data.first().map(|v| v.to_number_in(sys)).unwrap_or(Ok(0.0)),
261        }
262    }
263    /// Coercion to text as `&` does it.
264    pub fn to_text(&self) -> Result<String, CellError> {
265        match self {
266            Value::Empty => Ok(String::new()),
267            Value::Number(n) => Ok(number_to_text(*n)),
268            Value::Bool(b) => Ok(if *b { "TRUE".into() } else { "FALSE".into() }),
269            Value::Text(t) => Ok(t.to_string()),
270            Value::Error(e) => Err(*e),
271            Value::Array(a) => a.data.first().map(Value::to_text).unwrap_or(Ok(String::new())),
272        }
273    }
274    /// Coercion to a boolean as `IF` does it.
275    pub fn to_bool(&self) -> Result<bool, CellError> {
276        match self {
277            Value::Empty => Ok(false),
278            Value::Number(n) => Ok(*n != 0.0),
279            Value::Bool(b) => Ok(*b),
280            Value::Text(t) => {
281                if t.eq_ignore_ascii_case("TRUE") {
282                    Ok(true)
283                } else if t.eq_ignore_ascii_case("FALSE") {
284                    Ok(false)
285                } else {
286                    Err(CellError::Value)
287                }
288            }
289            Value::Error(e) => Err(*e),
290            Value::Array(a) => a.data.first().map(Value::to_bool).unwrap_or(Ok(false)),
291        }
292    }
293    /// Display text with General formatting (for logs, CSV and tests).
294    pub fn display(&self) -> String {
295        match self {
296            Value::Error(e) => e.as_str().to_string(),
297            v => v.to_text().unwrap_or_default(),
298        }
299    }
300    /// Type rank for comparison and sorting: numbers < text < booleans < errors; empty sorts last.
301    fn rank(&self) -> u8 {
302        match self {
303            Value::Number(_) => 0,
304            Value::Text(_) => 1,
305            Value::Bool(_) => 2,
306            Value::Error(_) => 3,
307            Value::Empty => 4,
308            Value::Array(_) => 5,
309        }
310    }
311}
312
313/// Excel's comparison for `=`, `<`… operators. Text compares case-insensitively; empty acts as
314/// 0, "" or FALSE depending on the other side. Errors are handled by the caller.
315pub fn compare(a: &Value, b: &Value) -> Ordering {
316    match (a, b) {
317        (Value::Empty, Value::Empty) => Ordering::Equal,
318        (Value::Empty, Value::Number(n)) => compare_numbers(0.0, *n),
319        (Value::Number(n), Value::Empty) => compare_numbers(*n, 0.0),
320        (Value::Empty, Value::Text(t)) => "".cmp(&t.to_lowercase() as &str),
321        (Value::Text(t), Value::Empty) => (t.to_lowercase() as String).as_str().cmp(""),
322        (Value::Empty, Value::Bool(b)) => false.cmp(b),
323        (Value::Bool(b), Value::Empty) => b.cmp(&false),
324        (Value::Number(x), Value::Number(y)) => compare_numbers(*x, *y),
325        (Value::Text(x), Value::Text(y)) => compare_text(x, y),
326        (Value::Bool(x), Value::Bool(y)) => x.cmp(y),
327        (Value::Error(x), Value::Error(y)) => x.code().cmp(&y.code()),
328        _ => a.rank().cmp(&b.rank()),
329    }
330}
331
332/// Compares numbers the way Excel does: both are rounded to 15 significant digits first, so binary
333/// noise such as `0.1+0.2` versus `0.3` counts as equal.
334pub fn compare_numbers(a: f64, b: f64) -> Ordering {
335    fn round15(x: f64) -> f64 {
336        if !x.is_finite() || x == 0.0 { x } else { format!("{x:.14e}").parse().unwrap_or(x) }
337    }
338    if a == b {
339        return Ordering::Equal;
340    }
341    // Numbers further apart than the 15th digit can't round together, and rounding keeps their
342    // order: skip the text round trip (this is on the sort and lookup hot path).
343    if (a - b).abs() > a.abs().max(b.abs()) * 1e-13 {
344        return a.partial_cmp(&b).unwrap_or(Ordering::Equal);
345    }
346    round15(a).partial_cmp(&round15(b)).unwrap_or(Ordering::Equal)
347}
348
349/// Case-insensitive text order (Unicode lowercase).
350pub fn compare_text(a: &str, b: &str) -> Ordering {
351    let mut ai = a.chars().flat_map(char::to_lowercase);
352    let mut bi = b.chars().flat_map(char::to_lowercase);
353    loop {
354        match (ai.next(), bi.next()) {
355            (None, None) => return Ordering::Equal,
356            (None, Some(_)) => return Ordering::Less,
357            (Some(_), None) => return Ordering::Greater,
358            (Some(x), Some(y)) if x != y => return x.cmp(&y),
359            _ => {}
360        }
361    }
362}
363
364/// Sorting order used by Sort (ascending): numbers, text, booleans, errors, then blanks last.
365pub fn sort_compare(a: &Value, b: &Value) -> Ordering {
366    match (a, b) {
367        (Value::Empty, Value::Empty) => Ordering::Equal,
368        (Value::Empty, _) => Ordering::Greater,
369        (_, Value::Empty) => Ordering::Less,
370        (Value::Error(_), Value::Error(_)) => Ordering::Equal,
371        _ => compare(a, b),
372    }
373}
374
375/// Number → text the way Excel converts in `&` and `TEXT(x,"General")`-free contexts: up to 15
376/// significant digits, scientific for very large or very small magnitudes.
377pub fn number_to_text(n: f64) -> String {
378    if n == 0.0 || !n.is_finite() {
379        return "0".into();
380    }
381    let a = n.abs();
382    if !(1e-9..1e15).contains(&a) && a.round() != a || a >= 1e15 {
383        return sci_text(n, 15);
384    }
385    // A value just below 1e15 can round up to 1e15 at 15 significant digits.
386    if format!("{:.14e}", a).parse::<f64>().is_ok_and(|r| r >= 1e15) {
387        return sci_text(n, 15);
388    }
389    // 15 significant digits, trailing zeros trimmed.
390    let mag = a.log10().floor() as i32;
391    let decimals = (14 - mag).clamp(0, 30) as usize;
392    let s = format!("{:.*}", decimals, n);
393    trim_decimal(&s)
394}
395
396/// Removes trailing zeros (and a trailing point) from a decimal string.
397pub fn trim_decimal(s: &str) -> String {
398    if s.contains('.') {
399        let t = s.trim_end_matches('0').trim_end_matches('.');
400        if t == "-0" { "0".into() } else { t.to_string() }
401    } else {
402        s.to_string()
403    }
404}
405
406/// Scientific text like `1.23456789012346E+15` with up to `sig` significant digits.
407pub fn sci_text(n: f64, sig: usize) -> String {
408    let s = format!("{:.*e}", sig.saturating_sub(1), n);
409    let Some((mant, exp)) = s.split_once('e') else { return s };
410    let mant = trim_decimal(mant);
411    let e: i32 = exp.parse().unwrap_or(0);
412    format!("{}E{}{:02}", mant, if e < 0 { '-' } else { '+' }, e.abs())
413}
414
415#[cfg(test)]
416mod tests {
417    use super::*;
418
419    #[test]
420    fn number_text() {
421        assert_eq!(number_to_text(1.0), "1");
422        assert_eq!(number_to_text(-2.5), "-2.5");
423        assert_eq!(number_to_text(0.1 + 0.2), "0.3");
424        assert_eq!(number_to_text(123456789012345.0), "123456789012345");
425        assert_eq!(number_to_text(1e15), "1E+15");
426        assert_eq!(number_to_text(1234567890123456.0), "1.23456789012346E+15");
427        assert_eq!(number_to_text(0.0001), "0.0001");
428        assert_eq!(number_to_text(1e-10), "1E-10");
429        assert_eq!(number_to_text(1.0 / 3.0), "0.333333333333333");
430    }
431
432    #[test]
433    fn number_text_rounds_up_to_scientific_boundary() {
434        assert_eq!(number_to_text(999999999999999.9), "1E+15");
435        assert_eq!(number_to_text(-999999999999999.9), "-1E+15");
436        assert_eq!(number_to_text(999999999999999.0), "999999999999999");
437    }
438
439    #[test]
440    fn coercion() {
441        assert_eq!(Value::from("12").to_number(), Ok(12.0));
442        assert_eq!(Value::from(" 1,234.5 ").to_number(), Ok(1234.5));
443        assert_eq!(Value::from("50%").to_number(), Ok(0.5));
444        assert_eq!(Value::from("abc").to_number(), Err(CellError::Value));
445        assert_eq!(Value::Bool(true).to_number(), Ok(1.0));
446        assert_eq!(Value::Empty.to_text(), Ok(String::new()));
447        assert_eq!(Value::from("true").to_bool(), Ok(true));
448        assert!(Value::number(f64::NAN).is_error());
449    }
450
451    #[test]
452    fn comparisons() {
453        assert_eq!(compare(&Value::from("abc"), &Value::from("ABC")), Ordering::Equal);
454        assert_eq!(compare(&Value::Number(5.0), &Value::from("1")), Ordering::Less);
455        assert_eq!(compare(&Value::from("z"), &Value::Bool(false)), Ordering::Less);
456        assert_eq!(compare(&Value::Empty, &Value::Number(0.0)), Ordering::Equal);
457        assert_eq!(sort_compare(&Value::Empty, &Value::Number(1.0)), Ordering::Greater);
458    }
459
460    #[test]
461    fn errors() {
462        for e in CellError::ALL {
463            assert_eq!(CellError::parse(e.as_str()), Some(e));
464        }
465        assert_eq!(CellError::parse("#div/0!"), Some(CellError::Div0));
466    }
467
468    #[test]
469    fn numbers_compare_at_15_significant_digits() {
470        let num = Value::Number;
471        assert_eq!(compare(&num(0.1 + 0.2), &num(0.3)), Ordering::Equal);
472        assert_eq!(compare(&num(1.1 * 3.0), &num(3.3)), Ordering::Equal);
473        assert_eq!(compare(&num(1.0), &num(1.0 + 1e-16)), Ordering::Equal);
474        assert_ne!(compare(&num(0.1 + 0.2), &num(0.3)), Ordering::Greater);
475        assert_eq!(compare(&num(1.0), &num(1.00000000000001)), Ordering::Less);
476        assert_eq!(compare(&num(1.0), &num(2.0)), Ordering::Less);
477    }
478
479    #[test]
480    fn arrays() -> Result<(), std::io::Error> {
481        let a = Array::new(2, 2, vec![1.0.into(), 2.0.into(), 3.0.into(), 4.0.into()]).ok_or_else(|| std::io::Error::other("valid array"))?;
482        assert_eq!(a.transpose().get(0, 1), Some(&Value::Number(3.0)));
483        assert!(Array::new(2, 2, vec![]).is_none());
484        let row = Array::row(vec![1.0.into(), 2.0.into()]);
485        assert_eq!(row.get_broadcast(5, 1), Value::Number(2.0));
486        Ok(())
487    }
488}
ravenrs_sheet/base/value.rs

ravenrs_sheet/base/
value.rs
