Source code

Download source archive ↓

src/openpyxl/utils/datetime.py

1# Copyright (c) 2010-2024 openpyxl
2
3"""Manage Excel date weirdness."""
4
5# Python stdlib imports
6import datetime
7from math import isnan
8import re
9
10
11# constants
12MAC_EPOCH = datetime.datetime(1904, 1, 1)
13WINDOWS_EPOCH = datetime.datetime(1899, 12, 30)
14CALENDAR_WINDOWS_1900 = 2415018.5   # Julian date of WINDOWS_EPOCH
15CALENDAR_MAC_1904 = 2416480.5       # Julian date of MAC_EPOCH
16CALENDAR_WINDOWS_1900 = WINDOWS_EPOCH
17CALENDAR_MAC_1904 = MAC_EPOCH
18SECS_PER_DAY = 86400
19
20ISO_FORMAT = '%Y-%m-%dT%H:%M:%SZ'
21ISO_REGEX = re.compile(r'''
22(?P<date>(?P<year>\d{4})-(?P<month>\d{2})-(?P<day>\d{2}))?T?
23(?P<time>(?P<hour>\d{2}):(?P<minute>\d{2})(:(?P<second>\d{2})(?P<microsecond>\.\d{1,3})?)?)?Z?''',
24                                       re.VERBOSE)
25ISO_DURATION = re.compile(r'PT((?P<hours>\d+)H)?((?P<minutes>\d+)M)?((?P<seconds>\d+(\.\d{1,3})?)S)?')
26
27
28def to_ISO8601(dt):
29    """Convert from a datetime to a timestamp string."""
30    if hasattr(dt, "microsecond") and dt.microsecond:
31        return dt.isoformat(timespec="milliseconds")
32    return dt.isoformat()
33
34
35def from_ISO8601(formatted_string):
36    """Convert from a timestamp string to a datetime object. According to
37    18.17.4 in the specification the following ISO 8601 formats are
38    supported.
39
40    Dates B.1.1 and B.2.1
41    Times B.1.2 and B.2.2
42    Datetimes B.1.3 and B.2.3
43
44    There is no concept of timedeltas in the specification, but Excel
45    writes them (in strict OOXML mode), so these are also understood.
46    """
47    if not formatted_string:
48        return None
49
50    match = ISO_REGEX.match(formatted_string)
51    if match and any(match.groups()):
52        parts = match.groupdict(0)
53        for key in ["year", "month", "day", "hour", "minute", "second"]:
54            if parts[key]:
55                parts[key] = int(parts[key])
56
57        if parts["microsecond"]:
58            parts["microsecond"] = int(float(parts['microsecond']) * 1_000_000)
59
60        if not parts["date"]:
61            dt = datetime.time(parts['hour'], parts['minute'], parts['second'], parts["microsecond"])
62        elif not parts["time"]:
63            dt = datetime.date(parts['year'], parts['month'], parts['day'])
64        else:
65            del parts["time"]
66            del parts["date"]
67            dt = datetime.datetime(**parts)
68        return dt
69
70    match = ISO_DURATION.match(formatted_string)
71    if match and any(match.groups()):
72        parts = match.groupdict(0)
73        for key, val in parts.items():
74            if val:
75                parts[key] = float(val)
76        return datetime.timedelta(**parts)
77
78    raise ValueError("Invalid datetime value {}".format(formatted_string))
79
80
81def to_excel(dt, epoch=WINDOWS_EPOCH):
82    """Convert Python datetime to Excel serial"""
83    if isinstance(dt, datetime.time):
84        return time_to_days(dt)
85    if isinstance(dt, datetime.timedelta):
86        return timedelta_to_days(dt)
87    if isnan(dt.year):  # Pandas supports Not a Date
88        return
89
90    if not hasattr(dt, "date"):
91        dt = datetime.datetime.combine(dt, datetime.time())
92
93    # rebase on epoch and adjust for < 1900-03-01
94    days = (dt - epoch).days
95    if 0 < days <= 60 and epoch == WINDOWS_EPOCH:
96        days -= 1
97    return days + time_to_days(dt)
98
99
100def from_excel(value, epoch=WINDOWS_EPOCH, timedelta=False):
101    """Convert Excel serial to Python datetime"""
102    if value is None:
103        return
104
105    if timedelta:
106        td = datetime.timedelta(days=value)
107        if td.microseconds:
108            # round to millisecond precision
109            td = datetime.timedelta(seconds=td.total_seconds() // 1,
110                                    microseconds=round(td.microseconds, -3))
111        return td
112
113    day, fraction = divmod(value, 1)
114    diff = datetime.timedelta(milliseconds=round(fraction * SECS_PER_DAY * 1000))
115    if 0 <= value < 1 and diff.days == 0:
116        return days_to_time(diff)
117    if 0 < value < 60 and epoch == WINDOWS_EPOCH:
118        day += 1
119    return epoch + datetime.timedelta(days=day) + diff
120
121
122def time_to_days(value):
123    """Convert a time value to fractions of day"""
124    return (
125        (value.hour * 3600)
126        + (value.minute * 60)
127        + value.second
128        + value.microsecond / 10**6
129        ) / SECS_PER_DAY
130
131
132def timedelta_to_days(value):
133    """Convert a timedelta value to fractions of a day"""
134    return value.total_seconds() / SECS_PER_DAY
135
136
137def days_to_time(value):
138    mins, seconds = divmod(value.seconds, 60)
139    hours, mins = divmod(mins, 60)
140    return datetime.time(hours, mins, seconds, value.microseconds)