Java : Add Comment XLS File Apache POI Example

In this blog post, we will see how to insert comments into Excel 97 - 2003 format workbooks, using Apache POI and Java, with an example program. This is in continuation to our last article, that explained about adding comments in XLSX (2007 and above) spreadsheets.You would need Apache POI and Java to try the example provided in this post. We will also see how to apply formatting to the comments like italics, underline comment text, change comment text font in the sample code. The code is tested working, please feel free to customize it to suit to your needs - and post us a comment if you have any questions.

XLS File - Add Comments Using Apache POI - Java Example


The full working Java example is provided below. The code is commented for convenience and you can follow through it easily.

 import java.io.FileOutputStream;
 import java.io.IOException;
 import org.apache.poi.hssf.usermodel.*;
 import org.apache.poi.ss.usermodel.IndexedColors;
 public class xlsCellComments
 {
   public static void main(String[] args)
     throws IOException
   {
         /* Step -1: Create a Excel 97 - 2003 compatible workbook / Add a worksheet */
     HSSFWorkbook xlsComment = new HSSFWorkbook();     
     HSSFCreationHelper richTextFactory = xlsComment.getCreationHelper();     
     HSSFSheet sheet = xlsComment.createSheet();
     /* Step -2: Create some Cells with Data so we can comment on */
     HSSFCell cell1 = sheet.createRow(4).createCell(8);
     cell1.setCellValue("Cell Commenting Through Java POI Example");
     HSSFCell cell2 = sheet.createRow(2).createCell(2);
     cell2.setCellValue("Cell Comments in XLS - Formatting Options");
         /* Step-3: Create Drawing Object to hold comment */
     HSSFPatriarch drawing = sheet.createDrawingPatriarch();     
         /* Step-4: Increase Comment Box Size - two anchor objects for two comments */   
     HSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 7, 8);
         HSSFClientAnchor anchor2 = drawing.createAnchor(0, 0, 0, 0, 0, 2, 6, 8);
         /* Step-5: Create the Comment Object */
     HSSFComment comment1 = drawing.createCellComment(anchor);
         HSSFComment comment2 = drawing.createCellComment(anchor2);
         /* Step-6: Rich Text Format Comments in Excel */
         HSSFRichTextString rtf1 = richTextFactory.createRichTextString("Let us apply some Rich Text Formatting to Comments now");
         /* Tip - to add new line include \n */
     comment1.setString(rtf1);
         /* Define author */
     comment1.setAuthor("Author1");
         /* Add comment to cell */
     cell1.setCellComment(comment1);
         HSSFRichTextString rtf2 = richTextFactory.createRichTextString("Another comment on a different cell");
     HSSFFont commentFormatter = xlsComment.createFont();
         /* Specify a custom font for the comment */
     commentFormatter.setFontName("Arial");
         /* Change Comment style to italics */
     commentFormatter.setItalic(true);
         /* Set Double Underline */
     commentFormatter.setUnderline(HSSFFont.U_DOUBLE);
         /* Set a custom comment color */
     commentFormatter.setColor(IndexedColors.RED.getIndex());
         /* Apply all the formatting options to the rich text string */
     rtf2.applyFont(commentFormatter);
     /* Add comment string */
     comment2.setString(rtf2);
         /* Set author */
     comment2.setAuthor("Author2");
         /* 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.xls";
     FileOutputStream out = new FileOutputStream(fname);
     xlsComment.write(out);
     out.close();
   }
 }

The output of this program is provided below:

XLS Add Comment- Java POI - Example Output
XLS Add Comment- Java POI - Example Output


XLS Insert Comment With Formatting - Java Example Program Output-2
XLS Insert Comment With Formatting - Java Example Program Output-2

No comments:

Post a Comment