Excel cell comment using Apache POI in Java

In this article, you will learn how to Add, Read, and Delete cell comments using Apache POI in java. Excel comments are used to add a note or explain a formula in a cell. It is useful for reminders, notes for others, and for cross-referencing other workbooks.

Table of Contents

Apache POI maven dependencies

Here we will use Apache POI-4.0.1 to create this application. So add the following maven dependencies into your maven project’s pom.xml file.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

If you want to see the complete Maven project set up to create an Apache POI application, see our other article Apache POI – Getting Started.

How to add a comment to an excel cell in java?

Excel comment is a kind of a text shape, so inserting a comment is very similar to placing a text box in an Excel worksheet.

Steps to add an excel cell comment using Apache POI

Create an HSSFWorkbook or XSSFWorkbook object.
Create a Sheet object. eg- wb.createSheet();
Create a Row and a Cell object then set the cell value.
Create a Drawing object. eg- sheet.createDrawingPatriarch();
Create a CreationHelper and then create a ClientAnchor. eg- wb.getCreationHelper(); ClientAnchor anchor = factory.createClientAnchor();
Create a Comment object. eg- drawing.createCellComment(anchor);
Set a RichTextString value to the comment object.
Now finally to add a comment, set the comment object to the specific Excel cell. eg- cell1.setCellComment(comment1);
Save the workbook to the Excel file.

Let’s see the below java code to insert cell comments in excel using Apache POI in java.

/**
	 * Method to add cell comment in Excel
	 */
	public static void addCellComment(Workbook wb) throws FileNotFoundException, IOException {

		Sheet sheet = wb.createSheet();
		Cell cell1 = sheet.createRow(1).createCell(1);
		cell1.setCellValue("B2");
		Cell cell2 = sheet.createRow(2).createCell(2);
		cell2.setCellValue("C3");

		Drawing<?> drawing = sheet.createDrawingPatriarch();
		CreationHelper factory = wb.getCreationHelper();
		ClientAnchor anchor = factory.createClientAnchor();

		// Normal comment creation
		Comment comment1 = drawing.createCellComment(anchor);
		RichTextString str1 = factory.createRichTextString("An example of normal comment!");
		comment1.setString(str1);
		comment1.setAuthor("Apache POI");

		// Adding the comment1 to Cell address B2
		cell1.setCellComment(comment1);

		// Decorated styled comment creation
		Comment comment2 = drawing.createCellComment(anchor);
		RichTextString str2 = factory.createRichTextString("An example of styled comment!");

		// Apply custom font to the text in the comment
		Font font = wb.createFont();
		font.setFontName("Arial");
		font.setFontHeightInPoints((short) 14);
		font.setBold(true);
		font.setColor(IndexedColors.RED.getIndex());
		str2.applyFont(font);

		comment2.setString(str2);
		comment2.setAuthor("Apache POI");

		// Adding the comment2 to Cell address C3
		cell2.setCellComment(comment2);

		// Saving the workbook to the .xlsx file
		try (FileOutputStream out = new FileOutputStream("E:/excel_comments.xlsx")) {
			wb.write(out);
		}
	}

NOTE: find the complete program source code in the below download link.

Add excel cell comment in java using apache poi — Added cell comments output XLSX file

How to read comments from the excel cell in java?

Read particular cell comments.
Read all available comments in the sheet.

Read specific cell comments in java

To read a specified excel cell comment, we should go to that specific cell and then read the comment using the getCellComment() method. If the comment is not found return a null value. Let’s see the below java code to demonstrate it.

/**
	 * Method to read specific cell comment
	 */
	public static void readCellComment(Workbook wb) {
		Sheet sheet = wb.getSheetAt(0);
		Comment comment1 = sheet.getCellComment(new CellAddress("B2"));
		RichTextString rts1 = comment1.getString();
		System.out.println("Comment in cell B2: " + rts1.getString());

		Comment comment2 = sheet.getCellComment(new CellAddress("C3"));
		RichTextString rts2 = comment2.getString();
		System.out.println("Comment in cell C3: " + rts2.getString());
	}

Read all comments from the Excel sheet in java

Let’s see the below code to read all the comments available in the excel sheet in java.

	/**
	 * Method to read all comments from the Excel sheet
	 */
	public static void readAllCellComment(Workbook wb) {
		Sheet sheet = wb.getSheetAt(0);

		Map<CellAddress, Comment> commentsMap = (Map<CellAddress, Comment>) sheet.getCellComments();
		if (commentsMap == null) {
			System.out.println("No comments found!");
			return;
		}
		Iterator<CellAddress> it = commentsMap.keySet().iterator();
		while (it.hasNext()) {
			System.out.println(commentsMap.get(it.next()).getString());
		}
	}

How to delete or remove excel cell comments in java?

Delete specific excel cell comments.
Delete all the available comments in the excel sheet.

Delete specific cell comments in java

To delete or remove a comment from a specific cell, we have to go to the cell address and then need to call the removeCellComment() method. Let’s see the below java code to demonstrate it.

	/**
	 * Method to remove comment from specific cell
	 */
	public static void removeCellComment(Workbook wb) throws FileNotFoundException, IOException {
		Sheet sheet = wb.getSheetAt(0);

		// Remove the comment from cell address B2
		sheet.getRow(1).getCell(1).removeCellComment();

		// Saving the workbook
		try (FileOutputStream out = new FileOutputStream("E:/excel_comments.xlsx")) {
			wb.write(out);
		}

	}

Delete all comments from the excel sheet

Let’s see the below code to delete all available comments from the excel sheet.

	/**
	 * Method to remove all comments from the sheet
	 */
	public static void removeAllCellComments(Workbook wb) throws FileNotFoundException, IOException {
		Sheet sheet = wb.getSheetAt(0);

		Map<CellAddress, Comment> commentsMap = (Map<CellAddress, Comment>) sheet.getCellComments();
		if (commentsMap == null) {
			System.out.println("No comments found!");
			return;
		}
		Iterator<CellAddress> it = commentsMap.keySet().iterator();
		while (it.hasNext()) {
			CellAddress ca = it.next();
			int rownum = ca.getRow();
			int cellnum = ca.getColumn();
			sheet.getRow(rownum).getCell(cellnum).removeCellComment();
		}

		// Saving the workbook
		try (FileOutputStream out = new FileOutputStream("E:/excel_comments.xlsx")) {
			wb.write(out);
		}
	}

Conclusion

In this article, you have seen how can you add an excel cell comment, read an excel cell comment, and delete excel cell comments using Apache poi in Java. You can download the complete source code example application from the below download link.

Learn about creating an Excel file here, How to write Excel files in java using Apache POI?

FAQ

java.lang.IllegalArgumentException: Multiple cell comments in one cell are not allowed, cell: A1?

In case of creating multiple comments, the Apache poi library creates the comment at cell: A1 first then it will be moved to the specified cell address. so if you add the comment at A1 and try to add another comment even to the other cell also it causes this error.

To avoid such an error you have to create the comment for cell: A1 at last.

Share with friends