use chrono::{Datelike, Duration, NaiveDate, NaiveDateTime, NaiveTime, Timelike};
use std::sync::atomic::{AtomicU64, Ordering};
use crate::exceptions::{Error, Result};
pub const MAC_EPOCH: [u32; 3] = [1904, 1, 1];
pub const WINDOWS_EPOCH: [u32; 3] = [1899, 12, 30];
pub const MAC_EPOCH_DAY: f64 = 2416480.5;
pub const WINDOWS_EPOCH_DAY: f64 = 2415018.5;
pub const CALENDAR_WINDOWS_1900: f64 = WINDOWS_EPOCH_DAY;
pub const CALENDAR_MAC_1904: f64 = MAC_EPOCH_DAY;
pub const SECS_PER_DAY: f64 = 86400.0;
pub const MJD_0: f64 = 2_400_000.5;
#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, Default)]
pub enum BaseDate {
#[default]
Windows1900,
Mac1904,
}
impl BaseDate {
pub fn offset(self) -> f64 {
match self {
BaseDate::Windows1900 => CALENDAR_WINDOWS_1900,
BaseDate::Mac1904 => CALENDAR_MAC_1904,
}
}
pub fn from_date1904_flag(value: Option<&str>) -> BaseDate {
match value {
Some("1") | Some("true") => BaseDate::Mac1904,
_ => BaseDate::Windows1900,
}
}
pub fn is_windows(self) -> bool {
matches!(self, BaseDate::Windows1900)
}
}
pub fn gcal2jd(year: i32, month: u32, day: u32) -> f64 {
let year = i64::from(year);
let month = i64::from(month);
let day = i64::from(day);
let a = (month - 14) / 12;
let mut mjd = (1461 * (year + 4800 + a)) / 4;
mjd += (367 * (month - 2 - 12 * a)) / 12;
let x = (year + 4900 + a) / 100;
mjd -= (3 * x) / 4;
(mjd + day - 2432076 + MJD_0.floor() as i64) as f64 + 0.5
}
pub fn jd2gcal(jd: f64) -> (i32, u32, u32) {
jd2gcal_int((jd + 0.5).floor() as i64)
}
pub fn jd2gcal_int(jdn: i64) -> (i32, u32, u32) {
if jdn > 2299160 {
let a = jdn + 32044;
let b = (4 * a + 3).div_euclid(146097);
let c = a - (146097 * b).div_euclid(4);
let d = (4 * c + 3).div_euclid(1461);
let e = c - (1461 * d).div_euclid(4);
let m = (5 * e + 2).div_euclid(153);
let day = e - (153 * m + 2).div_euclid(5) + 1;
let month = m + 3 - 12 * (m.div_euclid(10));
let year = 100 * b + d - 4800 + m.div_euclid(10);
(year as i32, month as u32, day as u32)
} else {
let c = jdn + 32082;
let d = (4 * c + 3).div_euclid(1461);
let e = c - (1461 * d).div_euclid(4);
let m = (5 * e + 2).div_euclid(153);
let day = e - (153 * m + 2).div_euclid(5) + 1;
let month = m + 3 - 12 * (m.div_euclid(10));
let year = d - 4800 + m.div_euclid(10);
(year as i32, month as u32, day as u32)
}
}
pub fn to_excel(dt: NaiveDateTime, base: BaseDate) -> f64 {
let mut jul = gcal2jd(dt.year(), dt.month(), dt.day()) - base.offset();
if jul <= 60.0 && base.is_windows() {
jul -= 1.0;
}
jul + time_to_days_datetime(dt)
}
pub fn date_to_excel(date: NaiveDate, base: BaseDate) -> f64 {
to_excel(date.and_time(NaiveTime::MIN), base)
}
pub fn from_excel(value: f64, base: BaseDate) -> ExcelDateTime {
let (year, month, day) = jd2gcal(value + base.offset());
let fractions = value - value.trunc();
let micro = (fractions * SECS_PER_DAY * 1_000_000.0).round() as i64;
if value > 0.0 && value < 1.0 || value < 0.0 && value > -1.0 {
return ExcelDateTime::Time(days_to_time(micro));
}
let Some(date) = NaiveDate::from_ymd_opt(year, month, day.max(1)) else {
return ExcelDateTime::DateTime(NaiveDateTime::default());
};
let midnight = date.and_time(NaiveTime::MIN);
match midnight.checked_add_signed(chrono::Duration::microseconds(micro)) {
Some(dt) => ExcelDateTime::DateTime(dt),
None => ExcelDateTime::Date(date),
}
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum ExcelDateTime {
DateTime(NaiveDateTime),
Date(NaiveDate),
Time(NaiveTime),
}
impl From<ExcelDateTime> for NaiveDateTime {
fn from(value: ExcelDateTime) -> Self {
match value {
ExcelDateTime::DateTime(dt) => dt,
ExcelDateTime::Date(d) => d.and_time(NaiveTime::MIN),
ExcelDateTime::Time(t) => NaiveDate::MIN.and_time(t),
}
}
}
pub fn time_to_days(time: NaiveTime) -> f64 {
let secs = (time.hour() * 3600 + time.minute() * 60 + time.second()) as f64
+ f64::from(time.nanosecond()) / 1_000_000_000.0;
secs / SECS_PER_DAY
}
pub fn time_to_days_datetime(dt: NaiveDateTime) -> f64 {
time_to_days(dt.time())
}
pub fn timedelta_to_days(delta: Duration) -> f64 {
delta.num_microseconds().unwrap_or(0) as f64 / 1_000_000.0 / SECS_PER_DAY
}
pub fn micros_to_days(micros: i64) -> f64 {
micros as f64 / 1_000_000.0 / SECS_PER_DAY
}
pub fn days_to_time(micros: i64) -> NaiveTime {
let total_secs = micros.div_euclid(1_000_000);
let rem_micros = micros.rem_euclid(1_000_000);
let minutes = total_secs.div_euclid(60);
let seconds = total_secs.rem_euclid(60);
let hours = minutes.div_euclid(60);
let mins = minutes.rem_euclid(60);
NaiveTime::from_hms_micro_opt(hours as u32, mins as u32, seconds as u32, rem_micros as u32)
.unwrap_or(NaiveTime::MIN)
}
pub fn datetime_to_w3cdtf(dt: NaiveDateTime) -> String {
dt.format("%Y-%m-%dT%H:%M:%SZ").to_string()
}
pub fn w3cdtf_to_datetime(formatted: &str) -> Result<NaiveDateTime> {
let digits: Vec<&str> = formatted
.split(|c: char| !c.is_ascii_digit())
.filter(|s| !s.is_empty())
.collect();
if digits.len() < 6 {
return Err(Error::Value(format!(
"Invalid W3CDTF timestamp: {formatted}"
)));
}
let parse = |s: &str| -> Result<i64> {
s.parse::<i64>()
.map_err(|_| Error::Value(format!("Invalid W3CDTF timestamp: {formatted}")))
};
let year = parse(digits[0])? as i32;
let month = parse(digits[1])? as u32;
let day = parse(digits[2])? as u32;
let hour = parse(digits[3])? as u32;
let minute = parse(digits[4])? as u32;
let second = parse(digits[5])? as u32;
NaiveDate::from_ymd_opt(year, month, day)
.and_then(|d| d.and_hms_opt(hour, minute, second))
.ok_or_else(|| Error::Value(format!("Invalid W3CDTF timestamp: {formatted}")))
}
#[derive(Debug, Clone, Copy)]
pub struct SharedDate {
pub excel_base_date: BaseDate,
}
impl Default for SharedDate {
fn default() -> Self {
SharedDate {
excel_base_date: BaseDate::Windows1900,
}
}
}
impl SharedDate {
pub fn new(base: BaseDate) -> Result<Self> {
Ok(SharedDate {
excel_base_date: base,
})
}
pub fn datetime_to_julian(&self, dt: NaiveDateTime) -> f64 {
to_excel(dt, self.excel_base_date)
}
pub fn from_julian(&self, value: f64) -> ExcelDateTime {
from_excel(value, self.excel_base_date)
}
pub fn time_to_julian(&self, hours: i64, minutes: i64, seconds: i64) -> f64 {
((hours * 3600) + (minutes * 60) + seconds) as f64 / SECS_PER_DAY
}
}
static CACHE_HITS: AtomicU64 = AtomicU64::new(0);
pub fn cache_hit_count() -> u64 {
CACHE_HITS.load(Ordering::Relaxed)
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn w3cdtf_round_trip() {
let dt = NaiveDate::from_ymd_opt(2013, 7, 15)
.unwrap()
.and_hms_opt(6, 52, 33)
.unwrap();
assert_eq!(datetime_to_w3cdtf(dt), "2013-07-15T06:52:33Z");
assert_eq!(
w3cdtf_to_datetime("2011-06-30T13:35:26Z").unwrap(),
NaiveDate::from_ymd_opt(2011, 6, 30)
.unwrap()
.and_hms_opt(13, 35, 26)
.unwrap()
);
assert_eq!(
w3cdtf_to_datetime("2013-03-04T12:19:01.00Z").unwrap(),
NaiveDate::from_ymd_opt(2013, 3, 4)
.unwrap()
.and_hms_opt(12, 19, 1)
.unwrap()
);
}
#[test]
fn to_excel_windows() {
let cases: Vec<(NaiveDate, f64)> = vec![
(NaiveDate::from_ymd_opt(1899, 12, 31).unwrap(), 0.0),
(NaiveDate::from_ymd_opt(1900, 1, 15).unwrap(), 15.0),
(NaiveDate::from_ymd_opt(1900, 2, 28).unwrap(), 59.0),
(NaiveDate::from_ymd_opt(1900, 3, 1).unwrap(), 61.0),
(NaiveDate::from_ymd_opt(2009, 12, 20).unwrap(), 40167.0),
];
for (date, expected) in cases {
assert_eq!(date_to_excel(date, BaseDate::Windows1900), expected);
}
let dt = NaiveDate::from_ymd_opt(2010, 1, 18)
.unwrap()
.and_hms_micro_opt(14, 15, 20, 1600)
.unwrap();
assert!((to_excel(dt, BaseDate::Windows1900) - 40196.5939815).abs() < 1e-9);
let old = NaiveDate::from_ymd_opt(1506, 10, 15)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap();
assert!((to_excel(old, BaseDate::Windows1900) - -143618.0).abs() < 1e-9);
}
#[test]
fn to_excel_mac() {
let cases: Vec<(NaiveDate, f64)> = vec![
(NaiveDate::from_ymd_opt(1904, 1, 1).unwrap(), 0.0),
(NaiveDate::from_ymd_opt(2011, 10, 31).unwrap(), 39385.0),
(NaiveDate::from_ymd_opt(2009, 12, 20).unwrap(), 38705.0),
];
for (date, expected) in cases {
assert_eq!(date_to_excel(date, BaseDate::Mac1904), expected);
}
}
#[test]
fn from_excel_windows() {
let cases: Vec<(f64, ExcelDateTime)> = vec![
(
40167.0,
ExcelDateTime::DateTime(
NaiveDate::from_ymd_opt(2009, 12, 20)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap(),
),
),
(
60.0,
ExcelDateTime::DateTime(
NaiveDate::from_ymd_opt(1900, 2, 28)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap(),
),
),
(
-25063.0,
ExcelDateTime::DateTime(
NaiveDate::from_ymd_opt(1831, 5, 18)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap(),
),
),
(
0.125,
ExcelDateTime::Time(NaiveTime::from_hms_opt(3, 0, 0).unwrap()),
),
];
for (value, expected) in cases {
assert_eq!(from_excel(value, BaseDate::Windows1900), expected);
}
assert_eq!(
from_excel(21980.0, BaseDate::Windows1900),
ExcelDateTime::DateTime(
NaiveDate::from_ymd_opt(1960, 3, 5)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap()
)
);
}
#[test]
fn from_excel_mac() {
assert_eq!(
from_excel(0.0, BaseDate::Mac1904),
ExcelDateTime::DateTime(
NaiveDate::from_ymd_opt(1904, 1, 1)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap()
)
);
assert_eq!(
from_excel(21980.0, BaseDate::Mac1904),
ExcelDateTime::DateTime(
NaiveDate::from_ymd_opt(1964, 3, 6)
.unwrap()
.and_hms_opt(0, 0, 0)
.unwrap()
)
);
}
#[test]
fn time_helpers() {
let t1 = NaiveTime::from_hms_micro_opt(13, 55, 12, 36).unwrap();
assert!((time_to_days(t1) - 0.5800000004166667).abs() < 1e-12);
let t2 = NaiveTime::from_hms_opt(3, 0, 0).unwrap();
assert!((time_to_days(t2) - 0.125).abs() < 1e-12);
let td = Duration::days(1) + Duration::hours(3);
assert!((timedelta_to_days(td) - 1.125).abs() < 1e-12);
assert_eq!(
days_to_time(51_320_001_600),
NaiveTime::from_hms_micro_opt(14, 15, 20, 1600).unwrap()
);
}
#[test]
fn round_trip_dates() {
for serial in [61.0, 40167.0, 40372.5] {
let dt: NaiveDateTime = from_excel(serial, BaseDate::Windows1900).into();
assert!((to_excel(dt, BaseDate::Windows1900) - serial).abs() < 1e-6);
}
let first: NaiveDateTime = from_excel(1.0, BaseDate::Windows1900).into();
assert_eq!(
NaiveDate::from_ymd_opt(1899, 12, 31)
.unwrap()
.and_time(dt_time()),
first
);
assert_eq!(to_excel(first, BaseDate::Windows1900), 0.0);
for serial in [0.0, 39385.0] {
let dt: NaiveDateTime = from_excel(serial, BaseDate::Mac1904).into();
assert!((to_excel(dt, BaseDate::Mac1904) - serial).abs() < 1e-6);
}
}
fn dt_time() -> chrono::NaiveTime {
chrono::NaiveTime::MIN
}
}