How can I add a bottom border to an Excel range in C#?

Question

I need to add a bottom border (a simple solid line) to certain rows on a spreadsheet. I have the range I need to work with defined, but attempts to add the border code have failed, as can be seen by the commented out attempts:

private ApplicationClass _xlApp;
private Workbook _xlBook;
private Sheets _xlSheets;
private Worksheet _xlSheet;
. . .
private void AddBottomBorder(int rowToBottomBorderize)
{
    var rangeToBottomBorderize = (Range)_xlSheet.Cells[rowToBottomBorderize, TOTALS_COL];
    //rangeToBottomBorderize.Borders[_xlApp.XlBordersIndex.xlEdgeBottom] = 
    //rangeToBottomBorderize.Borders[XlBordersIndex.xlEdgeBottom] = 1d;
    //rangeToBottomBorderize.Borders[_xlSheet.
    //_xlSheet.Cells[rowToBottomBorderize, TOTALS_COL].Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 1d;
   // someRange.Borders.Item[XlBordersIndex.xlEdgeBottom)] = ...what now?
}

Which object's properties or methods do I need to make assignations to or call, and how?

Answer 1

Try this:

setBorder( rangeToBottomBorderize.Borders[_xlApp.XlBordersIndex.xlEdgeBottom], XlBorderWeight.xlThick );

Helper function:

  private static void setBorder( Border border, XlBorderWeight borderWeight )
  {
     border.LineStyle = XlLineStyle.xlContinuous;
     border.ColorIndex = XlConstants.xlAutomatic;
     border.TintAndShade = 0;
     border.Weight = borderWeight;
  }

To clear a border you can use:

border.LineStyle = XlConstants.xlNone

For the border weight you might want .xlThin

Border Weight:

See: https://msdn.microsoft.com/query/dev14.query?appId=Dev14IDEF1&l=EN-US&k=k(Microsoft.Office.Interop.Excel.XlBorderWeight);k(TargetFrameworkMoniker-.NETFramework,Version%3Dv4.6);k(DevLang-csharp)&rd=true

For other line style options you could try ( I haven't tried these ):

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.border.linestyle.aspx

Here are my using statements ( you don't need all of these ):

using Microsoft.Office.Interop.Word;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using Border = Microsoft.Office.Interop.Excel.Border;
using Range = Microsoft.Office.Interop.Excel.Range;
using XlBorderWeight = Microsoft.Office.Interop.Excel.XlBorderWeight;
using XlLineStyle = Microsoft.Office.Interop.Excel.XlLineStyle;
using XlConstants = Microsoft.Office.Interop.Excel.Constants;

Answer 2

This works for me:

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;
}