Bug 153993 - FORMATTING: ODF do not preserve embedded text and lowercase in scientific format
Summary: FORMATTING: ODF do not preserve embedded text and lowercase in scientific format
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Laurent Balland
URL:
Whiteboard: target:24.2.0 inReleaseNotes:24.2
Keywords:
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2023-03-05 22:43 UTC by Matt Keveney
Modified: 2024-07-16 08:36 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Matt Keveney 2023-03-05 22:43:20 UTC
-- Tested on
    MacOS Ventura 13.2.1
    Libreoffice 7.5.1.2, build: fcbaee479e84c6cd81291587d2ee68cba099e129

-- To reproduce:

Launch LibreOffice
Click "Create/Calc Spreadsheet"
Enter the following in cells A1-A3:
    123.456
    1234.56
    12345.6
Select these three cells
Click "Format/Cells..."
In the "Category" list, click "User-defined"
In Format code enter: "###.000 e0"  ..but without the quotes
Click the 'add' button (bearing the green checkmark)
Note that:
    the "Category" switches to "Scientific"
    the new format appears as expected
    the example is formatted as desired (with a lowercase 'e' preceeded by a space)
Under category, click "User-defined"
    Verify that the new format appears here as well.
Click OK
Verify that the three cells are formatted as desired:
  123.456 e0
    1.235 e3
   12.346 e3

Click "File/Save," give the document a name, and click "Save"
Close the file, then close Libreoffice
Reopen the file.
Note that the cells are now formatted as:
  123.456E0
    1.235E3
   12.346E3
Click "Format/Cells"
Note that the format has been altered to:
  ###.000E0" "

Note that this altered format appears in both categories: "User-defined" and "Scientific"

-- Expected result:

Format should be saved as entered.

Or, if the format is erroneous, it should be corrected _immediately_, rather than waiting until the document is saved/reopened.

-- Documentation

The documentation is not entirely clear on whether a space is acceptable before the "e" in this style format. (Though this reporter would appreciate it!)

The documentation _does_ say that a lowercase "e" should be OK, but LibreOffice does not preserve it.

-- similar bug?

Bugs 146404 and 152724 seem very similar, though this reporter can't be sure it's the same underlying issue.
Comment 1 ady 2023-03-06 02:22:17 UTC
(In reply to Matt Keveney from comment #0)
> Bugs 146404 and 152724 seem very similar, though this reporter can't be sure
> it's the same underlying issue.

CC'ing Laurent Balland, who has been reviewing several of these number format issues recently.

Bug 146404 is about the custom [COLORnn] format.
Bug 152724 is about the "_X" custom format, to leave a space equal to the width of that "X" character.

There are other several bug reports related to supporting custom number format codes that are supported by other spreadsheet software.

In this case, this seems to be related to space character(s) before the "E" (and/or "e") in the scientific number format. I am not sure whether this is also specific to the right-side of the number, or the decimal side of the number, or... 

The precise "definition" of the problem might depend on how exactly the code understands the use of the E/e for scientific notation, and whether this is allowed or supported in ODF.

Two questions:
A_ Have you tried testing with a dev. release or testing version?
B_ Have you tried the same steps using other software (e.g. AOO and/or Excel for MacOS or any other) to see how the behavior is there?
Comment 2 Matt Keveney 2023-03-06 04:01:21 UTC
> A_ Have you tried testing with a dev. release or testing version?
> B_ Have you tried the same steps using other software
> (e.g. AOO and/or Excel for MacOS or any other) to see how the
> behavior is there?

I just tried google sheets, since that was easiest.

It does not accept format I reported above.  Instead it says 'invalid format' right in the format editor, which would be a great thing to add to LibreOffice.

I _could_ however enter the equivalent format: ###.000 e-0

This behaves as expected:

    https://docs.google.com/spreadsheets/d/1ecDo2_sJ_ag33H7Yf4VhVSoR60u6d24Z7XsgraPgaDc

The format with the added '-' does not help in LibreOffice; again, it's replaced with ###.000E0" " on save/reopen.

Aside: this format is just a stopgap.  What I _really_ want is support for SI prefixes (k,M,G,T,P,m,µ,n,p,f) and so forth.  I'll make an entry at https://ask.libreoffice.org/tag/feature-request... just thought I'd put a bug in your ear _here_, while I have your attention :-).  Very useful for electronics calculations.  Thanks!
Comment 3 Matt Keveney 2023-03-06 04:33:13 UTC
added related feature request:
https://ask.libreoffice.org/t/please-add-support-for-si-prefixes-in-number-formats/88844
Comment 4 Matt Keveney 2023-03-06 05:49:50 UTC
> B_ Have you tried the same steps using other software
> (e.g. AOO and/or Excel for MacOS or any other) to see how the
> behavior is there?

