How to change border style os several cells at once. NPOI

Question

I want to do this: Question

But do it using NPOI. Is there a way of providing the 4 "points" of the square and change its border to whatever i want?

Answer 1

No, there isn't, unless you program it yourself.

Also, border styles in NPOI (2.1.3.1) are buggy, basically when your ICellStyle has a same border style as some other ICellStyle (e.g. two cells have top black border) and you change one of them, the change is propagated to both styles (e.g. once you add left top border to one cell, it is also added to another cell).

I have created pull request, but this should work for now (beware of 64 000 limit of cell styles):

public void CreateBorder(ISheet sheet, int firstRow, int lastRow, int firstColumn, int lastColumn, BorderStyle borderStyle)
{
    // top line
    for (int column = firstColumn + 1; column < lastColumn; column++)
    {
        ICell topCell = GetCell(sheet, firstRow, column);
        ICellStyle topStyle = CreateCellStyle(topCell);
        using (new CellBorderLock(topStyle))
        {
            topStyle.BorderTop = borderStyle;
        }
        topCell.CellStyle = topStyle;
    }
    // top left corner
    ICell topLeftCell = GetCell(sheet, firstRow, firstColumn);
    ICellStyle topLeftStyle = CreateCellStyle(topLeftCell);
    using (new CellBorderLock(topLeftStyle))
    {
        topLeftStyle.BorderTop = borderStyle;
        topLeftStyle.BorderLeft = borderStyle;
    }
    topLeftCell.CellStyle = topLeftStyle;
    // top right corner
    ICell topRightCell = GetCell(sheet, firstRow, lastColumn);
    ICellStyle topRightStyle = CreateCellStyle(topRightCell);
    using (new CellBorderLock(topRightStyle))
    {
        topRightStyle.BorderTop = borderStyle;
        topRightStyle.BorderRight = borderStyle;
    }
    topRightCell.CellStyle = topRightStyle;

    // left line
    for (int row = firstRow + 1; row < lastRow; row++)
    {
        ICell leftCell = GetCell(sheet, row, firstColumn);
        ICellStyle leftStyle = CreateCellStyle(leftCell);
        using (new CellBorderLock(leftStyle))
        {
            leftStyle.BorderLeft = borderStyle;
        }
        leftCell.CellStyle = leftStyle;
    }

    // right line
    for (int row = firstRow + 1; row < lastRow; row++)
    {
        ICell rightCell = GetCell(sheet, row, lastColumn);
        ICellStyle rightStyle = CreateCellStyle(rightCell);
        using (new CellBorderLock(rightStyle))
        {
            rightStyle.BorderRight = borderStyle;
        }
        rightCell.CellStyle = rightStyle;
    }

    // bottom line
    for (int column = firstColumn + 1; column < lastColumn; column++)
    {
        ICell bottomCell = GetCell(sheet, lastRow, column);
        ICellStyle bottomStyle = CreateCellStyle(bottomCell);
        using (new CellBorderLock(bottomStyle))
        {
            bottomStyle.BorderBottom = borderStyle;
        }
        bottomCell.CellStyle = bottomStyle;
    }

    // bottom left corner
    ICell bottomLeftCell = GetCell(sheet, lastRow, firstColumn);
    ICellStyle bottomLeftStyle = CreateCellStyle(bottomLeftCell);
    using (new CellBorderLock(bottomLeftStyle))
    {
        bottomLeftStyle.BorderBottom = borderStyle;
        bottomLeftStyle.BorderLeft = borderStyle;
    }
    bottomLeftCell.CellStyle = bottomLeftStyle;

    // bottom right corner
    ICell bottomRightCell = GetCell(sheet, lastRow, lastColumn);
    ICellStyle bottomRightStyle = CreateCellStyle(bottomRightCell);
    using (new CellBorderLock(bottomRightStyle))
    {
        bottomRightStyle.BorderBottom = borderStyle;
        bottomRightStyle.BorderRight = borderStyle;
    }
    bottomRightCell.CellStyle = bottomRightStyle;
}

private ICellStyle CreateCellStyle(ICell cell)
{
    var style = cell.Sheet.Workbook.CreateCellStyle();
    style.CloneStyleFrom(cell.CellStyle);
    return style;
}

private ICell GetCell(ISheet sheet, int row, int column)
{
    IRow r = sheet.GetRow(row) ?? sheet.CreateRow(row);
    return r.GetCell(column) ?? r.CreateCell(column);
}

/// <summary>
/// Make a border style of <see cref="ICellStyle"/> unique for duration of a lock
/// so it doesn't propagate changes of border to other cell styles.
/// </summary>
public sealed class CellBorderLock : IDisposable
{
    private readonly ICellStyle style;

    public CellBorderLock(ICellStyle style)
    {
        this.style = style;
        style.BorderDiagonalLineStyle = BorderStyle.Thin;
        style.BorderDiagonal = BorderDiagonal.Forward;
    }

    public void Dispose()
    {
        style.BorderDiagonalLineStyle = BorderStyle.None;
        style.BorderDiagonal = BorderDiagonal.None;
    }
}

Code is CC0.