Generalized engineering notation in E formats don't work as expected #2578

Open
opened 2020-09-28 02:39:24 +00:00 by snoopyjc · 1 comment
snoopyjc commented 2020-09-28 02:39:24 +00:00 (Migrated from github.com)

Formats like ##0.0E+0 are a generalized form of Engineering notation, where the exponent is to be a multiple of the number of digits before the '.'. Using this specified format, Excel will give you the following results:

0: 000.0E+0
1: 1.0E+0
10: 10.0E+0
100: 100.0E+0
1000: 1.0E+3
10000: 10.0E+3
100000: 100.0E+3
1000000: 1.0E+6

It turns out that the number of format digits to the left of the decimal point do not represent the number of digits displayed--at least not directly, rather they represent, the multiple used to determine the exponent. If we use the format "0.00+E00" then the multiple is 1 (scientific format). The format "#0.00E+00" sets the multiple to 2 (exponents are -2, 0, 2, 4, etc.) The format "###0.00E+00" sets the multiple to 4 (exponents are -4, 0, 4, 8, etc.).

SSF should mirror this behavior.

Formats like ##0.0E+0 are a generalized form of Engineering notation, where the exponent is to be a multiple of the number of digits before the '.'. Using this specified format, Excel will give you the following results: 0: 000.0E+0 1: 1.0E+0 10: 10.0E+0 100: 100.0E+0 1000: 1.0E+3 10000: 10.0E+3 100000: 100.0E+3 1000000: 1.0E+6 It turns out that the number of format digits to the left of the decimal point do not represent the number of digits displayed--at least not directly, rather they represent, the multiple used to determine the exponent. If we use the format "0.00+E00" then the multiple is 1 (scientific format). The format "#0.00E+00" sets the multiple to 2 (exponents are -2, 0, 2, 4, etc.) The format "###0.00E+00" sets the multiple to 4 (exponents are -4, 0, 4, 8, etc.). SSF should mirror this behavior.
snoopyjc commented 2020-09-29 02:25:06 +00:00 (Migrated from github.com)

The code currently tries to do this properly but only handles a very limited case /^#+0.0E\+0$/ of formats, and it doesn't do the crazy thing with 0.0.

The code currently tries to do this properly but only handles a very limited case `/^#+0.0E\+0$/` of formats, and it doesn't do the crazy thing with 0.0.
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#2578
No description provided.