Created attachment 113150 [details] affected file The attached file is corrupted after saving with LibreOffice. 1) Open the attached file (created with MS Office) 2) Go on Tools -> Options -> LibreOffice Calc -> Formula 3) Set Formula syntax as "Excel A1" 4) open the sheet "pivot" (it's the last one) 5) Rx click -> Refresh (the Pivot Table is ok) 6) Save the file with the same file format (xlsx) 7) Close the file 8) Re open with MS Office. MS Office shows a dataloss message. 9) Click ok and try to recover the file 10) open the sheet "pivot" The Pivot Table is gone. There is only a flat table. If you try to open the file with LibreOffice the Pivot Table is ok. Tested on: * Version: 4.5.0.0.alpha0+ * Build ID: 1845b6af3991ca5521eef48aafe1d0489e2ff8f6 * TinderBox: Win-x86@42, Branch:master, Time: 2015-02-02_09:30:48 * Locale: it_IT * OS: Windows 7 Home Premium SP1 x86 * Version: 4.2.8.2 * Build ID: 48d50dbfc06349262c9d50868e5c1f630a573ebd * Locale: it_IT * OS: Windows 7 Home Premium SP1 x86 * Version: 4.5.0.0.alpha0+ * Build ID: 52d1de87e9a77fc7929309b0365bd786406ce022 * TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-02-04_01:50:43 * Locale: it_IT * OS: Ubuntu 14.04.1 x86_64 * Version: 4.3.6.1 * Build ID: 9629686a67dd1f357477c13325e45a66f3452bb9 * Locale: it_IT * OS: Ubuntu 14.04.1 x86_64 Office 365 * Version: 15.0.4675.1003 * Locale: it_IT * OS: Windows 7 Home Premium SP1 x86
See also https://bugs.documentfoundation.org/show_bug.cgi?id=78614 (PIVOTTABLE: pivot table not saved in xlsx)
Hello Marina, why it's not duplicate of bug 78614? Looks identical to me. Thank you
Yep, can confirm, but looks like a dupe, so closing. Win 8 32-bit MSO 2013 LibreOffice: Version: 4.5.0.0.alpha0+ Build ID: 96520ae764f0b47fa7f8c7ca42864cffef05bdb3 TinderBox: Win-x86@39, Branch:master, Time: 2015-02-08_06:56:42 Locale: fi_FI *** This bug has been marked as a duplicate of bug 78614 ***
This isn't a dupe. If you try to open the file with LibreOffice the Pivot Table works fine. If you try to open the file with Microsoft Office the file is corrupted. Please, follow all the steps to reproduce the bug. The Pivot Table works fine ONLY with LibreOffice. The problem could be related to bug 78614 but I'm not sure is the same.
(In reply to Marina Latini from comment #0) > The attached file is corrupted after saving with LibreOffice. > .. > 8) Re open with MS Office. MS Office shows a dataloss message. Data loss sucks. This bug just earned a MAB Badge. > * Version: 4.3.6.1 > * Build ID: 9629686a67dd1f357477c13325e45a66f3452bb9 > * Locale: it_IT > * OS: Ubuntu 14.04.1 x86_64 Blocks -> mab4.3 Priority -> highest Marina: is this a regression?
(In reply to Robinson Tryon (qubit) from comment #5) > > Blocks -> mab4.3 > Priority -> highest > > Marina: is this a regression? Retested with: * Version: 3.6.7.2 * Build ID: e183d5b * Locale: it_IT * OS: Windows 7 Home Premium SP1 x86 * OS: Ubuntu 14.04.2 LTS x86_64 I can confirm the bug. Set new version -> 3.6.7.2 I can't test with versions older than 3.6.7.2
I'm sorry for the wrong version. Affected version changed from 3.5.7.2 to 3.6.7.2
Created attachment 114002 [details] Affected file (saved with LO 3.5.7.2)
(In reply to Marina Latini from comment #6) > > Marina: is this a regression? > > Retested [and confirmed] with: > * Version: 3.6.7.2 > I can't test with versions older than 3.6.7.2 What's the trouble with older versions? TESTING with 3.5.7.2 + Ubuntu 14.04 (In reply to Marina Latini from comment #0) > The attached file is corrupted after saving with LibreOffice. REPRO Steps > 1) Open attachment 113150 [details] (created with MS Office) > 2) Go on Tools -> Options -> LibreOffice Calc -> Formula > 3) Set Formula syntax as "Excel A1" Ok > 4) open the sheet "pivot" (it's the last one) (was already the open sheet) > 5) Rx click -> Refresh (the Pivot Table is ok) > 6) Save the file with the same file format (xlsx) > 7) Close the file LibreOffice freezes for a while (my machine isn't the fastest ;-), but it does save the file eventually. The size explodes 10x from 349K -> 3.6MB (attachment 114002 [details]). > 8) Re open with MS Office. MS Office shows a dataloss message. I don't have MS-Office on this machine, but we should definitely try opening the result of this process w/MS-Office.
(In reply to Robinson Tryon (qubit) from comment #9) > > What's the trouble with older versions? > > TESTING with 3.5.7.2 + Ubuntu 14.04 Same problem. The file is corrupted for MS Office. :( > > I don't have MS-Office on this machine, but we should definitely try opening > the result of this process w/MS-Office. MS Office shows a dataloss message and the pivot table is gone :( tested with Office 365 * Version: 15.0.4675.1003 * Locale: it_IT * OS: Windows 7 Home Premium SP1 x86
Created attachment 114004 [details] office error (italian) translation from It to En: ----------------------------- removed : /xl/pivotTables/pivotTable18.xml recovered: /xl/worksheets/sheet16.xml with XML error. Not recoverable error row 2, column 0. recovered: /xl/worksheets/sheet17.xml Removed records: named range /xl/workbook.xml -----------------------------
I do not reproduce the bug behaviour as stated in the original report. Version: 5.0.0.1 Build ID: 9a0b23dd0ab9652e0965484934309f2d49a7758e Locale: zh-CN (zh_CN) When finish step 1-7 and reopen the xlsx file with MSO2010, the pivot table is OK. (althrough it shows a warning message that there is un-readable content and ask whether you want to repair the document, if you select yes, MSO tells you that the pivot cache in /xl/pivotCache/pivotCacheDefinition1.xml is repaired, and then shows a good pivot table).
@Marina Latini please retest with LibO 5.0 RC1 and tell if the issue is gone or not.
Marina - an interesting one =) this really belongs in Calc. I guess we are just screwing up the XML in that stream in some (perhaps trivial) way - any chance you can dig out the XML fragments involved ? could you try perturbing the XML ordering (attributes & elements), and pasting more details here ? would also be worth comparing what MS Office produces for an identical PivotTable - so that we can iterate towards that. Thanks ! =)
*** Bug 78614 has been marked as a duplicate of this bug. ***
Actually it is sufficient to open the original Excel file in LO and save it under a different name.xlsx and open that in Excel2010 to reproduce. (LO 5.2 master) Pretty-printing and comparing both files' xl/pivotCache/pivotCacheDefinition1.xml streams shows they are quite different, additionally to different <sharedItems> attributes the LO-saved one contains single values in <sharedItems><n> elements which the Excel-saved one does not. Plus, the original contains one further <extLst> element with an <ext uri=...> and <x14:pivotCacheDefinition/>, though adding that manually to the LO-saved stream didn't help.
Migrating Whiteboard tags to Keywords: (bibisectRequest)
(In reply to Eike Rathke from comment #16) > Pretty-printing and comparing both files' > xl/pivotCache/pivotCacheDefinition1.xml streams shows they are quite > different [...] What's the next step here? Is there any testing that an interested volunteer could perform, or does this bug just need dedicated dev time? Keywords -> filter:xlsx
I don't see any evidence that this is a regression. Removing bibisectRequest.
The Attached file, once resaved with LO 5.3.1.1 x64 (win7 pro) and then reopened with MS Office 2013 makes MS Office crash when trying to Update the PivotTable. When reopener with MS Office it asks me to repair the file, which I do.
I see the same crash when updating the Pivot table in MS Office (16) after repairing it.
A patch is submitted for review: https://gerrit.libreoffice.org/39018 This patch *only* targets Excel crashing on pivot table update.
A second patch to fix "corrupted" warning is submitted: https://gerrit.libreoffice.org/39114
Mike Kaganski committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=430774c4edcdba3e6a4e383d9ac9345a517e227f tdf#89139: dump pivotField items It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Mike Kaganski committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=47f0e83989c4c03d9690229b6433a5541032a3eb tdf#89139: pivotCache: output sharedItems children only for string fields It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Hello. Is this supposed to already be in libo-master-2017-06-28_00.47.42_LibreOfficeDev_6.0.0.0.alpha0_Win_x86.msi?
(In reply to Gerhard Schaber from comment #26) I'd expect it there.
Then either I am doing something wrong, or it just still crashes. I will try again with a newer build tomorrow, and get back to you after that.
Just to be on the save side. By then the fix will definitely be included.
With the attached example, I get an error loading the file in Excel 2013 after I saved it with libo-master-2017-06-29_00.58.30_LibreOfficeDev_6.0.0.0.alpha0_Win_x86 (sorry for the German): Entferntes Feature: PivotTable-Bericht von /xl/pivotTables/pivotTable1.xml-Part (PivotTable-Ansicht) Entfernte Datensätze: Eigenschaften der Arbeitsmappe von /xl/workbook.xml-Part (Arbeitsmappe) But it actually will not crash after refreshing the Pivot table. My own Excel file still crashes, though. I am trying reduce it to a sample spreadsheet.
Steps to make it still crash (with my Excel file at least): - Save the file with Excel 2013 when the Pivot table is still empty (just headers). - Close Excel - Open the file in LibreOfficeDev - Add some data - Refresh the Pivot table - Save and close the spreadsheet - Open it with Excel 2013 - Refresh the Pivot table
Yeah, Excel is still removing the pivot table (steps from description). Win 8.1 32-bit MSO 2013 LibreOffice Version: 6.0.0.0.alpha0+ Build ID: cac5c9f6081590b0548d3116bc4cd4a2509ec576 CPU threads: 4; OS: Windows 6.29; UI render: default; TinderBox: Win-x86@42, Branch:master, Time: 2017-07-01_00:41:48 Locale: fi-FI (fi_FI); Calc: group
Hello Mike. I would like to continue working on this issue. I have already prepared next patch which improve current Pivot support, according to documentation: https://technet.microsoft.com/pl-pl/library/documentformat.openxml.spreadsheet.shareditems.aspx The Excel is complains also about inproper containsMixedTypes field use. If you don't mind, I would like also add some unit tests for pivot. Please let me know, if I could continue your work.
(In reply to Bartosz from comment #33) Hi Bartosz! Of course, you are welcome to continue it, and I'll be happy to provide you with all the help I can! Feel free to re-assign the issue to you. Let's continue discussion of your gerrit patch there. Great to see progress on this!
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=98d7a294180915a0b090000e808fe65c64695b5d tdf#89139 Fix PivotCache fields according to OOXML specification It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Created attachment 135503 [details] Minimal file on which it is possible to reproduce issue with import/export date/time on Pivot
With 98d7a294180915a0b090000e808fe65c64695b5d there was an fix for proper export of fields: - XML_containsInteger - XML_containsBlank - XML_containsMixedTypes - XML_containsSemiMixedTypes There is still missing fix for export date/time fields. Expected fields format: <sharedItems containsDate="1" containsBlank="1" containsMixedTypes="1" minDate="2014-07-07T09:30:31" maxDate="2017-03-24T08:38:46" count="21"> <m/> <d v="2016-06-15T12:18:34"/> <d v="2017-02-10T09:57:31"/> <d v="2017-03-06T07:26:12"/> ... <d v="2014-07-07T09:30:31"/> <d v="2016-09-27T13:39:14"/> <d v="2014-10-01T13:56:34"/> <d v="2014-12-23T10:50:57"/> </sharedItems> Currently it imports/exports as float numbers: <cacheField name="pwdLastSet" numFmtId="0"> <sharedItems containsString="0" containsBlank="1" containsNumber="1" minValue="41827.3961887234" maxValue="42818.3602584734"></sharedItems> </cacheField> This is next issue, which prevents from import .xlsx without error by MS Excel
Created attachment 135753 [details] File which causing problems after export by LO 6.0 I noticed regression with LO 6.0 with attached file. After export attached file by LO 5.4, there is an error inside: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml all these issues was already resolved on master with: https://gerrit.libreoffice.org/#/c/41162/ and https://gerrit.libreoffice.org/#/c/40610/ Unfortunately, after export with LO 6.0 additional error apears inside: /xl/pivotTables/pivotTable1.xml I suspect that this regression could be introduced by: https://cgit.freedesktop.org/libreoffice/core/commit/?id=430774c4edcdba3e6a4e383d9ac9345a517e227f @Mike Could you please check that?
(In reply to Bartosz from comment #38) > I noticed regression with LO 6.0 with attached file. > After export attached file by LO 5.4, there is an error inside: > PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml > Unfortunately, after export with LO 6.0 additional error apears inside: > /xl/pivotTables/pivotTable1.xml > ... Strictly speaking, this is not a regression. Previously, both cache and table were exported incorrectly; the cache problems were detected, and it was discarded, and after that, the dangling table could not be refreshed (excel crashed). Now the cache is exported OK, and Excel detects that table is out of sync with cache, which is why it complains. A patch is under review: https://gerrit.libreoffice.org/41509
Mike Kaganski committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d828577bcd522eaa57e40f889c8f3ae563ea1a75 tdf#89139: list all items in pivot table definition, incl. hidden It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=baed960b8ce55370a040d0c0f49bd3fdc16bdf84 tdf#89139 Fix exporting of DateTime to CacheDefinition according to ISO 8601 It will be available in 6.0.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
*** Bug 108343 has been marked as a duplicate of this bug. ***
Created attachment 135962 [details] affected file, without any PivotTables Fields I noticed that the issue also appear when there is no any selected PivotTables Fields (see attachment). @Marina Could you please explain how are you creating this Pivot table? Please delete old pivot and recreate it. We already pushed a lot of fixes into LibreOffice 6.0. Please test the Pivot with LibreOffice 6.0 Dev, available at: http://dev-builds.libreoffice.org/daily/master/
Hi guys, There were some other pivot table fixes pushed to master in the last two weeks. One related issue for example handling of error cells (see bug 112893). It would be good to test the attached test document again and check whether there is still any issue, otherwise we can close this bug.
Created attachment 136835 [details] Comparison of pivot tables from 2017-10-07 I do not have any crashes with Excel 2013 anymore, and also no error message anymore. However, there still seems to be an issue how the Pivot table is arranged after an update of the table. Attached are a couple of screenshots of a spreadsheet stored with LO Dev from 2017-10-07. pivot2.jpg is how it looks like when I store the file. pivot3.jpg is how it looks like after loading it with that LO version again. pivot4.jpg is how Excel shows it after loading. And pivot5.jpg how it looks like after refreshing the Pivot table in Excel. I haven't been able to strip the original document down to something simple, that still shows the same behavior, yet.
Created attachment 136836 [details] Test file used for screenshots Stripped down version of the test file I used to create the previous screenshots.
Created attachment 136848 [details] Test file created with Excel If you refresh the Pivot table in the Excel test file with LO, it still looks normal, until you reload the file.
(In reply to Gerhard Schaber from comment #47) > Created attachment 136848 [details] > Test file created with Excel > > If you refresh the Pivot table in the Excel test file with LO, it still > looks normal, until you reload the file. Thanks for the testing. I also can reproduce this layout issue. I splitted it to a separate bug report (bug 113159). It makes easier to handle bugs if we have different issues with separate bug number. Let's close this bug report, since it was about corruption and data loss which is fixed now.
Sure. Thanks.