Bug 137167 - Validity List truncated when saving in Excel format
Summary: Validity List truncated when saving in Excel format
Status: RESOLVED NOTOURBUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.0.3 release
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.1.0
Keywords:
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2020-09-30 18:22 UTC by Aaron Korn
Modified: 2020-10-06 05:46 UTC (History)
0 users

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Aaron Korn 2020-09-30 18:22:11 UTC
Description:
Data Validity List is truncated when saving a LibreOffice Calc doc in any Excel file format

Steps to Reproduce:
1. Focus on a cell
2. Click on "Data/Validity..." menu
3. On Criteria Tab, select "List" from the "allow" drop down menu
4.  Create at least 9 rows in the "Entries" input box (my example list is below)
EXAMPLE LIST:  
Aluminum - Smooth Mill Finish
Aluminum - Stucco Embossed Mill Finish
Aluminum - Smooth White
Aluminum - Stucco Embossed White
Galvanized Steel - Smooth Mill Finish
Galvanized Steel - Stucco Embossed Mill Finish
Galvanized Steel - Smooth White
Galvanized Steel - Stucco Embossed White
Stainless Steel

Save the document in Excel format (cannot reproduce in ODF format)

Reopen the document and follow steps above to access the Validity list and you will find that the "Entries" list is truncated to the following: (note the last row on the list)

EXAMPLE after saving
Aluminum - Smooth Mill Finish
Aluminum - Stucco Embossed Mill Finish
Aluminum - Smooth White
Aluminum - Stucco Embossed White
Galvanized Steel - Smooth Mill Finish
Galvanized Steel - Stucco Embossed Mill Finish
Galvanized Steel - Smooth White
Galvanized Steel - S

Actual Results:
complete list is not saved

Expected Results:
entire list would be saved


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.0.0.3 (x64)
Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Comment 1 Buovjaga 2020-09-30 18:28:46 UTC
*** Bug 137168 has been marked as a duplicate of this bug. ***
Comment 2 Mike Kaganski 2020-09-30 19:25:20 UTC
This is not a bug, more specifically - not our bug.

OOXML standard does not put any restrictions on size of value of the related XML element (ECMA-376 Part 1 sect. 18.3.1.44 "formula1") - it is just a ST_Formula simple type (and related ยง18.18.35 only states that the simple type is a string).

[MS-OE376] Office Implementation Information for ECMA-376 Standards Support, though, does limit the length of ST_Formula to 8192 characters (2.1.1113 Part 4 Section 3.18.36). The other restrictions related to formula1 XML element, mentioned in the "Implementation Information" (2.1.653 Part 4 Section 3.3.1.41), are not related to the length of the value.

But 8192 characters is rather long string. It's funny how Excel puts further undocumented (? I couldn't find any official mention of this) restriction on the parameter: it only accepts 255 characters there. LibreOffice follows that in [1].

I have tested that using Excel 2016. It opens files with 255 characters there; it shows an error "We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." and drops the validity list if you change the XML manually to contain 256 characters, and it simply ignores your attempts to type more into Source field in Data Validation dialog after 255 characters.

[1] https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xecontent.cxx?r=1b43ccea#1752

Closing NOTOURBUG, since we cannot change Excel's behaviour, and we can't save files, although standard-compliant, that are claimed broken by "reference" implementation - Excel.
Comment 3 Eike Rathke 2020-10-05 20:46:24 UTC
See also bug 99856 for which the limitation was introduced to satisfy Excel.
Comment 4 Commit Notification 2020-10-05 20:49:17 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/f90500754fac014638214b5e061832b2c518aab6

Related tdf#137167: reference Excel pb on limitation for data validity formula

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.