Conditional format unexpected turn negative number to positive

[Raccoonwao]

Negative number is wrongly formatted to positive number in a condition format

Format: [>=1000000]#,,\" M\";####.00 Value: -12.3

Actual: 12.3 Expected: -12.3

Sample Program

console.log(`${value} => ${X.SSF.format([>=1000000]#,," M";####.00, -12.3)}\t\t format: ${format}``)

Seems negative value is A) converted to positive at https://github.com/SheetJS/sheetjs/blob/master/xlsx.js#L1012 but B) not converted back at https://github.com/SheetJS/sheetjs/blob/master/xlsx.js#L1062

The latter expects a format of 'n?' which I don't quite understand

[Raccoonwao]

General works though [>=1000000]#,,\" M\";####.00 => 12.3 \\ wrong [>=1000000]#,,\" M\";General => -12.3 \\ correct

[SheetJSDev]

The format has 2 parts. Normally, without a conditional, the second part handles negative values. As an example, using the format 0;0, -1 is actually rendered as "1" (no negative sign).

The conditionals are actually weird here and need a rethink. In specific, suppose the value is -2. Using the format [>=-1]0;0 Excel prints "2", but using the format [>=1]0;0 Excel prints "-2".

@snoopyjc any thoughts?

[snoopyjc]

Yes this is a very complex area of Excel. I have this working in my python version:

>>> from ssf import SSF
>>> ssf = SSF()
>>> ssf.format('[>=1000000]#,,\" M\";####.00', -12.3)
'-12.30'
>>> ssf.format('[>=1000000]#,,\" M\";General', -12.3)
'-12.3'

@SheetJSDev If you want to back-port it to JS, look at the last block of code in _check_fmt() around line 2989, plus the new _negcond() routine, and check where I call it in the '[' match part of _eval_fmt(), around line 2413.

https://github.com/snoopyjc/ssf/blob/master/ssf/ssf.py

[SheetJSDev]

Same problem:

>>> ssf.format('[>=-1]0;0', -2)
'-2'

According to Excel 2019 for Mac and Excel 2019 for Windows, using the format [>=-1]0;0, -2 is rendered as "2" (no negative sign)

There's no UI chicanery, as an XLSX file with the format has the following representation in the XML:

    <numFmt numFmtId="170" formatCode="[>=-1]0;0"/>

[snoopyjc]

Thanks! I will fix it and add more test cases.

-joe Sent from my AT&T iPhone

On Oct 8, 2020, at 3:26 AM, SheetJSDev [email protected] wrote:

 Same problem:

ssf.format('[>=-1]0;0', -2) '-2' According to Excel 2019 for Mac and Excel 2019 for Windows, using the format [>=-1]0;0, -2 is rendered as "2" (no negative sign)

There's no UI chicanery, as an XLSX file with the format has the following representation in the XML:

<numFmt numFmtId="170" formatCode="[>=-1]0;0"/>

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[Raccoonwao]

Any update for the fix?