GNOME Bugzilla – Bug 790756
Cell comments are gone when open exported file in MS Excel
Last modified: 2017-11-30 07:54:39 UTC
Report describes problem with cell comments when working with MS Excel file in both, Gnumeric and MS Excel. Steps to recreate: 1. Create an *.xls file (Excel 97 workbook type) in MS Excel with some comments - example attached. 2. Open file in Gnumeric, and add another comment, second one. Save the file. 3. Open modified file in MS Excel - there is a warning that file structure is wrong, and some data lost. All comments are gone. Played also with other formats: a) when import to *.xlsx, result mostly the same - all comments are gone, and you cannot work with comments anymore (add comment button disabled) b) when import to *.ods, new comments made in Gnumeric are visible ok, old comments made in Excel initially are actually present, but with zero length (i.e. the text in the comments from initial file is lost anyway). Using Gnumeric 1.12.32 on Lubuntu 17.10
Created attachment 364264 [details] sample initial Excel file with a comment
The comments are visible both in Gnumeric and Calc. I have no Excel at hand to test.
Yes, in Calc they are ok, but not in Excel. If resaved by Calc, after Gnumeric, then ok in Excel also, but if saved in Gnumeric - there is a problem. So probably something with the file format, not the data itself.
Checked today another format - Excel 5.0/95 (i.e. converted original xls file in Gnumeric to Excel 5.0). And I can see the comments (although the font is funny). With Excel 97/2003 format I've got "File error: data may have been lost."
I can confirm that the saved xlsx file has comments that can be seen by Gnumeric, but not by Excel. That shouldn't be too hard to fix. Re xls, that format is basically on life support. We might fix this if it is easy, but no major work will be done.
For xlsx, it looks like we do not save a drawing. Both Excel and LO do. There isn't much point in that drawing, except that it looks like Excel wants it to be there.
Fixed for xlsx. slimer: can you elaborate what you see wrong for ods?
We need this patch to the grammar. welinder@dicentra:~/gnome-src/gnumeric/test> diff -u ooxml-schema/sml.xsd{~,} --- ooxml-schema/sml.xsd~ 2015-02-10 08:23:52.456077000 -0500 +++ ooxml-schema/sml.xsd 2017-11-26 15:58:53.172949000 -0500 @@ -11,6 +11,17 @@ schemaLocation="shared-commonSimpleTypes.xsd"/> <xsd:import namespace="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" schemaLocation="dml-spreadsheetDrawing.xsd"/> + + <!-- MW: This import and this complexType added 20171126 --> + <xsd:import namespace="http://www.w3.org/XML/1998/namespace" + schemaLocation="http://www.w3.org/2001/xml.xsd"/> + <xsd:complexType name="CT_Text"> + <xsd:simpleContent> + <xsd:extension base="s:ST_String"> + <xsd:attribute ref="xml:space" use="optional"/> + </xsd:extension> + </xsd:simpleContent> + </xsd:complexType> <xsd:complexType name="CT_AutoFilter"> <xsd:sequence> <xsd:element name="filterColumn" minOccurs="0" maxOccurs="unbounded" type="CT_FilterColumn"/> @@ -1808,7 +1819,8 @@ <xsd:complexType name="CT_RElt"> <xsd:sequence> <xsd:element name="rPr" type="CT_RPrElt" minOccurs="0" maxOccurs="1"/> - <xsd:element name="t" type="s:ST_Xstring" minOccurs="1" maxOccurs="1"/> + <!-- MW: changed type from s:ST_String to CT_Text on 20171126 --> + <xsd:element name="t" type="CT_Text" minOccurs="1" maxOccurs="1"/> </xsd:sequence> </xsd:complexType> <xsd:complexType name="CT_RPrElt">
Created attachment 364469 [details] [review] Patch for test/ooxml/sml.xsd [not under version control] Same patch, as attachment. The file to patch uses DOS line endings, so the patch needs to use that too.
Hmm... For *.ods cannot currently recreate. So observed behavior was like that: 1. create *.xls file with a comment in MS Excel 2. open in Gnumeric, add one more comment, save as *.ods 3. open in MS Excel - 1st comment is present, but empty (length is zero, text is lost). 2nd comment is ok. Now after the same steps I can see both comments in Excel ok, but if I add a third comment in Excel, then back in Gnumeric there is a parsing error while open the file. Need to retest I believe, but will take some time, I have win/lin PCs in different locations. Perhaps the issue showed up because I played with Author field in Gnumeric at the first time.
Created attachment 364545 [details] Scenario 1: ODS file with a problem
Created attachment 364546 [details] Scenario 2: ODS file with all comments visible
I confirm, problem with *.ods persists when transferring files from Gnumeric to Excel. Scenario 1: 1. create *.xls file with a comment in MS Excel 2. open in Gnumeric, add one more comment in another cell, save as *.ods 3. open in MS Excel - 1st comment is present, but empty (length is zero, text is lost). 2nd comment is ok. Scenario 2: 1. create *.xls file with a comment in MS Excel 2. open in Gnumeric, add one more comment in another cell 3. modify original Excel comment, save as *.ods 4. open in MS Excel - all comments are ok. Attached sample ods for both scenarios.
Hmm... That may be a Gnumeric problem or an Excel problem. Andreas?
This looks like an Excel problem: In Scenario 1 the original Excel comment is: <office:annotation> <dc:creator>YKK</dc:creator> <text:p><text:span text:style-name="NS-font-size9"><text:span text:style-name="NS-colour-000000">sample comment</text:span></text:span></text:p></office:annotation> In Scenario 2 the now modified Excel comment has become: <office:annotation> <dc:creator>Master</dc:creator> <text:p><text:span text:style-name="NS-colour-000000">sample comment</text:span><text:line-break/>(modified)</text:p></office:annotation> The only significant difference I can see is that the Scenario 1 comment has a text:span inside a text:span
I confirm, if I delete inner text:span in the Scenario 1 ods file, then comment becomes visible in Excel, so nested tags is a problem for Excel.
So... xlsx: FIXED ods: NOTGNOME xls: WONTFIX ?
A pity about xls, but nothing to add from me I believe, if xlsx fixed now. How to apply the xlsx patch? I did not find sml.xsd in my Lubuntu installation. *feeling stupid*
The patch for the code is here: https://git.gnome.org/browse/gnumeric/commit/?id=f09cbffd1cc3d31230a087181d91f85ed587f3d2 The patch discussed in comment 9 is irrelevant unless you want to run the test suite. It patches a file from the official ooxml distribution. We do not distribute that -- it's big and there are likely licensing problems.
I am afraid this is for more experienced users (these are sources, right?) Will it be possible to get an update from Linux repository? I see Lubuntu still has 1.12.32 though...
It'll make its way -- eventually. Distributions generally do not pick up new versions of Gnumeric until they roll a new release of the distributions. In other words, it won't be fast.
Can Gnumeric download updates on its own?
No. In any case, we only distribute source code.
Then we can call it a day I believe, at least for now :)