Bug 114842 - FILESAVE: to XLSX: comments lose background colour
Summary: FILESAVE: to XLSX: comments lose background colour
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All Linux (All)
: medium normal
Assignee: Bartosz
Whiteboard: target:6.1.0
Depends on:
Blocks: XLSX-Comments
  Show dependency treegraph
Reported: 2018-01-05 05:17 UTC by Elmar
Modified: 2021-07-04 23:25 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

Comment exported from LO 3.3.0 to xlsx (displayed in MSO Excel 2013) (26.71 KB, image/png)
2018-01-05 08:42 UTC, Thomas Lendo
Example .xlsx file created by MS Office 2013, with colour comments (10.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-01-08 23:33 UTC, Bartosz

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2018-01-05 05:17:13 UTC
When save ODS to XLSX, the shading of comment boxes lose shading. Makes it impossible to read the comment when I hover over the cell (and comment is displayed.

Steps to Reproduce:
1. Capture data in cells
2. Add comment to a cell
3. Save to XLSX
4. Open XLSX in MS Excel: no background in comment, text blurs into normal background text

Actual Results:  
As described

Expected Results:
Background colour of comment should be saved in Excel file 

Reproducible: Always

User Profile Reset: No

Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Linux (All)
OS is 64bit: yes

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:57.0) Gecko/20100101 Firefox/57.0
Comment 1 Elmar 2018-01-05 05:20:57 UTC
Note: when I save to XLS there is no problem
Comment 2 Elmar 2018-01-05 05:23:59 UTC
Same problem if save ODS to XLSX using Calc v5.4
Comment 3 Thomas Lendo 2018-01-05 08:41:38 UTC
I can reproduce that the comment in the exported xlsx file has no background color instead of the default yellow. If you click into the comment to edit it, the yellow background occurs.

LibO (x64)
Build-ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
CPU-Threads: 8; BS: Windows 10.0; UI-Render: Standard

Opened in MSO Excel 2013.

When opened the xlsx file in LibO 6.0 again, the comment looks right.

Creating a spreadsheet with a comment in LibO 3.3.0 and exporting to xlsx results in a more weird presentation (also without a comment background color), see screenshot.
Comment 4 Thomas Lendo 2018-01-05 08:42:57 UTC
Created attachment 138892 [details]
Comment exported from LO 3.3.0 to xlsx (displayed in MSO Excel 2013)
Comment 5 Bartosz 2018-01-08 23:33:22 UTC
Created attachment 138990 [details]
Example .xlsx file created by MS Office 2013, with colour comments
Comment 6 Bartosz 2018-01-09 00:18:48 UTC
It seems that after changing shape type from  type="#_x005F_x0000_t202" to type="#_x0000_t202" , everything is displayed correctly.

I will prepare patch which fix that issue
Comment 7 Bartosz 2018-01-09 00:37:45 UTC
It seems that shape type must be defined:
For example:

<v:shapetype id="shapetype_202" coordsize="21600,21600" o:spt="202" path="m,l,21600l21600,21600l21600,xe">
        <v:stroke joinstyle="miter"/>
        <v:path gradientshapeok="t" o:connecttype="rect"/>

And correct type of shape needs to be used:
   <v:shape id="shape_0" fillcolor="#ff99cc" stroked="t" type="#shapetype_202"

In this case after providing:
(which type was defined before)
colours are imported correctly by MS Excel 2013
Comment 8 Bartosz 2018-01-12 23:30:22 UTC
Review which fix this issue:
Comment 9 Commit Notification 2018-02-07 13:50:02 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":


tdf#114842 Fix comment shape export for Calc

It will be available in 6.1.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:

Affected users are encouraged to test the fix and report feedback.
Comment 10 Eike Rathke 2018-02-12 12:06:03 UTC
As a side note: it looks odd that in the Excel generated attachment the shapetype ID is "_x0000_t202", which means the ID starts with a hex null character (which _x0000_ should decode to if not escaped as _x005F_x0000_). See ECMA-376-1:2016 page 3736, bstr (Basic String) for reference.

Question remains if the treatment of writing _x005F_x0000_ vs reading _x0000_ (and apparently not decoding as otherwise we would not write _x005F_x0000_) is actually correct. I have doubts.
Comment 11 Bartosz 2018-03-03 13:26:59 UTC
Hello Eike.
How to apply your comments?
What I should investigate to fix that naming convention?
Is there a risk that patch will not work correctly in specific conditions?
Comment 12 Eike Rathke 2018-03-20 19:50:24 UTC
I have no idea. I don't know why Excel writes that as unescaped "_x0000_t202" and what implication it has if we write it as escaped "_x005F_x0000_t202" instead. What happens if that is read with Excel and then saved again? Is the escapement preserved? Or removed? Or...?

Also, I guess that when LO reads the "_x0000_t202" the encoded _x0000_ is not decoded (which might lead to even more problems if it was because many places don't consider embedded nil characters). So this may be a special case where the _x0000_ should not be escaped.
Comment 13 Hema Hardoi 2018-11-14 06:25:44 UTC Comment hidden (spam)
Comment 14 Elina Williams 2018-11-16 07:01:05 UTC Comment hidden (spam)