Skip to main content

sheetkit_core/
cell.rs

1//! Cell value representation.
2//!
3//! Provides the [`CellValue`] enum which represents the typed value of a
4//! single cell in a worksheet. This is the high-level counterpart to the
5//! raw XML `Cell` element from `sheetkit-xml`.
6
7use std::fmt;
8
9use chrono::{NaiveDate, NaiveDateTime, NaiveTime, Timelike};
10
11/// Represents the value of a cell.
12#[derive(Debug, Clone, PartialEq, Default)]
13pub enum CellValue {
14    /// No value (empty cell).
15    #[default]
16    Empty,
17    /// Boolean value.
18    Bool(bool),
19    /// Numeric value (integers are stored as f64 in Excel).
20    Number(f64),
21    /// String value.
22    String(String),
23    /// Formula with optional cached result.
24    Formula {
25        expr: String,
26        result: Option<Box<CellValue>>,
27    },
28    /// A date/time value stored as an Excel serial number.
29    /// Integer part = days since 1899-12-30 (Excel epoch).
30    /// Fractional part = time of day (0.5 = noon).
31    Date(f64),
32    /// Error value (e.g. #DIV/0!, #N/A, #VALUE!).
33    Error(String),
34    /// Rich text: multiple formatted runs within a single cell.
35    RichString(Vec<crate::rich_text::RichTextRun>),
36}
37
38impl fmt::Display for CellValue {
39    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
40        match self {
41            CellValue::Empty => write!(f, ""),
42            CellValue::Bool(b) => write!(f, "{}", if *b { "TRUE" } else { "FALSE" }),
43            CellValue::Number(n) => {
44                // Display integers without decimal point
45                if n.fract() == 0.0 && n.is_finite() {
46                    write!(f, "{}", *n as i64)
47                } else {
48                    write!(f, "{n}")
49                }
50            }
51            CellValue::Date(serial) => {
52                if let Some(dt) = serial_to_datetime(*serial) {
53                    if serial.fract() == 0.0 {
54                        // Date only, no time component.
55                        write!(f, "{}", dt.format("%Y-%m-%d"))
56                    } else {
57                        write!(f, "{}", dt.format("%Y-%m-%d %H:%M:%S"))
58                    }
59                } else {
60                    write!(f, "{serial}")
61                }
62            }
63            CellValue::String(s) => write!(f, "{s}"),
64            CellValue::Formula { result, expr, .. } => {
65                if let Some(result) = result {
66                    write!(f, "{result}")
67                } else {
68                    write!(f, "={expr}")
69                }
70            }
71            CellValue::Error(e) => write!(f, "{e}"),
72            CellValue::RichString(runs) => {
73                write!(f, "{}", crate::rich_text::rich_text_to_plain(runs))
74            }
75        }
76    }
77}
78
79impl From<&str> for CellValue {
80    fn from(s: &str) -> Self {
81        CellValue::String(s.to_string())
82    }
83}
84
85impl From<String> for CellValue {
86    fn from(s: String) -> Self {
87        CellValue::String(s)
88    }
89}
90
91impl From<f64> for CellValue {
92    fn from(n: f64) -> Self {
93        CellValue::Number(n)
94    }
95}
96
97impl From<i32> for CellValue {
98    fn from(n: i32) -> Self {
99        CellValue::Number(f64::from(n))
100    }
101}
102
103impl From<i64> for CellValue {
104    fn from(n: i64) -> Self {
105        CellValue::Number(n as f64)
106    }
107}
108
109impl From<bool> for CellValue {
110    fn from(b: bool) -> Self {
111        CellValue::Bool(b)
112    }
113}
114
115impl From<NaiveDate> for CellValue {
116    fn from(date: NaiveDate) -> Self {
117        CellValue::Date(date_to_serial(date))
118    }
119}
120
121impl From<NaiveDateTime> for CellValue {
122    fn from(datetime: NaiveDateTime) -> Self {
123        CellValue::Date(datetime_to_serial(datetime))
124    }
125}
126
127/// Number of seconds in a day.
128const SECONDS_PER_DAY: f64 = 86_400.0;
129
130/// Convert a `NaiveDate` to an Excel serial number.
131///
132/// Serial number 1 = January 1, 1900. Accounts for the Excel 1900 leap year
133/// bug (serial 60 = the non-existent February 29, 1900).
134pub fn date_to_serial(date: NaiveDate) -> f64 {
135    let epoch = NaiveDate::from_ymd_opt(1899, 12, 31).expect("valid epoch date 1899-12-31");
136    let days = (date - epoch).num_days() as f64;
137    // Excel incorrectly considers 1900 a leap year. All dates from
138    // March 1, 1900 onward have serial numbers one higher than the
139    // actual day count from the epoch.
140    if days >= 60.0 {
141        days + 1.0
142    } else {
143        days
144    }
145}
146
147/// Convert a `NaiveDateTime` to an Excel serial number with fractional time.
148///
149/// The integer part represents the date (see [`date_to_serial`]) and the
150/// fractional part represents the time of day (0.5 = noon).
151pub fn datetime_to_serial(datetime: NaiveDateTime) -> f64 {
152    let date_part = date_to_serial(datetime.date());
153    let time = datetime.time();
154    let seconds_since_midnight =
155        time.hour() as f64 * 3600.0 + time.minute() as f64 * 60.0 + time.second() as f64;
156    date_part + seconds_since_midnight / SECONDS_PER_DAY
157}
158
159/// Convert an Excel serial number to a `NaiveDate`.
160///
161/// Returns `None` for invalid serial numbers (< 1), non-finite values, and
162/// serials beyond the date range representable by `chrono`.
163pub fn serial_to_date(serial: f64) -> Option<NaiveDate> {
164    if !serial.is_finite() {
165        return None;
166    }
167    let floored = serial.floor();
168    // Reject before the saturating `as i64` cast; the 2^53 bound is far
169    // beyond chrono's date range, so checked arithmetic below still applies.
170    if !(1.0..=9_007_199_254_740_992.0).contains(&floored) {
171        return None;
172    }
173    let serial_int = floored as i64;
174    let epoch = NaiveDate::from_ymd_opt(1899, 12, 31).expect("valid epoch date 1899-12-31");
175    // Serial 60 = February 29, 1900 (Excel bug -- this date does not exist).
176    // Map it to February 28, 1900 for practical purposes.
177    if serial_int == 60 {
178        return NaiveDate::from_ymd_opt(1900, 2, 28);
179    }
180    // For serial >= 61, subtract 1 to compensate for the phantom leap day.
181    let adjusted = if serial_int >= 61 {
182        serial_int - 1
183    } else {
184        serial_int
185    };
186    epoch.checked_add_signed(chrono::Duration::try_days(adjusted)?)
187}
188
189/// Convert an Excel serial number to a `NaiveDateTime`.
190///
191/// Returns `None` for any serial rejected by [`serial_to_date`].
192pub fn serial_to_datetime(serial: f64) -> Option<NaiveDateTime> {
193    let date = serial_to_date(serial)?;
194    let frac = serial.fract().abs();
195    let total_seconds = (frac * SECONDS_PER_DAY).round() as u32;
196    let hours = total_seconds / 3600;
197    let minutes = (total_seconds % 3600) / 60;
198    let seconds = total_seconds % 60;
199    let time = NaiveTime::from_hms_opt(hours, minutes, seconds)?;
200    Some(NaiveDateTime::new(date, time))
201}
202
203/// Returns `true` if the given number format ID is a built-in date or time format.
204///
205/// Built-in date format IDs: 14-22 and 45-47.
206pub fn is_date_num_fmt(num_fmt_id: u32) -> bool {
207    matches!(num_fmt_id, 14..=22 | 45..=47)
208}
209
210/// Returns `true` if a custom number format string looks like a date/time format.
211///
212/// Checks for common date/time tokens such as y, m, d, h, s in the format code.
213/// Ignores text in quoted strings and escaped characters.
214pub fn is_date_format_code(code: &str) -> bool {
215    let mut in_quotes = false;
216    let mut prev_backslash = false;
217    for ch in code.chars() {
218        if prev_backslash {
219            prev_backslash = false;
220            continue;
221        }
222        if ch == '\\' {
223            prev_backslash = true;
224            continue;
225        }
226        if ch == '"' {
227            in_quotes = !in_quotes;
228            continue;
229        }
230        if in_quotes {
231            continue;
232        }
233        let lower = ch.to_ascii_lowercase();
234        if matches!(lower, 'y' | 'd' | 'h' | 's') {
235            return true;
236        }
237        // 'm' is ambiguous (month vs minute); consider it a date token
238        // when not preceded by 'h' or followed by 's'. For simplicity,
239        // treat any bare 'm' as a date indicator.
240        if lower == 'm' {
241            return true;
242        }
243    }
244    false
245}
246
247#[cfg(test)]
248mod tests {
249    use super::*;
250
251    #[test]
252    fn test_cell_value_default_is_empty() {
253        let v = CellValue::default();
254        assert_eq!(v, CellValue::Empty);
255    }
256
257    #[test]
258    fn test_cell_value_from_str() {
259        let v: CellValue = "hello".into();
260        assert_eq!(v, CellValue::String("hello".to_string()));
261    }
262
263    #[test]
264    fn test_cell_value_from_string() {
265        let v: CellValue = String::from("world").into();
266        assert_eq!(v, CellValue::String("world".to_string()));
267    }
268
269    #[test]
270    fn test_cell_value_from_f64() {
271        let v: CellValue = 3.15.into();
272        assert_eq!(v, CellValue::Number(3.15));
273    }
274
275    #[test]
276    fn test_cell_value_from_i32() {
277        let v: CellValue = 42i32.into();
278        assert_eq!(v, CellValue::Number(42.0));
279    }
280
281    #[test]
282    fn test_cell_value_from_i64() {
283        let v: CellValue = 100i64.into();
284        assert_eq!(v, CellValue::Number(100.0));
285    }
286
287    #[test]
288    fn test_cell_value_from_bool() {
289        let v: CellValue = true.into();
290        assert_eq!(v, CellValue::Bool(true));
291
292        let v2: CellValue = false.into();
293        assert_eq!(v2, CellValue::Bool(false));
294    }
295
296    #[test]
297    fn test_cell_value_display() {
298        assert_eq!(CellValue::Empty.to_string(), "");
299        assert_eq!(CellValue::Bool(true).to_string(), "TRUE");
300        assert_eq!(CellValue::Bool(false).to_string(), "FALSE");
301        assert_eq!(CellValue::Number(42.0).to_string(), "42");
302        assert_eq!(CellValue::Number(3.15).to_string(), "3.15");
303        assert_eq!(CellValue::String("hello".to_string()).to_string(), "hello");
304        assert_eq!(
305            CellValue::Error("#DIV/0!".to_string()).to_string(),
306            "#DIV/0!"
307        );
308        assert_eq!(
309            CellValue::Formula {
310                expr: "A1+B1".to_string(),
311                result: Some(Box::new(CellValue::Number(42.0))),
312            }
313            .to_string(),
314            "42"
315        );
316        assert_eq!(
317            CellValue::Formula {
318                expr: "A1+B1".to_string(),
319                result: None,
320            }
321            .to_string(),
322            "=A1+B1"
323        );
324    }
325
326    // -- Date conversion tests --
327
328    #[test]
329    fn test_date_to_serial_jan_1_1900() {
330        let date = NaiveDate::from_ymd_opt(1900, 1, 1).unwrap();
331        assert_eq!(date_to_serial(date), 1.0);
332    }
333
334    #[test]
335    fn test_date_to_serial_feb_28_1900() {
336        let date = NaiveDate::from_ymd_opt(1900, 2, 28).unwrap();
337        assert_eq!(date_to_serial(date), 59.0);
338    }
339
340    #[test]
341    fn test_date_to_serial_mar_1_1900_accounts_for_leap_year_bug() {
342        // March 1, 1900 should be serial 61 (skipping the phantom Feb 29).
343        let date = NaiveDate::from_ymd_opt(1900, 3, 1).unwrap();
344        assert_eq!(date_to_serial(date), 61.0);
345    }
346
347    #[test]
348    fn test_date_to_serial_jan_1_2000() {
349        let date = NaiveDate::from_ymd_opt(2000, 1, 1).unwrap();
350        // Known value: January 1, 2000 = serial 36526 in Excel.
351        assert_eq!(date_to_serial(date), 36526.0);
352    }
353
354    #[test]
355    fn test_date_to_serial_jan_1_1970() {
356        let date = NaiveDate::from_ymd_opt(1970, 1, 1).unwrap();
357        assert_eq!(date_to_serial(date), 25569.0);
358    }
359
360    #[test]
361    fn test_serial_to_date_jan_1_1900() {
362        let date = serial_to_date(1.0).unwrap();
363        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 1, 1).unwrap());
364    }
365
366    #[test]
367    fn test_serial_to_date_feb_28_1900() {
368        let date = serial_to_date(59.0).unwrap();
369        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 2, 28).unwrap());
370    }
371
372    #[test]
373    fn test_serial_to_date_60_phantom_leap_day() {
374        // Serial 60 is the phantom Feb 29 1900. We map it to Feb 28.
375        let date = serial_to_date(60.0).unwrap();
376        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 2, 28).unwrap());
377    }
378
379    #[test]
380    fn test_serial_to_date_mar_1_1900() {
381        let date = serial_to_date(61.0).unwrap();
382        assert_eq!(date, NaiveDate::from_ymd_opt(1900, 3, 1).unwrap());
383    }
384
385    #[test]
386    fn test_serial_to_date_jan_1_2000() {
387        let date = serial_to_date(36526.0).unwrap();
388        assert_eq!(date, NaiveDate::from_ymd_opt(2000, 1, 1).unwrap());
389    }
390
391    #[test]
392    fn test_serial_to_date_invalid() {
393        assert!(serial_to_date(0.0).is_none());
394        assert!(serial_to_date(-1.0).is_none());
395    }
396
397    #[test]
398    fn test_serial_conversions_are_total_for_extreme_inputs() {
399        let extremes = [
400            f64::NAN,
401            f64::INFINITY,
402            f64::NEG_INFINITY,
403            1.0e11,
404            1.0e14,
405            1.0e17,
406            1.0e300,
407            f64::MAX,
408            -1.0e14,
409            -1.0e300,
410            f64::MIN,
411            i64::MAX as f64,
412            i64::MIN as f64,
413        ];
414        for serial in extremes {
415            assert!(
416                serial_to_date(serial).is_none(),
417                "serial_to_date({serial}) must be None"
418            );
419            assert!(
420                serial_to_datetime(serial).is_none(),
421                "serial_to_datetime({serial}) must be None"
422            );
423            assert_eq!(CellValue::Date(serial).to_string(), format!("{serial}"));
424        }
425    }
426
427    #[test]
428    fn test_serial_to_date_chrono_upper_boundary() {
429        let max = NaiveDate::MAX;
430        let serial = date_to_serial(max);
431        assert_eq!(serial_to_date(serial), Some(max));
432        assert!(serial_to_date(serial + 1.0).is_none());
433        assert!(serial_to_datetime(serial + 1.5).is_none());
434    }
435
436    #[test]
437    fn test_date_roundtrip() {
438        // Test that date_to_serial -> serial_to_date is a roundtrip for dates
439        // after March 1, 1900.
440        let dates = vec![
441            NaiveDate::from_ymd_opt(1900, 3, 1).unwrap(),
442            NaiveDate::from_ymd_opt(2024, 6, 15).unwrap(),
443            NaiveDate::from_ymd_opt(1999, 12, 31).unwrap(),
444            NaiveDate::from_ymd_opt(2100, 1, 1).unwrap(),
445        ];
446        for date in dates {
447            let serial = date_to_serial(date);
448            let roundtripped = serial_to_date(serial).unwrap();
449            assert_eq!(roundtripped, date, "roundtrip failed for {date}");
450        }
451    }
452
453    #[test]
454    fn test_datetime_to_serial_noon() {
455        let dt = NaiveDate::from_ymd_opt(2000, 1, 1)
456            .unwrap()
457            .and_hms_opt(12, 0, 0)
458            .unwrap();
459        let serial = datetime_to_serial(dt);
460        // 36526 + 0.5 = 36526.5
461        assert!((serial - 36526.5).abs() < 1e-9);
462    }
463
464    #[test]
465    fn test_datetime_to_serial_with_time() {
466        let dt = NaiveDate::from_ymd_opt(2000, 1, 1)
467            .unwrap()
468            .and_hms_opt(6, 0, 0)
469            .unwrap();
470        let serial = datetime_to_serial(dt);
471        // 6 AM = 0.25 of a day
472        assert!((serial - 36526.25).abs() < 1e-9);
473    }
474
475    #[test]
476    fn test_serial_to_datetime_noon() {
477        let dt = serial_to_datetime(36526.5).unwrap();
478        assert_eq!(dt.date(), NaiveDate::from_ymd_opt(2000, 1, 1).unwrap());
479        assert_eq!(dt.time(), NaiveTime::from_hms_opt(12, 0, 0).unwrap());
480    }
481
482    #[test]
483    fn test_datetime_roundtrip() {
484        let dt = NaiveDate::from_ymd_opt(2024, 3, 15)
485            .unwrap()
486            .and_hms_opt(14, 30, 45)
487            .unwrap();
488        let serial = datetime_to_serial(dt);
489        let roundtripped = serial_to_datetime(serial).unwrap();
490        assert_eq!(roundtripped, dt);
491    }
492
493    #[test]
494    fn test_cell_value_date_display_date_only() {
495        let serial = date_to_serial(NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
496        let cv = CellValue::Date(serial);
497        assert_eq!(cv.to_string(), "2024-06-15");
498    }
499
500    #[test]
501    fn test_cell_value_date_display_with_time() {
502        let dt = NaiveDate::from_ymd_opt(2024, 6, 15)
503            .unwrap()
504            .and_hms_opt(14, 30, 0)
505            .unwrap();
506        let serial = datetime_to_serial(dt);
507        let cv = CellValue::Date(serial);
508        assert_eq!(cv.to_string(), "2024-06-15 14:30:00");
509    }
510
511    #[test]
512    fn test_cell_value_from_naive_date() {
513        let date = NaiveDate::from_ymd_opt(2024, 1, 1).unwrap();
514        let cv: CellValue = date.into();
515        match cv {
516            CellValue::Date(s) => assert_eq!(s, date_to_serial(date)),
517            _ => panic!("expected Date variant"),
518        }
519    }
520
521    #[test]
522    fn test_cell_value_from_naive_datetime() {
523        let dt = NaiveDate::from_ymd_opt(2024, 1, 1)
524            .unwrap()
525            .and_hms_opt(12, 0, 0)
526            .unwrap();
527        let cv: CellValue = dt.into();
528        match cv {
529            CellValue::Date(s) => assert_eq!(s, datetime_to_serial(dt)),
530            _ => panic!("expected Date variant"),
531        }
532    }
533
534    #[test]
535    fn test_is_date_num_fmt() {
536        // Date formats: 14-22
537        for id in 14..=22 {
538            assert!(is_date_num_fmt(id), "expected {id} to be a date format");
539        }
540        // Time formats: 45-47
541        for id in 45..=47 {
542            assert!(is_date_num_fmt(id), "expected {id} to be a date format");
543        }
544        // Not date formats
545        assert!(!is_date_num_fmt(0));
546        assert!(!is_date_num_fmt(1));
547        assert!(!is_date_num_fmt(13));
548        assert!(!is_date_num_fmt(23));
549        assert!(!is_date_num_fmt(49));
550    }
551
552    #[test]
553    fn test_is_date_format_code() {
554        assert!(is_date_format_code("yyyy-mm-dd"));
555        assert!(is_date_format_code("m/d/yyyy"));
556        assert!(is_date_format_code("h:mm:ss"));
557        assert!(is_date_format_code("dd/mm/yyyy hh:mm"));
558        // Not date formats
559        assert!(!is_date_format_code("0.00"));
560        assert!(!is_date_format_code("#,##0"));
561        assert!(!is_date_format_code("0%"));
562        // Quoted text should be ignored
563        assert!(!is_date_format_code("\"date\"0.00"));
564        // Escaped characters should be ignored
565        assert!(!is_date_format_code("\\d0.00"));
566    }
567
568    #[test]
569    fn test_date_early_dates_before_leap_bug() {
570        // January 2, 1900 = serial 2
571        let date = NaiveDate::from_ymd_opt(1900, 1, 2).unwrap();
572        assert_eq!(date_to_serial(date), 2.0);
573        assert_eq!(serial_to_date(2.0).unwrap(), date);
574    }
575}