Error on format #,##.??;(#,##.??);0 #2551

New Issue

2015-02-24T06:34:13Z

wilg commented

2015-02-24 06:34:13 +00:00

(Migrated from github.com)

Seems to work in OpenOffice

From: http://superuser.com/a/205763

Seems to work in OpenOffice From: http://superuser.com/a/205763

SheetJSDev commented

2015-02-24 17:47:35 +00:00

(Migrated from github.com)

@wilg there are actually two issues here.

Excel magic: If you input that manually into Excel 2013, it autocorrects to #,###.??;(#,###.??);0 (this may very well be locale-specific). That type of massaging is currently not implemented, mostly because I haven't really stress tested the formats.

I will raise a different issue to continue this discussion.

The format #,###.?? is not considered valid. That's clearly a bug. There are two reasons why the parser bails:

A) There is no required character (like 0 or ?) to the left of the decimal point. Based on my initial interpretation of the grammar in [MS-XLS] 2.4.126 the format #,###. by itself is not valid (irrespective of the suffix). Excel clearly allows it, which means we should support it.

B) The ? after the decimal place is handled differently in different versions of Excel. Consider the value 0 with format #.0?0?#?#?#?# (why someone would do this is beyond me, but it's instructive for understanding what's happening internally). Excel 2011 renders this as .0 0, Excel '95 and LO render this as .0 0 0 0 0

The fix for (A) is straightforward, but (B) requires a decision to be made. Probably the best solution is to have applications specify a version of Excel (the files themselves store that information, so there's no real guessing involved)

@wilg there are actually two issues here. 1) Excel magic: If you input that manually into Excel 2013, it autocorrects to `#,###.??;(#,###.??);0` (this may very well be locale-specific). That type of massaging is currently not implemented, mostly because I haven't really stress tested the formats. I will raise a different issue to continue this discussion. 2) The format `#,###.??` is not considered valid. That's clearly a bug. There are two reasons why the parser bails: A) There is no required character (like `0` or `?`) to the left of the decimal point. Based on my initial interpretation of the grammar in [MS-XLS] 2.4.126 the format `#,###.` by itself is not valid (irrespective of the suffix). Excel clearly allows it, which means we should support it. B) The `?` after the decimal place is handled differently in different versions of Excel. Consider the value `0` with format `#.0?0?#?#?#?#` (why someone would do this is beyond me, but it's instructive for understanding what's happening internally). Excel 2011 renders this as `.0 0`, Excel '95 and LO render this as `.0 0 0 0 0` The fix for (A) is straightforward, but (B) requires a decision to be made. Probably the best solution is to have applications specify a version of Excel (the files themselves store that information, so there's no real guessing involved)

Sign in to join this conversation.