Bug 57973 - A new defect in the latest POI 3.12 when adding comments for the only A1 cell
Summary: A new defect in the latest POI 3.12 when adding comments for the only A1 cell
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.12-FINAL
Hardware: All All
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on: 56835
Blocks:
  Show dependency tree
 
Reported: 2015-05-29 08:42 UTC by Henry Zhu
Modified: 2019-08-05 06:45 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Henry Zhu 2015-05-29 08:42:40 UTC
We found a new defect in the latest POI 3.12 when adding comments for the only A1 cell(There is not any issue for the other cells).
The test source code is as below. After running the below source code, it will throw the below exception.
Exception in thread "main" java.lang.IllegalArgumentException: Multiple cell comments in one cell are not allowed, cell: A1
	at org.apache.poi.xssf.usermodel.XSSFDrawing.createCellComment(XSSFDrawing.java:318)
	at org.apache.poi.xssf.usermodel.XSSFDrawing.createCellComment(XSSFDrawing.java:52)
	at com.tibco.poi.xssf.CellComments.main(CellComments.java:38)


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.FileOutputStream;

/**
 * Demonstrates how to work with excel cell comments.
 * <p>
 * Excel comment is a kind of a text shape,
 * so inserting a comment is very similar to placing a text box in a worksheet
 * </p>
 *
 * @author Yegor Kozlov
 */
public class CellComments {
    public static void main(String[] args) throws IOException {
    	XSSFWorkbook wb = new XSSFWorkbook();

        CreationHelper factory = wb.getCreationHelper();

        XSSFSheet sheet = wb.createSheet();
        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = factory.createClientAnchor();
        
        Cell cell0 = sheet.createRow(0).createCell(0);
        cell0.setCellValue("Cell0");

        Comment comment0 = drawing.createCellComment(anchor);
        RichTextString str0 = factory.createRichTextString("Hello, World!");
        comment0.setString(str0);
        comment0.setAuthor("Apache POI");
        cell0.setCellComment(comment0);
        
        Cell cell1 = sheet.createRow(3).createCell(5);
        cell1.setCellValue("F4");
        Comment comment1 = drawing.createCellComment(anchor);
        RichTextString str1 = factory.createRichTextString("Hello, World!");
        comment1.setString(str1);
        comment1.setAuthor("Apache POI");
        cell1.setCellComment(comment1);

        Cell cell2 = sheet.createRow(2).createCell(2);
        cell2.setCellValue("C3");

        Comment comment2 = drawing.createCellComment(anchor);
        RichTextString str2 = factory.createRichTextString("XSSF can set cell comments");
        //apply custom font to the text in the comment
        Font font = wb.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short)14);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setColor(IndexedColors.RED.getIndex());
        str2.applyFont(font);

        comment2.setString(str2);
        comment2.setAuthor("Apache POI");
        comment2.setColumn(2);
        comment2.setRow(2);

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

    }
}
Comment 1 Dominik Stadler 2015-06-15 14:26:48 UTC
This check was added on-purpose as otherwise you could create invalid Excel workbooks, see bug 56835 for more details.

The anchor that you specify for comments has setCol/setRow methods which specify where the comment resides in the spreadsheet. When you add multiple comments you need to pass different anchors which specify the actual location of the comment, e.g. by adding the following between the calls to createComment() your code-sample is running fine:

        anchor = factory.createClientAnchor();
        anchor.setCol1(2);
        anchor.setCol2(3);
        anchor.setRow1(1);
        anchor.setRow2(2);

See also http://poi.apache.org/spreadsheet/quick-guide.html#CellComments