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