1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
###############################################################################
#
# Tests for the xlrd xldate.xldate_as_datetime() function.
#
import unittest
from datetime import datetime
from xlrd import xldate
not_1904 = False
is_1904 = True
class TestConvertToDateTime(unittest.TestCase):
"""
Testcases to test the _xldate_to_datetime() function against dates
extracted from Excel files, with 1900/1904 epochs.
"""
def test_dates_and_times_1900_epoch(self):
"""
Test the _xldate_to_datetime() function for dates and times in
the Excel standard 1900 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's 0.0 date in the 1900 epoch is 1 day before 1900.
('1899-12-31T00:00:00.000', 0),
# Date/time before the false Excel 1900 leapday.
('1900-02-28T02:11:11.986', 59.09111094906),
# Date/time after the false Excel 1900 leapday.
('1900-03-01T05:46:44.068', 61.24078782403),
# Random date/times in Excel's 0-9999.9999+ range.
('1982-08-25T00:15:20.213', 30188.010650613425),
('2065-04-19T00:16:48.290', 60376.011670023145),
('3222-06-11T03:08:08.251', 483014.13065105322),
('4379-08-03T06:14:48.580', 905652.26028449077),
('5949-12-30T12:59:54.263', 1479232.5416002662),
# End of Excel's date range.
('9999-12-31T23:59:59.000', 2958465.999988426),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def test_dates_only_1900_epoch(self):
"""
Test the _xldate_to_datetime() function for dates in the Excel
standard 1900 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's day 0 in the 1900 epoch is 1 day before 1900.
('1899-12-31', 0),
# Excel's day 1 in the 1900 epoch.
('1900-01-01', 1),
# Date/time before the false Excel 1900 leapday.
('1900-02-28', 59),
# Date/time after the false Excel 1900 leapday.
('1900-03-01', 61),
# Random date/times in Excel's 0-9999.9999+ range.
('1902-09-27', 1001),
('1999-12-31', 36525),
('2000-01-01', 36526),
('4000-12-31', 767376),
('4321-01-01', 884254),
('9999-01-01', 2958101),
# End of Excel's date range.
('9999-12-31', 2958465),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%d")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def test_dates_only_1904_epoch(self):
"""
Test the _xldate_to_datetime() function for dates in the Excel
Mac/1904 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's day 0 in the 1904 epoch.
('1904-01-01', 0),
# Random date/times in Excel's 0-9999.9999+ range.
('1904-01-31', 30),
('1904-08-31', 243),
('1999-02-28', 34757),
('1999-12-31', 35063),
('2000-01-01', 35064),
('2400-12-31', 181526),
('4000-01-01', 765549),
('9999-01-01', 2956639),
# End of Excel's date range.
('9999-12-31', 2957003),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%d")
got = xldate.xldate_as_datetime(excel_date[1], is_1904)
self.assertEqual(got, exp)
def test_times_only(self):
"""
Test the _xldate_to_datetime() function for times only, i.e, the
fractional part of the Excel date when the serial date is 0.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file. The 1899-12-31 date is Excel's day 0.
excel_dates = [
# Random times in Excel's 0-0.9999+ range for 1 day.
('1899-12-31T00:00:00.000', 0),
('1899-12-31T00:15:20.213', 1.0650613425925924E-2),
('1899-12-31T02:24:37.095', 0.10042934027777778),
('1899-12-31T04:56:35.792', 0.2059698148148148),
('1899-12-31T07:31:20.407', 0.31343063657407405),
('1899-12-31T09:37:23.945', 0.40097158564814817),
('1899-12-31T12:09:48.602', 0.50681252314814818),
('1899-12-31T14:37:57.451', 0.60969271990740748),
('1899-12-31T17:04:02.415', 0.71113906250000003),
('1899-12-31T19:14:24.673', 0.80167445601851861),
('1899-12-31T21:39:05.944', 0.90215212962962965),
('1899-12-31T23:17:12.632', 0.97028509259259266),
('1899-12-31T23:59:59.999', 0.99999998842592586),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
|
