Read date/time from the cell

[vchugreev]

How to correctly read the date/time from the cell?

[vchugreev]

I will explain the problem:

In the Excel file there is a cell:
E7 => 29.06.2017 9:28:48
Format: "14-Mar-12"

I'm trying to get it...

e7 := xlsx.GetCellValue("Sheet1", "e7")
println(e7)

Output: "42915.395"

[xuri]

Do you mean Sheet1!E7 is string type value 29.06.2017 9:28:48? If so, string type value can't be formatted as a date format. Please provide file attachments If you can.

[vchugreev]

xlsx?
1_2017-06-29.xlsx

[xuri]

This library just support Excel's built-in formats currently. The built-in formats
index (numFmtId = 164) in the attachment is out of this range. Same as the issue #50. Try to set new number format for Sheet1!E7 before get value:

style, _ = xlsx.NewStyle(`{"number_format":15}`)
xlsx.SetCellStyle("Sheet1", "E7", "E7", style)
e7 := xlsx.GetCellValue("Sheet1", "E7")
println(e7) // 29-Jun-17

[vchugreev]

Now it is clear. Thank you!

[richardgarnier]

I am not sure I understand how SetCellStyle works.
On a comment above the function there is the following:

//     Index | Format String
//    -------+----------------------------------------------------
//     0     | General
//     1     | 0
//     2     | 0.00
//     3     | #,##0
//     4     | #,##0.00
//     5     | ($#,##0_);($#,##0)
//     6     | ($#,##0_);[Red]($#,##0)
//     7     | ($#,##0.00_);($#,##0.00)
//     8     | ($#,##0.00_);[Red]($#,##0.00)
//     9     | 0%
//     10    | 0.00%
//     11    | 0.00E+00
//     12    | # ?/?
//     13    | # ??/??
//     14    | m/d/yy
//     15    | d-mmm-yy
//     16    | d-mmm
//     17    | mmm-yy
//     18    | h:mm AM/PM
//     19    | h:mm:ss AM/PM
//     20    | h:mm
//     21    | h:mm:ss
//     22    | m/d/yy h:mm

With this in mind the following makes perfect sense

xlsx.NewStyle(`{"number_format":15}`) // 29-Jun-17

However, when I tried for m/d/yy, I get the following:

xlsx.NewStyle(`{"number_format":14}`) // 11-15-21

The format seems to match the comment, so I'm not sure what I am missing.

Bonus question:
Is there any way to get the year in a yyyy format instead?

[richardgarnier]

Nevermind, it is probably easier to convert the excel date to a time.Time.
In most cases, this can be done the following way:

// note: this is off by two days on the real epoch (1/1/1900) because
// - the days are 1 indexed so 1/1/1900 is 1 not 0
// - Excel pretends that Feb 29, 1900 existed even though it did not
// The following function will fail for dates before March 1st 1900
// Before that date the Julian calendar was used so a conversion would be necessary
var excelEpoch = time.Date(1899, time.December, 30, 0, 0, 0, 0, time.UTC)

func excelDateToDate(excelDate string) time.Time {
	var days, _ = strconv.Atoi(excelDate)
	return excelEpoch.Add(time.Second * time.Duration(days*86400))
}

[alibitek]

My date is in the format mm.dd.yyyy how can I parse it with this library ?

[fatindeed]

when upgrade v2.3.1 to v2.3.2
xlsx.GetCellValue() will return a date-time string, like Jan 1. It's hard to be converted to time.
So you must set cell style to 0, and get cell value again. Then it will return the float value and you can convert it to time.

err := xlsx.SetCellStyle("sheet1", "A1", "A1", 0)
if err != nil {
	// do something
}
label, err := xlsx.GetCellValue("sheet1", "A1")
if err != nil {
	// do something
}
f, err := strconv.ParseFloat(label, 64)
if err != nil {
	// do something
}
t, err = excelize.ExcelDateToTime(f, false)
if err != nil {
	// do something
}

[yeguacelestial]

My date is in the format mm.dd.yyyy how can I parse it with this library ?

You can change the format of the output date as follows, with Format method:

func ExcelSerialDateToTime(serial string) string {
	days, _ := strconv.Atoi(serial)

	convertedTime := excelEpoch.Add(time.Second * time.Duration(days*86400)).Format("01-2-2006")

	return convertedTime
}

[brunql]

ParseFloat works better if you need time not just days:

func toDate(excelDate string) time.Time {
	var days, _ = strconv.ParseFloat(excelDate, 64)
	return excelEpoch.Add(time.Second * time.Duration(days*86400))
}

[ITheCorgi]

Hy everyone, is there any update regarding date issue?
It still scans in time format 04-04-2023. I want it in same way as it was stored in excel column (it may any date format).
For example, I store in date column value '04.04.2023', but library scans as '04-04-2023'.
Is there any solution or workaroud to specify in config to scan date types as is like raw text?

[alisherin]

Hy everyone, is there any update regarding date issue? It still scans in time format 04-04-2023. I want it in same way as it was stored in excel column (it may any date format). For example, I store in date column value '04.04.2023', but library scans as '04-04-2023'. Is there any solution or workaroud to specify in config to scan date types as is like raw text?

parsedDate, parsedDateErr := time.Parse("01-02-2006", "04-04-2023")
if parsedDateErr != nil {
log.Println(somenthingYouWant)
}
formatThatYouWant := parsedDate.Format("01.02.2006")

Maybe someone give you better answer) Good luck