Bug 99913 - Importing autofiltered XLSX and selecting cells copies hidden cells, too
Summary: Importing autofiltered XLSX and selecting cells copies hidden cells, too
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.0 release
Hardware: All All
: high major
Assignee: Not Assigned
Keywords: filter:xlsx, notBibisectable, regression
: 126505 (view as bug list)
Depends on:
Blocks: XLSX-Autofilter
  Show dependency treegraph
Reported: 2016-05-17 14:39 UTC by Peter Gervai
Modified: 2020-05-17 07:51 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

sample file (2.06 MB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-19 11:59 UTC, Peter Gervai
package containing xlsx + xls + ods (14.01 KB, application/x-zip-compressed)
2020-02-18 13:40 UTC, Julien Nabet
Test on Excel 365 (9.54 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-02-20 16:11 UTC, Julien Nabet

Note You need to log in before you can comment on or make changes to this bug.
Description Peter Gervai 2016-05-17 14:39:07 UTC
- a sheet created in excel have autofilter active on a column. xlsx saved.
- xlsx loaded into calc
- the fields look filtered
- select the column
- copy
- paste into another sheet
- BUG: all the values are copied, even hidden ones

- change anything in the original table (fiddle with autofilter), then only the corect (visible) cells are copied

(comment to self: via TRf)
Comment 1 Buovjaga 2016-05-18 10:10:12 UTC Comment hidden (obsolete)
Comment 2 Peter Gervai 2016-05-19 11:59:54 UTC
Created attachment 125167 [details]
sample file

* load it
* select 'id' column
* copy
* add a new sheet
* paste
* hidden values should not be copied, but thy are
Comment 3 Buovjaga 2016-05-25 09:22:21 UTC
Reproduced with the file

Win 7 Pro 64-bit Version:
Build ID: f688acfdae00ebdd891737e533d54368810185e1
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2016-05-18_00:11:31
Locale: fi-FI (fi_FI)
Comment 4 QA Administrators 2017-09-01 11:15:03 UTC Comment hidden (obsolete)
Comment 5 Peter Gervai 2017-09-05 06:38:05 UTC
Confirming that the bug is still present in v5. Build ID: 1:5.4.1-1
Comment 6 Timur 2018-07-10 17:46:22 UTC
Repro 6.2+
Comment 7 Peter Gervai 2019-03-13 01:24:11 UTC
It seems to be fixed in 
Build ID: 1:6.1.5~rc1-2

Both full column and manual column selection copies only visible content.
Comment 8 Buovjaga 2019-03-13 08:42:27 UTC
(In reply to Peter Gervai from comment #7)
> It seems to be fixed in 
> Version:
> Build ID: 1:6.1.5~rc1-2
> Both full column and manual column selection copies only visible content.

I can still reproduce with your steps in comment 2.

Arch Linux 64-bit
Build ID: adb08e892b37ea9e155abbdee4e0c9951a1d163b
CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: gtk3; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 12 March 2019
Comment 9 Julien Nabet 2020-02-18 13:40:29 UTC
Created attachment 157977 [details]
package containing xlsx + xls + ods

On Win10 with master sources updated today, I created an ods file with a simple autofilter, then I saved it to xls + xlsx format (the attachment contains the 3 files).
On each of the files, I selected filtered/not hidden lines and copy pasted them on a new sheet to compare number of lines pasted and selected, here are the results:
- ods -> OK
- xls -> OK
- xlsx -> KO
Comment 10 Julien Nabet 2020-02-18 13:42:56 UTC
Let's increase importance since:
- it's easily reproduceable
- it concerns several envs (all?)
- not a corner case
- you may miss the problem when working on your file (so a bit "stealthy")
Comment 11 Eike Rathke 2020-02-20 15:56:24 UTC
*** Bug 126505 has been marked as a duplicate of this bug. ***
Comment 12 Eike Rathke 2020-02-20 15:57:50 UTC
Copying from https://bugs.documentfoundation.org/show_bug.cgi?id=126505#c11

Problem seems to be that in the Excel file format there is no differentiation between filtered rows and manually hidden rows, there is just a hidden="true" attribute which on import does not result in a filtered row.

Can someone with Excel please create a short sample with an AutoFilter range where one row is filtered (which should not be included in a SUBTOTAL(9;...)) and one row is manually hidden (which should be included in a SUBTOTAL(9;...)) and attach it here? Thanks.
Comment 13 Julien Nabet 2020-02-20 16:11:36 UTC
Created attachment 158053 [details]
Test on Excel 365

Eike: on Excel 365, I created a brand new file following example at the end of https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939?ui=en-US&rs=en-US&ad=US.

Subtotal is 303 at the beginning => OK
I filtered data to filter out line with value = 10, subtotal is 293 => OK
I hid line with value 150, subtotal = 143 => ?? We expected 293 if I well understood the last comments.