Autosize excel comments with apache-poi

Question

We have a lot of excel reports generated by apache poi. Some of them contains comments in headers. Because of many reports we want to create generic solution for adding comments. As we noticed comments can be added to cells by code like this:

public static void addComment(final Workbook workbook, final Sheet sheet, final Cell cell, final Row row,
        final String comment) {

    final CreationHelper factory = workbook.getCreationHelper();

    final Drawing drawing = sheet.createDrawingPatriarch();

    final ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 3);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 5);

    final Comment cellComment = drawing.createCellComment(anchor);
    final RichTextString richText = factory.createRichTextString(comment);
    cellComment.setString(richText);
    cell.setCellComment(cellComment);
}

We also noticed that comment box size can by set using columns/rows index - this is the main problem for us because if first column has 100px and the second one has 1000px then comment width will be 1000px. Here is our question - is there a possibility to set comment size with pixels instead of columns/rows indexes using apache poi? Or maybe there is some way to automatically calculate comments size with apache poi?

Tony BenBrahim Tony BenBrahim · Accepted Answer · 2017-06-23T19:22:11

ClientAnchor supports offsets, at least the specific implementations do:

public XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)

dx1 is offset from the left, dx2 is offset from the right, dy1 is offset from the top, dy2 is offset from the bottom (col1,row1 is starting row col, col2, row2 is ending row col, non inclusive).

So to reduce the width, simply assign a non zero value to dx2.

dx1,dx2,dy1 and dy2 are in English Metric Units (EMU). There are 9525 pixels per EMU, so you have to use fairly large values.

Autosize excel comments with apache-poi

1 Answers