Conditional format unexpected turn negative number to positive #2594

New Issue

2020-10-08T03:27:56Z

Raccoonwao commented

2020-10-08 03:27:56 +00:00

(Migrated from github.com)

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

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 commented

2020-10-08 03:30:36 +00:00

(Migrated from github.com)

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

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

SheetJSDev commented

2020-10-08 03:40:16 +00:00

(Migrated from github.com)

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?

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 commented

2020-10-08 04:10:33 +00:00

(Migrated from github.com)

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

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 commented

2020-10-08 07:26:15 +00:00

(Migrated from github.com)

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"/>

Same problem: ```python >>> 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: ```xml <numFmt numFmtId="170" formatCode="[>=-1]0;0"/> ```

snoopyjc commented

2020-10-08 16:43:49 +00:00

(Migrated from github.com)

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 notifications@github.com 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.

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 <notifications@github.com> 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 commented

2021-10-04 07:23:17 +00:00

(Migrated from github.com)

Any update for the fix?

Sign in to join this conversation.