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
*** Bug 137168 has been marked as a duplicate of this bug. ***
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.
See also bug 99856 for which the limitation was introduced to satisfy Excel.
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.
*** Bug 155080 has been marked as a duplicate of this bug. ***