Bug 119913 - FILEOPEN: XLSX: Wrong subtotal using autofilter after hard recalculating
Summary: FILEOPEN: XLSX: Wrong subtotal using autofilter after hard recalculating
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5 all versions
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: Function-Subtotal XLSX-Autofilter
  Show dependency treegraph
 
Reported: 2018-09-16 21:51 UTC by p_kongstad
Modified: 2019-03-02 18:12 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
File with correct sum (10.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-09-16 21:51 UTC, p_kongstad
Details
File saved as xlsx and the sum is not correct when opened (5.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-09-16 21:52 UTC, p_kongstad
Details
everything looks the same (1.32 MB, video/mp4)
2018-09-17 06:31 UTC, BogdanB
Details
autofilter ods (120.53 KB, image/jpeg)
2018-09-17 10:53 UTC, MM
Details
autofilter xlsx (119.08 KB, image/jpeg)
2018-09-17 10:53 UTC, MM
Details
autofilter xlsx filter setting (118.63 KB, image/jpeg)
2018-09-17 10:54 UTC, MM
Details

Note You need to log in before you can comment on or make changes to this bug.
Description p_kongstad 2018-09-16 21:51:25 UTC
Created attachment 144920 [details]
File with correct sum

Hi,

When you use subtotal to show the sum using autofilter and save it as .XLSX then when opening the file again the sum is not according to the filter. It works ok with .ODS.

I have attached the same file saved as XLSX and ODS to show the issue.
Comment 1 p_kongstad 2018-09-16 21:52:50 UTC
Created attachment 144921 [details]
File saved as xlsx and the sum is not correct when opened

xlsx file attached
Comment 2 BogdanB 2018-09-17 06:31:08 UTC
Created attachment 144927 [details]
everything looks the same

Please describe better what the problem is exactly. You can see in my video that I open your both files with 2 versions of Libre Office 6.2 and 6.0 and on both files and both versions I get the same sum of 500, and the same values in page.
Comment 3 Xisco Faulí 2018-09-17 09:27:58 UTC
Thank you for reporting the bug.
Unfortunately without clear steps to reproduce it, we cannot track down the origin of the problem.
Please provide a clearer set of step-by-step instructions on how to reproduce the problem.
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the steps are provided
Comment 4 MM 2018-09-17 09:35:58 UTC
To be correct here, the autofilter should only show 100 value, not the 200 from the hidden rows, right ?!
After saving the ods as xlsx and reloading, calc showing 1300 instead.
Works fine when saving as xls.
When saving the ods as another ods and reloading, calc is showing 500.

So confirmed on ubuntu 16.04 x64 with Version: 6.2.0.0.alpha0+
Build ID: 2789bbfd607240f260dfb38b6e9c19c9cf49fca9
CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-09-09_22:35:03
Locale: en-US (en_US.UTF-8); Calc: threaded

and

winodws 7 x64 with Version: 6.0.6.2 (x64)
Build ID: 0c292870b25a325b5ed35f6b45599d2ea4458e77
CPU threads: 3; OS: Windows 6.1; UI render: default
Comment 5 Xisco Faulí 2018-09-17 09:40:50 UTC
Hi MM,
Could you please create a screencast? I can't reproduce what you're saying in comment 4
Comment 6 MM 2018-09-17 10:53:01 UTC
Created attachment 144934 [details]
autofilter ods
Comment 7 MM 2018-09-17 10:53:51 UTC
Created attachment 144935 [details]
autofilter xlsx
Comment 8 MM 2018-09-17 10:54:31 UTC
Created attachment 144936 [details]
autofilter xlsx filter setting
Comment 9 Oliver Brinzing 2018-09-17 16:49:00 UTC
i can confirm this issue with attached "File with correct sum" (sum.odt)

- open "sum.odt"
- save as "sum.xslx"
- close
- open "sum.xslx"
- if 500 is shown, press (Shif)+(Strg)+F9 (hard recalc) to show 1300

Version: 6.1.1.2 (x64)
Build-ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE); Calc: 

Version: 6.2.0.0.alpha0+ (x64)
Build ID: 7595fce391ba2aca49db87c93006302d0c2a64f2
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: de-DE (de_DE); Calc: threaded
Comment 10 BogdanB 2018-09-18 07:01:05 UTC
(In reply to Oliver Brinzing from comment #9)
> i can confirm this issue with attached "File with correct sum" (sum.odt)
 
> - open "sum.odt"
> - save as "sum.xslx"
> - close
> - open "sum.xslx"
> - if 500 is shown, press (Shif)+(Strg)+F9 (hard recalc) to show 1300

Confirm: after this steps and press F9 the sume change from 500 to 1300 
Version: 6.2.0.0.alpha0+ (x64)
Build ID: 18c5089df091bddeb8c2dc339776671964389040
CPU threads: 4; OS: Windows 10.0; UI render: default; 
TinderBox: Win-x86_64@42, Branch:master, Time: 2018-09-12_23:24:12
Locale: ro-RO (ro_RO); Calc: threaded
Comment 11 Xisco Faulí 2018-09-18 19:39:34 UTC
So, I can reproduce the problem back to 

LibreOffice 3.5.0 
Build ID: d6cde02

but not in

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

Actually, it's not needed to do the roundtrip from ODS to XLSX. If the attached XLSX is open with LibreOffice and then hard recalculated, it changes its value.

@Eike, I thought you could be interested in this issue...