Recently we at Collabora Productivity have made some substantial improvements to XLSX interoperability related to pivot tables, fixing many issues existed in Calc.
Personally I have committed these patches:
- 4a524379548c916d304a71980f6911231768018 – fixes a case when pivot tables exported by LibreOffice were considered broken and discarded by Excel
- 577b95f4d0cc1cf8836ff35fc30189c64fefb508 – workarounds a standard non-compliance of Excel 2016, which introduced a requirement directly contradicting MS OOXML standard
- b082998401e37e6c7534906601bc481423a6ded0 – introduced saving pivot table group fields to XLSX – an important feature missing up to that moment, without which there was no grouping by e.g. years/months in pivot tables resulting from export
- 0871804bab38e1b2fdc19ff176189356e155f367 – fixed order of items in group fields, without which selecting October in an exported pivot table filter could give November’s results in Excel
- 26e85974a0287ab5869e7ff0145a66b853d66a02 – introduced saving number formats of data fields to XLSX, without which results like $12 345 could become plain 12345 after pivot table refresh in Excel
- 323ac4c2338dde36c10b9889e4b656dc685ba1ed – introduced round-tripping of pivot table style as defined in original XLSX, so that the pivot table would have original look in Excel (may need refresh though) after being edited in Calc
- e913727c7ee3af0bb4031c6829abfb3373306492 – fixed a problem introduced in the second commit in this list, when data field name arrived empty in Excel
- 7f6a6664a1f3a37a97d02d5f0894300aff0d8db5 – allows LibreOffice to import data field names as defined in Excel, thus preserving the names when round-tripping XLSX pivot tables.
These changes allow our customers, and the whole LibreOffice user community, to enjoy better interoperability when using XLSX format. They will be available in LibreOffice version 6.3 later this summer; and they are immediately available for our customers in this week’s Collabora Office 6.0 update 28.
Thanks to our valuable customers who make these improvements possible funding the work!