Bug 56835 - [PATCH] Unreadable content when adding multiple comments to cell
Summary: [PATCH] Unreadable content when adding multiple comments to cell
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords: PatchAvailable
Depends on:
Blocks: 57973
  Show dependency tree
 
Reported: 2014-08-10 10:07 UTC by Javen O'Neal
Modified: 2015-06-15 14:26 UTC (History)
1 user (show)



Attachments
patch that raises IllegalArgumentException if comment already exists in cell (1.16 KB, text/plain)
2014-08-12 01:39 UTC, Javen O'Neal
Details
patch (1.16 KB, patch)
2014-08-12 01:40 UTC, Javen O'Neal
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Javen O'Neal 2014-08-10 10:07:36 UTC
Tested on 3.10-FINAL and 3.11 nightly 2014-07-21 (nothing special about this build, I just happened to have it on my system)

If I use POI to add two comments to the same cell in an XSSFWorkbook, after writing the workbook to disk, when I open the file in Microsoft Excel, I receive the following error dialog:

Microsoft Excel
"Excel found unreadable content in 'add_two_comments_to_one_cell_test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
Yes | No

Repairs to 'add_two_comments_to_one_cell_test.xlsx'.
Excel was able to open the file by repairing or removing the unreadable content.
Removed Records: Comments from /xl/comments1.xml.part (Comments)

Upon opening the repaired file, only the first comment appears to be in the workbook. The second comment was removed in the repair process.

Since Microsoft Excel can't handle multiple comments in the same cell (comments with the same Col1 and Row1), should POI should raise an error or warning when trying to add a second comment to a cell?

Java code to reproduce the issue (translated from Jython)

class TestWriteTwoCommentsToOneCell {
    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Cell cell = wb.createSheet('Sheet1').createRow(0).createCell(0);

        addCommentToCell(cell, "First comment", "Apache POI");
        addCommentToCell(cell, "Second comment", "Apache POI");

        FileOutputStream out = new FileOutputStream("add_two_comments_to_one_cell_test.xlsx");
        wb.write(out);
        out.close();
    }

    public void addComment(Cell cell, String commentText, String author) {
        //http://poi.apache.org/spreadsheet/quick-guide.html#CellComments
        Sheet sheet = cell.getSheet();
        Row row = cell.getRow();
        Drawing drawing = sheet.createDrawingPatriarch();

        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex()+1);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum()+3);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(commentText);
        comment.setString(str);
        comment.setAuthor(author);

        // Assign the comment to the cell
        cell.setCellComment(comment);
    }
}
Comment 1 Nick Burch 2014-08-10 10:11:01 UTC
As per the javadocs, you should only call createDrawingPatriarch once, and only if your file doesn't already have any drawings / comments

What happens if you correct that part?
Comment 2 Javen O'Neal 2014-08-10 13:34:38 UTC
The JavaDocs http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#createDrawingPatriarch() and source code http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?revision=1614684&view=markup line 415 indicate createDrawingPatriarch searches the workbook for the drawing object for the sheet if a drawing has already been created.

I don't get an Unreadable content error from Microsoft Excel if I run the following code:

Row row = wb.createSheet("Sheet1").createRow(0);
Cell cell1 = row.createCell(0);
Cell cel21 = row.createCell(1);

addCommentToCell(cell1, "First comment", "Apache POI");
addCommentToCell(cell2, "Second comment", "Apache POI");

I don't think this is a problem with createDrawingPartiarch()

I think inside drawing.createCellComment(anchor) a check needs to be made if a cell comment already exists in the same drawing with the same Row1 and Column1 as anchor.
svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDrawing.java?revision=1511789&view=markup Line 288

If you agree I will try to put together a patch.
Comment 3 Javen O'Neal 2014-08-12 01:39:07 UTC
Created attachment 31904 [details]
patch that raises IllegalArgumentException if comment already exists in cell

This patch raises an IllegalArgumentException if comment already exists in (anchor.getRow1(), anchor.getCol1())

This eliminates the chance that a cell has multiple comments, which is currently not supported in Microsoft Excel.
Comment 4 Javen O'Neal 2014-08-12 01:40:18 UTC
Created attachment 31905 [details]
patch

Adding attachment as a patch
Comment 5 Dominik Stadler 2014-10-21 15:16:37 UTC
Thanks for the patch, this is applied with r1633397.