How to add Cell Comments to Excel sheet using POI?

Question

I am using the following code to generate excel.

http://www.docjar.com/html/api/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.html

import java.io.*;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class BigGridDemo {
   private static final String XML_ENCODING = "UTF-8";

   public static void main(String[] args) throws Exception {

       // Step 1. Create a template file. Setup sheets and workbook-level objects such as
       // cell styles, number formats, etc.

       XSSFWorkbook wb = new XSSFWorkbook();
       XSSFSheet sheet = wb.createSheet("Big Grid");

       Map<String, XSSFCellStyle> styles = createStyles(wb);
       //name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
       String sheetRef = sheet.getPackagePart().getPartName().getName();

       //save the template
       FileOutputStream os = new FileOutputStream("template.xlsx");
       wb.write(os);
       os.close();

       //Step 2. Generate XML file.
       File tmp = File.createTempFile("sheet", ".xml");
       Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
       generate(fw, styles);
       fw.close();

       //Step 3. Substitute the template entry with the generated data
       FileOutputStream out = new FileOutputStream("big-grid.xlsx");
       substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
       out.close();
   }

   /**
    * Create a library of cell styles.
    */
   private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
       Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
       XSSFDataFormat fmt = wb.createDataFormat();

       XSSFCellStyle style1 = wb.createCellStyle();
       style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
       style1.setDataFormat(fmt.getFormat("0.0%"));
       styles.put("percent", style1);

       XSSFCellStyle style2 = wb.createCellStyle();
       style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
       style2.setDataFormat(fmt.getFormat("0.0X"));
       styles.put("coeff", style2);

       XSSFCellStyle style3 = wb.createCellStyle();
       style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
       style3.setDataFormat(fmt.getFormat("$#,##0.00"));
       styles.put("currency", style3);

       XSSFCellStyle style4 = wb.createCellStyle();
       style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
       style4.setDataFormat(fmt.getFormat("mmm dd"));
       styles.put("date", style4);

       XSSFCellStyle style5 = wb.createCellStyle();
       XSSFFont headerFont = wb.createFont();
       headerFont.setBold(true);
       style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
       style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
       style5.setFont(headerFont);
       styles.put("header", style5);

       return styles;
   }

   private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {

       Random rnd = new Random();
       Calendar calendar = Calendar.getInstance();

       SpreadsheetWriter sw = new SpreadsheetWriter(out);
       sw.beginSheet();

       //insert header row
       sw.insertRow(0);
       int styleIndex = styles.get("header").getIndex();
       sw.createCell(0, "Title", styleIndex);
       sw.createCell(1, "% Change", styleIndex);
       sw.createCell(2, "Ratio", styleIndex);
       sw.createCell(3, "Expenses", styleIndex);
       sw.createCell(4, "Date", styleIndex);

       sw.endRow();

       //write data rows
       for (int rownum = 1; rownum < 100000; rownum++) {
           sw.insertRow(rownum);

           sw.createCell(0, "Hello, " + rownum + "!");
           sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
           sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
           sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
           sw.createCell(4, calendar, styles.get("date").getIndex());

           sw.endRow();

           calendar.roll(Calendar.DAY_OF_YEAR, 1);
       }
       sw.endSheet();
   }

   /**
    *
    * @param zipfile the template file
    * @param tmpfile the XML file with the sheet data
    * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
    * @param out the stream to write the result to
    */
       private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
       ZipFile zip = new ZipFile(zipfile);

       ZipOutputStream zos = new ZipOutputStream(out);

       @SuppressWarnings("unchecked")
       Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
       while (en.hasMoreElements()) {
           ZipEntry ze = en.nextElement();
           if(!ze.getName().equals(entry)){
               zos.putNextEntry(new ZipEntry(ze.getName()));
               InputStream is = zip.getInputStream(ze);
               copyStream(is, zos);
               is.close();
           }
       }
       zos.putNextEntry(new ZipEntry(entry));
       InputStream is = new FileInputStream(tmpfile);
       copyStream(is, zos);
       is.close();

       zos.close();
   }

   private static void copyStream(InputStream in, OutputStream out) throws IOException {
       byte[] chunk = new byte[1024];
       int count;
       while ((count = in.read(chunk)) >=0 ) {
         out.write(chunk,0,count);
       }
   }

   /**
    * Writes spreadsheet data in a Writer.
    * (YK: in future it may evolve in a full-featured API for streaming data in Excel)
    */
   public static class SpreadsheetWriter {
       private final Writer _out;
       private int _rownum;

       public SpreadsheetWriter(Writer out){
           _out = out;
       }

       public void beginSheet() throws IOException {
           _out.write("<?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
                   "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
           _out.write("<sheetData>\n");
       }

       public void endSheet() throws IOException {
           _out.write("</sheetData>");
           _out.write("</worksheet>");
       }

       /**
        * Insert a new row
        *
        * @param rownum 0-based row number
        */
       public void insertRow(int rownum) throws IOException {
           _out.write("<row r=\""+(rownum+1)+"\">\n");
           this._rownum = rownum;
       }

       /**
        * Insert row end marker
        */
       public void endRow() throws IOException {
           _out.write("</row>\n");
       }

       public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
           String ref = new CellReference(_rownum, columnIndex).formatAsString();
           _out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
           if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
           _out.write(">");
           _out.write("<is><t>"+value+"</t></is>");
           _out.write("</c>");
       }

       public void createCell(int columnIndex, String value) throws IOException {
           createCell(columnIndex, value, -1);
       }

       public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
           String ref = new CellReference(_rownum, columnIndex).formatAsString();
           _out.write("<c r=\""+ref+"\" t=\"n\"");
           if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
           _out.write(">");
           _out.write("<v>"+value+"</v>");
           _out.write("</c>");
       }

       public void createCell(int columnIndex, double value) throws IOException {
           createCell(columnIndex, value, -1);
       }

       public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
           createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
       }
   }
}

