How to: Create or Modify a Cell Style

• Dec 09, 2022
• 4 minutes to read

Create a Custom Style

Create a New Style

Use the StyleCollection.Add method of the IWorkbook.Styles collection to create a new style with a given name. The style’s format settings are identical to the Normal built-in style. Use the Style object’s properties to specify format characteristics for the new style.

View Example

FormattingActions.cs

// Add a new style under the "My Style" name to the style collection.
Style myStyle = workbook.Styles.Add("My Style");

// Specify the style's format characteristics.
myStyle.BeginUpdate();
try {
    // Set the font color to blue.
    myStyle.Font.Color = Color.Blue;

    // Set the font size to 12.
    myStyle.Font.Size = 12;

    // Center text in cells.
    myStyle.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;

    // Set the background fill.
    myStyle.Fill.BackgroundColor = Color.LightBlue;
    myStyle.Fill.PatternType = PatternType.LightGray;
    myStyle.Fill.PatternColor = Color.Yellow;
} 
finally {
    myStyle.EndUpdate();
}

FormattingActions.vb

' Add a new style under the "My Style" name to the style collection.
Dim myStyle As Style = workbook.Styles.Add("My Style")

' Specify the style's format characteristics.
myStyle.BeginUpdate()
Try
    ' Set the font color to blue.
    myStyle.Font.Color = Color.Blue

    ' Set the font size to 12.
    myStyle.Font.Size = 12

    ' Center text in cells.
    myStyle.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center

    ' Set the background fill.
    myStyle.Fill.BackgroundColor = Color.LightBlue
    myStyle.Fill.PatternType = PatternType.LightGray
    myStyle.Fill.PatternColor = Color.Yellow
Finally
    myStyle.EndUpdate()
End Try

Custom styles are available to users from the Style Gallery.

Create a New Style Based on a Built-In Style

1. Use the StyleCollection.Add method to add a new style to the IWorkbook.Styles collection.

2. Call the Style.CopyFrom method for the created style to copy all format settings from the specified Microsoft® Excel® built-in style to the new style.

View Example

FormattingActions.cs

// Add a new style under the "My Good Style" name to the style collection.
Style myGoodStyle = workbook.Styles.Add("My Good Style");

// Copy all format settings from the built-in Good style.
myGoodStyle.CopyFrom(BuiltInStyleId.Good);

// Modify the style's format characteristics if needed.
// ...

FormattingActions.vb

' Add a new style under the "My Good Style" name to the style collection.
Dim myGoodStyle As Style = workbook.Styles.Add("My Good Style")

' Copy all format settings from the built-in Good style.
myGoodStyle.CopyFrom(BuiltInStyleId.Good)

' Modify the style's format characteristics if needed.
' ...

The image below shows the Style Gallery with a copy of the “Good” built-in style.

Create a New Style Based on Existing Cell Formatting

Use the StyleCollection.Add method overload with the range parameter to create a custom style based on format attributes of an existing cell or cell range.

C#

// Create a style based on format settings of the "B2" cell.
Style customStyle = workbook.Styles.Add("Custom Style", worksheet["B2"]);

// Modify the style's format characteristics if needed.
// ...

VB.NET

' Create a style based on format settings of the "B2" cell.
Dim customStyle As Style = workbook.Styles.Add("Custom Style", worksheet("B2"))

' Modify the style's format characteristics if needed.
' ...

Modify an Existing Style

1. Use a style‘s index or name to obtain it from the IWorkbook.Styles collection.

2. To change the style’s format attributes, modify the Style object’s properties within the Formatting.BeginUpdate - Formatting.EndUpdate method calls.

View Example

FormattingActions.cs

// Access a style you want to modify.
Style myGoodStyle = workbook.Styles["My Good Style"];

// Change the style's format characteristics.
myGoodStyle.BeginUpdate();
try {
    myGoodStyle.Fill.BackgroundColor = Color.LightYellow;
    // ...
}
finally {
    myGoodStyle.EndUpdate();
}

FormattingActions.vb

' Access a style you want to modify.
Dim myGoodStyle As Style = workbook.Styles("My Good Style")

' Change the style's format characteristics.
myGoodStyle.BeginUpdate()
Try
    myGoodStyle.Fill.BackgroundColor = Color.LightYellow
    ' ...
Finally
    myGoodStyle.EndUpdate()
End Try

You can also use the CellRange.Style property to access and modify a style applied to a specific cell or cell range. Style modifications are propagated to all other cells that use this style.

C#

// Modify the style applied to the "F10" cell.
workbook.Worksheets[0].Cells["F10"].Style.Fill.BackgroundColor = Color.SeaShell;

// Modify the style applied to the "K8:M11" cell range.
workbook.Worksheets[0].Range.Parse("K8:M11").Style.Font.Color = Color.Red;

VB.NET

' Modify the style applied to the "F10" cell.
workbook.Worksheets(0).Cells("F10").Style.Fill.BackgroundColor = Color.HotPink

' Modify the style applied to the "K8:M11" cell range.
workbook.Worksheets(0).Range.Parse("K8:M11").Style.Font.Color = Color.Red