Knowledgebase (2329)
Children categories
Cell borders refer to lines that can be added around a cell or range of cells. They can be used to serve different purposes, such as to separate sections in a worksheet, draw readers' attention to important cells, or make the worksheet look more presentable. This article will introduce how to add or remove cell borders in Excel in C# and VB.NET using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Add Cell Borders in Excel in C# and VB.NET
Spire.XLS for .NET allows adding various kinds of borders to cells in Excel, such as left border, right border, top border, bottom border, diagonal borders, inside borders and outside borders.
You can add a specific border or multiple borders to individual cells or ranges of cells. In addition, you can also set different line styles and line colors for the borders. The following are the main steps to apply different kinds of cell borders with different line styles and line colors:
- Initialize an instance of the Workbook class.
- Get a specific worksheet by its index through Workbook.Worksheets[int] property.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Get specific borders (such as left, right, top, bottom and diagonal) from the Borders collection of the cell range through CellRange.Borders[BordersLineType] property.
- Set the line styles of the specific borders through IBorder.LineStyle property.
- Set the line colors of the specific borders through IBorder.Color property.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Add outside borders and/or inside borders to the cell range using CellRange.BorderAround(LineStyleType, Color) method and/or CellRange.BorderInside(LineStyleType, Color) method. Note that inside borders cannot be applied to a single cell.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Set the line styles and line colors for borders of the cell range through BordersCollection.LineStyle and BordersCollection.Color properties, then set the line style and color for diagonal borders of the cell range.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;
namespace AddCellBorders
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Set left, right, top, bottom and diagonal up borders for cell B2
CellRange range = sheet.Range["B2"];
IBorder leftBorder = range.Borders[BordersLineType.EdgeLeft];
leftBorder.LineStyle = LineStyleType.MediumDashDotDot;
leftBorder.Color = Color.Red;
IBorder rightBorder = range.Borders[BordersLineType.EdgeRight];
rightBorder.LineStyle = LineStyleType.MediumDashed;
rightBorder.Color = Color.Red;
IBorder topBorder = range.Borders[BordersLineType.EdgeTop];
topBorder.LineStyle = LineStyleType.Medium;
topBorder.Color = Color.Red;
IBorder bottomBorder = range.Borders[BordersLineType.EdgeBottom];
bottomBorder.LineStyle = LineStyleType.Medium;
bottomBorder.Color = Color.Red;
IBorder diagonalUpBorder = range.Borders[BordersLineType.DiagonalUp];
diagonalUpBorder.LineStyle = LineStyleType.Thin;
diagonalUpBorder.Color = Color.Red;
//Set diagonal borders for cell C4
range = sheet.Range["C4"];
diagonalUpBorder = range.Borders[BordersLineType.DiagonalUp];
diagonalUpBorder.LineStyle = LineStyleType.Double;
diagonalUpBorder.Color = Color.Blue;
IBorder diagonalDownBorder = range.Borders[BordersLineType.DiagonalDown];
diagonalDownBorder.LineStyle = LineStyleType.Double;
diagonalDownBorder.Color = Color.Blue;
//Set outside borders for cell D6
range = sheet.Range["D6"];
range.BorderAround(LineStyleType.Double, Color.Green);
//Set inside borders for cell range E8:F10
range = sheet.Range["E8:F10"];
range.BorderInside(LineStyleType.MediumDashed, Color.DarkGray);
//Set inside and outside borders for cell range F12:G14
range = sheet.Range["F12:G14"];
range.BorderInside(LineStyleType.MediumDashed, Color.Pink);
range.BorderAround(LineStyleType.Medium, Color.Magenta);
//Set borders for cell range G16:H18
range = sheet.Range["G16:H18"];
range.Borders.LineStyle = LineStyleType.Thick;
range.Borders.Color = Color.Cyan;
//Set line style and line color of diagonal borders for cell range G16:H18
diagonalUpBorder = range.Borders[BordersLineType.DiagonalUp];
diagonalUpBorder.LineStyle = LineStyleType.Dotted;
diagonalUpBorder.Color = Color.DarkGray;
diagonalDownBorder = range.Borders[BordersLineType.DiagonalDown];
diagonalDownBorder.LineStyle = LineStyleType.Dotted;
diagonalDownBorder.Color = Color.DarkGray;
//Save the result file
workbook.SaveToFile("AddBorders.xlsx", ExcelVersion.Version2013);
}
}
}
Remove Cell Borders in Excel in C# and VB.NET
You can remove all borders of a cell or range of cells by setting the CellRange.Borders.LineStyle property as LineStyleType.None. The following are the details steps:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet by its index through Workbook.Worksheets[int] property.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Remove the borders of the cell range by setting CellRange.Borders.LineStyle property as LineStyleType.None.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace RemoveCellBorders
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("AddBorders.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Remove borders of cell range G16:H18
CellRange range = sheet.Range["G16:H18"];
range.Borders.LineStyle = LineStyleType.None;
workbook.SaveToFile("RemoveBorders.xlsx", ExcelVersion.Version2013);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
When you’re creating or reviewing a worksheet, you may want to format text in some specific cells using font styles in order to make them stand out. For example, you can change the font type, font color, font size and make text bold. This article will show you how to apply fonts to individual cells or cell ranges by using Spire.XLS for .NET.
- Apply Different Fonts to Different Cells
- Appy Multiple Fonts in a Single Cell
- Apply a Font to a Cell Range
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Apply Different Fonts to Different Cells
Spire.XLS provides the CellRange.Style.Font property which you can use to set or change the font name, color, size and style in a cell easily. The following are the steps to apply a font style to a specific cell using Spire.XLS for .NET.
- Create a Workbook object.
- Get the first worksheet using Workbook.Worksheets[index] property.
- Get a specific cell using Worksheet.Range[int Row, int Column] property.
- Set the value of the cell using CellRange.Value property.
- Set the font name, color, size and style of the cell value through the properties under the CellRange.Value.Font object.
- Save the workbook to an Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using System.Drawing;
namespace ApplySingleFontInCellRange
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Set font name
int row = 1;
sheet.Range[row, 1].Value = "Font Name";
sheet.Range[row, 2].Value = "Arial Black";
sheet.Range[row, 2].Style.Font.FontName = "Arial Black";
//Set font size
sheet.Range[row += 2, 1].Value = "Font Size";
sheet.Range[row, 2].Value = "15";
sheet.Range[row, 2].Style.Font.Size = 15;
//Set font color
sheet.Range[row += 2, 1].Value = "Font Color";
sheet.Range[row, 2].Value = "Red";
sheet.Range[row, 2].Style.Font.Color = Color.Red;
//Make text bold
sheet.Range[row += 2, 1].Value = "Bold";
sheet.Range[row, 2].Value = "Bold";
sheet.Range[row, 2].Style.Font.IsBold = true;
//Make text italic
sheet.Range[row += 2, 1].Value = "Italic";
sheet.Range[row, 2].Value = "Italic";
sheet.Range[row, 2].Style.Font.IsItalic = true;
//Underline text
sheet.Range[row += 2, 1].Value = "Underline";
sheet.Range[row, 2].Value = "Underline";
sheet.Range[row, 2].Style.Font.Underline = FontUnderlineType.Single;
//Strikethrough text
sheet.Range[row += 2, 1].Value = "Strikethrough ";
sheet.Range[row, 2].Value = "Strikethrough ";
sheet.Range[row, 2].Style.Font.IsStrikethrough = true;
//Auto fit column width
sheet.AllocatedRange.AutoFitColumns();
//Save the workbook to an Excel file
workbook.SaveToFile("ApplySingleFontInCell.xlsx", ExcelVersion.Version2016);
}
}
}
Appy Multiple Fonts in a Single Cell
Mixing fonts in a single cell can help you emphasize some specific characters within the cell. The following are the steps to apply multiple fonts in a cell using Spire.XLS for .NET.
- Create a Workbook object.
- Get the first worksheet using Workbook.Worksheets[index] property.
- Create two ExcelFont objects using Workbook.CreateFont() method.
- Get a specific cell using Worksheet.Range[int Row, int Column] property, and set the rich text content of the cell using CellRange.RichText.Text property.
- Apply the two ExcelFont objects to the rich text using RichText.SetFont() method.
- Save the workbook to an Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace ApplyMultipleFontsInACell
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Create a font
ExcelFont font1 = workbook.CreateFont();
font1.FontName = "Arial Black";
font1.KnownColor = ExcelColors.LightBlue;
font1.IsBold = true;
font1.Size = 13;
//Create another font
ExcelFont font2 = workbook.CreateFont();
font2.KnownColor = ExcelColors.Red;
font2.IsBold = true;
font2.IsItalic = true;
font2.FontName = "Algerian";
font2.Size = 15;
//Returns a RichText object from a specified cell
RichText richText = sheet.Range["A1"].RichText;
//Set the text of RichText object
richText.Text = "Buy One, Get One Free";
//Apply the first font to specified range of characters
richText.SetFont(0, 16, font1);
//Apply the second font to specified range of characters
richText.SetFont(17, 21, font2);
//Set column width
sheet.Columns[0].ColumnWidth = 33;
//Save the workbook to an Excel file
workbook.SaveToFile("ApplyMultipleFonts.xlsx", ExcelVersion.Version2016);
}
}
}
Apply a Font to a Cell Range
Spire.XLS provides the CellStyle class to manage the cell formatting such as fill color, text alignment and font style. You can create a cell style and apply it to a cell range or the whole worksheet using CellRange.ApplyStyle() method and Worksheet.ApplyStyle() method, respectively. The following are the steps to apply a font to a cell range using Spire.XLS for .NET.
- Create a Workbook object.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get the first worksheet using Workbook.Worksheets[index] property.
- Create a CellStyle object using Workbook.Styles.Add() method, and set the font style through the CellStyle.Font property.
- Apply the cell style to a cell range using CellRange.ApplyStyle() method.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using System.Drawing;
namespace ApplyFontToCellRange
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Create a CellStyle object
CellStyle fontStyle = workbook.Styles.Add("headerFontStyle");
//Set the font color, size and style
fontStyle.Font.Color = Color.White;
fontStyle.Font.IsBold = true;
fontStyle.Font.Size = 12;
fontStyle.HorizontalAlignment = HorizontalAlignType.Center;
//Create a CellStyleFlag object, setting the FontColor, FontBold, FontSize and HorizontalAlignment properties to true
CellStyleFlag flag = new CellStyleFlag();
flag.FontColor = true;
flag.FontBold = true;
flag.FontSize = true;
flag.HorizontalAlignment = true;
//Apply the cell style to header row
sheet.Range[1, 1, 1, 8].ApplyStyle(fontStyle, flag);
//Apply the cell style to the whole worksheet
//sheet.ApplyStyle(fontStyle);
//Save the workbook to another Excel file
workbook.SaveToFile("ApplyFontToCellRange.xlsx", ExcelVersion.Version2016);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
C#/VB.NET: Set Background Color and Pattern for Excel Cells
2022-04-07 07:49:00 Written by AdministratorBy default, the cells in an Excel document are formatted with a background color of transparent. When you need to emphasize some important data in particular cells, Microsoft Excel provides the "Fill Color" formatting option to change the background color or pattern style of the cells. In this article, you will learn how to programmatically set background color and pattern style for a specified cell or cell range in Excel using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Set Background Color and Pattern for Excel Cells
The detailed steps are as follows.
- Instantiate a Workbook object.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Get a specified cell range using Worksheet.Range[] property.
- Get the style of the specified cell range using CellRange.Style property.
- Set the background color for the specified cell range using CellStyle.Color property.
- Set the fill pattern style for the specified cell range using CellStyle.FillPattern property.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using System.Drawing;
using Spire.Xls;
namespace CellBackground
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\data.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Set background color for Range ["A1:E1"] and ["A2:A10"]
worksheet.Range["A1:E1"].Style.Color = Color.MediumSeaGreen;
worksheet.Range["A2:A10"].Style.Color = Color.LightYellow;
//Set background color for cell E6
worksheet.Range["E6"].Style.Color = Color.Red;
//Set pattern style for Range ["B4:D5"]
worksheet.Range["B4:D5"].Style.FillPattern = ExcelPatternType.Percent125Gray;
//Save the result file
workbook.SaveToFile("CellBackground.xlsx", ExcelVersion.Version2013);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
