Bug 99913 - Importing autofiltered XLSX and selecting cells copies hidden cells, too
Summary: Importing autofiltered XLSX and selecting cells copies hidden cells, too
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 release
Hardware: All All
: high major
Assignee: Tünde Tóth
URL:
Whiteboard: target:7.2.0 target:7.1.3
Keywords: filter:xlsx, notBibisectable, regression
: 126505 137519 (view as bug list)
Depends on:
Blocks: XLSX-Autofilter
  Show dependency treegraph
 
Reported: 2016-05-17 14:39 UTC by Peter Gervai
Modified: 2021-04-08 12:38 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


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

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: 5.2.0.0.alpha1+
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 Comment hidden (obsolete)
Comment 6 Timur 2018-07-10 17:46:22 UTC Comment hidden (obsolete)
Comment 7 Peter Gervai 2019-03-13 01:24:11 UTC Comment hidden (obsolete)
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: 6.1.5.1
> 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
Version: 6.3.0.0.alpha0+
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.
Comment 14 b. 2020-10-17 12:02:28 UTC
*** Bug 137519 has been marked as a duplicate of this bug. ***
Comment 15 Commit Notification 2021-03-30 11:36:49 UTC
Tünde Tóth committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/ae60c27f936642ecd4391d11fcd73a6d461d0b7f

tdf#99913 XLSX import: set filtered flag for rows

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 16 Commit Notification 2021-04-06 10:42:53 UTC
Tünde Tóth committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/adef1cc017517882f4630e31fa643102c21faad0

tdf#99913 XLSX import: set filtered flag for rows

It will be available in 7.1.3.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 NISZ LibreOffice Team 2021-04-08 12:38:15 UTC
Verified in: 

Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: ea4fb1559f7b99a0bfaf18f26cb3b6972c9cde1c
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded