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); } }
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?
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.
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.
Created attachment 31905 [details] patch Adding attachment as a patch
Thanks for the patch, this is applied with r1633397.