How to add Cell Comments using above code?

I have ooxml format for cell comment :

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<authors><author/></authors>

<commentList>
<comment ref="E3" authorId="0">

       <text>
               <r>
                       <rPr>
                               <sz val="10"/>
                               <rFont val="Arial"/>
                       </rPr>
                       <t>Please select appropriate option</t>
               </r>
       </text>

</comment>

<comment ref="E4" authorId="0">
       <text>
               <r>
                       <rPr>
                               <sz val="10"/>
                               <rFont val="Arial"/>
                       </rPr>
                       <t>Please mark yes against your choice</t>
               </r>
       </text>
</comment>
</commentList>

</comments>

How to use it?

Answer 1

To get something like this...

...use this code (full running example):

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsComments {
    public static void main(String[] args) {
        try {
            new XlsComments().go();
        } catch (IOException ex) {
            Logger.getLogger(XlsComments.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void go() throws IOException {
        Workbook workbook = new XSSFWorkbook(); //create workbook
        Sheet sheet = workbook.createSheet(); //create sheet
        Cell cell = getOrCreateCell(sheet, 5, 2); //create cell
        cell.setCellValue("Cell with comment"); //write text to cell
        //add comment to cell C6 (row 5, column 2):
        addComment(workbook, sheet, 5, 2, "the author", "content of comment");
        //write to disc and close workbook:
        workbook.write(new FileOutputStream(new File("c:/temp/comments.xlsx")));
        workbook.close();
    }

    public Cell getOrCreateCell(Sheet sheet, int rowIdx, int colIdx) {
        Row row = sheet.getRow(rowIdx);
        if (row == null) {
            row = sheet.createRow(rowIdx);
        }

        Cell cell = row.getCell(colIdx);
        if (cell == null) {
            cell = row.createCell(colIdx);
        }

        return cell;
    }

    public void addComment(Workbook workbook, Sheet sheet, int rowIdx, int colIdx, String author, String commentText) {
        CreationHelper factory = workbook.getCreationHelper();
        //get an existing cell or create it otherwise:
        Cell cell = getOrCreateCell(sheet, rowIdx, colIdx);

        ClientAnchor anchor = factory.createClientAnchor();
        //i found it useful to show the comment box at the bottom right corner
        anchor.setCol1(cell.getColumnIndex() + 1); //the box of the comment starts at this given column...
        anchor.setCol2(cell.getColumnIndex() + 3); //...and ends at that given column
        anchor.setRow1(rowIdx + 1); //one row below the cell...
        anchor.setRow2(rowIdx + 5); //...and 4 rows high

        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(anchor);
        //set the comment text and author
        comment.setString(factory.createRichTextString(commentText));
        comment.setAuthor(author);

        cell.setCellComment(comment);
    }
}

Please note the created file in c:/temp/comments.xlsx. The author is displayed in the lower left corner of the excel application when the cell is selected.

Answer 2

private void createHeaderRow(String value, Row row, XSSFWorkbook wb, Context context, Logger logger) {
    try {
        logger.logInfo("header string is " + value);
        XSSFCellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        String[] arr = value.split("\\$");
        for (int i = 0; i < arr.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(arr[i]);
            String comment = addHeaderComment(arr[i], context, logger);
            Drawing drawing = cell.getSheet().createDrawingPatriarch();
            CreationHelper factory = wb.getCreationHelper();
            ClientAnchor anchor = factory.createClientAnchor();
            anchor.setCol1(i);
            anchor.setCol2(i + 3);
            anchor.setRow1(row.getRowNum());
            anchor.setRow2(row.getRowNum() + 4);
            Comment ocomment = drawing.createCellComment(anchor);
            ocomment.setString(factory.createRichTextString(comment));
            ocomment.setAuthor(context.getCurrentUser().getUserName());
            cell.setCellComment(ocomment);
            cell.setCellStyle(style);
        }
    } catch (Exception e) {
        logger.logError("Exception occurred in createHeaderRow method...", e);
    }
}