NPOI - Adding cell comment to the left of a cell

I am currently using this code to show a comment for a given cell :

public static void AddCellComment(ICell cell, IDrawing patr)
    {
        var commentString = "Something";

        var anchor = new XSSFClientAnchor
        {
            Col1 = cell.ColumnIndex,
            Col2 = cell.ColumnIndex + 2,
            Row1 = cell.RowIndex,
            Row2 = cell.RowIndex + 1
        };

        var comment = patr.CreateCellComment(anchor);
        comment.String = new XSSFRichTextString(commentString);

        cell.CellComment = comment;
    }

This successfully creates a 2 column by 1 row wide comment for a given cell to the right of the cell. I tried putting cell.ColumnIndex - 2 in Col1 or Col2 and it either results in a corrupted workbook where none of the comments work or in an invisible comment. Is there a way to have the comment display to the left of the cell?

Solution

All right, so here are the results of my experiments:

First off, if you hope you can precisely define the position of the comment when its owning cell is hovered, you'll be disappointed.

This is impossible. It is simply not supported by Excel.

When hovering a cell that has a comment, Excel decides by itself where to place the comment and seems to always show it on the right of the cell. You can try to move the comment around in edition mode, it will still be displayed at the same place when the cell is hovered. (I experienced it myself and had confirmation in this tutorial link.

Then, knowing that, what you have control upon is:

The size of the comment box.
Its location, but only when in edition mode.

Both characteristics are governed by XSSFClientAnchor properties. As per npoi source code, Col1, Row1 and Col2, Row2 define two cells that in turn represent the area (and location in edition mode only) of the comment: cell #1 will be included in the area whereas, cell #2 will not.

The first cell must be the top-left of the comment area (precisely, the top-left of cell #1 will be the top-left of the comment box)
The second one must be the bottom-right of the area (precisely, the top-left of cell #2 will be the bottom-right of the comment box)

I think this rule explains why some of your attempts ended with weird or empty comments (I reproduced some of those too): given the above rule, you must always have: Col2 > Col1 and Row2 > Row1. Although I didn't test it, I also suspect that (absolutely) negative columns or rows wouldn't work, hence when subtracting values from the input cell's column or row, you should make sure the result does not end up < 0...

One last note concerning XSSFClientAnchor: there are 4 other properties that can help you fine tune the size and (edition mode only) placement of the comment: Dx1, Dy1, Dx2 and Dy2: these four properties allow you to add/subtract some size to both cells x and y coordinates. They are expressed in a weird unit: EMU. You can fit 9525 EMU in a pixel.

With all this knowledge, I crafted a simple test (based on a mix of yours and an npoi tutorial). Here it is:

private static void Main()
{
    var workbook = new XSSFWorkbook();
    var sheet = workbook.CreateSheet("My sheet");

    var row = sheet.CreateRow(10);
    var cell = row.CreateCell(10);
    cell.SetCellValue("Here");

    var patr = sheet.CreateDrawingPatriarch();
    AddCellComment(cell, patr);

    using var stream = new FileStream(@"c:\temp\test.xlsx", FileMode.Create, FileAccess.Write);
    workbook.Write(stream);
}

private static int PixelsToEmus(int pixels) => pixels * Units.EMU_PER_PIXEL;

private static void AddCellComment(ICell cell, IDrawing patr)
{
    // Let's make a 3x2 cells comment area, then tweak it a bit
    var anchor = new XSSFClientAnchor
    {
        // Top left cell
        Col1 = 5, // 6th column
        Row1 = 5, // 6th row
        // Bottom right cell
        Col2 = 8, // 3 cells wide
        Row2 = 7, // 2 cells high
        // Top left shift
        Dx1 = PixelsToEmus(10), // 10 pixels to the left of 6th column's left border
        Dy1 = PixelsToEmus(10), // 10 pixels to the bottom of 6th row's top border
        // Bottom right shift
        Dx2 = PixelsToEmus(30),  // 30-10=20 pixels wider than 3 columns
        Dy2 = PixelsToEmus(10),  // exactly as high as 2 rows
    };

    var comment = patr.CreateCellComment(anchor);
    comment.String = new XSSFRichTextString("Something");
    cell.CellComment = comment;
}

When running this, I end up with these results (showing both the hovered placement and the edition mode placement):

To be complete, I double-checked what was written in the resulting xslx (after having unzipped it, i took a look at test\xl\drawings\vmlDrawing1.vml and inparticular the <x:Anchor> tag of the Note object in whichwe find the exact values we set in the program:

<x:Anchor>5, 10, 5, 10, 8, 30, 7, 10</x:Anchor>

The source code of npoi helped me (and hopefully you) understand how averything was working:

PS: For these tests, I used a .NET Core 3.1 app and NPOI v2.5.2 Nuget package