Format ;($0.0) #2559

Open
opened 2017-06-22 14:39:14 +00:00 by hy9be · 7 comments
hy9be commented 2017-06-22 14:39:14 +00:00 (Migrated from github.com)
SSF.format('[<=-1000](0.0,"K");[<0]($0.0)', -200);

returns

(20$0.0)
``` SSF.format('[<=-1000](0.0,"K");[<0]($0.0)', -200); ``` returns ``` (20$0.0) ```
SheetJSDev commented 2017-06-22 16:53:13 +00:00 (Migrated from github.com)

@hy9be can confirm that is an issue as part of the second part of a conditional format. Simpler repro:

SSF.format(';($0.0)', -200);
@hy9be can confirm that is an issue as part of the second part of a conditional format. Simpler repro: ```js SSF.format(';($0.0)', -200); ```
hy9be commented 2017-06-27 14:19:53 +00:00 (Migrated from github.com)

@SheetJSDev Are you guys looking into it?

I could also try to fix it if you guys can give me some hint. The source codes do not have many comments. That makes it a bit hard to debug for me.

@SheetJSDev Are you guys looking into it? I could also try to fix it if you guys can give me some hint. The source codes do not have many comments. That makes it a bit hard to debug for me.
n8agrin commented 2017-11-02 16:26:46 +00:00 (Migrated from github.com)
The issue stems from this line https://github.com/SheetJS/ssf/commit/d273a28d54b3f0478e568e59b0d9159c68a8731d#diff-2bf8a02964fc7c103bafd1eea1a85abdL126
n8agrin commented 2017-11-03 16:17:42 +00:00 (Migrated from github.com)

I spent several hours yesterday trying to dig into this and come up with a fix, but was ultimately defeated by the complexity of the code.

@SheetJSDev do you guys have some kind of grammar from which this code is generated? I found it really difficult to navigate with so many conditionals and single letter variables to juggle. I'm assuming everything in this repo is machine generated. If you're willing to let go of the source, I'd be happy to continue to dig in here.

I spent several hours yesterday trying to dig into this and come up with a fix, but was ultimately defeated by the complexity of the code. @SheetJSDev do you guys have some kind of grammar from which this code is generated? I found it really difficult to navigate with so many conditionals and single letter variables to juggle. I'm assuming everything in this repo is machine generated. If you're willing to let go of the source, I'd be happy to continue to dig in here.
SheetJSDev commented 2017-11-03 17:35:11 +00:00 (Migrated from github.com)

@n8agrin The "official grammar" is in [MS-XLS] 2.4.126 and there's an attempt to describe the behavior in ECMA-376 18.8.30-18.8.31 (pages 1776-1792 of the Fifth Edition PDF, I exported the relevant pages in a new PDF linked at the bottom of this comment).

We started from there and quickly found it diverged from actual Excel behavior (our general opinion is that it's generally correct for the standard format codes but quickly degrades for anything remotely nonstandard). Incidentally, even Excel Online doesn't support custom number formats, probably because they also succumbed to the mess.

The code you're looking at written by hand, and in retrospect we probably should have used more descriptive variable names. We're going to try to clean it up soon.

The error in this case is in how the overflow is handled with the currency symbol. There's a weird trick in the eval_fmt function to determine where the decimal point starts and draw digits. The reason for this trick is apparent when considering formats like ###0 "Million" 000 "Thousand" 0 "Hundred" 00:

screen shot 2017-11-03 at 13 30 26

Ecma Office Open XML Part 1 - Fundamentals And Markup Language Reference.pdf

@n8agrin The "official grammar" is in [[MS-XLS] 2.4.126](https://msdn.microsoft.com/en-us/library/dd944946.aspx) and there's an attempt to describe the behavior in ECMA-376 18.8.30-18.8.31 (pages 1776-1792 of the Fifth Edition PDF, I exported the relevant pages in a new PDF linked at the bottom of this comment). We started from there and quickly found it diverged from actual Excel behavior (our general opinion is that it's generally correct for the standard format codes but quickly degrades for anything remotely nonstandard). Incidentally, even Excel Online doesn't support custom number formats, probably because they also succumbed to the mess. The code you're looking at written by hand, and in retrospect we probably should have used more descriptive variable names. We're going to try to clean it up soon. The error in this case is in how the overflow is handled with the currency symbol. There's a weird trick in the [`eval_fmt` function](https://github.com/SheetJS/ssf/blob/master/bits/82_eval.js#L137) to determine where the decimal point starts and draw digits. The reason for this trick is apparent when considering formats like `###0 "Million" 000 "Thousand" 0 "Hundred" 00`: <img width="447" alt="screen shot 2017-11-03 at 13 30 26" src="https://user-images.githubusercontent.com/6070939/32387554-356671ee-c09b-11e7-8506-7aa9c6f3ff85.png"> [Ecma Office Open XML Part 1 - Fundamentals And Markup Language Reference.pdf](https://github.com/SheetJS/ssf/files/1441889/Ecma.Office.Open.XML.Part.1.-.Fundamentals.And.Markup.Language.Reference.pdf)
jgdovin commented 2019-08-29 13:31:00 +00:00 (Migrated from github.com)

This is still a large issue for a project we are working on. I plan to look at it in the future but if anyone has a fix ready for this that would be great.

This is still a large issue for a project we are working on. I plan to look at it in the future but if anyone has a fix ready for this that would be great.
cubewise-tryan commented 2019-12-05 06:40:08 +00:00 (Migrated from github.com)

I was having the same issue, I have created a pull request with a fix SheetJS/ssf#39

I was having the same issue, I have created a pull request with a fix SheetJS/ssf#39
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#2559
No description provided.