xls icon indicating copy to clipboard operation
xls copied to clipboard

Numbers with some special formats reads as date

Open sergeilem opened this issue 7 years ago • 4 comments

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

sergeilem avatar Aug 18 '17 08:08 sergeilem

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

extrame avatar Sep 01 '17 06:09 extrame

Any news on this?

hielfx avatar Sep 27 '17 18:09 hielfx

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.

EtienneBruines avatar Feb 07 '18 08:02 EtienneBruines

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

extrame avatar Mar 08 '18 08:03 extrame