How to add borders around Excel range using C#?

Question

Could somebody tell me how to add borders around the outside of a range of cells in another colour? Ideally I would like to be able to do this with a single method I will have to do this multiple times. After searching for this I found two methods that would apparently do this - BorderAround and BorderAround2. I suppose my first question is what is the difference between these two methods? I tried using each of these and only BorderAround2 was recognised?

Anyway, `BorderAround2' almost does what I wanted. I used the following line of code which did put a border around the outside of the range, but it was black, rather than red:

ws.get_Range("B2", "E3").BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick, Excel.XlColorIndex.xlColorIndexNone, Color.FromArgb(255, 0, 0), Type.Missing);

The MSDN documentation for this method states:

You must specify either ColorIndex or Color, but not both.

How do I go about doing this? If I set the ColourIndex parameter to Type.Missing or to null or miss it out completely, it produces an error. Any help would be appreciated.

Finally I should point out that I found a workaround solution here where you set the set the various edges separately, but as I say, I was hoping to do this using a single method as it has to be repeated multiple times.

Answer 1

To add a border to one or more sides of an Excel Range (range of cells, which can normally be comprised of 1..many rows and 1..many columns, but for this specific scenario, we probably want to stick with one row and 1..many columns), you only need do three things:

0) Define the range
1) Get a reference to the Range's Borders array
2) Assign a border to one or more of the Border array's edges/sides (top, bottom, left, right)

First, define the range over which you want to operate on like so:

var rowToBottomBorderizeRange = _xlSheet.Range[_xlSheet.Cells[rowToBottomBorderize, ITEMDESC_COL], _xlSheet.Cells[rowToBottomBorderize, TOTALS_COL]];

Next, obtain a reference to the Range's Borders array like this:

Borders border = rowToBottomBorderizeRange.Borders;

Finally, assign a border to one or more of the Border array's edges; for example, if you want to add a boder to the bottom, like so:

border[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;

Putting it all together, the code could be:

var rowToBottomBorderizeRange = _xlSheet.Range[_xlSheet.Cells[rowToBottomBorderize, ITEMDESC_COL], _xlSheet.Cells[rowToBottomBorderize, TOTALS_COL]];
Borders border = rowToBottomBorderizeRange.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;

If you do this in several places, you could make a method out of it:

private void AddBottomBorder(int rowToBottomBorderize)
{
    var rowToBottomBorderizeRange = _xlSheet.Range[_xlSheet.Cells[rowToBottomBorderize, ITEMDESC_COL], _xlSheet.Cells[rowToBottomBorderize, TOTALS_COL]];
    Borders border = rowToBottomBorderizeRange.Borders;
    border[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
}

The example above shows just adding a bottom border, but you can add top, left, or right border lines just as easily, replacing "xlEdgeBottom" with "xlEdgeTop", "xlEdgeRight", or "xlEdgeLeft"

Or, you could add borders all around a range like this:

private void Add360Borders(int rowToBorderize)
{
    var rowToBottomBorderizeRange = _xlSheet.Range[_xlSheet.Cells[rowToBorderize, ITEMDESC_COL], _xlSheet.Cells[rowToBorderize, TOTALS_COL]];
    Borders border = rowToBorderizeRange.Borders;
    border[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
    border[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
    border[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
    border[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
}

Note: You will need to define the sheet like this:

private Worksheet _xlSheet;

...and reference the Microsoft.Office.Interop.Excel assembly in your solution.