Apply Borders in Excel

22 Dec 20229 minutes to read

Applying borders around specific cells makes those cells stand out to people who view the document. For example, this is used when calling attention to totals, other specific numbers, or words in the file. Alternatively, as the borders are thicker and more pronounceable than the default grid lines, you can add border lines around all the cells to define each to prevent from blending together when users view the document. You can also use different line styles for borders.

The following code shows how to apply borders in Excel with different line styles to cells using Interop and XlsIO for .NET.

Interop

private void ApplyBorders()
{
  //Instantiate the application object
  var excelApp = new Microsoft.Office.Interop.Excel.Application();

  //Add a workbook
  Workbook workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value);

  //Get the first sheet
  Worksheet worksheet = (Worksheet)workbook.Sheets["Sheet1"];

  //Put some text into different cells (A2, A4, A6, and A8)
  worksheet.Cells[2, 1] = "Hair Lines";
  worksheet.Cells[4, 1] = "Thin Lines";
  worksheet.Cells[6, 1] = "Medium Lines";
  worksheet.Cells[8, 1] = "Thick Lines";

  //Define a range object (A2)
  Range range;
  range = worksheet.get_Range("A2", "A2");
  //Get the borders collection
  Borders borders = range.Borders;
  //Set the hair lines style
  borders.LineStyle = XlLineStyle.xlContinuous;
  borders.Weight = 1d;

  //Define a range object (A4)
  range = worksheet.get_Range("A4", "A4");
  //Get the borders collection
  borders = range.Borders;
  //Set the thin lines style
  borders.LineStyle = XlLineStyle.xlContinuous;
  borders.Weight = 2d;

  //Define a range object (A6)
  range = worksheet.get_Range("A6", "A6");
  //Get the borders collection
  borders = range.Borders;
  //Set the medium lines style
  borders.LineStyle = XlLineStyle.xlContinuous;
  borders.Weight = 3d;

  //Define a range object (A8)
  range = worksheet.get_Range("A8", "A8");
  //Get the borders collection
  borders = range.Borders;
  //Set the thick lines style
  borders.LineStyle = XlLineStyle.xlContinuous;
  borders.Weight = 4d;

  //Autofit column A
  worksheet.get_Range("A2", "A2").EntireColumn.AutoFit();

  //Save the file
  workbook.SaveAs("InteropOutput_Borders.xlsx");

  //Quit the application
  excelApp.Quit();
}
Private Sub ApplyBorders()
  'Instantiate the application object
  Dim excelApp = New Microsoft.Office.Interop.Excel.Application()

  'Add a workbook
  Dim workbook As Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)

  'Get the first sheet
  Dim worksheet As Worksheet = workbook.Sheets("Sheet1")

  'Put some text into different cells (A2, A4, A6, and A8)
  worksheet.Cells(2, 1) = "Hair Lines"
  worksheet.Cells(4, 1) = "Thin Lines"
  worksheet.Cells(6, 1) = "Medium Lines"
  worksheet.Cells(8, 1) = "Thick Lines"

  'Define a range object (A2)
  Dim range As Range
  range = worksheet.Range("A2", "A2")
  'Get the borders collection
  Dim borders As Borders = range.Borders
  'Set the hair lines style
  borders.LineStyle = XlLineStyle.xlContinuous
  borders.Weight = 1.0R

  'Define a range object (A4)
  range = worksheet.Range("A4", "A4")
  'Get the borders collection
  borders = range.Borders
  'Set the thin lines style
  borders.LineStyle = XlLineStyle.xlContinuous
  borders.Weight = 2.0R

  'Define a range object (A6)
  range = worksheet.Range("A6", "A6")
  'Get the borders collection
  borders = range.Borders
  'Set the medium lines style
  borders.LineStyle = XlLineStyle.xlContinuous
  borders.Weight = 3.0R

  'Define a range object (A8)
  range = worksheet.Range("A8", "A8")
  'Get the borders collection
  borders = range.Borders
  'Set the thick lines style
  borders.LineStyle = XlLineStyle.xlContinuous
  borders.Weight = 4.0R

  'Autofit column A
  worksheet.Range("A2", "A2").EntireColumn.AutoFit()

  'Save the file
  workbook.SaveCopyAs("InteropOutput_Borders.xlsx")

  'Quit the application
  excelApp.Quit()
End Sub

XlsIO

private void ApplyBorders()
{
  using (ExcelEngine excelEngine = new ExcelEngine())
  {
    //Instantiate the application object
    IApplication application = excelEngine.Excel;

    //Create a workbook
    IWorkbook workbook = application.Workbooks.Create(1);

    //Get the first sheet
    IWorksheet worksheet = workbook.Worksheets[0];

    //Put some text into different cells (A2, A4, A6, and A8)
    worksheet.Range[2, 1].Value = "Hair Lines";
    worksheet.Range[4, 1].Value = "Thin Lines";
    worksheet.Range[6, 1].Value = "Medium Lines";
    worksheet.Range[8, 1].Value = "Thick Lines";

    //Define a range object (A2)
    IRange range;
    range = worksheet.Range["A2"];
    //Set the borders with hair lines style
    range.BorderAround(ExcelLineStyle.Hair);

    //Define a range object (A4)
    range = worksheet.Range["A4"];
    //Set the borders with thin lines style
    range.BorderAround(ExcelLineStyle.Thin);

    //Define a range object (A6)
    range = worksheet.Range["A6"];
    //Set the borders with medium lines style
    range.BorderAround(ExcelLineStyle.Medium);

    //Define a range object (A8)
    range = worksheet.Range["A8"];
    //Set the borders with thick lines style
    range.BorderAround(ExcelLineStyle.Thick);

    //Autofit column A
    worksheet.AutofitColumn(1);

    //Save the workbook
    workbook.SaveAs("XlsIOOutput_Borders.xlsx");
  }
}
Private Sub ApplyBorders()
  Using excelEngine As ExcelEngine = New ExcelEngine()
    'Instantiate the application object
    Dim application As IApplication = excelEngine.Excel

    'Create a workbook
    Dim workbook As IWorkbook = application.Workbooks.Create(1)

    'Get the first sheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    'Put some text into different cells (A2, A4, A6, and A8)
    worksheet.Range(2, 1).Value = "Hair Lines"
    worksheet.Range(4, 1).Value = "Thin Lines"
    worksheet.Range(6, 1).Value = "Medium Lines"
    worksheet.Range(8, 1).Value = "Thick Lines"

    'Define a range object (A2)
    Dim range As IRange
    range = worksheet.Range("A2")
    'Set the borders with hair lines style
    range.BorderAround(ExcelLineStyle.Hair)

    'Define a range object (A4)
    range = worksheet.Range("A4")
    'Set the borders with thin lines style
    range.BorderAround(ExcelLineStyle.Thin)

    'Define a range object (A6)
    range = worksheet.Range("A6")
    'Set the borders with medium lines style
    range.BorderAround(ExcelLineStyle.Medium)

    'Define a range object (A8)
    range = worksheet.Range("A8")
    'Set the borders with thick lines style
    range.BorderAround(ExcelLineStyle.Thick)

    'Autofit column A
    worksheet.AutofitColumn(1)

    'Save as Excel file
    workbook.SaveAs("XlsIOOutput_Borders.xlsx")
  End Using
End Sub