Date format 55 not detected as a date in .xls file

[appel1]

Looks like we're missing some built-in formats. 0-163 are reserved for built-in formats but we only have up to 49. 55 looks to be a date when saving with Excel on a computer set to Japanese.

Need to find out if these are documented anywhere.

[appel1]

From openoffice.org:

27 Date [$-0411]GE.M.D 			50 Date [$-0411]GE.M.D
28 Date [$-0411]GGGE年 M 月 D 日 	51 Date [$-0411]GGGE年 M 月 D 日
29 Date [$-0411]GGGE年 M 月 D 日 	52 Date [$-0411]YYYY 年 M 月
30 Date [$-0411]M/D/YY 			53 Date [$-0411]M 月 D 日
31 Date [$-0411]YYYY 年 M 月 D 日 	54 Date [$-0411]GGGE年 M 月 D 日
32 Time [$-0411]h 時 mm分 		55 Date [$-0411]YYYY 年 M 月
33 Time [$-0411]h"時"mm"分"ss"秒" 	56 Date [$-0411]M 月 D 日
34 Date [$-0411]YYYY 年 M 月 		57 Date [$-0411]GE.M.D
35 Date [$-0411]M 月 D 日 		58 Date [$-0411]GGGE年 M 月 D 日
36 Date [$-0411]GE.M.D

According to [ECMA-376] part 4, section 3.8.30, these are different depending on the current locale. So one set of values when current locale is CHT, JPN or KOR. In xlsx files there seems to be an attribute for this, but which one does old Excel versions use?

[appel1]

Looks like NumberFormat.Parser class needs to be aware of the locale as specified between the [] characters. So we correctly handle the characters G and E in the above formats. See [ECMA-376] part 4, section 3.8.30.

Apparently when using Japanese characters you don't have to enclose them in ". What are the rules here?

@andersnm Do you have any suggestions on how to best handle this?

[appel1]

Based on a quick test Excel allows you to use pretty much anything as a literal without quotes unless it means something in the format syntax.

[andersnm]

Hi @appel1,

Here's the same literals issue in the ExcelNumberFormat bugtracker: andersnm/ExcelNumberFormat#30

I started looking into it, but couldn't find a quick fix so its in the backlog for now.

[appel1]

Thai from [ECMA-376]

59	t0
60	t0.00
61	t#,##0
62	t#,###0.00

67	t0%
68	t0.00%
69	t# ?/?
70	t# ??/??
71	ว/ด/ปปปป
72	ว-ดดด-ปป
73	ว-ดดด
74	ดดด-ปป
75	ช:นน
76	ช:นน:ทท
77	ว/ด/ปปปป ช:นน
78	นน:ทท
79	[ช]:นน:ทท
80	นน:ทท.0
81	d/m/bb

[appel1]

Perhaps this can be solved in two parts. The first where we change to determine whether a cell is a DateTime by looking at the number format index for the built-in formats instead of going via the format string.

Then we'd need to change GetNumberFormatString to be culture aware and also provide an overload where you specify a culture. Not sure how to handle the breaking change since it looks like the formats that are hardcoded now are en-us or something similar?

[appel1]

Related to #543