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:
Created attachment 148977 [details] atached the xlxs file created in lunix machine
please attach source ODS file
(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?
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.
Can you confirm if the xlsx file created with pivot table in Linux machine can be opened in MS office (Windows Machine) without any lost of pivot functionality. Please let me know the process.
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone else confirms it.
Created attachment 149242 [details] test pivot - ods Tested with Version: 6.3.0.0.alpha0+ Build ID: c7ad7849d54fd3dad67e7779102f65b8b2f04881 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; after save as xlsx I can open pivot in excel2010. We need your .ods file to reproduce the bug.
Please send us a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document.
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/
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?
Please attach a sample ODS created in LibreOffice, an already buggy file is not useful in itself.
Created attachment 149530 [details] atached the xlxs file created in lunix machine Attached the xlsx file created in linux machine with libreoffice 6.3
Again, please attach the spreadsheet in ODS format, so the correctness of export can be tested both now and after a future fix.
Created attachment 149532 [details] ODS with pivot table created in Calc Attached the requested ods file also created from linux system
Hi Team, Any update on my request please? Regards, Pradip
Dear Baron, Can I have any update please. Regards, Pradip
correcting status
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.
Created attachment 149810 [details] Screenshot of Excel's context menu
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
*** Bug 150954 has been marked as a duplicate of this bug. ***
Dear Pradip_0382, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
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.
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.
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.
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.
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
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.
(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.
There is also https://gerrit.libreoffice.org/c/core/+/182529.
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.
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)
Looks good to me. Plese close as Fixed and port to 25.2. Should there be some issues, we can follow up.
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.
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.
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.
Not sure if we should have this in Release Notes, seems worth.
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.
Created attachment 200083 [details] Before fix
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