windowsforms-15429-controls-and-libraries-spreadsheet-examples-formatting-how-to-create-or-modify-a-style.md
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();
}
' 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.
Use the StyleCollection.Add method to add a new style to the IWorkbook.Styles collection.
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.
// ...
' 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.
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.
// ...
' 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.
' ...
Use a style‘s index or name to obtain it from the IWorkbook.Styles collection.
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();
}
' 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.
// 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;
' 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