Bug 119190 - Protecting sheets removes the all cell comments in LibreOffice Calc if the file format had been given as 'xlsx'
Summary: Protecting sheets removes the all cell comments in LibreOffice Calc if the fi...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2 all versions
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard: target:7.3.0
Keywords: bibisected, bisected, dataLoss, regression
: 129053 (view as bug list)
Depends on:
Blocks: Cell-Sheet-Protection XLSX-Sheet XLSX-Comments
  Show dependency treegraph
 
Reported: 2018-08-10 05:01 UTC by efkan
Modified: 2021-11-26 15:10 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Example ods made in Calc, saved as xlsx then opened in Excel (71.76 KB, image/png)
2019-12-02 12:01 UTC, NISZ LibreOffice Team
Details
Password protected ods with comment (9.79 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-12-02 12:02 UTC, NISZ LibreOffice Team
Details
Password protected xlsx with comment (9.50 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-12-02 12:03 UTC, NISZ LibreOffice Team
Details
Excel made file opened in Excel and Calc (68.50 KB, image/png)
2019-12-02 12:04 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description efkan 2018-08-10 05:01:42 UTC
Description:
Current; 
LibreOffice versions: 5.1, 6.0.5.2 
OS: Elementary OS 0.4.1 x64 (a derivation of Ubuntu 16.04.2) 
Kernel: 4.4.0-131-generic
File saving format: *.xlsx

When I protect a sheet, the all comments I wrote on cells will be removed.

The steps to reproduce the bug: 
1. open a blank sheet 
2. right clik on any cell and select the command of "insert comment" 
3. write down some text 
4. right clik on the current sheet name from the bottom of the sheet 
5. select the command of "protect sheet" 
6. press on "OK" button on the dialog window (so, give a blank password) 
7. save the current file with "*.xlsx" (MS Excel 2007-2019) file extension and close Calc application 
8. re-open the file

Now it can be seen that there is no any comment on any cell.

Steps to Reproduce:
1. open a blank sheet 
2. right clik on any cell and select the command of "insert comment" 
3. write down some text 
4. right clik on the current sheet name from the bottom of the sheet 
5. select the command of "protect sheet" 
6. press on "OK" button on the dialog window (so, give a blank password) 
7. save the current file with "*.xlsx" (MS Excel 2007-2019) file extension and close Calc application 
8. re-open the file
9. see that there is no any comment on any cell

Actual Results:
The all written comments have gone.

Expected Results:
The comments can be seen when mouse pointer over the cells. 
On the other hand, the cells that have comments are specified by a tiny red square at the top-left of the cells.


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 2 Mike Kaganski 2018-08-10 08:34:18 UTC
Repro with Version: 6.1.0.3 (x64)
Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1
CPU threads: 12; OS: Windows 10.0; UI render: GL; 
Locale: ru-RU (ru_RU); Calc: CL
Comment 3 Xisco Faulí 2018-08-13 19:17:39 UTC
I can reproduce it back to 

Version: 4.2.0.0.alpha1+
Build ID: fc8f44e82de4ebdd50ac5fbb9207cd1a59a927e3

but not in

Version: 4.1.0.0.alpha1+
Build ID: a2c9d4f8bbde97f175bae4df771273a61251f40

it needs to be bisected with bibisect-42max repo
Comment 5 himajin100000 2018-09-23 19:41:37 UTC
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/workbookfragment.cxx?r=f87be145#497
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheethelper.cxx?r=60bc2635#1567
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheethelper.cxx?r=60bc2635#959
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheethelper.cxx?r=60bc2635#1328
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/commentsbuffer.cxx?r=3e1d3c25#202

Note:
CommentsFragment, CommentsBuffer , Comment are all different.
Calling  importOoxFragment here will not directly trigger Comment::finalizeImport , but tries to call CommentsFragment::finalizeImport, which is just the (empty) inherited method(FragmentHandler2::finalizeImport().
So we don't need to worry about the following code.

https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheetfragment.cxx?r=f87be145#353

****
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/workbookfragment.cxx?r=f87be145#437
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/workbookfragment.cxx?r=f87be145#480
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/workbookfragment.cxx?r=f87be145#334
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/workbookfragment.cxx?r=f87be145#256
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/workbookhelper.cxx?r=fabbeb7a#958
https://opengrok.libreoffice.org/xref/core/oox/source/core/xmlfilterbase.cxx?r=60bc2635#338

etc. and the code eventually calls

https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheetfragment.cxx?r=f87be145#638
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheethelper.cxx?r=60bc2635#1562
https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/worksheethelper.cxx?r=60bc2635#947

as importSheetFragments comes before (*aIt)->finalizeDrawingImport() in WorkbookFragment::finalizeImport(), comment is not added to the protected sheets.
Comment 6 raal 2018-12-29 15:58:38 UTC
This seems to have begun at the below commit.
Adding Cc: to Noel Power ; Could you possibly take a look at this one?
Thanks
 1dd01719cbf3c784715206f9c204705d1b8c31ad is the first bad commit
commit 1dd01719cbf3c784715206f9c204705d1b8c31ad
Author: Matthew Francis <mjay.francis@gmail.com>
Date:   Sat Sep 5 19:31:33 2015 +0800

    source-hash-8ce1124359783df750d176b8390b4eb0f54fb6ba
    
    commit 8ce1124359783df750d176b8390b4eb0f54fb6ba
    Author:     Noel Power <noel.power@suse.com>
    AuthorDate: Thu Aug 1 14:29:34 2013 +0100
    Commit:     Noel Power <noel.power@suse.com>
    CommitDate: Thu Aug 1 16:59:22 2013 +0100
    
        2nd step to export SheetProtection fdo#64753, actually export the data
    
        Change-Id: Id9fa1f9b917f9df8ca0ead02dfd5405388039b4b
Comment 7 Timur 2019-12-02 09:59:12 UTC
*** Bug 129053 has been marked as a duplicate of this bug. ***
Comment 8 NISZ LibreOffice Team 2019-12-02 11:56:20 UTC
This looks more like an import time problem:
- Excel-made xlsx file with comment is opened without comment in Calc
- Calc-made xlsx file with comment is opened with comment in Excel but without comment in Calc.
Comment 9 NISZ LibreOffice Team 2019-12-02 12:01:26 UTC
Created attachment 156240 [details]
Example ods made in Calc, saved as xlsx then opened in Excel
Comment 10 NISZ LibreOffice Team 2019-12-02 12:02:43 UTC
Created attachment 156241 [details]
Password protected ods with comment

password is: hello
Comment 11 NISZ LibreOffice Team 2019-12-02 12:03:15 UTC
Created attachment 156242 [details]
Password protected xlsx with comment

password is: hello
Comment 12 NISZ LibreOffice Team 2019-12-02 12:04:07 UTC
Created attachment 156243 [details]
Excel made file opened in Excel and Calc
Comment 13 Justin L 2021-11-26 09:00:13 UTC
LO 3.6 round-tripping attachment 156241 [details]: no comments exported, not protected.
LO 4.0: comment exported/imported, not protected.  Opens XLSX attachment 156242 [details] as protected though (and of course no comment).

So comment EXPORT support first came in the 4.0 range https://cgit.freedesktop.org/libreoffice/core/log/?id=3238606c8470f3eaeada3fc601e186ec5cfac925&qt=range&q=f9a453fb01908e16032abdbf1f895666e1d260a6..20d4cd5e08c1400fcc5ae5eb45861f429b914969&ofs=50

P.S. LO 3.5 can read the non-protected comments generated by LO 4.0.
Comment 14 Justin L 2021-11-26 09:07:35 UTC
Unfortunately, I hadn't bothered to first check if this was still a problem. comment have been importing since 6.4.0.2.

author	Noel Grandin on 2020-01-01 09:37:37 +0100
commit 4992d61600536fe14b97b718dbb11f00e936c6a9
    tdf#129228 speedup opening of xlsx file with lots of comments
Comment 15 Xisco Faulí 2021-11-26 11:01:57 UTC
(In reply to Justin L from comment #14)
> Unfortunately, I hadn't bothered to first check if this was still a problem.
> comment have been importing since 6.4.0.2.
> 
> author	Noel Grandin on 2020-01-01 09:37:37 +0100
> commit 4992d61600536fe14b97b718dbb11f00e936c6a9
>     tdf#129228 speedup opening of xlsx file with lots of comments

I do confirm it. Nice finding.
Unittest added in https://gerrit.libreoffice.org/c/core/+/125874
Comment 16 Commit Notification 2021-11-26 15:10:03 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/224026a38ddc4008d1c73fa1ba95f672f03fac63

tdf#119190: sc_subsequent_filters_test2: Add unittest

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