Bug 99856 - FILESAVE Long data-validity-list when saves as XLSX give error in Excel, due to exceed 255 character limit of <formula1>
Summary: FILESAVE Long data-validity-list when saves as XLSX give error in Excel, due ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.7.2 release
Hardware: All All
: medium normal
Assignee: Justin L
URL:
Whiteboard: target:5.5.0 target:5.4.0.1
Keywords:
Depends on:
Blocks:
 
Reported: 2016-05-15 11:48 UTC by vlb
Modified: 2017-05-26 07:54 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Data-test file (12.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-05-15 12:03 UTC, vlb
Details
Example of exported file (4.93 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-09-25 23:48 UTC, Bartosz
Details
Minimum data test file (7.76 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-25 23:54 UTC, Bartosz
Details
Data test file fix in Office 2010 (8.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-09-25 23:58 UTC, Bartosz
Details
Validation list created with Excel 2010 with special characters ("<", ">", "'", "&") (6.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-09-28 22:33 UTC, Bartosz
Details
.xlsx file with maximum allowed characters in <formula1> (8.16 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-09-29 10:48 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description vlb 2016-05-15 11:48:36 UTC

    
Comment 1 vlb 2016-05-15 12:03:21 UTC
Created attachment 125058 [details]
Data-test file
Comment 2 vlb 2016-05-15 12:03:41 UTC
windows 10 64 bit and use LO 5.1.3

In referring to bug 86575.
I have test in 3.5.7 to 5.1.4

Bug reproduce:
1) Open attachment
2) see Data-validity-list in cell a1
3) save as file *.xslx
4) open in excel and give error and data-validity-lis is gone
Comment 3 Buovjaga 2016-05-17 13:58:25 UTC
Repro.

Win 8.1 32-bit
LibreOffice Version: 5.2.0.0.alpha1+
Build ID: cc1a0ba927ad6f85103059aa8e6108017f436304
CPU Threads: 4; OS Version: Windows 6.2; UI Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-05-17_02:31:19
Locale: fi-FI (fi_FI)
MSO 2013
Comment 4 Commit Notification 2016-05-19 17:00:19 UTC Comment hidden (no-value)
Comment 5 Eike Rathke 2016-05-19 17:07:54 UTC Comment hidden (no-value)
Comment 6 Eike Rathke 2016-05-19 17:08:50 UTC
@moggi: apparently Excel doesn't like something we save to the <dataValidation> element, idea?
Comment 7 Markus Mohrhard 2016-05-19 22:05:05 UTC
(In reply to Eike Rathke from comment #6)
> @moggi: apparently Excel doesn't like something we save to the
> <dataValidation> element, idea?

@Eike: do you have a file? Normally it shows up as a validation error.
Comment 8 Eike Rathke 2016-05-20 11:59:38 UTC
@Markus: hum.. take https://bugs.documentfoundation.org/attachment.cgi?id=125058 of this bug? ;-)
Comment 9 Bartosz 2016-09-25 23:48:31 UTC
Created attachment 127634 [details]
Example of exported file
Comment 10 Bartosz 2016-09-25 23:54:56 UTC
Created attachment 127635 [details]
Minimum data test file
Comment 11 Bartosz 2016-09-25 23:58:25 UTC
Created attachment 127636 [details]
Data test file fix in Office 2010
Comment 12 Bartosz 2016-09-26 00:18:49 UTC
To resolve that issue characters like "<", ">" and "&" must be replaced with:
&lt; &gt; and &amp;
Comment 13 Bartosz 2016-09-28 22:33:07 UTC
Created attachment 127708 [details]
Validation list created with Excel 2010 with special characters ("<", ">", "'", "&")
Comment 14 Bartosz 2016-09-29 10:48:41 UTC
Created attachment 127720 [details]
.xlsx file with maximum allowed characters in <formula1>

The root cause of that issue is too many characters in <formula1> field (255 characters limit).
When the list will be smaller, then MS Excel opening such file correctly.

Unfortunately I cannot provide solution for that issue.
Comment 15 Justin L 2017-05-25 17:58:20 UTC
proposed fix: gerrit.libreoffice.org/38038 tdf#99856

Several other data validation values can also cause a corrupt file.
-non-list items: value starts with a number, followed by non-numbers
-non-list items: values containing spaces, especially followed by a number
-date/time: invalid date/time value (possible, but not easy to do in LO).
Comment 16 Commit Notification 2017-05-25 21:30:25 UTC
Justin Luth committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b139bf96574010f317c543bc45d23dd6a5810621

tdf#99856 xlsx export: limit dataValidation list to 255 chars

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2017-05-26 07:33:11 UTC
Justin Luth committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=21b7cb2737d95f281e28e248c4dad306a5c6463f&h=libreoffice-5-4

tdf#99856 xlsx export: limit dataValidation list to 255 chars

It will be available in 5.4.0.1.

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

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