How to: Create or Modify a Style

  • 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.

// 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();
}

Custom styles are available to users from the Style Gallery.

SpreadsheetControl_NewStyle

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.

// 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.
// ...

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

SpreadsheetControl_CopyStyle

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.


// 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.
// ...

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.

// 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();
}

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.


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