xls
xls copied to clipboard
Numbers with some special formats reads as date
When user uses specific format for number, for example format "# ##0.00_ ;[Red]-# ##0.00" (users love this format), this number will be read as date. This is because XfRk.String() reads as date all numbers with formatNo>=164 and above format have formatNo=190. It would be better to check format string and if it contains "#" or ".00", read it as a number.
col.go:
func (xf *XfRk) String(wb *WorkBook) string {
idx := int(xf.Index)
if len(wb.Xfs) > idx {
fNo := wb.Xfs[idx].formatNo()
if fNo >= 164 { // user defined format
if fomt := wb.Formats[fNo]; fomt != nil {
if (strings.Contains(fomt.str, "#") || strings.Contains(fomt.str, ".00")){
//If format contains # or .00 then this is a number
return xf.Rk.String()
}else{
i, f, isFloat := xf.Rk.number()
if !isFloat {
f = float64(i)
}
t := timeFromExcelTime(f, wb.dateMode == 1)
return t.Format(time.RFC3339) //TODO it should be international and format as the describled style
}
}
// see http://www.openoffice.org/sc/excelfileformat.pdf
} else if 14 <= fNo && fNo <= 17 || fNo == 22 || 27 <= fNo && fNo <= 36 || 50 <= fNo && fNo <= 58 { // jp. date format
i, f, isFloat := xf.Rk.number()
if !isFloat {
f = float64(i)
}
t := timeFromExcelTime(f, wb.dateMode == 1)
return t.Format(time.RFC3339) //TODO it should be international
}
}
return xf.Rk.String()
}
Also extra import needed (strings).
Or you can implement more precise heuristics, take a look at is_date_format_string function from python's xlrd here https://github.com/python-excel/xlrd/blob/master/xlrd/formatting.py (line 457).
yes, it is a complicated job, thanks for the information, I will check the xlrd code
Any news on this?
As not needing dates myself, I added && false
to line 55 in col.go
to prevent it from formatting it as a date.
This is why sometimes the resulting string is General
.
My knowledge is unfortunately not enough to provide an actual fix for this.
anyone want this feature,please use the branch#new_formatter. It is a unfinished branch, but can be used for basic number and data output,.