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: [REQUEST] Formatting in spreadsheet

Hi Dennis,

As said in my previous mail to the forum I asked the LibreOffice forum on users@global.libreoffice.org. The person reacting to my question was Brian Barker <b.m.barker@btinternet.com>. You can see the threads on http://listarchives.libreoffice.org/global/users/msg39603.html .

I created a small testfile, set the formatting for col B to 0,00# (by LO immediately recognized as user-defined). The I saved the file as .ods. Directly after I saved the same file as .xlsx.
If I reopen the ,ods file, the formatting has changed to 0,000
Opening the .xlsx file (both with MS Excel as LibreOffice Calc) the formatting stays as defined 0,00#

Attachment: TestSheetFormatting.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Attachment: TestSheetFormatting.ods
Description: application/vnd.oasis.opendocument.spreadsheet

Format before saving:
Value FormattedValue Comment
120 120,00
12 12,00
1,5 1,50
1 1,00
0,1 0,10
0,22 0,22
0,225 0,225 Half cent

Format after reopening from .ods: 
Value FormattedValue Comment
120 120,000
12 12,000
1,5 1,500
1 1,000
0,1 0,100
0,22 0,220
0,225 0,225 Half cent

I do have a workaround by setting the default to 0,000 and a conditional formatting to 0,00 with the formula MOD(C2;0,01)=0
This is not the idea, though 

Also, it feels bad that Excel processes and saves/restore the 0,00# format correctly.


PS- I know that the forum possible strips the files, but Dennis will get them..

Op 3 jul. 2014, om 18:33 heeft Dennis E. Hamilton het volgende geschreven:

Hi Rob,
As you may have seen on the comment list, I have run into some other problems where openoffice implementations of ODF appear to do something as expected but the saved and reloaded document is different and may be very difficult to repair when it is finally noticed. 
I believe the round-tripping through Excel worked because the format pattern was sent to Excel.  Andreas confirmed there are no format patterns in ODF and Calc has to translate the pattern to what ODF does have and then reconstruct the pattern that users see when an ODF spreadsheet is read.
I am going to start collecting test documents for some of these, and also submit bug reports.
My request:
In a document where you used the pattern with the ,00# at the end, can you save that document as an .ods, reload that document, and see what the format of the cell is shown as in the reloaded document?
If it is changed, I will create a test document that shows it and submit it somewhere.
Also, where did you receive your feedback from LO.  Was it on one of the LO mailing lists? 
-- Dennis E. Hamilton
    dennis.hamilton@acm.org    +1-206-779-9430
    https://keybase.io/orcmid  PGP F96E 89FF D456 628A
PS: To make an enhancement request, you can use the ODF TC comment list.  However, it is difficult to express the request because the patterns you use and the pattern you want are not part of ODF but something LibreOffice Calc is providing at its user interface.  ODF has nothing to say about that.  You could ask for patterns to be another way to specify the presentation of table cells having numeric content, but then there needs to be some way to describe the rules for such patterns so that anyone could implement them correctly.  This could take a very long time to appear in a version of the ODF specification. 
PPS: There is an ODF OpenFormula spreadsheet function, TEXT(v, format) that formats a number, v, to the format of a specified format string. The format string is implementation-defined, and the v has to be in a different cell, of course.  You might check to see what LibreOffice Calcspecifies about TEXT, although I think your conditional formatting case is better.
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.

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