Bug 75840 - Currency format after FILESAVE for a xlsx made from excel is wrong
Summary: Currency format after FILESAVE for a xlsx made from excel is wrong
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.1.1 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-03-06 13:21 UTC by Pierre CARRET
Modified: 2017-03-28 20:23 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Test created With MS Excel (8.98 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-05-05 13:33 UTC, Pierre CARRET
Details
Test "saved as" with Libre Office (5.92 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-05-05 13:33 UTC, Pierre CARRET
Details
Backtrace of crash when unchecking thousands separator (8.32 KB, text/plain)
2014-11-24 10:47 UTC, Buovjaga
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pierre CARRET 2014-03-06 13:21:38 UTC
Create a new xlsx file from Excel (2010 in my case).
Put an amount in a cell and format it with a standard monetary format (in my case # ##0,00 €, for exemple 123,45 €).
Save it.
Open it with Spreadsheet, the display is fine (exact format is # ##0,00 "€"). Save the file with a new name or modify it and save.
Open it again with Spreadsheet, the display is divide by 1000 for example 0,12 €. The format saved was # ##0,00 \€.

This is weird because both of this formats give 0,12 € in Excel and Spreadsheet, one working is # ##0,00\ €. This may depend of the regional settings, mines are French.
Comment 1 Jorendc 2014-03-06 21:09:06 UTC
Mmh, strange. I think I can't reproduce it completely

What I did using Mac OSX 10.9, LibreOffice Version: 4.2.2.1
Build ID: 3be8cda0bddd8e430d8cda1ebfd581265cca5a0f and Excel for Mac 2011:
* Open Excel
* In cell A1 I enter 123,45
* Right click > Format Cell
* Choose 'currency' format with 2 decimals
* OK; cell now shows 123,45€
* Save as .xlsx

* Opened saved .xlsx using LibreOffice
* Content in cell A1 shows 123,45€ as expected
* Right click > Format Cell
* Format is 'Number' with format code: #.##0,00 "€";[RED]-#.##0,00 "€"

Not sure this is 'bad' behavior (should be imported as currency, not?)

* Resaved as .xlsx
* Open .xlsx in Excel
* Shown content in cell A1 is 123,45€  which is correct.
* (in Excel) Right click > Format Cells
* Format is 'Custom' with format code: #.##0,00 "€";[RED]-#.##0,00 "€"

So, looks like the number format isn't roundtripped correctly.

Kind regards,
Joren
Comment 2 Pierre CARRET 2014-03-11 17:37:19 UTC
Hello Joren,

Sorry for the late anwser, i was pretty busy at work.
My build id is d7dbbd7842e6a58b0f521599204e827654e1fb8b.

> * Open Excel
> * In cell A1 I enter 123,45
> * Right click > Format Cell
> * Choose 'currency' format with 2 decimals
> * OK; cell now shows 123,45€
> * Save as .xlsx

ok.

> * Opened saved .xlsx using LibreOffice
> * Content in cell A1 shows 123,45€ as expected
> * Right click > Format Cell
> * Format is 'Number' with format code: #.##0,00 "€";[RED]-#.##0,00 "€"

ok, but exactly i have # ##0,00 "€";[RED]-# ##0,00 "€". At this moment the display is fine for the original cell.
If I apply this format in a new cell with a values of 123,45 as a custom format, it displays 1,23€. 
For information in Right click > Format Cell > Number, the last one with the description # ##0,00 "€";[RED]-# ##0,00 "€", the example displayed here is 1,23€. Maybe something wrong with the french version ?

> * Resaved as .xlsx
> * Open .xlsx in Excel

Or LibreOffice, the result is the same for me.

> * Shown content in cell A1 is 123,45€  which is correct.

Not for me, it displays 1,23€.
* Right click > Format Cell shows : # ##0,00 €;[RED]-# ##0,00 €
The " are gone.

I don't know if it's the same for every versions but in the french version the format # ##0,00 €;[RED]-# ##0,00 € doesn't display the result excpected. And this is the case for both LibreOffice and Excel 2010.
This is because of the space I think, # ##0,00\ €;[RED]-# ##0,00\ € works as excepted.

Kind regards.
Comment 3 Jean-Baptiste Faure 2014-05-01 08:58:37 UTC
Hi Pierre,

Please could you attach the xlsx file created with MS-Excel, so that tester who do not have MS-Excel can try to reproduce the problem you describe?

Best regards. JBF
Comment 4 Pierre CARRET 2014-05-05 13:33:11 UTC
Created attachment 98486 [details]
Test created With MS Excel
Comment 5 Pierre CARRET 2014-05-05 13:33:55 UTC
Created attachment 98487 [details]
Test "saved as" with Libre Office
Comment 6 Jean-Baptiste Faure 2014-05-05 19:03:59 UTC
It seems there is a problem with the thousands separator: if I open your xslx file with LO 4.2.5.0.0+ under Ubuntu 14.04 x86-64 and try to modify the cell format by unchecking the Thousands separator, LO crashes.

Please, could you test that on your side?

Best regards. JBF
Comment 7 Jean-Baptiste Faure 2014-05-05 19:06:23 UTC
Same crash with the master build from sources updated last night (Build ID: d6c42a503fb4237d5aa86eece3bf1fc1fba87a4b)

Best regards. JBF
Comment 8 Pierre CARRET 2014-05-06 08:01:29 UTC
Hi Jean-Baptiste,

No crash on my side under windows 7 Pro SP1 x64 when I uncheck the thousand separator. I think you find an other issue :).
But I don't think the thousands separator is the problem.
As I said before the format "# ##0,00 €;[RED]-# ##0,00 €" saved by LO doesn't display the result excpected. And this is the case for both LibreOffice and Excel 2010.
This is because of the space before the "€" character. The format "# ##0,00\ €;[RED]-# ##0,00\ €" works as excepted on both.

Best regards.
Comment 9 Buovjaga 2014-11-24 10:47:07 UTC
Created attachment 109931 [details]
Backtrace of crash when unchecking thousands separator

attachment 98486 [details] crashes LibO, when unchecking the thousands separator in cell formatting.

Win 7 64-bit Version: 4.5.0.0.alpha0+
Build ID: 772befa08f02b08da1ab493896a0744bc73781c7
TinderBox: Win-x86@51-TDF, Branch:MASTER, Time: 2014-11-23_22:39:02
Comment 10 Buovjaga 2014-11-24 10:48:20 UTC
I'm setting to NEW. If for nothing else, the crashing.
Comment 11 QA Administrators 2015-12-20 16:16:10 UTC Comment hidden (obsolete)
Comment 12 Jean-Baptiste Faure 2016-01-06 21:40:30 UTC
Still crashing when I try to uncheck the thousands separator with
Version: 5.1.0.1.0+
Build ID: f1d55069f1997e40fa20a5251d450eefe9171268
CPU Threads: 4; OS Version: Linux 4.2; UI Render: default; 
Ubuntu_15.10_x86-64
Locale : fr-FR (fr_FR.UTF-8)

Workaround:
1/ open the xlsx file with LO
2/ remove the direct formatting
3/ save as .ods
4/ add currency formatting
5/ save
Now you can uncheck thousands separator without crash.

Best regards. JBF
Comment 13 QA Administrators 2017-03-06 14:20:56 UTC Comment hidden (obsolete)
Comment 14 Jean-Baptiste Faure 2017-03-28 20:23:02 UTC
No crash anymore with :

Version: 5.3.3.0.0+
Build ID: 8472f86d64fddd0afd7440cdb72eafbef7dcb301
Threads CPU : 4; Version de l'OS :Linux 4.4; UI Render : par défaut; VCL : gtk3; Moteur de mise en page : nouveau; 
Ubuntu_16.04_x86-64
Locale : fr-FR (fr_FR.UTF-8); Calc: single

Version: 5.4.0.0.alpha0+
Build ID: 9aee1f80a50f950a8c2035df6ccdc2b420e1e4f8
Threads CPU : 4; Version de l'OS :Linux 4.4; UI Render : par défaut; VCL : gtk3; 
Ubuntu_16.04_x86-64
Locale : fr-FR (fr_FR.UTF-8); Calc: single

Closing as WorksForMe. Please, feel free to reopen if you disagree.

Best regards. JBF