OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Re: [office-comment] Formatting in spreadsheet


I tested all of this in LibreOffice Calc. I got the next phraze from Brian Barker <b.m.barker@btinternet.com> in the Libre Office forum (users@global.libreoffice.org):

The definition of ODF says that the format code is "a sequence of characters with an implementation-defined meaning", so clearly it cannot be saved in the document file exactly as you enter and see it. A bit of experimentation suggests that instead, an explanation of the format is included in the "styles" element, and this allows only such things as "decimal-places", "grouping" (whether you want the thousands separator), "currency-symbol", "min-exponent-digits", and so on. This would suggest that your desired format, although acted upon by Calc, indeed cannot be saved in an .ods file and is lost when you attempt this.

I just asked him to specify where this is found. This is what I got back from him:

Op 3 jul. 2014, om 16:32 heeft Brian Barker het volgende geschreven:
At 15:46 03/07/2014 +0200, you wrote:
Could you please provide me info as to where in the ODF standards the styles element is described?

I didn't know anything about this, so I looked on the web. All I could find was the definition of the TEXT() function, whose second argument is indeed the same sort of format string and must have to follow the same syntax. This was at
http://docs.oasis-open.org/office/v1.2/cos01/OpenDocument-v1.2-cos01-part2.html#__RefHeading__1018880_715980110 . This implies that the zeroes and hashes and so on cannot be saved in documents.

I also created a spreadsheet document with your format and other similar ones, and unpacked and looked inside the resulting .ods files. That's where I found the XML document representation, including "decimal-places", "grouping", "currency-symbol", "min-exponent-digits", and so on. For your unusual format to be represented in this form, there would have to be a corresponding quantitative description for conditional trailing digits, and I deduce that there isn't. You can no doubt find descriptions of these tokens at the OASIS web site too; see
http://docs.oasis-open.org/office/v1.2/cd05/OpenDocument-v1.2-cd05.pdf ; start at 19.345.

Brian Barker 


Op 3 jul. 2014, om 04:25 heeft Andreas J. Guelzow het volgende geschreven:

On 14-07-02 06:39 PM, Dennis E. Hamilton wrote:

Rob, I have no idea how an enhancement request could be defined.  I have not found where the handling of formatting strings is prescribed in ODF.

There are no "formatting strings" in ODF.  Number formatting is achieved via the <number:number-style> element. The interesting child element in this context is <number:number>.

  Perhaps someone who says that is the problem can point us to where that limitation can be found in the ODF specification.


There is only one ODF format definition and it is independent of production and consumption.


There is nothing in ODF that would have round-tripping fail.  If the application can’t actually produce something in a document that you attempted to represent, it should not have presented it to you as if it would.

I assume you are referring here to some sort of "native ODF application" whatever its definition may be. ODF is a file-format it really does not (and should not) say anything about an applications features.

That is not something the ODF specification can fix.  Round-tripping failures of an ODF-native product with itself are not possibly originated in the ODF specification.

The only sensible definition of "ODF-native" that would make this a true statement uses probably exactly this property I would think.

Not-with-standing this, since ODF allows for extensions in its extended-conformance class, any application in fact can round trip any file through (extended conforming)  ODF.



(I am not on the ODF TC, so you should first start by having someone identify where this situation is determined in the ODF specification.  Then the ODF TC can decide if there is an actual issue.)


-   Dennis


From: Rob Jasper [mailto:Rob@famJasper.nl]
Sent: Wednesday, July 2, 2014 16:02
To: dennis.hamilton@acm.org
Cc: office-comment@lists.oasis-open.org
Subject: Re: [office-comment] Formatting in spreadsheet


Hi Dennis,


this is a great idea!


I did the following:

The default formatting of the column is 0,000

Then I created conditional formatting to be 0,00 with the following formula: MOD(C2;0,01)=0


This is a great workaround!


Still, it is weird that the original formatting works, and round-trips through .xlsx, but not through ODF...

Also, MS Excel picks it up.


So, the functionality is there, but it can;t be saved in ODF.


Should this be worded as an enhancement request, and how do I do that?


Thanks for your help,




Op 2 jul. 2014, om 22:12 heeft Dennis E. Hamilton het volgende geschreven:

Hi Rob,

I think you can do this, but perhaps not the way you expect.  

I don’t know where in the ODF specification there is any rule about formats like "#.##0,00#", but you can do it with conditional styles.  

That is, try creating a conditional style on whether the value *100 is an exact integer or not, and then have 2 digits after the decimal or 3, depending on the condition result.  That’s definitely possible in ODF and the only question is whether it is supported by the Calc implementation of your choice.

-- Dennis E. Hamilton
   dennis.hamilton@acm.org    +1-206-779-9430
   https://keybase.io/orcmid  PGP F96E 89FF D456 628A

PS: It seems to me that accepting values and presenting them as you expect, but not round-tripping that form with itself, is a bug regardless of how the ODF specification is being interpreted for this case.

- - - - - Original Message - - - - - -
From: Rob Jasper [mailto:Rob@famJasper.nl]
Sent: Wednesday, July 2, 2014 10:11
To: office-comment@lists.oasis-open.org
Subject: [office-comment] Formatting in spreadsheet

I have a problem saving customer number format #.##0,00# on ods files.
I am using LibreOffice. I have a spreadsheet in which I keep my stamp collection.  
In one of the columns I keep the orginal face value of the stamp. Normally this is a value of X dutch guilders and YY cents, which is denoted as X,YY (we use a , for decimal separator). Example 0,25 for a quarter.
My problem is that in the older stamps there are values like 12 and a half cents, which I denote at 0,125. So far so good. Now I want the extra 3rd digit only to be displayed when actually used. So, I see values like:
LO processes this fine, if a save the .ods file and reopen it the formatting of the column has changed to 0,000 . Above fields are then displayed as:
According to the LibreOffice folks the ODF standard does not allow this. If I save in oodoc (.xlsx) the formatting stays in tact. Of course I want to save in a real standard :-(.
Can you confirm the opinion of the LO people?  
If this is the case, than I really feel this is a flaw or omision in the standards.
How can I get this on the radar to be repaired in the standards?
Rob Jasper,

This publicly archived list offers a means to provide input to the
OASIS Open Document Format for Office Applications (OpenDocument) TC.

In order to verify user consent to the Feedback License terms and
to minimize spam in the list archive, subscription is required
before posting.

Subscribe: office-comment-subscribe@lists.oasis-open.org
Unsubscribe: office-comment-unsubscribe@lists.oasis-open.org
List help: office-comment-help@lists.oasis-open.org
List archive: http://lists.oasis-open.org/archives/office-comment/
Feedback License: http://www.oasis-open.org/who/ipr/feedback_license.pdf
List Guidelines: http://www.oasis-open.org/maillists/guidelines.php
Committee: http://www.oasis-open.org/committees/tc_home.php?wg_abbrev=office
Join OASIS: http://www.oasis-open.org/join/


Andreas J. Guelzow
Professor of Mathematical & Computing Sciences
Concordia University College of Alberta

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]