Applying styles is one of the simplest ways to enhance the professionalism and readability of Excel spreadsheets. Excel provides a wide range of built-in styles that allow users to quickly format cells, ranges, or worksheets. Additionally, users can create custom styles to specify fonts, colors, borders, number formats, and more, tailored to their individual preferences. Whether you're designing professional reports, sales presentations, or project management plans, knowing how to use styles effectively helps make data more visually appealing and easier to understand.

In this guide, you will learn how to apply styles to cells or worksheets in Excel in Python using Spire.XLS for Python.

• Apply a Built-in Style to Cells in Excel in Python
• Apply a Custom Style to Cells in Excel in Python
• Apply a Custom Style to a Worksheet in Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Apply a Built-in Style to Cells in Excel in Python

Spire.XLS for Python offers the CellRange.BuiltInStyle property, which enables developers to apply built-in styles, such as Title, Heading 1, and Heading 2 to individual cells or ranges in Excel. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet by its index using the Workbook.Worksheets[index] property.
• Get the desired cell or range of cells using the Worksheet.Range[] property.
• Apply a built-in style to the cell or range of cells using the CellRange.BuiltInStyle property.
• Save the resulting file using the Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first sheet
sheet = workbook.Worksheets[0]

# Get the desired cell range
range = sheet.Range["A1:H1"]
# Apply a built-in style to the cell range
range.BuiltInStyle = BuiltInStyles.Heading2

# Save the resulting file
workbook.SaveToFile("ApplyBuiltinStyle.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply a Custom Style to Cells in Excel in Python

Developers can use the Workbook.Styles.Add() method to create a custom style, which can then be applied to individual cells or ranges using the CellRange.Style property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet by its index using the Workbook.Worksheets[index] property.
• Get the desired cell or range of cells using the Worksheet.Range[] property.
• Add a custom style to the workbook using the Workbook.Styles.Add() method.
• Define the formatting, such as the font size, font color, text alignment, cell borders and cell background color, using the properties of the CellStyle class.
• Apply the custom style to the cell or range of cells using the CellRange.Style property.
• Save the resulting file using the Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first sheet
sheet = workbook.Worksheets[0]

# Get the desired cell range
range = sheet.Range["A1:H1"]

# Add a custom style to the workbook
style = workbook.Styles.Add("CustomCellStyle")
# Set the font size
style.Font.Size = 13
# Set the font color
style.Font.Color = Color.get_White()
# Bold the text
style.Font.IsBold = True
# Set the vertical text alignment
style.VerticalAlignment = VerticalAlignType.Bottom
# Set the horizontal text alignment
style.HorizontalAlignment = HorizontalAlignType.Left
# Set the bottom border color
style.Borders[BordersLineType.EdgeBottom].Color = Color.get_GreenYellow()
# Set the bottom border type
style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium
# Set the background color
style.Color = Color.get_CornflowerBlue()

# Apply the custom style to the cell range
range.Style = style

# Save the resulting file
workbook.SaveToFile("ApplyCustomStyle.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Apply a Custom Style to a Worksheet in Excel in Python

In certain cases, it may be necessary to apply a custom style to an entire worksheet rather than to specific cells or ranges. This can be accomplished using the Worksheet.ApplyStyle() method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get a specific worksheet by its index using the Workbook.Worksheets[index] property.
• Add a custom style to the workbook using the Workbook.Styles.Add() method.
• Define the formatting, such as the font size, font color, and cell background color, using the properties of the CellStyle class.
• Apply the custom style to the worksheet using the Worksheet.ApplyStyle() method.
• Save the resulting file using the Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first sheet
sheet = workbook.Worksheets[0]

# Add a custom style to the workbook
style = workbook.Styles.Add("CustomSheetStyle")
# Set the font size
style.Font.Size = 12
# Set the font color
style.Font.Color = Color.FromRgb(91, 155, 213)
# Set the cell background color
style.Color = Color.FromRgb(242, 242, 242)

# Apply the custom style to the worksheet
sheet.ApplyStyle(style)

# Save the resulting file
workbook.SaveToFile("ApplyCustomStyleToSheet.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

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.