in Troubleshooting

Adding comments to spreadsheets with POI

I was just recently working on a project using NPOI, the .NET equivalent of POI, which is a great library for writing Microsoft Office documents. I was using HSSF, it’s spreadsheet library (Horrible Spreadsheet Format according to Google), and attempting to add Comments to cells.

At first I thought it wasn’t working at all. Then I noticed there was a single cell that had the little red marker that tells you there is a comment. After a little debugging, I realized that this was the last comment added, and guessed that I was doing something to clear the comments I created with each loop iteration.

The block of code that creates comments is something like:

HSSFCell cell //...
// ...
HSSFPatriarch patr = sheet.CreateDrawingPatriarch(); // should only be called once
HSSFComment hssfComment = (HSSFComment) patr.CreateCellComment(
    new HSSFClientAnchor(0, 0, 0, 0,
    cell.ColumnIndex, cell.RowIndex, // start cell index (for size)
    cell.ColumnIndex+4, cell.RowIndex+6) // end cell index (for size)
    );
hssfComment.String = new HSSFRichTextString("Comment text");
cell.CellComment = hssfComment;

Unfortunately I had been creating the drawing patriarch in each loop. I didn’t realize it should be a top-level container for the comments. So make sure to not re-create the drawing patriarch.

  1. Did you ever figure out how to add multiple comments, and not have them become all visible?

Comments are closed.