How to add borders around an Excel range using C #?

Question

Can someone tell me how to add borders around a range of cells of a different color? Ideally, I would like to be able to do this with one method, which I will have to do this several times. After that, I found two methods that are likely to do this - BorderAround and BorderAround2 . I believe my first question is, what is the difference between the two methods? I tried to use each of them, and only BorderAround2 was recognized?

In any case, `BorderAround2 'almost does what I wanted. I used the following line of code that placed the border around the range, but it was black, not 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 reads:

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

How can I do it? If I set the ColourIndex parameter to Type.Missing or to null or skipped it completely, it will throw an error. Any help would be appreciated.

Finally, I should note that I found a workaround here , where you set many different edges separately, but, as I said, I was hoping to do this using one method, since it needs to be repeated several times.

Answer 1

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

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

First, define the range that you want to work on like this:

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

Next, refer to the Range Borders array as follows:

 Borders border = rowToBottomBorderizeRange.Borders; 

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

 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 can make a way 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 above example only shows adding a bottom border, but you can easily add top, left or right border lines by replacing "xlEdgeBottom" with "xlEdgeTop", "xlEdgeRight" or "xlEdgeLeft"

Or you can add borders around the 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 as follows:

 private Worksheet _xlSheet; 

... and refer to the Microsoft.Office.Interop.Excel assembly in your solution.

Answer 2

Try:

 ws.get_Range("B2", "E3").Borders.LineStyle = Excel.XlLineStyle.xlContinuous; ws.get_Range("B2", "E3").Borders.Color = ColorTranslator.ToOle(Color.Red);