Add Comment Excel Sheet Java POI Example

In this blog post, we will discuss how to add / Insert comments to cells in an Excel (both XLS / XLSX) workbook using Apache POI and Java with an example program. We will also explore the possibilities of using rich text formatting on the comments and discuss the following;

  • Using different fonts
  • Varying font height / weight
  • Changing Font Color
  • Setting Author name against the comments
  • Adding new line characters inside a comment
  • Rich Text Format only specific parts of a comment
  • Varying the size of the comments box in POI

We will write a single Java program that explains how to do this all in one go. The Java code is commented well enough for you to understand how adding comments work in POI. Follow the code given below:

Excel (XLSX) - Add Comments - Java POI Example Program


 import java.io.FileOutputStream;
 import java.io.IOException;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.apache.poi.xssf.usermodel.*;
 import org.apache.poi.ss.usermodel.IndexedColors;
 public class InsertCellComments
 {
   public static void main(String[] args)
     throws IOException
   {
         /* Start with a workbook object to write comments into */
     XSSFWorkbook xlsx_comments = new XSSFWorkbook();
     /* Now add a worksheet into the workbook */
     XSSFCreationHelper richTextFactory = xlsx_comments.getCreationHelper();     
     XSSFSheet sheet = xlsx_comments.createSheet();
     /* Create a CELL with some value so we can comment on */
     XSSFCell cell1 = sheet.createRow(3).createCell(5);
     cell1.setCellValue("Some value here");
     
         /* Create Drawing Object to hold comment */
     XSSFDrawing drawing = sheet.createDrawingPatriarch();     
         /* Let us draw a big comment box to hold lots  of comment data */
     XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
     /* Create a comment object */
     XSSFComment comment1 = drawing.createCellComment(anchor);
         /* Create some comment text as Rich Text String */
     XSSFRichTextString rtf1 = richTextFactory.createRichTextString("I'm going to type a big comment in this box and see how it appears. And some newline characters also \n\n new line \n newline");
         /* You can also insert newline character in comments using \n */
     comment1.setString(rtf1);
         /* Define the author for the comments,you can set each comment to have a different author */
     comment1.setAuthor("Thinktibits");
         /* Stamp the comment to the cell */
     cell1.setCellComment(comment1);
         /* Let us now define another comment and explore some more options */
         /* Create another Cell */
     XSSFCell cell2 = sheet.createRow(2).createCell(2);
     cell2.setCellValue("Some more value");
     XSSFClientAnchor anchor2 = drawing.createAnchor(0, 0, 0, 0, 0, 2, 6, 8);
     XSSFComment comment2 = drawing.createCellComment(anchor2);
     XSSFRichTextString rtf2 = richTextFactory.createRichTextString("Formatting options for comments in Apache POI");
     XSSFFont commentFormatter = xlsx_comments.createFont();
         /* Specify a custom font for comment text in Apache POI */
     commentFormatter.setFontName("Verdana");
         /* Specify a custom font height */
     commentFormatter.setFontHeightInPoints((short)14);
         /* Specify bold weight of the font */
     commentFormatter.setBoldweight((short)700);
         /* Set a custom comment color */
     commentFormatter.setColor(IndexedColors.BLUE.getIndex());
         /* Apply all the formatting options to the rich text string */
     rtf2.applyFont(commentFormatter);
     /* Add comment string */
     comment2.setString(rtf2);
         /* Set author */
     comment2.setAuthor("Apache POI");
         /* Set the column of the Cell that contains the comment */
     comment2.setColumn(2);
         /* Set the row of the cell that contains the comment */
     comment2.setRow(2);
     /* Write the output to a file */
     String fname = "comments.xlsx";
     FileOutputStream out = new FileOutputStream(fname);
     xlsx_comments.write(out);
     out.close();
   }
 }

The code above creates an Excel spreadsheet in XLSX format, with two cells with comments included in it. This can be seen from the output screenshots below:

Comment 1- XLSX Comment Apache POI Example
Comment 1- XLSX Comment Apache POI Example 

Formatting Options in Comments - Apache POI Example
Formatting Options in Comments - Apache POI Example
In the next post, we will see how to add comments to XLS format versions with suitable examples.

2 comments:

  1. hello , at the end of this article is mentioned "in the next post, we will see how to add comments to XLS format versions with suitable examples" => Unfortunately i don't see any url mentioning it.
    Is this article really available on your website? if yes thank you for sharing it and displaying the url

    ReplyDelete
  2. Thank you ThinkBits for this helpful post, I am daily working on Excel sheet, so it will be more useful for me, I always preferred free worksheet to do plan, keep sharing more stuff :)

    ReplyDelete