Bug 150961

Summary:	Pivot table sorting option is gone after resaving in LO in xlsx format
Product:	LibreOffice	Reporter:	Thomas Jiang <thomasjiang>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	NEW ---
Severity:	normal	CC:	adrianlopezmg, aron.budea, raal, stephane.guillou
Priority:	medium	Keywords:	bibisected, bisected, filter:xlsx, regression
Version:	4.4.0.3 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:	Kohei Yoshida
Bug Depends on:
Bug Blocks:	112416
Attachments:	The xlsx file with pivot table option created by Office Excel The xlsx file resaved using LO The xlsx file with pivot table option created by Office Excel 365

Description Thomas Jiang 2022-09-14 17:59:10 UTC

Description:
The pivot table sorting option that was setup in Office Excel is gone after the file is resaved by LO.

Steps to Reproduce:
1. Create a spreadsheet in Office Excel, and create a pivot table.
2. In the pivot table, set one of the row to be sort by desc, and only show top 5 items. Save the Excel file in Office Excel in the format of xlsx.
3. Open the file in LO calc, save it in xlsx format, and close LO calc.
4. Reopen the file using LO calc, one will find that the sorting option/show top  option is gone.

Actual Results:
The pivot table sorting option that was setup in Office Excel is gone after the file is resaved by LO, following the above steps.

Expected Results:
We expect that LO calc can save the sorting option in pivot table even when it saves the file in xlsx file format.


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Version: 7.4.0.3 / LibreOffice Community
Build ID: f85e47c08ddd19c015c0114a68350214f7066f5a
CPU threads: 32; OS: Linux 5.14; UI render: default; VCL: gtk3
Locale: en-CA (en_CA.UTF-8); UI: en-US
Calc: threaded

Comment 1 Thomas Jiang 2022-09-14 18:25:39 UTC

Created attachment 182448 [details]
The xlsx file with pivot table option created by Office Excel

Comment 2 Thomas Jiang 2022-09-14 18:26:50 UTC

Created attachment 182449 [details]
The xlsx file resaved using LO

In this file, even though in the pivot table is still there, but sorting option is gone.

Comment 3 Telesto 2022-09-15 18:08:48 UTC

I confirm with the file attached, however I'm unable to re-create such a pivot table from scratch with LibreOffice or MSO Excel Online.

It might be Office 2013 specific..

Comment 4 Thomas Jiang 2022-09-16 02:28:23 UTC

Telesto, which Office Excel version are you using?

Comment 5 Telesto 2022-09-16 14:18:28 UTC

(In reply to Thomas Jiang from comment #4)
> Telesto, which Office Excel version are you using?

Excel 365

Comment 6 Thomas Jiang 2022-09-16 15:59:21 UTC

Created attachment 182500 [details]
The xlsx file with pivot table option created by Office Excel 365

We created the same pivot table using Office 365, and we can confirm the same issue. Use he attached xlsx by Office Excel 365 as reference.

Comment 7 raal 2022-09-17 12:58:32 UTC

Confirm with Version: 7.5.0.0.alpha0+ / LibreOffice Community
Build ID: e46f9cc4b506c325cbe1060777bbc81fd1630f49
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: cs-CZ (cs_CZ.UTF-8); UI: en-US
Calc: threaded

in 6.2

but not in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

Comment 8 raal 2022-09-17 13:39:59 UTC

This seems to have begun at the below commit.
 61c7a88e60ab9d5079194c16bc0dec0772f9f76d is the first bad commit
commit 61c7a88e60ab9d5079194c16bc0dec0772f9f76d
Author: Matthew Francis <mjay.francis@gmail.com>
Date:   Sun Mar 15 01:59:58 2015 +0800

    source-hash-f5aeaa3845ae794a6320f082b1354f946d96cec1
    
    commit f5aeaa3845ae794a6320f082b1354f946d96cec1
    Author:     Kohei Yoshida <kohei.yoshida@collabora.com>
    AuthorDate: Fri Aug 22 22:57:05 2014 -0400
    Commit:     Kohei Yoshida <kohei.yoshida@collabora.com>
    CommitDate: Thu Aug 28 21:37:08 2014 -0400
    
        bnc#822173: Initial work on exporting pivot cache and pivot table to xlsx.

Comment 9 Stéphane Guillou (stragu) 2023-09-25 20:39:27 UTC

*** Bug 157193 has been marked as a duplicate of this bug. ***

Comment 10 Stéphane Guillou (stragu) 2023-09-25 20:41:01 UTC

Sorting does not need to be created in MSO, all can be done in LO as can be seen in duplicate bug 157193.