Bug 150961 - Pivot table sorting option is gone after resaving in LO in xlsx format
Summary: Pivot table sorting option is gone after resaving in LO in xlsx format
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, filter:xlsx, regression
: 157193 (view as bug list)
Depends on:
Blocks: Pivot-Table-XLSX
  Show dependency treegraph
 
Reported: 2022-09-14 17:59 UTC by Thomas Jiang
Modified: 2024-07-22 12:27 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
The xlsx file with pivot table option created by Office Excel (13.52 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-09-14 18:25 UTC, Thomas Jiang
Details
The xlsx file resaved using LO (10.30 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-09-14 18:26 UTC, Thomas Jiang
Details
The xlsx file with pivot table option created by Office Excel 365 (17.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2022-09-16 15:59 UTC, Thomas Jiang
Details

Note You need to log in before you can comment on or make changes to this bug.
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.