This project has moved and is read-only. For the latest updates, please go here.

Inside Vertical and Horizontal borders - how?

Oct 2, 2012 at 1:32 PM

Hello!

Can anyone help me and tell how to set inside borders (vertical and horizontal) for selected range?

Thanks,

May 20, 2014 at 6:59 PM
I know this is an old post, but in searching for more efficient ways of setting all borders (inner/outer, horizontal/vertical) of a range, I came across it.

From what I can tell by the EPPlus documentation and samples, the idiomatic way of doing this is the following (demonstrated with an extension method):
static void BorderAll(
    this ExcelRangeBase @this,
    ExcelBorderStyle borderStyle)
{
    var border = @this.Style.Border;
    border.Left.Style = borderStyle;
    border.Right.Style = borderStyle;
    border.Top.Style = borderStyle;
    border.Bottom.Style = borderStyle;
}
This will set all inner borders as well as the outer borders of the range.

On my machine, the above code takes about 4.1 seconds to complete for the range A2:Z6000. To get a little boost in speed (down to about 2.3 seconds over the same test range), I usually use the following implementation:
static void BorderAll(
    this ExcelRangeBase @this,
    ExcelBorderStyle borderStyle)
{
    var fromRow = @this.Start.Row;
    var fromCol = @this.Start.Column;
    var toRow = @this.End.Row;
    var toCol = @this.End.Column;

    var numRows = toRow - fromRow + 1;
    var numCols = toCol - fromCol + 1;

    @this.Style.Border.BorderAround(borderStyle);

    for (var rowOffset = 1; rowOffset < numRows; rowOffset += 2)
    {
        var row = @this.Offset(rowOffset, 0, 1, numCols);
        row.Style.Border.BorderAround(borderStyle);
    }

    for (var colOffset = 1; colOffset < numCols; colOffset += 2)
    {
        var col = @this.Offset(0, colOffset, numRows, 1);
        col.Style.Border.BorderAround(borderStyle);
    }
}
The above code tries to skip as much range object creation as possible, which is what seems to slow down explicit approaches like this one.

The process of setting all these borders seems to be unavoidably slow compared to setting them within Excel. Excel sets all borders on the test range mentioned above almost instantly.

Also, I haven't tried Interop.Excel and don't know whether something there might be faster.