Bug 57571 - Spreadsheet with cell comments cannot be opened Excel
Summary: Spreadsheet with cell comments cannot be opened Excel
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.11-FINAL
Hardware: PC All
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks: 58175
  Show dependency tree
 
Reported: 2015-02-11 23:39 UTC by Gennadiy Vaysman
Modified: 2015-07-24 12:09 UTC (History)
0 users



Attachments
Test to reproduce. (9.58 KB, text/plain)
2015-02-11 23:39 UTC, Gennadiy Vaysman
Details
zip with two outputs valid (XSSF) invalid (SXSSF) (157.40 KB, application/msi)
2015-02-12 00:13 UTC, Gennadiy Vaysman
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gennadiy Vaysman 2015-02-11 23:39:06 UTC
Created attachment 32457 [details]
Test to reproduce.

SXSSF output with cell comments cannot be opened in Excel 2013. Excel shows a dialog "We found some problems...". After Excel attempts to "fix" the doc, it shows the following message: "Removed Records: Comments from /xl/comments1.xml part (Comments)" 
After this, all comments are gone. 

See attached test to reproduce. It' based on POI's own SSPerformanceTest, with a few modifications (added comments, changed to default packaging, etc). 
If you run it as below, the problem should be evident:
  ... -Dexcel.comments=some SSPerformanceTest SXSSF 10000 6 1 

By contrast, XSSF works fine. After running the test with "XSSF" as the first arg, the resulting spreadsheet is valid.

From our perspective, it's a blocker since we use comments extensively.
NB: also submitting another bug about comments causing quick OOM in SXSSF
Comment 1 Nick Burch 2015-02-11 23:44:59 UTC
Can POI XSSF read the SXSSF comment file without error? Can it see the comments? Can older versions of Excel read it without error? If you generate the same file with XSSF and SXSSF, then unzip the two, how do the two files differ in the xml inside?
Comment 2 Gennadiy Vaysman 2015-02-12 00:13:00 UTC
Created attachment 32459 [details]
zip with two outputs valid (XSSF) invalid (SXSSF)

Attaching a zip with two outputs valid (XSSF) invalid (SXSSF). Both are generated by the harness attached. 
Attempted to open the invalid XLSX with Excel 2003. It failed
Comment 3 Nick Burch 2015-02-12 08:33:43 UTC
How do the two files differ though? (The OOXMLPrettyPrint tool could help with this, if you don't fancy just unzipping the two files and diffing yourself)
Comment 4 Gennadiy Vaysman 2015-02-12 16:11:32 UTC
I found root cause and workaround. 
Here is how I add comments. Notice two extra calls added that address the problem. Now I can open spreadsheets in Excel
    private static void createComment(Drawing drawing, Cell cell)
    {
        CreationHelper factory = workBook.getCreationHelper();

        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 2);
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex()+1);

        Comment comment = drawing.createCellComment(anchor);
        comment.setAuthor("Tester");
        comment.setString(factory.createRichTextString("Cell " + cell.getColumnIndex() + "x" + cell.getRowIndex() + " commment"));
        cell.setCellComment(comment);
        comment.setColumn(cell.getColumnIndex());   // <<<<<<<< fixes the problem        comment.setRow(cell.getRowIndex());    // <<<<<<<< fixes the problem
    }
Now, the diff on vmlDrawing1.xml highlighted the problem I have addressed by those two extra calls. Observe that for SXSSF, <Row> and <Column> are always 0 for ALL comments:
  <v:shape id="_x0000_s1248" type="#_xssf_cell_comment" style="position:absolute; visibility:hidden" fillcolor="#ffffe1" o:insetmode="auto">
    <v:fill color="#ffffe1"/>
    <v:shadow on="t" color="black" obscured="t"/>
    <v:path o:connecttype="none"/>
    <v:textbox style="mso-direction-alt:auto"/>
    <x:ClientData ObjectType="Note">
      <x:MoveWithCells/>
      <x:SizeWithCells/>
      <x:Anchor>2, 0, 7500, 0, 4, 0, 7501, 0</x:Anchor>
      <x:AutoFill>False</x:AutoFill>
      <x:Row>0</x:Row>
      <x:Column>0</x:Column>
    </x:ClientData>
  </v:shape>
  <v:shape id="_x0000_s1249" type="#_xssf_cell_comment" style="position:absolute; visibility:hidden" fillcolor="#ffffe1" o:insetmode="auto">
    <v:fill color="#ffffe1"/>
    <v:shadow on="t" color="black" obscured="t"/>
    <v:path o:connecttype="none"/>
    <v:textbox style="mso-direction-alt:auto"/>
    <x:ClientData ObjectType="Note">
      <x:MoveWithCells/>
      <x:SizeWithCells/>
      <x:Anchor>4, 0, 7500, 0, 6, 0, 7501, 0</x:Anchor>
      <x:AutoFill>False</x:AutoFill>
      <x:Row>0</x:Row>
      <x:Column>0</x:Column>
    </x:ClientData>
  </v:shape>
Since workaround is good, I am lowering the severity of this bug
Comment 5 Dominik Stadler 2015-07-24 12:03:01 UTC
This is now fixed again via r1692483, it was a regression that was introduced in 2013 as part of fixing Bug 54920, your workaround was pretty much the fix as well, thanks for reporting!