xldate.py 7.7 KB

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