How to add comments in excel sheet cell using Apache POI Java API

Introduction

In excel file we have seen there are comments on text cells or columns. So in this tutorial we 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 we will create an excel file using Apache POI and Java programming language. In the excel file we will create a sheet and on particular columns on particular rows we will add or insert comments using Java and Apache POI library.

Prerequisites

Eclipse 2020-06, Java at least 1.8, Apache POI 4.1.2

Setup Project

We are going to create a gradle based project in Eclipse. The name of the project is java-apache-poi-excel-add-comments.

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 we will see how to add or insert comments into excel cell or column.

First we create a WorkBook object and Sheet from WorkBook object using the following lines of code:

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

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

Then we create a cell with some text value.

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

Finally we 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 the Application

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

Thanks for reading.

Leave a Comment