Bug 123353 - FILESAVE XLSX AutoFilter button lost when exporting MS Excel created XLSX files if only empty cells are set to visible
Summary: FILESAVE XLSX AutoFilter button lost when exporting MS Excel created XLSX fil...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Autofilter
  Show dependency treegraph
 
Reported: 2019-02-11 15:01 UTC by NISZ LibreOffice Team
Modified: 2019-03-02 18:12 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
The original file created in Microsoft Excel (14.19 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-11 15:02 UTC, NISZ LibreOffice Team
Details
The file exported with LibreOffice Calc (19.39 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-11 15:02 UTC, NISZ LibreOffice Team
Details
A screenshot showcasing the lost button (69.49 KB, image/png)
2019-02-11 15:02 UTC, NISZ LibreOffice Team
Details
Rows with empty cells hidden – original file created with MS Excel (14.23 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-11 15:03 UTC, NISZ LibreOffice Team
Details
Rows with empty cells hidden – file exported with Calc (19.47 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-11 15:03 UTC, NISZ LibreOffice Team
Details
A screenshot showcasing the lost blue marking (80.83 KB, image/png)
2019-02-11 15:04 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2019-02-11 15:01:46 UTC
Description:
LibreOffice Calc doesn't export well Excel made spreadsheets with AutoFilters and empty cells:
– The AutoFilter button is lost when only the rows with empty cells are visible and everything else is hidden – the filter itself is still active, and the settings can be still checked or changed from the Standard Filter window (Data – Standard filter), but the button itself is gone.
– The blue marking from the AutoFilter button is lost when only rows empty cells are hidden – but the button itself is still visible. Clicking on the button tells that all values are shown (including empty values), which is not true – turning off and on again the filtering of empty cells fixes the issue.

Note, that there are two different symptoms, depending on whether only empty values are shown or hidden, they are most likely caused by the same bug related to the handling of empty values in filters.

Additional details regarding the issue:
The issue only arises as described above if the filter was added in Microsoft Excel and the spreadsheet was saved as XLSX and reloaded with LibreOffice Calc. Calc made filters work better with empty values, even when saved as XLSX and reloaded multiple times, but still aren't perfect: while the button and the blue marking are preserved, clicking on the button shows everything – including the (empty) value – unchecked. So, handling of empty values in filters in XLSX files are broken in general.

Steps to Reproduce:
    1. Create a new spreadsheet in Microsoft Excel;
    2. Add some well filterable values to the first columns of the first 10 rows – make sure that you leave some cells empty;
    3. Turn on the autofilter for the first column, and hide everything except the empty cells;
    4. Save the file as XLSX and close MS Excel;
    5. Open the XLSX file in LibreOffice Calc, then save and reload it;

Actual Results:
The button for the AutoFilter is lost;

Expected Results:
The button for the AutoFilter should be preserved;


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 NISZ LibreOffice Team 2019-02-11 15:02:17 UTC
Created attachment 149142 [details]
The original file created in Microsoft Excel
Comment 2 NISZ LibreOffice Team 2019-02-11 15:02:33 UTC
Created attachment 149143 [details]
The file exported with LibreOffice Calc
Comment 3 NISZ LibreOffice Team 2019-02-11 15:02:52 UTC
Created attachment 149144 [details]
A screenshot showcasing the lost button
Comment 4 NISZ LibreOffice Team 2019-02-11 15:03:31 UTC
Created attachment 149145 [details]
Rows with empty cells hidden – original file created with MS Excel
Comment 5 NISZ LibreOffice Team 2019-02-11 15:03:50 UTC
Created attachment 149146 [details]
Rows with empty cells hidden – file exported with Calc
Comment 6 NISZ LibreOffice Team 2019-02-11 15:04:15 UTC
Created attachment 149147 [details]
A screenshot showcasing the lost blue marking
Comment 7 NISZ LibreOffice Team 2019-02-12 08:48:46 UTC
Reproduciable with:
LibreOffice 3.5.0rc3 
Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Comment 8 Roman Kuznetsov 2019-02-12 16:27:26 UTC
confirm in

Version: 6.3.0.0.alpha0+
Build ID: b45289e48e0f354b9996e2846dd041db4a9947ce
CPU threads: 4; OS: Linux 4.18; UI render: default; VCL: kde5; 
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-02-08_12:51:03
Locale: ru-RU (ru_RU.UTF-8); UI-Language: en-US
Calc: threaded

Serge, may be you will be interest in this bug?