Download it now!
Bug 41425 - FILESAVE: Formatting and/or data validation, positioned after last non-empty cell, is lost when saving to XLS or XLSX
Summary: FILESAVE: Formatting and/or data validation, positioned after last non-empty ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0 target:6.1.1
Keywords:
: 56823 91682 94393 106720 (view as bug list)
Depends on:
Blocks: XLSX Cell-Validity XLS 120012
  Show dependency treegraph
 
Reported: 2011-10-03 10:18 UTC by Scott M. Sanders
Modified: 2018-09-20 16:05 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
lots of data validation (112.50 KB, application/x-msexcel)
2011-10-03 10:18 UTC, Scott M. Sanders
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Scott M. Sanders 2011-10-03 10:18:16 UTC
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.
Comment 1 Scott M. Sanders 2011-10-03 10:59:42 UTC
Validation saves fine using OOo 3.4 beta 1.
Comment 2 Björn Michaelsen 2011-12-23 12:33:47 UTC Comment hidden (obsolete)
Comment 3 Scott M. Sanders 2011-12-30 07:49:43 UTC
This persists in 3.5.0beta2.
Comment 4 mathiasc 2012-06-18 04:56:14 UTC
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.
Comment 5 Scott M. Sanders 2012-09-27 19:36:00 UTC
It works (now?) as XLSX.

But it still fails as XLS.
Comment 6 ign_christian 2014-06-24 06:10:59 UTC
*** Bug 56823 has been marked as a duplicate of this bug. ***
Comment 7 ign_christian 2014-06-24 06:15:35 UTC
Change Version to 3.5.4 -> based on Bug 56823
Platform All
Comment 8 ign_christian 2014-06-24 06:45:34 UTC
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
Comment 9 QA Administrators 2015-07-18 17:42:56 UTC Comment hidden (obsolete)
Comment 10 Buovjaga 2015-10-16 20:10:15 UTC
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)
Comment 11 sammy.trojette 2016-02-25 09:57:16 UTC
Bug is still reproducible.
Win 7, LibreOffice Version: 5.0.3.2 (x64)
Build-ID: e5f16313668ac592c1bfb310f4390624e3dbfb75
Comment 12 Bartosz 2016-08-07 04:02:55 UTC
I tested with LibreOffice 5.1.4.2 on Ubuntu 16.04 and it is validation list is showing correctly.
Comment 13 Deric Lima 2017-07-31 10:15:33 UTC
I tested with Calc v 5.4.0.3 and Ubuntu 16.04.
Its working for XLS but is not working for XLSX format.
Comment 14 Buovjaga 2017-07-31 13:14:11 UTC
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
Comment 15 Deric Lima 2017-07-31 13:36:32 UTC
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
Comment 16 Martin Spacek 2017-08-04 22:25:07 UTC
*** Bug 91682 has been marked as a duplicate of this bug. ***
Comment 17 Martin Spacek 2017-08-04 22:31:28 UTC
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.
Comment 18 Martin Spacek 2017-08-04 22:31:44 UTC
*** Bug 106720 has been marked as a duplicate of this bug. ***
Comment 19 Martin Spacek 2017-08-04 22:32:27 UTC
*** Bug 94393 has been marked as a duplicate of this bug. ***
Comment 20 Martin Spacek 2017-08-04 22:41:46 UTC
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
Comment 21 Martin Spacek 2017-08-04 22:50:12 UTC
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.
Comment 22 Cor Nouws 2017-08-09 14:28:37 UTC
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
Comment 23 László Németh 2018-08-03 16:04:10 UTC
workaround in https://gerrit.libreoffice.org/#/c/58575/
Comment 24 László Németh 2018-08-07 08:21:58 UTC
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.
Comment 25 László Németh 2018-08-07 08:29:36 UTC
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.
Comment 26 Commit Notification 2018-08-10 07:24:40 UTC
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.
Comment 27 Commit Notification 2018-08-14 12:05:06 UTC
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.
Comment 28 László Németh 2018-08-14 12:35:49 UTC
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.