Bug 52522 - FILEOPEN: Data validation: date ranges are lost on .xlsx import
Summary: FILEOPEN: Data validation: date ranges are lost on .xlsx import
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86 (IA32) Windows (All)
: high major
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Reported: 2012-07-26 08:46 UTC by mitch074
Modified: 2015-05-03 07:55 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Excel 2007 file to reproduce the problem (8.23 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2012-07-26 08:46 UTC, mitch074
Validity Cell Range definition (40.89 KB, image/png)
2012-11-20 08:42 UTC, damahevi
Buggy dropdown for cell ranges (9.76 KB, image/png)
2012-11-20 08:43 UTC, damahevi

Note You need to log in before you can comment on or make changes to this bug.
Description mitch074 2012-07-26 08:46:22 UTC
Created attachment 64702 [details]
Excel 2007 file to reproduce the problem

Problem description: 
When a cell has a data validation defined on a date range (between min and max dates), LibO doesn't import the definition of the range.

Attached file has a validation range defined between 2012/01/01 and 2013/01/01 on cell B1.

Steps to reproduce:
1. open attached file
2. enter a date between 2012/01/01 and 2013/01/01 in cell B1

Current behaviour:
A valid date is rejected: actual range is now 1900/01/00 to 1900/01/00

Expected behaviour:
A valid date should be allowed in the cell. Note that this works in OOo 3.4.0.

Platform (if different from the browser): 
Browser: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:14.0) Gecko/20100101 Firefox/14.0.1
Comment 1 damahevi 2012-08-08 17:15:22 UTC
This happens also with Version (Build ID: 932b512) on MacOSX!

Even worse: when you select the (empty) drop down list defined by a validity on a deleted range libreoffice hangs with full cpu usage! Could only kill this process! (Same when saved as ods)

However this worked really fine with 3.5 !!!

Please help!
Comment 2 mitch074 2012-09-08 08:49:18 UTC
Tested and confirmed again on 3.6.1 - data validation is an often used feature. Note that the file can be saved and reopened properly when saved to .xls, so the problem is more than likely located in the .xlsx filter.
Comment 3 sasha.libreoffice 2012-11-09 12:15:27 UTC
Thanks for bugreport
Not reproduced in 3.6.3 on RFR 17 64 bit. May be fixed already.
If problem still exist, tell what shown in dialog which appears by Data->Validity for cell B1 in Calc.
Comment 4 mitch074 2012-11-12 04:02:49 UTC
This could be specific to the Windows official builds, as I can't reproduce on Ubuntu Oneiric (LibO 3.5) either. Changing bug description accordingly.
Comment 5 sasha.libreoffice 2012-11-12 05:27:30 UTC
[not reproduced] also in and 3.6.3 on Windows XP 32 bit
Comment 6 damahevi 2012-11-19 09:56:39 UTC
Bug still exists with Version (Build ID: 58f22d5) with Mac OSX Mountain Lion. However there is a small enhancement (perhaps of Mountain Lion): the process does not need to be killed anymore - but CPU load is still getting high and the popup remains empty :(

When I try the attached file, cell B1 is empty.
Comment 7 sasha.libreoffice 2012-11-19 10:36:15 UTC
tell what shown in dialog which appears by Data->Validity for cell B1 in Calc.
(attach screenshot)
Comment 8 damahevi 2012-11-20 08:41:12 UTC
Ah now I see: the Validity of Date Fields works, but the Data Validity for cell ranges does not work!
(see attached screenshots)
Steps to reproduce:

1. Data->Define Range (on sheet2)
2. Set Validity on B2 as Cell Range and provide the name of the defined range
3. Check that the suggestion works.
4. save and close file
5. reopen file
6. Now instead of the values of the cells there is only the name of the range definition in the drop down list!

However with a file created with MS Office the dropdown is empty with high cpu load.

Sorry, if this should be in a separate bug.
Comment 9 damahevi 2012-11-20 08:42:24 UTC
Created attachment 70299 [details]
Validity Cell Range definition
Comment 10 damahevi 2012-11-20 08:43:13 UTC
Created attachment 70300 [details]
Buggy dropdown for cell ranges
Comment 11 Björn Michaelsen 2014-01-17 00:43:37 UTC
(This is an automated message.)

LibreOffice development currently prioritizes bugs with the so called MAB (most annoying bugs) -- as this bug has not run through that process (including writing a short rationale for this bug being a candidate and other who are watching the tracker bug silently approving that rationale etc.) its priority is set to high. Note this is effectively no change in the urgency assigned to this bug, as we are currently not making a difference between high and highest and severity is untouched.

You can find out more about MABs and how the process works by contacting libreoffice qa on irc:


The QA wiki page also gives you hints on how to get in contact with the team (if IRC fails you, your next best choice is the mailing list):

Comment 12 Joel Madero 2015-05-02 15:44:05 UTC
** 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.2 or later)

   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)


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-05-02
Comment 13 mitch074 2015-05-02 21:50:49 UTC
I can't reproduce with my initial test case anymore, be it on Win32-x86 or Linux-x64. Problem seems fixed.
Comment 14 steve -_- 2015-05-03 07:55:11 UTC
Worksforme is the correct state since we don't have a specific commit fixing this issue.

mitch: thanks for your update!