Source code

Download source archive ↓

src/xlrd/xldate.py

1# -*- coding: utf-8 -*-
2# Copyright (c) 2005-2008 Stephen John Machin, Lingfo Pty Ltd
3# This module is part of the xlrd package, which is released under a
4# BSD-style licence.
5# No part of the content of this file was derived from the works of David Giffin.
6"""
7Tools for working with dates and times in Excel files.
8
9The conversion from ``days`` to ``(year, month, day)`` starts with
10an integral "julian day number" aka JDN.
11FWIW:
12
13- JDN 0 corresponds to noon on Monday November 24 in Gregorian year -4713.
14
15More importantly:
16
17- Noon on Gregorian 1900-03-01 (day 61 in the 1900-based system) is JDN 2415080.0
18- Noon on Gregorian 1904-01-02 (day  1 in the 1904-based system) is JDN 2416482.0
19
20"""
21import datetime
22
23_JDN_delta = (2415080 - 61, 2416482 - 1)
24assert _JDN_delta[1] - _JDN_delta[0] == 1462
25
26# Pre-calculate the datetime epochs for efficiency.
27epoch_1904 = datetime.datetime(1904, 1, 1)
28epoch_1900 = datetime.datetime(1899, 12, 31)
29epoch_1900_minus_1 = datetime.datetime(1899, 12, 30)
30
31# This is equivalent to 10000-01-01:
32_XLDAYS_TOO_LARGE = (2958466, 2958466 - 1462)
33
34
35class XLDateError(ValueError):
36    "A base class for all datetime-related errors."
37
38
39class XLDateNegative(XLDateError):
40    "``xldate < 0.00``"
41
42
43class XLDateAmbiguous(XLDateError):
44    "The 1900 leap-year problem ``(datemode == 0 and 1.0 <= xldate < 61.0)``"
45
46
47class XLDateTooLarge(XLDateError):
48    "Gregorian year 10000 or later"
49
50
51class XLDateBadDatemode(XLDateError):
52    "``datemode`` arg is neither 0 nor 1"
53
54
55class XLDateBadTuple(XLDateError):
56    pass
57
58
59def xldate_as_tuple(xldate, datemode):
60    """
61    Convert an Excel number (presumed to represent a date, a datetime or a time) into
62    a tuple suitable for feeding to datetime or mx.DateTime constructors.
63
64    :param xldate: The Excel number
65    :param datemode: 0: 1900-based, 1: 1904-based.
66    :raises xlrd.xldate.XLDateNegative:
67    :raises xlrd.xldate.XLDateAmbiguous:
68
69    :raises xlrd.xldate.XLDateTooLarge:
70    :raises xlrd.xldate.XLDateBadDatemode:
71    :raises xlrd.xldate.XLDateError:
72    :returns: Gregorian ``(year, month, day, hour, minute, nearest_second)``.
73
74    .. warning::
75
76      When using this function to interpret the contents of a workbook, you
77      should pass in the :attr:`~xlrd.book.Book.datemode`
78      attribute of that workbook. Whether the workbook has ever been anywhere
79      near a Macintosh is irrelevant.
80
81    .. admonition:: Special case
82
83        If ``0.0 <= xldate < 1.0``, it is assumed to represent a time;
84        ``(0, 0, 0, hour, minute, second)`` will be returned.
85
86    .. note::
87
88        ``1904-01-01`` is not regarded as a valid date in the ``datemode==1``
89        system; its "serial number" is zero.
90    """
91    if datemode not in (0, 1):
92        raise XLDateBadDatemode(datemode)
93    if xldate == 0.00:
94        return (0, 0, 0, 0, 0, 0)
95    if xldate < 0.00:
96        raise XLDateNegative(xldate)
97    xldays = int(xldate)
98    frac = xldate - xldays
99    seconds = int(round(frac * 86400.0))
100    assert 0 <= seconds <= 86400
101    if seconds == 86400:
102        hour = minute = second = 0
103        xldays += 1
104    else:
105        # second = seconds % 60; minutes = seconds // 60
106        minutes, second = divmod(seconds, 60)
107        # minute = minutes % 60; hour    = minutes // 60
108        hour, minute = divmod(minutes, 60)
109    if xldays >= _XLDAYS_TOO_LARGE[datemode]:
110        raise XLDateTooLarge(xldate)
111
112    if xldays == 0:
113        return (0, 0, 0, hour, minute, second)
114
115    if xldays < 61 and datemode == 0:
116        raise XLDateAmbiguous(xldate)
117
118    jdn = xldays + _JDN_delta[datemode]
119    yreg = ((((jdn * 4 + 274277) // 146097) * 3 // 4) + jdn + 1363) * 4 + 3
120    mp = ((yreg % 1461) // 4) * 535 + 333
121    d = ((mp % 16384) // 535) + 1
122    # mp /= 16384
123    mp >>= 14
124    if mp >= 10:
125        return ((yreg // 1461) - 4715, mp - 9, d, hour, minute, second)
126    else:
127        return ((yreg // 1461) - 4716, mp + 3, d, hour, minute, second)
128
129
130def xldate_as_datetime(xldate, datemode):
131    """
132    Convert an Excel date/time number into a :class:`datetime.datetime` object.
133
134    :param xldate: The Excel number
135    :param datemode: 0: 1900-based, 1: 1904-based.
136
137    :returns: A :class:`datetime.datetime` object.
138    """
139
140    # Set the epoch based on the 1900/1904 datemode.
141    if datemode:
142        epoch = epoch_1904
143    else:
144        if xldate < 60:
145            epoch = epoch_1900
146        else:
147            # Workaround Excel 1900 leap year bug by adjusting the epoch.
148            epoch = epoch_1900_minus_1
149
150    # The integer part of the Excel date stores the number of days since
151    # the epoch and the fractional part stores the percentage of the day.
152    days = int(xldate)
153    fraction = xldate - days
154
155    # Get the the integer and decimal seconds in Excel's millisecond resolution.
156    seconds = int(round(fraction * 86400000.0))
157    seconds, milliseconds = divmod(seconds, 1000)
158
159    return epoch + datetime.timedelta(days, seconds, 0, milliseconds)
160
161
162# === conversions from date/time to xl numbers
163
164def _leap(y):
165    if y % 4: return 0
166    if y % 100: return 1
167    if y % 400: return 0
168    return 1
169
170_days_in_month = (None, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
171
172
173def xldate_from_date_tuple(date_tuple, datemode):
174    """
175    Convert a date tuple (year, month, day) to an Excel date.
176
177    :param year: Gregorian year.
178    :param month: ``1 <= month <= 12``
179    :param day: ``1 <= day <= last day of that (year, month)``
180    :param datemode: 0: 1900-based, 1: 1904-based.
181    :raises xlrd.xldate.XLDateAmbiguous:
182    :raises xlrd.xldate.XLDateBadDatemode:
183    :raises xlrd.xldate.XLDateBadTuple:
184      ``(year, month, day)`` is too early/late or has invalid component(s)
185    :raises xlrd.xldate.XLDateError:
186    """
187    year, month, day = date_tuple
188
189    if datemode not in (0, 1):
190        raise XLDateBadDatemode(datemode)
191
192    if year == 0 and month == 0 and day == 0:
193        return 0.00
194
195    if not (1900 <= year <= 9999):
196        raise XLDateBadTuple("Invalid year: %r" % ((year, month, day),))
197    if not (1 <= month <= 12):
198        raise XLDateBadTuple("Invalid month: %r" % ((year, month, day),))
199    if  (day < 1 or
200         (day > _days_in_month[month] and not(day == 29 and month == 2 and _leap(year)))):
201        raise XLDateBadTuple("Invalid day: %r" % ((year, month, day),))
202
203    Yp = year + 4716
204    M = month
205    if M <= 2:
206        Yp = Yp - 1
207        Mp = M + 9
208    else:
209        Mp = M - 3
210    jdn = (1461 * Yp // 4) + ((979 * Mp + 16) // 32) + \
211        day - 1364 - (((Yp + 184) // 100) * 3 // 4)
212    xldays = jdn - _JDN_delta[datemode]
213    if xldays <= 0:
214        raise XLDateBadTuple("Invalid (year, month, day): %r" % ((year, month, day),))
215    if xldays < 61 and datemode == 0:
216        raise XLDateAmbiguous("Before 1900-03-01: %r" % ((year, month, day),))
217    return float(xldays)
218
219
220def xldate_from_time_tuple(time_tuple):
221    """
222    Convert a time tuple ``(hour, minute, second)`` to an Excel "date" value
223    (fraction of a day).
224
225    :param hour: ``0 <= hour < 24``
226    :param minute: ``0 <= minute < 60``
227    :param second: ``0 <= second < 60``
228    :raises xlrd.xldate.XLDateBadTuple: Out-of-range hour, minute, or second
229    """
230    hour, minute, second = time_tuple
231    if 0 <= hour < 24 and 0 <= minute < 60 and 0 <= second < 60:
232        return ((second / 60.0 + minute) / 60.0 + hour) / 24.0
233    raise XLDateBadTuple("Invalid (hour, minute, second): %r" % ((hour, minute, second),))
234
235
236def xldate_from_datetime_tuple(datetime_tuple, datemode):
237    """
238    Convert a datetime tuple ``(year, month, day, hour, minute, second)`` to an
239    Excel date value.
240    For more details, refer to other xldate_from_*_tuple functions.
241
242    :param datetime_tuple: ``(year, month, day, hour, minute, second)``
243    :param datemode: 0: 1900-based, 1: 1904-based.
244    """
245    return (
246        xldate_from_date_tuple(datetime_tuple[:3], datemode) +
247        xldate_from_time_tuple(datetime_tuple[3:])
248    )