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
|
package excelize
import (
"fmt"
"testing"
"time"
"github.com/stretchr/testify/assert"
)
type dateTest struct {
ExcelValue float64
GoValue time.Time
}
var trueExpectedDateList = []dateTest{
{0.0000000000000000, time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)},
{25569.000000000000, time.Unix(0, 0).UTC()},
// Expected values extracted from real spreadsheet
{1.0000000000000000, time.Date(1900, time.January, 1, 0, 0, 0, 0, time.UTC)},
{1.0000115740740740, time.Date(1900, time.January, 1, 0, 0, 1, 0, time.UTC)},
{1.0006944444444446, time.Date(1900, time.January, 1, 0, 1, 0, 0, time.UTC)},
{1.0416666666666667, time.Date(1900, time.January, 1, 1, 0, 0, 0, time.UTC)},
{2.0000000000000000, time.Date(1900, time.January, 2, 0, 0, 0, 0, time.UTC)},
{43269.000000000000, time.Date(2018, time.June, 18, 0, 0, 0, 0, time.UTC)},
{43542.611111111109, time.Date(2019, time.March, 18, 14, 40, 0, 0, time.UTC)},
{401769.00000000000, time.Date(3000, time.January, 1, 0, 0, 0, 0, time.UTC)},
}
var excelTimeInputList = []dateTest{
{0.0, time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)},
{60.0, time.Date(1900, 2, 28, 0, 0, 0, 0, time.UTC)},
{61.0, time.Date(1900, 3, 1, 0, 0, 0, 0, time.UTC)},
{41275.0, time.Date(2013, 1, 1, 0, 0, 0, 0, time.UTC)},
{44450.3333333333, time.Date(2021, time.September, 11, 8, 0, 0, 0, time.UTC)},
{401769.0, time.Date(3000, 1, 1, 0, 0, 0, 0, time.UTC)},
}
func TestTimeToExcelTime(t *testing.T) {
for i, test := range trueExpectedDateList {
t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
excelTime, err := timeToExcelTime(test.GoValue)
assert.NoError(t, err)
assert.Equalf(t, test.ExcelValue, excelTime,
"Time: %s", test.GoValue.String())
})
}
}
func TestTimeToExcelTime_Timezone(t *testing.T) {
location, err := time.LoadLocation("America/Los_Angeles")
if !assert.NoError(t, err) {
t.FailNow()
}
for i, test := range trueExpectedDateList {
t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
_, err := timeToExcelTime(test.GoValue.In(location))
assert.NoError(t, err)
})
}
}
func TestTimeFromExcelTime(t *testing.T) {
for i, test := range excelTimeInputList {
t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
assert.Equal(t, test.GoValue, timeFromExcelTime(test.ExcelValue, false))
})
}
for hour := 0; hour < 24; hour++ {
for min := 0; min < 60; min++ {
for sec := 0; sec < 60; sec++ {
date := time.Date(2021, time.December, 30, hour, min, sec, 0, time.UTC)
excelTime, err := timeToExcelTime(date)
assert.NoError(t, err)
dateOut := timeFromExcelTime(excelTime, false)
assert.EqualValues(t, hour, dateOut.Hour())
assert.EqualValues(t, min, dateOut.Minute())
assert.EqualValues(t, sec, dateOut.Second())
}
}
}
}
func TestTimeFromExcelTime_1904(t *testing.T) {
_, _ = shiftJulianToNoon(1, -0.6)
timeFromExcelTime(61, true)
timeFromExcelTime(62, true)
}
func TestExcelDateToTime(t *testing.T) {
// Check normal case
for i, test := range excelTimeInputList {
t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
timeValue, err := ExcelDateToTime(test.ExcelValue, false)
assert.Equal(t, test.GoValue, timeValue)
assert.NoError(t, err)
})
}
// Check error case
_, err := ExcelDateToTime(-1, false)
assert.EqualError(t, err, newInvalidExcelDateError(-1).Error())
}
|
