About Excel two special dates: 1900/1/0 1900/2/29

[CHANTXU64]

Description

Excel中有两个特殊日期：1900/1/0（值为0）、1900/2/29（值为50）。如果我要在xlsx文件中设置这两个值我该如何设置呢（比如我只想设置时间，在Excel中日期就会默认成1900/1/0，excelize就无法设置一模一样的值）？SetCellValue设置日期格式好像只能用time.Time来设置，而golang的time.Time不包含这两个特殊日期。

ExcelDateToTime也无法正确处理这两个日期，且文档内未说明。

Excelize version or commit ID:

version 2.6.0

Environment details (OS, Microsoft Excel™ version, physical, etc.):
macOS: Microsoft Excel, WPS

[xuri]

The value of date 1900/2/29 is 60, you can set number for the cell, then create and bind the custom time and date number format to the cells. For example, set the date value for A1 and A2:

fmtCode := "yyyy/mm/dd;@"
styleID, err := f.NewStyle(&excelize.Style{CustomNumFmt: &fmtCode})
if err != nil {
    fmt.Println(err)
}
if err := f.SetCellValue("Sheet1", "A1", 0); err != nil {
    fmt.Println(err)
}
if err := f.SetCellValue("Sheet1", "A2", 60); err != nil {
    fmt.Println(err)
}
if err := f.SetCellStyle("Sheet1", "A1", "A2", styleID); err != nil {
    fmt.Println(err)
}

[CHANTXU64]

对于设置一个单元格的值，使用SetCellValue，但要设置这两个特殊日期，为什么还要特地使用SetCellStyle修改样式？我认为这不合理，而且还会改变原有的样式。能不能增加一个SetCellSpecialDate之类的方法，在不改变原有方法的前提下能够设置这两个特殊日期？

[xuri]

Thanks for your advice. The function SetCellValue is also implemented in two-part as in the example above up, and it only set the number format for the cell which uses the default styles to avoid breaking the existing styles. I think that not required to introduce a new exported function, and I'd like to update comments in the documentation for the developers who wanna set these special dates.

[CHANTXU64]

明白了，谢谢。我还有个问题，如果我想设置某个单元格为“2022-01-01”，那么我如何知道这个表是不是启用了“date1904”呢？

[xuri]

I have added 1900 or 1904 date system support for getting cell value, and now you can check if the 1904 date system is enabled via GetWorkbookPrOptions like this:

var date1904 excelize.Date1904
if err := f.GetWorkbookPrOptions(&date1904); err != nil {
    fmt.Println(err)
}
fmt.Printf("date1904: %t\n", date1904)

[CHANTXU64]

timeToExcelTime() 未处理date1904。

func TestDate1904(t *testing.T) {
  file := excelize.NewFile()
  var date1904 excelize.Date1904 = true
  if err := file.SetWorkbookPrOptions(&date1904); err != nil {
    fmt.Println(err)
  }
  tt, _ := time.Parse("2006-01-02 15:04:05", "2000-01-01 00:00:00")
  if err := file.SetCellValue("Sheet1", "A1", tt); err != nil {
    fmt.Println(err)
  }
  value, _ := file.GetCellValue("Sheet1", "A1", excelize.Options{RawCellValue: true})
  assert.Equal(t, "35064", value)
  assert.NotEqual(t, "36526", value)
  file.Close()
}

[xuri]

I have let set cell value support 1904 date system in commit eed431e. Please upgrade to the master branch code.

[CHANTXU64]

Thank you!