Bug 138704 - FILEOPEN XLSX AutoFilter in Table imported incorrectly, interacts weirdly with normal autofilter
Summary: FILEOPEN XLSX AutoFilter in Table imported incorrectly, interacts weirdly wit...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: XLSX-Autofilter XLSX-DataRange
  Show dependency treegraph
Reported: 2020-12-07 00:14 UTC by LeroyG
Modified: 2023-08-21 03:05 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Sample .ods file (25.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-12-07 00:16 UTC, LeroyG
xlsx file without sensible data (7.98 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-12-07 13:36 UTC, LeroyG
Table in original file side by side in Excel and Calc (125.66 KB, image/png)
2020-12-10 10:08 UTC, NISZ LibreOffice Team

Note You need to log in before you can comment on or make changes to this bug.
Description LeroyG 2020-12-07 00:14:05 UTC
Original AutoFilter range remains valid, although new AutoFilter range is set. Need to copy and paste the data to a new spreadsheet to avoid the issue.

Steps to Reproduce:
1. Open a xlsx file that includes autofilters (in my case, answers from a MS Forms in Calc)
2. Hide AutoFilter
3. Add some columns to the right (D:F in the sample file)
4. Add AutoFilter
5. Apply AutoFilter
6. Hide AutoFilter

Actual Results:
Steps 4, 5 and 6 do not take in account the new columns if done while cursor is in the original columns.
Steps 4, 5 and 6 works if done while cursor is in the new columns.

Expected Results:
When hidding AutoFilter range must be automatically reseted.

Reproducible: Always

User Profile Reset: No

OpenGL enabled: Yes

Additional Info:
Version: (x86)
Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; 
Locale: es-MX (es_MX); UI-Language: en-US
Calc: threaded
Comment 1 LeroyG 2020-12-07 00:16:25 UTC
Created attachment 167888 [details]
Sample .ods file

3 columns are inheritd from the .xlsx file, and 3 columns are new.
Comment 2 Aron Budea 2020-12-07 02:04:21 UTC
I can confirm with the attached ODS, but not with an ODS I saved from an autofiltered XLSX as described in the steps. Can you share the XLSX as well?

What's odd to me is that the ODS is lacking an _xlnm._FilterDatabase named range that comes via the XLSX autofilter import (see eg. bug 118592 and bug 127301).
Comment 3 LeroyG 2020-12-07 13:36:41 UTC
Created attachment 167902 [details]
xlsx file without sensible data

The downloaded .xlsx file was edited with LO and saved.
Comment 4 NISZ LibreOffice Team 2020-12-09 09:39:57 UTC
Confirming with the XLSX file in:

Version: (x64)
Build ID: 796c7f612603490dda9277ced0f6ab3cce3bc116
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: default; VCL: win
Locale: en-US (hu_HU); UI: en-US
Calc: CL

When row 1 is selected or the cursor is placed in a cell in the A-C columns (and even D too - strange), the problem is reproducible. 
Works fine of the cursor is in a cell of E-F (etc...) columns.

When columns A-F are selected, all of them gets the new autofilter. But then selecting row 1 and turning autofilter off and on makes it appear only in the newly added D-F column - buggy behavior is inverted.

Also happens in older versions: 6.0, 5.0, 4.0, 3.5 - not a new regression.
Comment 5 NISZ LibreOffice Team 2020-12-10 10:08:11 UTC
Created attachment 168031 [details]
Table in original file side by side in Excel and Calc

This A1:C61 range is formatted as a table in Excel, with the Filter button option enabled.

Calc has the Database range feature similar to this, but the filter button option is missing from the UI.
Comment 6 NISZ LibreOffice Team 2021-07-14 12:13:07 UTC
Playing with this in Excel as well, I see:
- In Excel you can either turn on/off the autofilter for the table or the new data outside of the table, if the selection of cells/rows/columns is inside or outside of the table.
If you select a range that contains cells (rows/columns) in both the table and outside of it, the autofilter button is disabled.

- In Calc if the selection is inside the table (Database Range), the autofilter affects only the table. 
* This is true if the selection is only partially inside the table like C2:D2 (Excel does not allow autofilter in this case, Calc shouldn't either).
* If the selection is one cell and it's outside the table and not in Column D), then the autofilter is turned on / off for all columns. (this works as if there was no table, but should consider whether it's inside the table or outside and affect only the table or the columns outside of it)
* If the selection is at least partially in Column D, it turns on/off the autofilter in the table. (wrong, autofilter should be disabled)
* If the selection is at least two cells in columns E-F then the selected range has the autofilter turned on (this works correctly, same as if there was no table).
Comment 7 m_a_riosv 2021-08-20 10:36:45 UTC
If I'm not wrong, when calc has a filter on a database defined range, it works connected with that range, and also keeps for that range the options for sort.
Seems when it comes from xlsx a database range is created, so the filter it's connected to it, and no add new columns to the filter.
Comment 8 QA Administrators 2023-08-21 03:05:34 UTC
Dear LeroyG,

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 with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

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) from https://downloadarchive.documentfoundation.org/libreoffice/old/

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: https://web.libera.chat/?settings=#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team