| Summary: | Spreadsheet with cell comments cannot be opened Excel | ||
|---|---|---|---|
| Product: | POI | Reporter: | Gennadiy Vaysman <genka_v> |
| Component: | SXSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | regression | ||
| Priority: | P2 | ||
| Version: | 3.11-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
| Bug Depends on: | |||
| Bug Blocks: | 58175 | ||
| Attachments: |
Test to reproduce.
zip with two outputs valid (XSSF) invalid (SXSSF) |
||
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? 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
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) 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
|
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