Download it now!
Bug 90104 - Data validity error when saving decimal number to .xlsx format
Summary: Data validity error when saving decimal number to .xlsx format
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: Other All
: medium normal
Assignee: Tibor Nagy (NISZ)
URL:
Whiteboard: target:7.1.0
Keywords:
: 93122 102368 (view as bug list)
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2015-03-19 15:43 UTC by Paavo Ingalsuo
Modified: 2020-11-17 09:33 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
screenshot image of a data validity cell before and after saving in .xlsx format. (343.29 KB, image/jpeg)
2015-03-19 15:43 UTC, Paavo Ingalsuo
Details
Bug DEMO table (12.09 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-04-16 14:30 UTC, Paavo Ingalsuo
Details
Data validation error when saving .ods to .xlsx format (4.98 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-02-05 15:19 UTC, Paavo Ingalsuo
Details
The example file saved by LO 7.1alpha - still bad (5.76 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-07 12:34 UTC, NISZ LibreOffice Team
Details
The Calc-saved example manually fixed up and saved in Excel (9.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-07 12:34 UTC, NISZ LibreOffice Team
Details
The original file in Calc and the fixed up xlsx in Excel (121.49 KB, image/png)
2020-10-07 12:36 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Paavo Ingalsuo 2015-03-19 15:43:35 UTC
Created attachment 114195 [details]
screenshot image of a data validity cell before and after saving in .xlsx format.

Data validity error when saving decimal number to .xlsx format
Error when sawing a spreadsheet in .xlsx -format.

If write to data validity:
1,5  ; after saving in in MS - EXCEL .xlsx format, it looks :

1
5

Have as attachment a -.jpg screenshot image.
In this image on the right side how it looks before saving and
on the left side of this same image, how it looks, when re open this .xlsx
Comment 1 raal 2015-03-20 20:43:08 UTC
I can confirm with 
Version: 4.5.0.0.alpha0+
Build ID: e3167924fd28c8b854f23139dbf49f53e6282ef7
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-03-17_03:10:47

LibreOffice 3.5.0 
Build ID: d6cde02
Comment 2 tommy27 2016-04-16 07:29:13 UTC Comment hidden (obsolete)
Comment 3 Paavo Ingalsuo 2016-04-16 14:30:45 UTC
Created attachment 124401 [details]
Bug DEMO table

In use.:

UBUNTU LINUX, version 15.10.

LibreOffice:
Version: 5.0.5.2
Build ID: 1:5.0.5~rc2-0ubuntu2
Comment 4 Cor Nouws 2016-05-25 09:19:48 UTC
*** Bug 93122 has been marked as a duplicate of this bug. ***
Comment 5 Paavo Ingalsuo 2017-02-05 15:06:12 UTC
Ubuntu 16.10 
LibreOffice Version: 5.2.2.2

Bug: translation from .ods to .xlsx
Comment 6 Paavo Ingalsuo 2017-02-05 15:15:24 UTC
Old bug. 
Still exists!

I have Ubuntu 16.10 and LibreOffice Version: 5.2.2.2
Bug: When i save a file from .ods to .xlsx,  
data validation list with decimal numbers understand's as new line.

Attachements: 
validity-bug.ods and 
validity-bug.xlsx
Comment 7 Paavo Ingalsuo 2017-02-05 15:19:18 UTC
Created attachment 130920 [details]
Data validation error when saving .ods to .xlsx format
Comment 8 Martin Spacek 2017-08-04 22:35:46 UTC
*** Bug 102368 has been marked as a duplicate of this bug. ***
Comment 9 QA Administrators 2018-08-05 02:47:14 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2020-08-05 04:56:09 UTC Comment hidden (obsolete)
Comment 11 b. 2020-08-07 10:13:39 UTC
bug still present in actual 7.1.0.0 dev (winx64), imho a 'locale evaluation' problem, ex$el (2010) is funny, accepts 1.5 instead of 1,5 , but shows as '1. Mai' in the dropdown list after entering 1.5 somewhere else in the sheet, 

but calc has it's own problems, 

as mentioned by OP after saving in xlsx format and reopening decimal fractions are interpreted 'comma separated' in the dropdown, 

in 7.1 the dropdown doesn't stay on screen after a touchpad tab, but flashes short and disappears, it's neccessary to hold the mouse button down while hovering to the list item,
Comment 12 NISZ LibreOffice Team 2020-10-07 12:34:00 UTC
Created attachment 166155 [details]
The example file saved by LO 7.1alpha - still bad
Comment 13 NISZ LibreOffice Team 2020-10-07 12:34:41 UTC
Created attachment 166156 [details]
The Calc-saved example manually fixed up and saved in Excel
Comment 14 NISZ LibreOffice Team 2020-10-07 12:36:22 UTC
Created attachment 166157 [details]
The original file in Calc and the fixed up xlsx in Excel

It should be possible to correctly save this in xlsx.
Comment 15 NISZ LibreOffice Team 2020-10-07 12:37:51 UTC
@Tibor Nagy I thought you might be interested in this one...
Comment 16 Commit Notification 2020-10-19 08:43:40 UTC
Tibor Nagy committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/699372466384113cf015be714e0067e857f8fa5c

tdf#90104 XLSX export: fix commas in data validation list

It will be available in 7.1.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 17 VLB 2020-10-19 12:48:35 UTC
Can this issue also be solved for LO 7.03?
Comment 18 b. 2020-10-20 21:12:40 UTC
looks good now :-)

thanks for fixing @Tibor Nagy

(didn't do extensive test, and only win7x64, thus more tests and comments appreciated ...) 

backport planned? ver. 6? ver. 7?
Comment 19 VLB 2020-10-30 17:53:55 UTC
(In reply to VLB from comment #17)
> Can this issue also be solved for LO 7.03.1?
I have test in LO 7.0.3.1 and it didn't work here.
Comment 20 NISZ LibreOffice Team 2020-11-17 09:33:40 UTC
Verified in:

Version: 7.1.0.0.alpha1+ (x64)
Build ID: 72056f39e01e36afac36c62988331b6813c8214f
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded

Thanks for fixing!