title: How to add Cell Comments to Excel sheet using POI?
body: <p>I am using the following code to generate excel.</p>
  
  <p><a href="http://www.docjar.com/html/api/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.html" rel="noreferrer">http://www.docjar.com/html/api/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.html</a></p>
  
  <pre><code>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&lt;String, XSSFCellStyle&gt; 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&lt;String, XSSFCellStyle&gt; createStyles(XSSFWorkbook wb){
         Map&lt;String, XSSFCellStyle&gt; styles = new HashMap&lt;String, XSSFCellStyle&gt;();
         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&lt;String, XSSFCellStyle&gt; 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 &lt; 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&lt;ZipEntry&gt; en = (Enumeration&lt;ZipEntry&gt;) 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)) &gt;=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("&lt;?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?&gt;" +
                     "&lt;worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"&gt;" );
             _out.write("&lt;sheetData&gt;\n");
         }
  
         public void endSheet() throws IOException {
             _out.write("&lt;/sheetData&gt;");
             _out.write("&lt;/worksheet&gt;");
         }
  
         /**
          * Insert a new row
          *
          * @param rownum 0-based row number
          */
         public void insertRow(int rownum) throws IOException {
             _out.write("&lt;row r=\""+(rownum+1)+"\"&gt;\n");
             this._rownum = rownum;
         }
  
         /**
          * Insert row end marker
          */
         public void endRow() throws IOException {
             _out.write("&lt;/row&gt;\n");
         }
  
         public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
             String ref = new CellReference(_rownum, columnIndex).formatAsString();
             _out.write("&lt;c r=\""+ref+"\" t=\"inlineStr\"");
             if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
             _out.write("&gt;");
             _out.write("&lt;is&gt;&lt;t&gt;"+value+"&lt;/t&gt;&lt;/is&gt;");
             _out.write("&lt;/c&gt;");
         }
  
         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("&lt;c r=\""+ref+"\" t=\"n\"");
             if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
             _out.write("&gt;");
             _out.write("&lt;v&gt;"+value+"&lt;/v&gt;");
             _out.write("&lt;/c&gt;");
         }
  
         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);
         }
     }
  }
  </code></pre>
  
  <p>How to add Cell Comments using above code?</p>
  
  <p>I have ooxml format for cell comment :</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
  &lt;comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"&gt;
  &lt;authors&gt;&lt;author/&gt;&lt;/authors&gt;
  
  &lt;commentList&gt;
  &lt;comment ref="E3" authorId="0"&gt;
  
         &lt;text&gt;
                 &lt;r&gt;
                         &lt;rPr&gt;
                                 &lt;sz val="10"/&gt;
                                 &lt;rFont val="Arial"/&gt;
                         &lt;/rPr&gt;
                         &lt;t&gt;Please select appropriate option&lt;/t&gt;
                 &lt;/r&gt;
         &lt;/text&gt;
  
  &lt;/comment&gt;
  
  &lt;comment ref="E4" authorId="0"&gt;
         &lt;text&gt;
                 &lt;r&gt;
                         &lt;rPr&gt;
                                 &lt;sz val="10"/&gt;
                                 &lt;rFont val="Arial"/&gt;
                         &lt;/rPr&gt;
                         &lt;t&gt;Please mark yes against your choice&lt;/t&gt;
                 &lt;/r&gt;
         &lt;/text&gt;
  &lt;/comment&gt;
  &lt;/commentList&gt;
  
  &lt;/comments&gt;
  </code></pre>
  
  <p>How to use it?</p>