How To Add Comments In Excel Sheet Cell Using Apache POI Java API

Introduction

In an excel file, you might have seen there are comments on text cells or columns. So in this tutorial you will see how to add or insert comments in excel sheet cell using Apache POI Java API.

Comments can help you to remember what all are formulae or instructions you want to provide to other excel users.

In this example, I will create an excel file using Apache POI and Java programming language. In the excel file, I will create a sheet and on particular columns of particular rows, I will add or insert comments using Java and Apache POI library.

Prerequisites

Java 8+, Apache POI 4.1.2 – 5.2.2, Gradle 5.6, Maven 3.8.5

Project Setup

I am going to create a gradle or maven based project in an IDE. The name of the project is java-apache-poi-excel-add-comments.

For maven based project you can use the following pom.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>java-apache-poi-excel-add-comments</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.2</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
			</plugin>
		</plugins>
	</build>
</project>

The build.gradle script is given below:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.2'
    
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.10.1')
}

Add/Insert Comments into Excel Cell

Now you will see how to add or insert comments into excel cell or column.

First of all, I create a WorkBook object and Sheet from WorkBook object using the following lines of code:

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();

Next I create CreationHelper object that will help us to create comment text.

Then I create a cell with some text value.

Next I create the drawing area to hold the comment text for the cell or column.

Finally I create the Comment object, set the comment text and author and set to the cell or column.

This is a simple comment without any format or text decoration but you can try with different attributes of Comment object.

package com.roytuts.java.apache.poi.excel.add.comments;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Shape;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelAddComments {

	public static void main(String[] args) throws IOException {
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		
		CreationHelper creationHelper = (XSSFCreationHelper) workbook.getCreationHelper();		

		Cell cell = sheet.createRow(1).createCell(2);
		cell.setCellValue("Cell One String Value");

		Drawing<Shape> drawing = (Drawing<Shape>) sheet.createDrawingPatriarch();
		ClientAnchor clientAnchor = drawing.createAnchor(0, 0, 0, 0, 0, 2, 7, 12);

		Comment comment = (Comment) drawing.createCellComment(clientAnchor);

		RichTextString richTextString = creationHelper.createRichTextString(
				"We can put a long comment here with \n a new line text followed by another \n new line text");

		comment.setString(richTextString);
		comment.setAuthor("Soumitra");

		cell.setCellComment(comment);
		
		Cell cellOther = sheet.createRow(3).createCell(2);
		cellOther.setCellValue("Cell Other String Value");
		
		ClientAnchor clientAnchorOther = drawing.createAnchor(0, 0, 0, 0, 0, 7, 12, 17);

		Comment commentOther = (Comment) drawing.createCellComment(clientAnchorOther);

		RichTextString richTextStringOther = creationHelper.createRichTextString(
				"Other long comment here with \n a new line text followed by another \n new line text");

		commentOther.setString(richTextStringOther);
		commentOther.setAuthor("Roy Tutorials");
		
		cellOther.setCellComment(commentOther);

		FileOutputStream out = new FileOutputStream("excel-add-comments.xlsx");
		workbook.write(out);		
		
		out.close();
		workbook.close();
	}

}

Now let’s move on to Testing the Application section to see how comments look like.

Testing Comments in Excel Sheet

Once you are done with coding part then you can mouse over the text on a cell and you will see the comment automatically pops up.

In the below image I have only shown for one cell text. You can also check the other cell comment.

add comments in excel sheet cell using apache poi java api

Source Code

Download

Leave a Reply

Your email address will not be published.