Multiple comments apache poi

Question

I am trying to assign multiple comments to multiple cells. When opening the file excel complains that "Excel found unreadable content in ... do you want to recover the content of this workbook?"

When I have only one comment it works, 2 or more comments it fails. See my code below:

    String oper = "OPERATION TO DO AFTER UPLOAD";
    Cell c = row.createCell(0);
    c.setCellType(Cell.CELL_TYPE_STRING);
    c.setCellValue(oper);
    c.setCellStyle(headerStyle);


    CreationHelper factory = wb.getCreationHelper();
    Drawing drawing = sh.createDrawingPatriarch();


//comment for operation
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(c.getColumnIndex());
        anchor.setCol2(c.getColumnIndex()+1);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum()+3);
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString("Please note that UPDATE will translate to INSERT (if ENTITY_ID column is empty) or UPDATE (if ENTITY_ID column is a number)");
        comment.setString(str);
        comment.setAuthor("me");
        // Assign the comment to the cell
        c.setCellComment(comment);

        String entity_id = "ENTITY ID";
        c = row.createCell(1);
        c.setCellType(Cell.CELL_TYPE_STRING);
        c.setCellValue(entity_id);
        c.setCellStyle(headerStyle);

        // comment for EntityID

        ClientAnchor anchorEid = factory.createClientAnchor();
        anchorEid.setCol1(c.getColumnIndex());
        anchorEid.setCol2(c.getColumnIndex() + 1);
        anchorEid.setRow1(row.getRowNum());
        anchorEid.setRow2(row.getRowNum() + 3);
        Comment commentEid = drawing.createCellComment(anchorEid);
        RichTextString strEid = factory
                .createRichTextString("Please note that UPDATE will translate to INSERT (if ENTITY_ID column is empty) or UPDATE (if ENTITY_ID column is a number)");
        commentEid.setString(strEid);
        commentEid.setAuthor("me");
        // Assign the comment to the cell
        c.setCellComment(commentEid);

What is the problem there? I understand you have to create the Drawing only once but I did not cross that ....

Which version of `POI` do you use and are you using `HSSF` or `XSSF`? At first glance I can see no problem with your code. I use similar code to create dozens of comments and it works without a problem. The necessary steps are all there. Reusing the `Drawing`and `CreationHelper`is not a problem either. — Sebastian_H, Sep 10 '14 at 14:25
Did you see the warnings on `createDrawingPatriarch()` WRT only doing it once, and how it'll eat any drawings/comments already in your sheet for HSSF? — Gagravarr, Sep 10 '14 at 15:32
2Sebastian. I use 3.10-FINAL. I am using XSSF (SXSSFWorkbook). — kosta5, Sep 11 '14 at 10:54
This might be a limitation of `SXSSF`. Take a look at this: http://poi.apache.org/spreadsheet/index.html#SXSSF+%28Since+POI+3.8+beta3%29. `SXSSF` has a rather limited feature set and in the listing it states that comments are not supported by `SXSSF`. You could try to change your code to pure `XSSF` and see if your problem persists there. @Gagravarr should be able to tell you if `SXSSF` really has problem with cell comments, since he's a POI contributor. — Sebastian_H, Sep 12 '14 at 09:57
I would assume that much. @Gagravarr can you confirm that? I think it really might be limitation of SXSSF... (you can only have one comment in single spreadsheet). Should I raise a feature request for this? (I understand SXSSF is write-only and does not support all functions but still, this is useful) — kosta5, Sep 12 '14 at 10:24
Oh gosh, RTFM: http://poi.apache.org/spreadsheet/ It says right there for SXSSF Comments --> No (well I proved that you can have 1 comment :) ) — kosta5, Sep 12 '14 at 10:26
@kosta5 Im using SXSSF and I can use comments, but my problem is that when I use to create a large spreadshseet it runs out of memory. — Paul Taylor, Mar 21 '17 at 08:30

score 2 · Accepted Answer · edited Sep 14 '14 at 21:20

2

So as it turns out when using SXSSF you can not use comments (you should not I guess). You can only add one comment that will be valid in spreadsheet that is kinda useless.

For details see table on poi documentation located here: poi.apache.org/spreadsheet --> It says right there for SXSSF Comments --> No (well I proved that you can have 1 comment :) )

edited Sep 14 '14 at 21:20

Sebastian_H

349
3
13

answered Sep 12 '14 at 11:10

kosta5

1,129
3
14
36

Multiple comments apache poi

1 Answers1

Linked