Just tested Apache OpenOffice 4.1.14.
It has similar behavior, only worse!
It does not recognize the three ### as signifying engineering units, so the test data listed above initially displays as:

  123.456 e0
  123.456 e1
  123.456 e2

Then, after save/reload it looks like this:

  123.456E+0
  123.456E+1
  123.456E+2

And the format code has been changed to this:

    000.000E+0

If you place your cursor in the format code edit window, you can see that a _trailing_ space has been added to the format code, but no double-quote characters are visible as in LibreOffice.

so... please don't use the current OpenOffice code as a source of inspiration :).
Comment 5 Laurent Balland 2023-03-11 09:57:58 UTC
This bug about saving in ODF format has two parts:
- format code is always treated as uppercase. So, scientific format is always restored as "E" and not "e".
- Embedded text is not permitted in scientific format

For this special format, only XLSX permits to preserve it.

Sign for exponent in ODF was treated in bug 90133 and introduce in LibO since version 5.0.0
Comment 6 Laurent Balland 2023-04-21 14:37:28 UTC
(In reply to Matt Keveney from comment #2)
> Aside: this format is just a stopgap.  What I _really_ want is support for
> SI prefixes (k,M,G,T,P,m,µ,n,p,f) and so forth.  I'll make an entry at
> https://ask.libreoffice.org/tag/feature-request... just thought I'd put a
> bug in your ear _here_, while I have your attention :-).  Very useful for
> electronics calculations.  Thanks!
SI prefixes request is filled in bug 90708. However, I am not so enthusiast with SI prefixes, as it fails with combined unit: 1000 m² is NOT 1 km² (see bug 90708 comment 7)

But this actual request may be achieved with a new attributes to the format, containing a text replacement of scientific notation.
Comment 7 Matt Keveney 2023-04-21 20:34:51 UTC
> But this actual request may be achieved with a new attributes to the format,
> containing a text replacement of scientific notation.

I'm not following you.  Is this "text replacement" an _existing_ feature? ..or an alternate feature proposal?

If the former, please point me to the documentation.
Comment 8 Laurent Balland 2023-04-22 10:37:03 UTC
(In reply to Matt Keveney from comment #7)
> > But this actual request may be achieved with a new attributes to the format,
> > containing a text replacement of scientific notation.
> 
> I'm not following you.  Is this "text replacement" an _existing_ feature?
> ..or an alternate feature proposal?
> 
> If the former, please point me to the documentation.

No, it was just an idea how to fix it.
First of all, I planed to fix embedded text in scientific notation. See some progress here:
https://gerrit.libreoffice.org/c/core/+/150804
Comment 9 Commit Notification 2023-06-27 15:26:35 UTC
Laurent Balland committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8ef4d3a4160af967937e9bb7c60bee4a8eb71a1b

related tdf#153993 Embedded text in scientific numbers

It will be available in 24.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2023-11-01 08:11:12 UTC
Laurent Balland committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/cc721291cc4a53503398b82dd86baa521b2b13cc

tdf#153993 Extend ODF: lowercase for exponent char

It will be available in 24.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.