forked from qax-os/excelize
-
Notifications
You must be signed in to change notification settings - Fork 0
/
date.go
216 lines (198 loc) · 6.66 KB
/
date.go
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
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
// Copyright 2016 - 2023 The excelize Authors. All rights reserved. Use of
// this source code is governed by a BSD-style license that can be found in
// the LICENSE file.
//
// Package excelize providing a set of functions that allow you to write to and
// read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and
// writing spreadsheet documents generated by Microsoft Excel™ 2007 and later.
// Supports complex components by high compatibility, and provided streaming
// API for generating or reading data from a worksheet with huge amounts of
// data. This library needs Go version 1.16 or later.
package excelize
import (
"math"
"time"
)
const (
nanosInADay = float64((24 * time.Hour) / time.Nanosecond)
dayNanoseconds = 24 * time.Hour
maxDuration = 290 * 364 * dayNanoseconds
roundEpsilon = 1e-9
)
var (
daysInMonth = []int{31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}
excel1900Epoc = time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)
excel1904Epoc = time.Date(1904, time.January, 1, 0, 0, 0, 0, time.UTC)
excelMinTime1900 = time.Date(1899, time.December, 31, 0, 0, 0, 0, time.UTC)
excelBuggyPeriodStart = time.Date(1900, time.March, 1, 0, 0, 0, 0, time.UTC).Add(-time.Nanosecond)
)
// timeToExcelTime provides a function to convert time to Excel time.
func timeToExcelTime(t time.Time, date1904 bool) (float64, error) {
date := excelMinTime1900
if date1904 {
date = excel1904Epoc
}
if t.Before(date) {
return 0, nil
}
tt, diff, result := t, t.Sub(date), 0.0
for diff >= maxDuration {
result += float64(maxDuration / dayNanoseconds)
tt = tt.Add(-maxDuration)
diff = tt.Sub(date)
}
rem := diff % dayNanoseconds
result += float64(diff-rem)/float64(dayNanoseconds) + float64(rem)/float64(dayNanoseconds)
// Excel dates after 28th February 1900 are actually one day out.
// Excel behaves as though the date 29th February 1900 existed, which it didn't.
// Microsoft intentionally included this bug in Excel so that it would remain compatible with the spreadsheet
// program that had the majority market share at the time; Lotus 1-2-3.
// https://www.myonlinetraininghub.com/excel-date-and-time
if !date1904 && t.After(excelBuggyPeriodStart) {
result++
}
return result, nil
}
// shiftJulianToNoon provides a function to process julian date to noon.
func shiftJulianToNoon(julianDays, julianFraction float64) (float64, float64) {
switch {
case -0.5 < julianFraction && julianFraction < 0.5:
julianFraction += 0.5
case julianFraction >= 0.5:
julianDays++
julianFraction -= 0.5
case julianFraction <= -0.5:
julianDays--
julianFraction += 1.5
}
return julianDays, julianFraction
}
// fractionOfADay provides a function to return the integer values for hour,
// minutes, seconds and nanoseconds that comprised a given fraction of a day.
// values would round to 1 us.
func fractionOfADay(fraction float64) (hours, minutes, seconds, nanoseconds int) {
const (
c1us = 1e3
c1s = 1e9
c1day = 24 * 60 * 60 * c1s
)
frac := int64(c1day*fraction + c1us/2)
nanoseconds = int((frac%c1s)/c1us) * c1us
frac /= c1s
seconds = int(frac % 60)
frac /= 60
minutes = int(frac % 60)
hours = int(frac / 60)
return
}
// julianDateToGregorianTime provides a function to convert julian date to
// gregorian time.
func julianDateToGregorianTime(part1, part2 float64) time.Time {
part1I, part1F := math.Modf(part1)
part2I, part2F := math.Modf(part2)
julianDays := part1I + part2I
julianFraction := part1F + part2F
julianDays, julianFraction = shiftJulianToNoon(julianDays, julianFraction)
day, month, year := doTheFliegelAndVanFlandernAlgorithm(int(julianDays))
hours, minutes, seconds, nanoseconds := fractionOfADay(julianFraction)
return time.Date(year, time.Month(month), day, hours, minutes, seconds, nanoseconds, time.UTC)
}
// doTheFliegelAndVanFlandernAlgorithm; By this point generations of
// programmers have repeated the algorithm sent to the editor of
// "Communications of the ACM" in 1968 (published in CACM, volume 11, number
// 10, October 1968, p.657). None of those programmers seems to have found it
// necessary to explain the constants or variable names set out by Henry F.
// Fliegel and Thomas C. Van Flandern. Maybe one day I'll buy that journal and
// expand an explanation here - that day is not today.
func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) {
l := jd + 68569
n := (4 * l) / 146097
l = l - (146097*n+3)/4
i := (4000 * (l + 1)) / 1461001
l = l - (1461*i)/4 + 31
j := (80 * l) / 2447
d := l - (2447*j)/80
l = j / 11
m := j + 2 - (12 * l)
y := 100*(n-49) + i + l
return d, m, y
}
// timeFromExcelTime provides a function to convert an excelTime
// representation (stored as a floating point number) to a time.Time.
func timeFromExcelTime(excelTime float64, date1904 bool) time.Time {
var date time.Time
wholeDaysPart := int(excelTime)
// Excel uses Julian dates prior to March 1st 1900, and Gregorian
// thereafter.
if wholeDaysPart <= 61 {
const OFFSET1900 = 15018.0
const OFFSET1904 = 16480.0
const MJD0 float64 = 2400000.5
var date time.Time
if date1904 {
date = julianDateToGregorianTime(MJD0, excelTime+OFFSET1904)
} else {
date = julianDateToGregorianTime(MJD0, excelTime+OFFSET1900)
}
return date
}
floatPart := excelTime - float64(wholeDaysPart) + roundEpsilon
if date1904 {
date = excel1904Epoc
} else {
date = excel1900Epoc
}
durationPart := time.Duration(nanosInADay * floatPart)
date = date.AddDate(0, 0, wholeDaysPart).Add(durationPart)
if date.Nanosecond()/1e6 > 500 {
return date.Round(time.Second)
}
return date.Truncate(time.Second)
}
// ExcelDateToTime converts a float-based Excel date representation to a time.Time.
func ExcelDateToTime(excelDate float64, use1904Format bool) (time.Time, error) {
if excelDate < 0 {
return time.Time{}, newInvalidExcelDateError(excelDate)
}
return timeFromExcelTime(excelDate, use1904Format), nil
}
// isLeapYear determine if leap year for a given year.
func isLeapYear(y int) bool {
if y == y/400*400 {
return true
}
if y == y/100*100 {
return false
}
return y == y/4*4
}
// getDaysInMonth provides a function to get the days by a given year and
// month number.
func getDaysInMonth(y, m int) int {
if m == 2 && isLeapYear(y) {
return 29
}
return daysInMonth[m-1]
}
// validateDate provides a function to validate if a valid date by a given
// year, month, and day number.
func validateDate(y, m, d int) bool {
if m < 1 || m > 12 {
return false
}
if d < 1 {
return false
}
return d <= getDaysInMonth(y, m)
}
// formatYear converts the given year number into a 4-digit format.
func formatYear(y int) int {
if y < 1900 {
if y < 30 {
y += 2000
} else {
y += 1900
}
}
return y
}