Created attachment 51907 [details] lots of data validation Hi. I have a file with lots of data validation, I open it in LibreOffice, and I do nothing else but save it as either XLS or XLSX. The resulting XLS or XLSX now have no data validation. Attached is the original file.
Validation saves fine using OOo 3.4 beta 1.
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
This persists in 3.5.0beta2.
Hi. This bug persists with LibreOffice 3.5.4.2 - 350m1(Build:2). Quite annoying. Saving a spreadsheet in ODS preserves the validation rules, everything is correct. Saving in XLS erases them. If I save a spreadsheet with MS Excel 2010 in XLS and open it with LibreOffice, the validation rules do not appear. If I generate a spreadsheet in XLS using PHPExcel, validation rules are preserved at first opening with LibreOffice, but are erased as soon as I save the file. Some kind of problem with the xls format I suppose.
It works (now?) as XLSX. But it still fails as XLS.
*** Bug 56823 has been marked as a duplicate of this bug. ***
Change Version to 3.5.4 -> based on Bug 56823 Platform All
Still happen in LO 4.2.5.2 - Ubuntu 12.04 x86 Also with XLSX : Bug 67146 Saving as XLS looks ok with AOO 4.1.0
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.1 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-07-18
Still repro. Putting severity to major as this is data loss. Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+ Build ID: 186f32f63434e16ff5776251657f902d5808ed3d TinderBox: Win-x86@39, Branch:master, Time: 2015-10-16_09:42:47 Locale: en-US (fi_FI)
Bug is still reproducible. Win 7, LibreOffice Version: 5.0.3.2 (x64) Build-ID: e5f16313668ac592c1bfb310f4390624e3dbfb75
I tested with LibreOffice 5.1.4.2 on Ubuntu 16.04 and it is validation list is showing correctly.
I tested with Calc v 5.4.0.3 and Ubuntu 16.04. Its working for XLS but is not working for XLSX format.
Still repro with saving attachment 51907 [details] as XLSX or XLS. Deric: I wonder how it can be working with XLS for you? I look at Data - Validity and no input help or error alert content is present after saving. Arch Linux 64-bit, KDE Plasma 5 Version: 6.0.0.0.alpha0+ Build ID: b66cfdbc1aa43fb42cf881bcc702798e95a50a9c CPU threads: 8; OS: Linux 4.11; UI render: default; VCL: kde4; Locale: fi-FI (fi_FI.UTF-8); Calc: group Built on July 23rd 2017
Buovjaga you're right. Im sorry, I checked my excel now and saw that I left one cell with data in the end of the range. This workaround works, but for me is a bad solution because the excel that Im working will be imported using phpexcel. The bug is present even for XLS with Ubuntu 16.04 and Calc v 5.4.0.3
*** Bug 91682 has been marked as a duplicate of this bug. ***
This comment by Aron Budea sounds like a promising start at narrowing down the root cause in the code: https://bugs.documentfoundation.org/show_bug.cgi?id=106720#c5 Quoting: The bug seems to be in XclExpCellTable::XclExpCellTable( const XclExpRoot& rRoot ). http://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xetable.cxx#2437 This is where validation data is collected: if( ScfTools::CheckItem( rItemSet, ATTR_VALIDDATA, false ) ) http://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xetable.cxx#2641 However, the large loop in that function ends with the last row where there's data, and in this case there's no more data in rows 4-5, so they aren't considered: the application exits the loop before being able to take care of the validation data there.
*** Bug 106720 has been marked as a duplicate of this bug. ***
*** Bug 94393 has been marked as a duplicate of this bug. ***
I can also confirm that this bug is present for both .xls and .xlsx but not .ods, as of LO Calc 5.4.0.3
This seems to be a problem with any kind of formatting, not just validity rules, e.g. cell colour, number formatting, probably anything that can be set in the "Format Cells" dialog. I've updated the bug title to reflect this.
I see the problem (with http://bugs.documentfoundation.org/attachment.cgi?id=116086 anyway) already in version 3.3.0.4 Not in AOO 4.1.2
workaround in https://gerrit.libreoffice.org/#/c/58575/
WORKAROUND for keeping styles and validation for blank rows bottom the last 1000 blank rows (the suggested patch do that): Put a space in a neutral cell in the last (or plus one) blank row. Every blank rows are saved correctly, if there is a non-empty cell after them.
WORKAROUND 2 (more work, but without the not-empty cell trick): format the whole column by clicking on the column identifier and set the requested style (ie. let the style and validation of the blank rows default formatting), and add not default formatting to the headers.
László Németh committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=99b9ea63bfc9a5fe63a0cd7b30b66ce2c1bde08e tdf#41425 XLS/XLSX export: workaround for style and validation loss It will be available in 6.2.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.
László Németh committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d7cbaac61b8f3575184c675a760907c3b4bb225e&h=libreoffice-6-1 tdf#41425 XLS/XLSX export: workaround for style and validation loss It will be available in 6.1.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.
The workaround has solved this problem for the first 1000 blank rows after the last non-empty row. The remaining problem needs a new issue.