diff options
| -rw-r--r-- | date.go | 23 | ||||
| -rw-r--r-- | date_test.go | 42 |
2 files changed, 64 insertions, 1 deletions
@@ -15,7 +15,20 @@ func timeToUTCTime(t time.Time) time.Time { // timeToExcelTime provides function to convert time to Excel time. func timeToExcelTime(t time.Time) float64 { - return float64(t.UnixNano())/8.64e13 + 25569.0 + // TODO in future this should probably also handle date1904 and like TimeFromExcelTime + var excelTime float64 + excelTime = 0 + // check if UnixNano would be out of int64 range + for t.Unix() > 9223372036 { + // reduce by aprox. 290 years, which is max for int64 nanoseconds + deltaDays := 290 * 364 + delta := time.Duration(deltaDays * 8.64e13) + excelTime = excelTime + float64(deltaDays) + t = t.Add(-delta) + } + // finally add remainder of UnixNano to keep nano precision + // and 25569 which is days between 1900 and 1970 + return excelTime + float64(t.UnixNano())/8.64e13 + 25569.0 } // shiftJulianToNoon provides function to process julian date to noon. @@ -90,6 +103,7 @@ func doTheFliegelAndVanFlandernAlgorithm(jd int) (day, month, year int) { // timeFromExcelTime provides function to convert an excelTime representation // (stored as a floating point number) to a time.Time. func timeFromExcelTime(excelTime float64, date1904 bool) time.Time { + const MDD int64 = 106750 // Max time.Duration Days, aprox. 290 years var date time.Time var intPart = int64(excelTime) // Excel uses Julian dates prior to March 1st 1900, and Gregorian @@ -113,6 +127,13 @@ func timeFromExcelTime(excelTime float64, date1904 bool) time.Time { } else { date = time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC) } + + // Duration is limited to aprox. 290 years + for intPart > MDD { + durationDays := time.Duration(MDD) * time.Hour * 24 + date = date.Add(durationDays) + intPart = intPart - MDD + } durationDays := time.Duration(intPart) * time.Hour * 24 durationPart := time.Duration(dayNanoSeconds * floatPart) return date.Add(durationDays).Add(durationPart) diff --git a/date_test.go b/date_test.go new file mode 100644 index 0000000..bf071e0 --- /dev/null +++ b/date_test.go @@ -0,0 +1,42 @@ +package excelize + +import ( + "testing" + "time" +) + +type dateTest struct { + ExcelValue float64 + GoValue time.Time +} + +func TestTimeToExcelTime(t *testing.T) { + trueExpectedInputList := []dateTest { + {0.0, time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)}, + {25569.0, time.Unix(0, 0)}, + {43269.0, time.Date(2018, 6, 18, 0, 0, 0, 0, time.UTC)}, + {401769.0, time.Date(3000, 1, 1, 0, 0, 0, 0, time.UTC)}, + } + + for _, test := range trueExpectedInputList { + if test.ExcelValue != timeToExcelTime(test.GoValue) { + t.Fatalf("Expected %v from %v = true, got %v\n", test.ExcelValue, test.GoValue, timeToExcelTime(test.GoValue)) + } + } +} + +func TestTimeFromExcelTime(t *testing.T) { + trueExpectedInputList := []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)}, + {401769.0, time.Date(3000, 1, 1, 0, 0, 0, 0, time.UTC)}, + } + + for _, test := range trueExpectedInputList { + if test.GoValue != timeFromExcelTime(test.ExcelValue, false) { + t.Fatalf("Expected %v from %v = true, got %v\n", test.GoValue, test.ExcelValue, timeFromExcelTime(test.ExcelValue, false)) + } + } +} |