date_test.go

   1 package excelize
   2
   3 import (
   4         "fmt"
   5         "testing"
   6         "time"
   7
   8         "github.com/stretchr/testify/assert"
   9 )
  10
  11 type dateTest struct {
  12         ExcelValue float64
  13         GoValue    time.Time
  14 }
  15
  16 var trueExpectedDateList = []dateTest{
  17         {0.0000000000000000, time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)},
  18         {25569.000000000000, time.Unix(0, 0).UTC()},
  19
  20         // Expected values extracted from real spreadsheet
  21         {1.0000000000000000, time.Date(1900, time.January, 1, 0, 0, 0, 0, time.UTC)},
  22         {1.0000115740740740, time.Date(1900, time.January, 1, 0, 0, 1, 0, time.UTC)},
  23         {1.0006944444444446, time.Date(1900, time.January, 1, 0, 1, 0, 0, time.UTC)},
  24         {1.0416666666666667, time.Date(1900, time.January, 1, 1, 0, 0, 0, time.UTC)},
  25         {2.0000000000000000, time.Date(1900, time.January, 2, 0, 0, 0, 0, time.UTC)},
  26         {43269.000000000000, time.Date(2018, time.June, 18, 0, 0, 0, 0, time.UTC)},
  27         {43542.611111111109, time.Date(2019, time.March, 18, 14, 40, 0, 0, time.UTC)},
  28         {401769.00000000000, time.Date(3000, time.January, 1, 0, 0, 0, 0, time.UTC)},
  29 }
  30
  31 var excelTimeInputList = []dateTest{
  32         {0.0, time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)},
  33         {60.0, time.Date(1900, 2, 28, 0, 0, 0, 0, time.UTC)},
  34         {61.0, time.Date(1900, 3, 1, 0, 0, 0, 0, time.UTC)},
  35         {41275.0, time.Date(2013, 1, 1, 0, 0, 0, 0, time.UTC)},
  36         {44450.3333333333, time.Date(2021, time.September, 11, 8, 0, 0, 0, time.UTC)},
  37         {401769.0, time.Date(3000, 1, 1, 0, 0, 0, 0, time.UTC)},
  38 }
  39
  40 func TestTimeToExcelTime(t *testing.T) {
  41         for i, test := range trueExpectedDateList {
  42                 t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
  43                         excelTime, err := timeToExcelTime(test.GoValue, false)
  44                         assert.NoError(t, err)
  45                         assert.Equalf(t, test.ExcelValue, excelTime,
  46                                 "Time: %s", test.GoValue.String())
  47                 })
  48         }
  49 }
  50
  51 func TestTimeToExcelTime_Timezone(t *testing.T) {
  52         location, err := time.LoadLocation("America/Los_Angeles")
  53         if !assert.NoError(t, err) {
  54                 t.FailNow()
  55         }
  56         for i, test := range trueExpectedDateList {
  57                 t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
  58                         _, err := timeToExcelTime(test.GoValue.In(location), false)
  59                         assert.NoError(t, err)
  60                 })
  61         }
  62 }
  63
  64 func TestTimeFromExcelTime(t *testing.T) {
  65         for i, test := range excelTimeInputList {
  66                 t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
  67                         assert.Equal(t, test.GoValue, timeFromExcelTime(test.ExcelValue, false))
  68                 })
  69         }
  70         for hour := 0; hour < 24; hour++ {
  71                 for min := 0; min < 60; min++ {
  72                         for sec := 0; sec < 60; sec++ {
  73                                 date := time.Date(2021, time.December, 30, hour, min, sec, 0, time.UTC)
  74                                 // Test use 1900 date system
  75                                 excel1900Time, err := timeToExcelTime(date, false)
  76                                 assert.NoError(t, err)
  77                                 date1900Out := timeFromExcelTime(excel1900Time, false)
  78                                 assert.EqualValues(t, hour, date1900Out.Hour())
  79                                 assert.EqualValues(t, min, date1900Out.Minute())
  80                                 assert.EqualValues(t, sec, date1900Out.Second())
  81                                 // Test use 1904 date system
  82                                 excel1904Time, err := timeToExcelTime(date, true)
  83                                 assert.NoError(t, err)
  84                                 date1904Out := timeFromExcelTime(excel1904Time, true)
  85                                 assert.EqualValues(t, hour, date1904Out.Hour())
  86                                 assert.EqualValues(t, min, date1904Out.Minute())
  87                                 assert.EqualValues(t, sec, date1904Out.Second())
  88                         }
  89                 }
  90         }
  91 }
  92
  93 func TestTimeFromExcelTime_1904(t *testing.T) {
  94         julianDays, julianFraction := shiftJulianToNoon(1, -0.6)
  95         assert.Equal(t, julianDays, 0.0)
  96         assert.Equal(t, julianFraction, 0.9)
  97         julianDays, julianFraction = shiftJulianToNoon(1, 0.1)
  98         assert.Equal(t, julianDays, 1.0)
  99         assert.Equal(t, julianFraction, 0.6)
 100         assert.Equal(t, timeFromExcelTime(61, true), time.Date(1904, time.March, 2, 0, 0, 0, 0, time.UTC))
 101         assert.Equal(t, timeFromExcelTime(62, true), time.Date(1904, time.March, 3, 0, 0, 0, 0, time.UTC))
 102 }
 103
 104 func TestExcelDateToTime(t *testing.T) {
 105         // Check normal case
 106         for i, test := range excelTimeInputList {
 107                 t.Run(fmt.Sprintf("TestData%d", i+1), func(t *testing.T) {
 108                         timeValue, err := ExcelDateToTime(test.ExcelValue, false)
 109                         assert.Equal(t, test.GoValue, timeValue)
 110                         assert.NoError(t, err)
 111                 })
 112         }
 113         // Check error case
 114         _, err := ExcelDateToTime(-1, false)
 115         assert.EqualError(t, err, newInvalidExcelDateError(-1).Error())
 116 }