Bug Hunting Session
Bug 94393 - if I open an MS Excel XLSX file in Calc, dropdowns in cells do not show
Summary: if I open an MS Excel XLSX file in Calc, dropdowns in cells do not show
Status: RESOLVED DUPLICATE of bug 41425
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-09-21 04:11 UTC by Elmar
Modified: 2017-08-04 22:32 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
xlsx dropdown does nto work in Calc (4.37 KB, image/png)
2015-09-24 05:53 UTC, Elmar
Details
MS Excel sample file (16.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-09-24 05:56 UTC, Elmar
Details
added a dropdown (cell B30) using Calc (9.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-09-24 06:51 UTC, Elmar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2015-09-21 04:11:42 UTC
User-Agent:       Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:40.0) Gecko/20100101 Firefox/40.0
Build Identifier: LibreOffice 5.0.1.2

This applies to v4.4.3.2 as well

Reproducible: Always

Steps to Reproduce:
1. Open document created in MS Excel, save as xlsx
2. Open in LO Calc
3. Dropdown is not visible



[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes


Reset User Profile?Not a bug
Comment 1 raal 2015-09-21 06:02:29 UTC Comment hidden (obsolete)
Comment 2 Elmar 2015-09-24 05:53:49 UTC
Created attachment 118982 [details]
xlsx dropdown does nto work in Calc
Comment 3 Elmar 2015-09-24 05:56:20 UTC
Created attachment 118983 [details]
MS Excel sample file

Note that if I use MS Excel to save the file to a .xls file, the problem remains.
Comment 4 Elmar 2015-09-24 06:49:15 UTC
Looking into the matter, it seems that there is a difference in approach between Excel and Calc.

In Excel, you can create the list in cells, then link the cells from the cell which is to be a dropdown. No named range is created.

In Calc, you can create the list in cells, but then you need to name the range. In the dropdown cell, you reference the named range.

I then saved the the file I added the dropdown using Calc. I opened the new file using Excel, the new dropdown I created worked (cell B30), but none of the others (which had been created using Excel) worked any longer.

The Name that was created in Calc to define the range is also not visible in Excel.

Unfortunately, even though govts claim to support open source, they tend to all use MS. Consequently they create forms which one has to complete. If you are an exclusively Open user (and do not have the MS tools), then this can be a problem.

I checked this out in OpenOffice Calc, and it acts in the same way as LO Calc. Thus it is not a regression or bug, but an inconsistency between Excel and Calc. Not sure how you deal with this,
Comment 5 Elmar 2015-09-24 06:51:24 UTC
Created attachment 118984 [details]
added a dropdown (cell B30) using Calc

The new dropdown (cell B30) works in Calc and Excel, however, the original dropdowns created using Excel no longer work in Excel.
Comment 6 Buovjaga 2015-09-28 08:26:25 UTC
Confirmed.

Win 7 Pro 64-bit, Version: 5.0.2.2 (x64)
Build ID: 37b43f919e4de5eeaca9b9755ed688758a8251fe
Locale: fi-FI (fi_FI)

Version: 5.1.0.0.alpha1+ (x64)
Build ID: 8d16834a9820fb8c983753c3e2a05922885060b9
TinderBox: Win-x86_64@62-TDF, Branch:MASTER, Time: 2015-09-22_21:29:32
Locale: en-US (fi_FI)
Comment 7 Ian 2016-04-21 13:35:27 UTC
Our charity has MS Office on some computers and Libre office on others. I am creating a spreadsheet with drop-down boxes and have been creating this in Excel. However, when I try to open the spreadsheet (Template) in Calc, the drop-downs are not there.
I am using a sheet I with a columns to populate each drop-down box.
In Excel the validation criteria in Data is "Lists", In Calc "Cell Range"
In Excel the formula  is <=Lists!$x$n:$x$n>
In Calc the formula is <$LISTS.$x$n:$x$n>
Comment 8 QA Administrators 2017-05-22 13:26:39 UTC Comment hidden (obsolete)
Comment 9 Richard Léger 2017-06-08 14:28:46 UTC
In this post (https://www.amazon.co.uk/Converter-VicTsing-Transmission-Computer-Projector/dp/B01M1IQ7F9/ref=pd_sim_23_4?_encoding=UTF8&psc=1&refRID=3MVH3EK0XMVE1QEBHXD7) someone suggest the following as possible workaround:

"...
When you use "Data", "Validity", from the "Allow" drop down select "Cell Range" instead of "List", and then use a range of cells to list the available options.

This works saving to xls or xlsx from LibreOffice 5.2.0.2, possibly also from earlier versions but I have not tested.
..."

I haven't tested but I thought it may help someone perhaps.

It would be great of course if LO could manage directly to save this info correctly in xls and xlsx files... for improved compatibility.

I can confirm that opening the "MS Excel sample file" attached to this bug in LO 5.2.7.2 (x64) installed on Win10 Pro x64 OS, I cannot see or select the dropdown cells in the file they appear as standard cells with "Please Select" as text content. 

So bug is still present.
Comment 10 Richard Léger 2017-06-08 14:30:11 UTC
(In reply to Richard Léger from comment #9)
> In this post
> (https://www.amazon.co.uk/Converter-VicTsing-Transmission-Computer-Projector/
> dp/B01M1IQ7F9/
> ref=pd_sim_23_4?_encoding=UTF8&psc=1&refRID=3MVH3EK0XMVE1QEBHXD7) someone
> suggest the following as possible workaround:

Bad copy paste!!! 

I meant this post:
https://ask.libreoffice.org/en/question/73753/drop-down-lists-created-in-lo-calc-not-working-in-exel/

Sorry I could not correct my original post after posting...
Comment 11 Buovjaga 2017-06-08 15:44:54 UTC
(In reply to Richard Léger from comment #10)
> Sorry I could not correct my original post after posting...

Yep, unfortunately this is not yet implemented: https://bugzilla.mozilla.org/show_bug.cgi?id=1144473
Comment 12 Elmar 2017-06-09 05:44:45 UTC
I opened the Excel file and the dropdowns are all there.
Saved the file using Calc, opened that, all is fine.

Checked with
Version: 5.3.3.2
Build ID: 1:5.3.3~rc2-0ubuntu0.16.04.1~lo0
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; VCL: gtk2; Layout Engine: new; 
Locale: en-ZA (en_GB.UTF-8); Calc: group

and
Version: 5.5.0.0.alpha0+
Build ID: ddf8539d97ce044b7df8d51d6ec72ec864b40fb8
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-06-04_22:10:59
Locale: en-ZA (en_GB.UTF-8); Calc: group

-----------------------------------------------------

also opened the excel file using
Linux Mint MATE (don't think this is material, but)
Version: 5.1.4.2
Build ID: 1:5.1.4-0ubuntu1
CPU Threads: 1; OS Version: Linux 4.4; UI Render: default; 
Locale: en-ZA (en_ZA.UTF-8)
IT PRESENTED SAME PROBLEM

When I open the ODS saved in v5.5, the dropdowns are all there

THUS
It seems it must have been fixed sometime between v5.1 and v5.3
That's great!
Comment 13 Buovjaga 2017-06-09 05:51:38 UTC
Fantastic news. Let's tweak status to WFM as we don't know the fixing commit.
Comment 14 Richard Léger 2017-06-09 14:48:16 UTC
My version of Windows 10 where I tested it is: 1607 (OS Build 14393.1198)
If that can be of any help for further testing.

Any chance the fix can be backed ported in the current stable version of LO?

When is the next stable version of LO coming out?
Comment 15 Buovjaga 2017-06-09 15:19:29 UTC
(In reply to Richard Léger from comment #14)
> My version of Windows 10 where I tested it is: 1607 (OS Build 14393.1198)
> If that can be of any help for further testing.
> 
> Any chance the fix can be backed ported in the current stable version of LO?
> 
> When is the next stable version of LO coming out?

Elmar said the fix is already in 5.3.3, which is the current stable version.
Comment 16 Martin Spacek 2017-08-04 22:17:29 UTC
This bug is NOT fixed. I've tried it on two Ubuntu machines running 5.4.0.3, one Ubuntu machine running 5.3.4.2, and a Mac running 5.0. As described elsewhere, using LO Calc to add a validity rule of any kind (not just a list) to a cell in an empty column, then saving it to .xlsx, then re-opening it (whether in Excel or LO Calc), reveals that the validity rule wasn't saved at all. However, putting e.g. a space on line 1000 of that column before saving to .xlsx prevents the bug from arising. Presumably, for .xlsx, LO Calc doesn't bother saving anything past the last non-empty cell in a column, even if formatting or validity rules are applied to those subsequent cells.

This isn't a problem when saving to .ods, and I don't think it ever was.

This post sums it up nicely, and also confirms the bug still exists in 5.4.0.3:

https://ask.libreoffice.org/en/question/77843/validity-rules-are-missing-when-a-calc-file-is-re-opened/

I think this is part of a more general problem: LO Calc doesn't save any types of formatting to .xlsx, such as cell colour, after the last "empty" row, where empty means no values entered.
Comment 17 Martin Spacek 2017-08-04 22:32:27 UTC

*** This bug has been marked as a duplicate of bug 41425 ***