Using SXSSF(APACHE POI) and adding comment does not generate proper excel file

Question

I am having problems generating a valid xlsx file using SXSSF poi api. The below code generates proper excel file if I use : Workbook wb = new XSSFWorkbook(); but fails if I use: SXSSFWorkbook wb = new SXSSFWorkbook(100);

Error: Removed Records: Comments from /xl/comments1.xml part (Comments)

Please help me understand what is wrong with the code. I am looking to generate a excel file with cell comments using SXSSF api.

Actual code:

    public static void main(String[] args) throws Exception {
   // Workbook wb = new XSSFWorkbook();
    SXSSFWorkbook wb = new SXSSFWorkbook(100);
    Sheet sh = wb.createSheet();       
    for(int rownum = 0; rownum < 1000; rownum++){
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
            setCellComment(cell,address);                
        }
    }

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

 protected static void setCellComment(Cell cell, String message) {
        Drawing drawing = cell.getSheet().createDrawingPatriarch();
        CreationHelper factory = cell.getSheet().getWorkbook()
                .getCreationHelper();
        // 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(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex() + 1);
        anchor.setDx1(100);
        anchor.setDx2(100);
        anchor.setDy1(100);
        anchor.setDy2(100);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(message);
        comment.setString(str);
        comment.setAuthor("Apache POI");
        // Assign the comment to the cell
        cell.setCellComment(comment);
    }

Simon Tarplin Simon Tarplin · Accepted Answer · 2015-02-11T16:40:22

I had the same issue and solved it by setting the row and column properties on the Comment.

Amending the setCellComment() method in your example as follows should solve the problem.

protected static void setCellComment(Cell cell, String message) {

    ...

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

    // Set the row and column here
    comment.setRow(cell.getRowIndex());
    comment.setColumn(cell.getColumnIndex());

    // Assign the comment to the cell
    cell.setCellComment(comment);
}

I discovered this solution by looking at the source of the setCellComment() method in the XSSFCell class. The corresponding method in SXSSFCell does not set the row or column.

Using SXSSF(APACHE POI) and adding comment does not generate proper excel file

1 Answers