Bug 117274 - FILESAVE XLSX: Drawing Objects (Shapes) multiply every time an XLSX file with comments roundtrips
Summary: FILESAVE XLSX: Drawing Objects (Shapes) multiply every time an XLSX file with...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: medium normal
Assignee: László Németh
URL:
Whiteboard: target:6.4.0 target:6.5.0 target:6.4.0.1
Keywords: filter:xlsx
: 127051 128818 129831 130255 130527 (view as bug list)
Depends on:
Blocks: OOXML-Shapes XLSX-Comments
  Show dependency treegraph
 
Reported: 2018-04-27 08:32 UTC by Gabor Kelemen
Modified: 2020-03-19 15:29 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel 2010 (17.48 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-27 08:32 UTC, Gabor Kelemen
Details
The example file in Calc 6.1 (183.77 KB, image/png)
2018-04-27 08:41 UTC, Gabor Kelemen
Details
The example file saved by LO6.1, extended with a new comment (17.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-27 08:42 UTC, Gabor Kelemen
Details
The example file as saved by Calc (147.60 KB, image/png)
2018-04-27 08:54 UTC, Gabor Kelemen
Details
The example file saved after Calc by Excel 2010 (19.21 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-27 08:57 UTC, Gabor Kelemen
Details
Importing the roundtripped file to Calc (152.04 KB, image/png)
2018-04-27 09:05 UTC, Gabor Kelemen
Details
Saving and reopening the roundtripped file in Calc (152.35 KB, image/png)
2018-04-27 09:07 UTC, Gabor Kelemen
Details
Importing the roundtripped file to Calc - after a second RT (130.54 KB, image/png)
2018-04-27 09:21 UTC, Gabor Kelemen
Details
Saving and reopening the roundtripped file in Calc - after a second RT (135.47 KB, image/png)
2018-04-27 09:22 UTC, Gabor Kelemen
Details
The original file after a few roundtrips to illustrate the growth of objects (18.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-04-27 09:23 UTC, Gabor Kelemen
Details
The original file after a few roundtrips, and with print preview. (262.94 KB, image/png)
2018-04-27 09:25 UTC, Gabor Kelemen
Details
The .vml file from the original example file (1.10 KB, text/plain)
2018-04-27 09:29 UTC, Gabor Kelemen
Details
The .vml file from the example file after a Calc export (1003 bytes, text/plain)
2018-04-27 09:30 UTC, Gabor Kelemen
Details
We have retested this problem and it seems to be fixed in version 6.1.2. (354.08 KB, image/png)
2018-11-26 09:49 UTC, NISZ LibreOffice Team
Details
We have retested this problem and it seems to be fixed in version 6.1.2. (17.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-11-26 09:50 UTC, NISZ LibreOffice Team
Details
Result of a roundtrip on the original file with current master (682.17 KB, image/png)
2019-02-15 12:24 UTC, Gabor Kelemen
Details
Roundtripped original file with LO master - Excel (11.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-10-14 12:39 UTC, NISZ LibreOffice Team
Details
Screenshot of the example files in Calc after roundtrip (62.49 KB, image/png)
2019-10-14 12:42 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-04-27 08:32:36 UTC
Created attachment 141676 [details]
Example file from Excel 2010

Drawing Objects/shapes from the comments object multiply every time, when an XLSX file with comments is saved in LibreOffice Calc. The bug only occurs if the file was saved in LibreOffice Calc, then in Microsoft Excel and then open/saved in LibreOffice Calc again.
It does not matter however, if it was originally created in LibreOffice Calc or Microsoft Excel, whether the comments were added in Calc or Excel.

Steps to reproduce:
1. Create a new XLSX file in Microsoft Excel 2010/2013/216 and add a comment to a cell. Save the file.
2. Open the file with LibreOffice Calc (turn on the Navigator, just see what is happening), add a comment to another cell and save as *.XLSX
3. Open the file with Microsoft Excel and add a comment to a cell, and save as *.XLSX
4. Open the file with LibreOffice Calc and watch the navigator: there will be two Drawing Objects, but we cannot see them in the spreadsheet. We only see if we want to print the spreadsheet. (Print Preview)
5. Save the file as *.XLSX and reload the file: the original Drawing Objects name will appear as “CustomShape1”.
6. If we open this file with Microsoft Excel and save again, and then import/export with LibreOffice Calc: the Drawing Objects multiply again.

Actual results:
Every time we open and save an *.xlsx file with LibreOffice Calc, which was saved earlier with Microsoft Excel and contains comments, the Drawing Objects will be duplicated and we cannot print normally the spreadsheet, only if we turn off the Drawing Objects print checkbox.

We tried to look into the generated XLSX files. We see that for each comment a new drawing object is created into xl/drawings/vmlDrawing1.vml. 
This file when saved/created in Calc is considered broken by Excel, then the object contained in it is moved into xl/drawings/drawing1.xml and a new vmlDrawing1.vml is created. 
Again opening and saving the file in Calc, this vmlDrawing1.vml file gets broken again, and Excel adds its content to drawing1.xml and creates a new vmlDrawing1.vml.

Expected results:
There should no drawing object appear in the Navigator after we open a spreadsheet with comments.


Version: 6.1.0.0.alpha0+
Build ID: aa45e2745f14c5626fe163939dc7d101efe9d1cd
CPU threads: 4; OS: Windows 6.1; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-04-25_00:27:22
Locale: hu-HU (hu_HU); Calc: group
Comment 1 Gabor Kelemen 2018-04-27 08:41:59 UTC
Created attachment 141678 [details]
The example file in Calc 6.1
Comment 2 Gabor Kelemen 2018-04-27 08:42:37 UTC
Created attachment 141679 [details]
The example file saved by LO6.1, extended with a new comment
Comment 3 Gabor Kelemen 2018-04-27 08:54:54 UTC
Created attachment 141682 [details]
The example file as saved by Calc
Comment 4 Gabor Kelemen 2018-04-27 08:57:15 UTC
Created attachment 141683 [details]
The example file saved after Calc by Excel 2010
Comment 5 Gabor Kelemen 2018-04-27 09:05:09 UTC
Created attachment 141685 [details]
Importing the roundtripped file to Calc
Comment 6 Gabor Kelemen 2018-04-27 09:07:24 UTC
Created attachment 141686 [details]
Saving and reopening the roundtripped file in Calc
Comment 7 Gabor Kelemen 2018-04-27 09:21:56 UTC
Created attachment 141690 [details]
Importing the roundtripped file to Calc - after a second RT
Comment 8 Gabor Kelemen 2018-04-27 09:22:53 UTC
Created attachment 141691 [details]
Saving and reopening the roundtripped file in Calc - after a second RT
Comment 9 Gabor Kelemen 2018-04-27 09:23:37 UTC
Created attachment 141693 [details]
The original file after a few roundtrips to illustrate the growth of objects
Comment 10 Gabor Kelemen 2018-04-27 09:25:12 UTC
Created attachment 141694 [details]
The original file after a few roundtrips, and with print preview.

The hidden drawing objects appear on top in the print preview hiding the first pages contents.
Comment 11 Gabor Kelemen 2018-04-27 09:29:12 UTC
Created attachment 141695 [details]
The .vml file from the original example file
Comment 12 Gabor Kelemen 2018-04-27 09:30:05 UTC
Created attachment 141696 [details]
The .vml file from the example file after a Calc export
Comment 13 Buovjaga 2018-05-24 12:05:17 UTC
Repro from scratch

Win 8.1 32-bit
MSO 2013
LibO Version: 6.1.0.0.alpha1+
Build ID: 23c5125148a8110d88385b29570bf0b7d4400458
CPU threads: 4; OS: Windows 6.3; UI render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-05-12_00:15:25
Locale: fi-FI (fi_FI); Calc: group
Comment 14 NISZ LibreOffice Team 2018-11-26 09:49:46 UTC
Created attachment 147035 [details]
We have retested this problem and it seems to be fixed in version 6.1.2.
Comment 15 NISZ LibreOffice Team 2018-11-26 09:50:12 UTC
Created attachment 147036 [details]
We have retested this problem and it seems to be fixed in version 6.1.2.
Comment 16 Gabor Kelemen 2018-12-04 10:46:18 UTC
Errr, a small update.

My colleague who retested this used Excel 2007 on his machine, which created no errors.
But newer Excel versions are still setting aside the VML files created with LO, so: back to new, sorry for the noise.
Comment 17 Xisco Faulí 2018-12-05 16:31:46 UTC Comment hidden (obsolete)
Comment 18 Buovjaga 2018-12-05 18:11:22 UTC Comment hidden (obsolete)
Comment 19 miltonh26 2018-12-20 08:00:18 UTC
Just confirmed against a file that was putting many shape objects onto the sheets which contained comments when saved by LO Calc. This was using the most recent release of LO version 6.1.4.2. Sure enough, deleting all the comments from the sheet and saving the file again, the shape objects were not present when the file was reopened. Of course, I had manually deleted all the shape objects beforehand. 

It does not matter if the comment was written in Excel or LO Calc.  

This bug has been present for a very long time and has caused untold trouble giving people Calc files saved by LO where the extra shape objects were added to the file because it contained comments.

This bug really needs to be fixed with a high priority. Thanks.
Comment 20 miltonh26 2019-02-15 04:39:17 UTC Comment hidden (obsolete)
Comment 21 Xisco Faulí 2019-02-15 09:47:03 UTC Comment hidden (obsolete)
Comment 22 Gabor Kelemen 2019-02-15 10:25:58 UTC
(In reply to Xisco Faulí from comment #21)
> (In reply to miltonh26 from comment #20)
> > Appears to be fixed in 1:6.1.4-0ubuntu0.18.10.1 running Kubuntu 18.10.
> 
> @Gabor, could you please doublecheck ?

Sure, but since he mentions Kubuntu, I don't have huge hopes. It's *Excel* that creates phantom objects from our slightly broken vml files.

My team really needs to solve this mystery :).
Comment 23 Gabor Kelemen 2019-02-15 12:24:25 UTC
Created attachment 149312 [details]
Result of a roundtrip on the original file with current master

My team still sees this happening on a full LO-Excel-LO RT with the master bibisect repo.
Comment 24 László Németh 2019-09-12 13:52:34 UTC
Proposed fix: https://gerrit.libreoffice.org/#/c/78831/
Comment 25 László Németh 2019-09-13 06:00:28 UTC
tdf#117274 XLSX export: fix VML comment shape type id

to avoid shape multiplication in a mixed environment.

MSO needs the special unescaped VML comment shape type
id "_x0000_t202" to avoid of generating also DrawingML
shapes from the VML ones. Calc export escaped the shape
type ids ("_x005F_x0000_t202"), keeping also the DrawingML
shapes, so every round trip between LO and MSO resulted
more and more broken shapes in the document.

See also commit ea7274259a211170ee9f5a8714a2f9acd8bed6d8
"tdf#114842 Fix comment shape export for Calc".
Comment 26 Commit Notification 2019-09-13 06:01:05 UTC
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/c5d37d324cd6d17114c209de1862344ad58a1d93%5E%21

tdf#117274 XLSX export: fix VML comment shape type id

It will be available in 6.4.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 NISZ LibreOffice Team 2019-10-14 12:39:57 UTC
Created attachment 154994 [details]
Roundtripped original file with LO master - Excel

Seems to work fine in:

Version: 6.4.0.0.alpha0+ (x86)
Build ID: 489eef894e7034873ad262f9dfca554022db1b09
CPU threads: 4; OS: Windows 6.3; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2019-10-01_23:43:38
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: CL
Comment 28 NISZ LibreOffice Team 2019-10-14 12:42:19 UTC
Created attachment 154995 [details]
Screenshot of the example files in Calc after roundtrip

Attachment #154994 [details] in Calc master has no unwanted drawing objects unlike those seen on attachment #141690 [details]
Comment 29 Timur 2019-11-20 18:45:26 UTC
It says "This patchset was cherry picked to branch libreoffice-6-3 as commit baf69bba158f4bfb24f5794b6b248b328387eba7" but not here.
Comment 30 Commit Notification 2019-12-06 07:11:27 UTC
László Németh committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/579db0d2d3d2ddf5a5f8feaa2e7cc4dde81d7701

cleanup "tdf#117274 XLSX export: fix VML comment shape type id"

It will be available in 6.5.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 31 Commit Notification 2019-12-06 13:15:56 UTC
László Németh committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/35191846feb19751e247cd228d7dcc6ddfdf2c8b

cleanup "tdf#117274 XLSX export: fix VML comment shape type id"

It will be available in 6.4.0.1.

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 NISZ LibreOffice Team 2020-01-06 13:31:49 UTC
*** Bug 129831 has been marked as a duplicate of this bug. ***
Comment 33 NISZ LibreOffice Team 2020-01-30 08:33:27 UTC
*** Bug 130255 has been marked as a duplicate of this bug. ***
Comment 34 NISZ LibreOffice Team 2020-02-06 14:45:50 UTC
*** Bug 127051 has been marked as a duplicate of this bug. ***
Comment 35 Timur 2020-02-13 09:07:56 UTC
*** Bug 130527 has been marked as a duplicate of this bug. ***
Comment 36 Timur 2020-02-13 09:26:12 UTC
Hello Gabor, László.
I kindly ask you to:
1. explain what happens with already wrong files like attachment 155840 [details] 
2. see if bug 128818 is a duplicate or it needs some fix   
3. backport to 6.3.
Comment 37 NISZ LibreOffice Team 2020-02-13 09:48:30 UTC
(In reply to Timur from comment #36)
> Hello Gabor, László.
> I kindly ask you to:
> 1. explain what happens with already wrong files like attachment 155840 [details]
> [details] 
> 2. see if bug 128818 is a duplicate or it needs some fix   
> 3. backport to 6.3.

Hi Timur

Already wrong files like attachment #155840 [details] can be manually repaired in 6.4 by:
- Opening the Navigator
- Double clicking on the drawing objects named "AutoShape xx" to highlight them and pressing Delete on the keyboard
- Saving the file and reloading in Excel or Calc will not show any of those white squares.

The bug #128818 is definitely a duplicate.

Backport to 6.3 was not approved (but not explicitly rejected either) at https://gerrit.libreoffice.org/c/core/+/78872 

Cheers
Gabor
Comment 38 Timur 2020-02-13 12:16:31 UTC
*** Bug 128818 has been marked as a duplicate of this bug. ***
Comment 39 Timur 2020-02-14 08:07:06 UTC
*** Bug 130527 has been marked as a duplicate of this bug. ***
Comment 40 Timur 2020-03-19 15:29:07 UTC Comment hidden (obsolete)