Numbers with some special formats reads as date

[sergeilem]

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).

[extrame]

yes, it is a complicated job, thanks for the information, I will check the xlrd code

[hielfx]

Any news on this?

[EtienneBruines]

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.

[extrame]

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,.