Bug 123225 - Loss of functions in XLSX pivot table's context menu until refreshed (see comment 18)
Summary: Loss of functions in XLSX pivot table's context menu until refreshed (see com...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.0.0.alpha0+
Hardware: All All
: medium minor
Assignee: Bayram Çiçek
URL:
Whiteboard: target:25.8.0 target:25.2.3 inRelease...
Keywords: filter:xlsx
Depends on:
Blocks: Pivot-Table-XLSX
  Show dependency treegraph
 
Reported: 2019-02-07 11:23 UTC by Pradip_0382
Modified: 2025-03-29 19:08 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
atached the xlxs file created in lunix machine (25.64 MB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-07 11:29 UTC, Pradip_0382
Details
test pivot - ods (26.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-02-12 21:56 UTC, raal
Details
atached the xlxs file created in lunix machine (987.79 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-02-22 14:17 UTC, Pradip_0382
Details
ODS with pivot table created in Calc (299.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-02-22 15:10 UTC, Pradip_0382
Details
Screenshot of Excel's context menu (29.22 KB, image/png)
2019-03-08 00:24 UTC, Aron Budea
Details
"Cannot determine which PivotTable field to sort by" when sorting in Excel (25.14 KB, image/png)
2025-02-11 10:44 UTC, Bayram Çiçek
Details
simple ods file with pivot table (21.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2025-02-17 16:10 UTC, Bayram Çiçek
Details
pivotTable#.xml diff after and before patches (329.21 KB, image/png)
2025-03-28 18:55 UTC, Piotr Osada
Details
Before fix (262.28 KB, image/png)
2025-03-29 19:05 UTC, Piotr Osada
Details
After fix (280.74 KB, image/png)
2025-03-29 19:08 UTC, Piotr Osada
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Pradip_0382 2019-02-07 11:23:46 UTC
Description:
I am creating a pivot table with LibreOffice calc 6.1 in linux and try to save as to 'Excel 2007-2019(.xlsx)' to create an .xlsx file to open in window machine with MS office 2016/2010.

When i open the same .xlsx file window machine then i can see only the text data in pivot sheet.all pivot functionality has been lost but same file working good in linux machine where it created.



Steps to Reproduce:
1.Create pivot table with libreoffice calc in linux machine
2. Saveas to .xlsx file with filter 'Excel 2007-2019' 
3. working good
4. Transfer the same .xlsx file to windows machine 
5. open the same .xlsx file in windows machine with ms office 2016


Actual Results:
I can see only the text data in pivot sheet and all pivot functionality moved out.

Expected Results:
all pivot functionality moved should be there in windows machine


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Comment 1 Pradip_0382 2019-02-07 11:29:51 UTC Comment hidden (no-value)
Comment 2 Roman Kuznetsov 2019-02-07 11:45:58 UTC Comment hidden (obsolete)
Comment 3 Roman Kuznetsov 2019-02-07 11:49:10 UTC
(In reply to Roman Kuznetsov from comment #2)
> please attach source ODS file

I'm sorry. You direct save file to XLSX from LibreOffice without creating of ODS file, right?
Comment 4 Pradip_0382 2019-02-11 13:22:06 UTC
yes we can save the file directly xlsx in linux machine with LibreOffice but my problem is that the pivot functionality is not working in windows machine with MS office.
Comment 5 Pradip_0382 2019-02-11 13:31:12 UTC Comment hidden (no-value)
Comment 6 Xisco Faulí 2019-02-12 16:38:50 UTC Comment hidden (obsolete)
Comment 7 raal 2019-02-12 21:56:24 UTC Comment hidden (obsolete)
Comment 8 raal 2019-02-12 21:58:22 UTC Comment hidden (obsolete)
Comment 9 Aron Budea 2019-02-12 23:50:05 UTC
I opened a similar bug report, bug 123421 on various pivot table export issues, and noted that the file exported from LO 6.1.0.3 wasn't even a proper pivot table, and your version is close to that.

Pradip, please give it a try with a 6.3 daily build from here: https://dev-builds.libreoffice.org/daily/master/
Comment 10 Pradip_0382 2019-02-22 06:16:50 UTC
Hi,
I tried with 6.3 libreoffice.
Created pivot report and save as .xlsx in Linux machine.Tried to open the same file in windows machine with Ms office. 

Issue:Missing some functionalities  of pivot report. but after click refresh button i can get all pivot functionalities there.

Is there any fix for to overcome the refresh button click?
Comment 11 Aron Budea 2019-02-22 06:53:08 UTC
Please attach a sample ODS created in LibreOffice, an already buggy file is not useful in itself.
Comment 12 Pradip_0382 2019-02-22 14:17:35 UTC Comment hidden (no-value)
Comment 13 Aron Budea 2019-02-22 14:29:53 UTC Comment hidden (obsolete)
Comment 14 Pradip_0382 2019-02-22 15:10:25 UTC
Created attachment 149532 [details]
ODS with pivot table created in Calc

Attached the requested ods file also created from linux system
Comment 15 Pradip_0382 2019-02-25 11:54:33 UTC Comment hidden (no-value)
Comment 16 Pradip_0382 2019-02-26 15:49:11 UTC Comment hidden (no-value)
Comment 17 raal 2019-02-27 15:26:10 UTC Comment hidden (obsolete)
Comment 18 Aron Budea 2019-03-08 00:23:46 UTC
Thanks for the sample, Pradip! I can confirm the loss of functions in Excel until the pivot table is refreshed.
Another very simple sample for repro (an XLSX created in Excel): attachment 133861 [details].

- Roundtrip the sample in Calc, and open in Excel.
- Right click on a cell inside the table.

=> The menu has a lot fewer options than the original until the pivot table is refreshed.

Observed using LO 6.3.0.0.alpha0+ (4f810905fa74128871f2fe924a3d28a79f4e4261) / Windows 7.
Comment 19 Aron Budea 2019-03-08 00:24:39 UTC
Created attachment 149810 [details]
Screenshot of Excel's context menu
Comment 20 Jürgen Mähnß 2021-11-10 11:36:59 UTC
Similar behaviour on Windows machine:

Create ods with pivot table in calc (LO 7.2.2)
Save copy as xlsx (Excel 2007-365) 

Open with LO: OK
Open xlsx with EXCEL (MS Office Professional Plus 2019): Failure report screen. When you commit, pivot table is converted to text

Open ods directly with MSO EXCEL: error screen but works fine

Open ods with MS EXCEL and save: pivot table stays in proper shape

Open ods in LO and save copy as xls
Open xls in MS EXEL: OK
Comment 21 Roman Kuznetsov 2022-09-17 18:48:31 UTC
*** Bug 150954 has been marked as a duplicate of this bug. ***
Comment 22 QA Administrators 2025-01-17 03:12:42 UTC Comment hidden (obsolete)
Comment 23 Timur 2025-02-04 14:45:25 UTC
Repro in 25.8+ with original sample and with attachment 149245 [details] from bug 123421.
This was improved in 6.2, as before pivot was not recognized in MSO.
Comment 24 Bayram Çiçek 2025-02-11 10:44:20 UTC
Created attachment 199132 [details]
"Cannot determine which PivotTable field to sort by" when sorting in Excel

I guess that's because Calc does not import&export <rowItems> and <colItems> elements of the xl/pivotTables/pivotTable*.xml

You will also get "Cannot determine which PivotTable field to sort by" warning on Excel if you try to e.g. sort the items - alongside the loss of functions.
Comment 25 Bayram Çiçek 2025-02-17 16:10:35 UTC
Created attachment 199252 [details]
simple ods file with pivot table

Hi all.

I have a patch for this here https://gerrit.libreoffice.org/c/core/+/181795 and I need your test & review. 

(Please note that this is for ODS to XLSX conversion (export). I didn't check pivot tables created in Excel -> open in Calc (import).)

Thank you.

---

With the ODS files here I have tested on

Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: f4cf9971ceaf72a630fe1d1dd8154326daca9832
CPU threads: 32; OS: Linux 6.12; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

exported the file as XLSX -> opened in MS Excel Professional Plus 2019 -> I see we have all functionalities on context menu and there is no "Cannot determine which PivotTable field to sort by" warning when sorting.

--- Additionally

(In reply to Pradip_0382 from comment #14)
> Created attachment 149532 [details]
> ODS with pivot table created in Calc
> 
> Attached the requested ods file also created from linux system
this ODS doc is too big and it takes more than 2 mins to open it in LO. So, I simplified the doc and upload it as an attachment. Thank you.
Comment 26 Commit Notification 2025-02-20 08:33:53 UTC
Bayram Çiçek committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/02398c20b206c016f210705f6de312c1eecf0d19

tdf#123225 - export <rowItems> and <colItems>

It will be available in 25.8.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 27 Timur 2025-02-27 08:29:39 UTC
Previous patch fixes the main part of the table. 
Remeining issue is Total row, and in Total column not a full right-click menu.
Follow up patch in review is https://gerrit.libreoffice.org/c/core/+/182242
Comment 28 Commit Notification 2025-02-27 08:54:03 UTC
Bayram Çiçek committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/3b3ba9c329b691ef600ad9ecc419e818b385ce0e

tdf#123225 - export <i t="grand"> of row/colItems

It will be available in 25.8.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 29 Bayram Çiçek 2025-02-27 09:37:24 UTC
(In reply to Timur from comment #27)
> Previous patch fixes the main part of the table. 
> Remaining issue is Total row, and in Total column not a full right-click
> menu.
> Follow up patch in review is https://gerrit.libreoffice.org/c/core/+/182242

Last patch improves the export of row/colItems.

The only issue we should fix in a follow-up patch is that, the test doc here https://bugs.documentfoundation.org/show_bug.cgi?id=123225#c14 TestData_6.3.ods (this is a quite big file):

<colItems count="..."> and <rowItems count="...">. Calc sets the count attribute to 4. But it should be 5. because if PT refreshed on Excel, it'll be set to 5 (which is correct one).

Thanks.

Note: this does not happen for all documents.
Comment 30 Timur 2025-03-13 08:58:04 UTC
There is also https://gerrit.libreoffice.org/c/core/+/182529.
Comment 31 Commit Notification 2025-03-13 11:12:43 UTC
Bayram Çiçek committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/765bf4bff2b8d4ed9569e735dc663ac55ae7a424

tdf#123225 - calculate count attribute correctly

It will be available in 25.8.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 32 Bayram Çiçek 2025-03-13 12:01:14 UTC
This is now ready to test. you can test the with the docs in the attachments uploaded here before.

please do not confuse this bug with 
tdf#150954 - Pivot table is gone after the file is saved in xlsx file.(see https://bugs.documentfoundation.org/show_bug.cgi?id=150954#c4)
Comment 33 Timur 2025-03-13 13:35:56 UTC
Looks good to me. Plese close as Fixed and port to 25.2.
Should there be some issues, we can follow up.
Comment 34 Commit Notification 2025-03-14 10:07:21 UTC
Bayram Çiçek committed a patch related to this issue.
It has been pushed to "libreoffice-25-2":

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

tdf#123225 - export <rowItems> and <colItems>

It will be available in 25.2.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 35 Commit Notification 2025-03-16 10:48:03 UTC
Bayram Çiçek committed a patch related to this issue.
It has been pushed to "libreoffice-25-2":

https://git.libreoffice.org/core/commit/49d4da5aed854e8af44dc2e69d11710631f6cd0e

tdf#123225 - export <i t="grand"> of row/colItems

It will be available in 25.2.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 36 Commit Notification 2025-03-17 03:56:57 UTC
Bayram Çiçek committed a patch related to this issue.
It has been pushed to "libreoffice-25-2":

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

tdf#123225 - calculate count attribute correctly

It will be available in 25.2.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 37 Timur 2025-03-20 11:36:46 UTC
Not sure if we should have this in Release Notes, seems worth.
Comment 38 Piotr Osada 2025-03-28 18:55:12 UTC
Created attachment 200069 [details]
pivotTable#.xml diff after and before patches

Fixed, verified in:

Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: a22f83249699b41317f2d6923878c369e0344598
CPU threads: 16; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Vulkan; VCL: win
Locale: pl-PL (pl_PL); UI: en-US
Calc: CL threaded



Made in Visual Studio Code with "OOXML Viewer"
https://marketplace.visualstudio.com/items?itemName=yuenm18.ooxml-viewer

Comparison of:
xl/pivotTables/pivotTable*.xml

handling result before and after commits:

https://gerrit.libreoffice.org/c/core/+/181795
https://gerrit.libreoffice.org/c/core/+/182242
https://gerrit.libreoffice.org/c/core/+/182529

(In reply to Timur from comment #37)
> Not sure if we should have this in Release Notes, seems worth.

In my opinion, it's worth to mention in Release Notes. Because Pivot Table is quite important component of spreadsheets.
Comment 39 Piotr Osada 2025-03-29 19:05:18 UTC
Created attachment 200083 [details]
Before fix
Comment 40 Piotr Osada 2025-03-29 19:08:48 UTC
Created attachment 200084 [details]
After fix

"Before": XLSX saved in LO:

Version: 25.2.2.2 (X86_64) / LibreOffice Community
Build ID: 7370d4be9e3cf6031a51beef54ff3bda878e3fac
CPU threads: 16; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win
Locale: pl-PL (pl_PL); UI: en-US
Calc: CL threaded



"After": XLSX saved in LO (28.03.2025):

Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: a22f83249699b41317f2d6923878c369e0344598
CPU threads: 16; OS: Windows 11 X86_64 (build 26100); UI render: Skia/Vulkan; VCL: win
Locale: pl-PL (pl_PL); UI: en-US
Calc: CL threaded