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;
The button for the AutoFilter is lost;
The button for the AutoFilter should be preserved;
User Profile Reset: No
Created attachment 149142 [details]
The original file created in Microsoft Excel
Created attachment 149143 [details]
The file exported with LibreOffice Calc
Created attachment 149144 [details]
A screenshot showcasing the lost button
Created attachment 149145 [details]
Rows with empty cells hidden – original file created with MS Excel
Created attachment 149146 [details]
Rows with empty cells hidden – file exported with Calc
Created attachment 149147 [details]
A screenshot showcasing the lost blue marking
Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
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
Serge, may be you will be interest in this bug?