Set Cell Borders and Styles in Excel Using Python

A report needs more than accurate data — it needs clear layout. Borders separate rows and columns, font styles highlight key figures, and alignment keeps numbers and text in their proper places. These are the basic techniques that make an Excel report readable. When reports must be generated in batch or refreshed on a schedule, setting formats through a script is more reliable than adjusting them by hand.

This article uses Spire.XLS for Python to show how to apply borders to a cell range, create a custom style and apply it to entire rows or columns, apply Excel built-in styles, and set font properties directly.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

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

Applying Borders to a Cell Range

The most common need is to add uniform borders to a data range. After obtaining a CellRange, use the Borders property to set them in bulk:

workbook = Workbook()
workbook.LoadFromFile("report.xlsx")
sheet = workbook.Worksheets[0]

# Get the entire data range from the first row/column to the last
cr = sheet.Range[sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn]

# Set border line style and color at once
cr.Borders.LineStyle = LineStyleType.Double
cr.Borders.Color = Color.get_CadetBlue()

# Turn off diagonal borders
cr.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.none
cr.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.none

workbook.SaveToFile("Bordered.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Borders.LineStyle and Borders.Color apply to every border inside the range at once. Diagonal borders are set by default; if they are not wanted, explicitly set DiagonalDown and DiagonalUp to LineStyleType.none. LineStyleType also includes common styles such as Thin, Medium, Dotted, and Dashed.

Creating a Custom Style and Applying It to a Row or Column

When several rows or columns share the same format, setting each cell individually is verbose. A named style can be created once and then applied to whole rows or columns:

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Create a named style
style = workbook.Styles.Add("headerStyle")
style.VerticalAlignment = VerticalAlignType.Center
style.HorizontalAlignment = HorizontalAlignType.Center
style.Font.Color = Color.get_Blue()
style.ShrinkToFit = True

# Define a bottom border inside the style
style.Borders[BordersLineType.EdgeBottom].Color = Color.get_OrangeRed()
style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Dotted

# Apply it to the first column
sheet.Columns[0].CellStyleName = style.Name
sheet.Columns[0].Text = "Test"

workbook.SaveToFile("ColumnStyle.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The style object returned by workbook.Styles.Add() supports the full set of properties: font, alignment, borders, fill, and more. Once defined, apply it through CellStyleName = style.Name to sheet.Columns[i] or sheet.Rows[i], and a single line formats the entire column or row. Replacing sheet.Columns[0] with sheet.Rows[1] applies the same style to the second row.

Applying Excel Built-in Styles

Excel ships with a set of built-in styles (such as Title, Heading, Good, Bad). They can be applied directly, saving the effort of choosing colors manually:

workbook = Workbook()
workbook.LoadFromFile("report.xlsx")
sheet = workbook.Worksheets[0]

# Apply the Title style to the first row
sheet.Range["A1:E1"].BuiltInStyle = BuiltInStyles.Title

workbook.SaveToFile("BuiltInStyle.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The BuiltInStyles enum corresponds to the predefined styles in the "Cell Styles" gallery on Excel's Home tab, which is useful when the report should match Excel's native look.

Setting Font Properties Directly

When only the font of a specific range needs to change, there is no need to create a style object — work through Range.Style.Font directly:

workbook = Workbook()
workbook.LoadFromFile("report.xlsx")
sheet = workbook.Worksheets[0]

# Font name and size
sheet.Range["B1"].Style.Font.FontName = "Comic Sans MS"
sheet.Range["B1"].Style.Font.Size = 45

# Bold, italic, underline
sheet.Range["B2:D2"].Style.Font.IsBold = True
sheet.Range["B3:D7"].Style.Font.IsItalic = True
sheet.Range["B3:B7"].Style.Font.Underline = FontUnderlineType.Single

# Font color and strikethrough
sheet.Range["B1"].Style.Font.Color = Color.get_CornflowerBlue()
sheet.Range["D3"].Style.Font.IsStrikethrough = True

workbook.SaveToFile("FontStyles.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The Font object exposes properties such as FontName, Size, IsBold, IsItalic, Underline, Color, and IsStrikethrough, each settable independently. Underline takes a FontUnderlineType enum value (such as Single, Double, AccountingSingle).

Practical Tips

• Borders.LineStyle applies to all borders in the range; to set only one side, use Borders[BordersLineType.EdgeBottom] and similar to target it individually.
• A custom style defined once can be reused in many places, which is more efficient than per-cell formatting and easier to revise later.
• ShrinkToFit = True shrinks text to fit the column width, suitable for columns whose content length is unpredictable.
• Named colors like Color.get_CadetBlue() are easy to remember; Color.FromArgb(r, g, b) can specify any color.
• Call workbook.Dispose() when finished to release resources.

Conclusion

This article covered several ways to format Excel cells with Python: adding borders through Borders, creating a named style with workbook.Styles.Add() and applying it to whole rows or columns, applying Excel built-in styles through BuiltInStyle, and setting font properties directly through Range.Style.Font. Combined inside a report-generation script, these operations let formatting be fully automated.