[Libreoffice-bugs] [Bug 119913] New: subtotal XLSX

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

Bug ID: 119913
Summary: subtotal XLSX
Product: LibreOffice
Version: 6.1.1.2 release
Hardware: All
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@op.pl

Created attachment 144920
--> https://bugs.documentfoundation.org/attachment.cgi?id=144920&action=edit
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

--- Comment #1 from ***@op.pl ---
Created attachment 144921
--> https://bugs.documentfoundation.org/attachment.cgi?id=144921&action=edit
File saved as xlsx and the sum is not correct when opened

xlsx file attached

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

BogdanB <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #2 from BogdanB <***@gmail.com> ---
Created attachment 144927
--> https://bugs.documentfoundation.org/attachment.cgi?id=144927&action=edit
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.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

Xisco Faulí <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@libreoffice.org
Status|UNCONFIRMED |NEEDINFO
Ever confirmed|0 |1

--- Comment #3 from Xisco Faulí <***@libreoffice.org> ---
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

MM <***@vivaldi.net> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW

--- Comment #4 from MM <***@vivaldi.net> ---
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-***@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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

--- Comment #5 from Xisco Faulí <***@libreoffice.org> ---
Hi MM,
Could you please create a screencast? I can't reproduce what you're saying in
comment 4

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

--- Comment #6 from MM <***@vivaldi.net> ---
Created attachment 144934
--> https://bugs.documentfoundation.org/attachment.cgi?id=144934&action=edit
autofilter ods

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

--- Comment #7 from MM <***@vivaldi.net> ---
Created attachment 144935
--> https://bugs.documentfoundation.org/attachment.cgi?id=144935&action=edit
autofilter xlsx

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

--- Comment #8 from MM <***@vivaldi.net> ---
Created attachment 144936
--> https://bugs.documentfoundation.org/attachment.cgi?id=144936&action=edit
autofilter xlsx filter setting

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

Oliver Brinzing <***@gmx.de> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmx.de

--- Comment #9 from Oliver Brinzing <***@gmx.de> ---
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

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

Oliver Brinzing <***@gmx.de> changed:

What |Removed |Added
----------------------------------------------------------------------------
OS|Linux (All) |All

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

--- Comment #10 from BogdanB <***@gmail.com> ---
(In reply to Oliver Brinzing from comment #9)

Post by b***@bugs.documentfoundation.org
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-***@42, Branch:master, Time: 2018-09-12_23:24:12
Locale: ro-RO (ro_RO); Calc: threaded

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

Xisco Faulí <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
Summary|subtotal XLSX |FILEOPEN: XLSX: Wrong
| |subtotal using autofilter
| |after hard recalculating
Version|6.1.1.2 release |3.5 all versions

--- Comment #11 from Xisco Faulí <***@libreoffice.org> ---
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...

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=119913

Roman Kuznetsov <***@mail.ru> changed:

What |Removed |Added
----------------------------------------------------------------------------
Keywords| |filter:xlsx
Blocks| |109330, 108897


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=108897
[Bug 108897] [META] XLSX (OOXML) bug tracker
https://bugs.documentfoundation.org/show_bug.cgi?id=109330
[Bug 109330] [META] Subtotal function bugs and enhancements