1use std::fmt;
8
9use chrono::{NaiveDate, NaiveDateTime, NaiveTime, Timelike};
10
11#[derive(Debug, Clone, PartialEq, Default)]
13pub enum CellValue {
14 #[default]
16 Empty,
17 Bool(bool),
19 Number(f64),
21 String(String),
23 Formula {
25 expr: String,
26 result: Option<Box<CellValue>>,
27 },
28 Date(f64),
32 Error(String),
34 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 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 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
127const SECONDS_PER_DAY: f64 = 86_400.0;
129
130pub 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 if days >= 60.0 {
141 days + 1.0
142 } else {
143 days
144 }
145}
146
147pub 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
159pub fn serial_to_date(serial: f64) -> Option<NaiveDate> {
164 if !serial.is_finite() {
165 return None;
166 }
167 let floored = serial.floor();
168 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 if serial_int == 60 {
178 return NaiveDate::from_ymd_opt(1900, 2, 28);
179 }
180 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
189pub 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
203pub fn is_date_num_fmt(num_fmt_id: u32) -> bool {
207 matches!(num_fmt_id, 14..=22 | 45..=47)
208}
209
210pub 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 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 #[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 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 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 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 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 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 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 for id in 14..=22 {
538 assert!(is_date_num_fmt(id), "expected {id} to be a date format");
539 }
540 for id in 45..=47 {
542 assert!(is_date_num_fmt(id), "expected {id} to be a date format");
543 }
544 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 assert!(!is_date_format_code("0.00"));
560 assert!(!is_date_format_code("#,##0"));
561 assert!(!is_date_format_code("0%"));
562 assert!(!is_date_format_code("\"date\"0.00"));
564 assert!(!is_date_format_code("\\d0.00"));
566 }
567
568 #[test]
569 fn test_date_early_dates_before_leap_bug() {
570 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}