learn_openpyxl

openpyxl - 4. Styles and Formatting

4.1 Fonts

You can control font attributes like name, size, bold, italic, color, etc., using the Font class from openpyxl.styles, as below:

from openpyxl import Workbook
from openpyxl.styles import Font

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = "Styled Text"

my_font = Font(
    name = "Arial",
    size = 20,
    bold = True,
    italic = True,
    underline = "double",
    strikethrough = True,
    shadow = True,
    color = "0000FF"
)

sheet["A1"].font = my_font

workbook.save("font_styles.xlsx")

Source code reference:

Default Font Styles:

DEFAULT_FONT = Font(name="Calibri", sz=11, family=2, b=False, i=False,
                    color=Color(theme=1), scheme="minor")

Referece on underline:

    u = NestedNoneSet(values=('single', 'double', 'singleAccounting',
                             'doubleAccounting'))
    underline = Alias("u")

Note: the color can be specified using RGB hex codes (e.g., “FF0000” for red) or named colors(?), from documentation (https://openpyxl.pages.heptapod.net/openpyxl/styles.html#colours), you can find aRGB colours and Indexed Colours. (need test on the named colors)

4.2 Fill Colors

Cell background colors are controlled with the PatternFill class:

from openpyxl import Workbook
from openpyxl.styles import PatternFill

workbook = Workbook()
sheet = workbook.active

sheet["A1"] = "Yellow Fill"

# Solid Fill Style
my_fill1 = PatternFill(
    start_color = "FFFF00",
    end_color = "FFFF00",
    fill_type = "solid"
)

sheet["A1"].fill = my_fill1

sheet["B2"] = "Light Up Blue"

# Solid Fill Style
my_fill2 = PatternFill(
    start_color = "0000FF",
    end_color = "FFFFFF",
    fill_type = "lightUp"
)

sheet["B2"].fill = my_fill2

workbook.save("fill_styles.xlsx")

Source code reference:

Fill Color Naming Conversion Mapping with Excel:

Fill Pattern Full Name Short Name Name in Excel
FILL_SOLID solid Solid
FILL_PATTERN_DARKDOWN darkDown Reverse Diagonal Stripe
FILL_PATTERN_DARKGRAY darkGray 75% Gray
FILL_PATTERN_DARKGRID drakGrid Diagonal Crosshatch
FILL_PATTERN_DARKHORIZONTAL darkHorizontal Horizontal Stripe
FILL_PATTERN_DARKTRELLIS darkTrellis Thick Diagonal Crosshatch
FILL_PATTERN_DARKUP darkUp Diagonal Stripe
FILL_PATTERN_DARKVERTICAL darkVertical Vertical Stripe
FILL_PATTERN_GRAY0625 gray0625 6.25% Gray
FILL_PATTERN_GRAY125 gray125 12.5% Gray
FILL_PATTERN_LIGHTDOWN lightDown Thin Reverse Diagonal Stripe
FILL_PATTERN_LIGHTGRAY lightGray 25% Gray
FILL_PATTERN_LIGHTGRID lightGrid Thin Horizontal Crosshatch
FILL_PATTERN_LIGHTHORIZONTAL lightHorizontal Thin Horizontal Stripe
FILL_PATTERN_LIGHTTRELLIS lightTrellis Thing Diagonal Crosshatch
FILL_PATTERN_LIGHTUP lightUp Thin Diagonal Stripe
FILL_PATTERN_LIGHTVERTICAL lightVertical Thin Vertical Stripe
FILL_PATTERN_MEDIUMGRAY mediumGray 50% Gray

4.3 Borders

Borders are defined using the Border, Side classes:

from openpyxl import Workbook
from openpyxl.styles import Border, Side

workbook = Workbook()
sheet = workbook.active

sheet["B2"] = "Thin Border"

sheet["D2"] = "Thick Border"

thin_border = Border(
    left = Side(style = 'thin'),
    right = Side(style = 'thin'),
    top = Side(style = 'thin'),
    bottom = Side(style = 'dashDotDot', color = "0000FF")
)

thick_border = Border(
    left = Side(style = 'thick', color = "FF0000"),
    right = Side(style = 'thick'),
    top = Side(style = 'thick'),
    bottom = Side(style = 'thick', color = "0000FF")
)

sheet["B2"].border = thin_border

sheet["D2"].border = thick_border

workbook.save("border_styles.xlsx")

Soruce code reference:

Valid Border Side Styles:

style = NoneSet(values=('dashDot','dashDotDot', 'dashed','dotted',
  'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
  'mediumDashed', 'slantDashDot', 'thick', 'thin'))

4.4 Alignment

Cell content alignment is controlled with the Alignment class:

from openpyxl import Workbook
from openpyxl.styles import Alignment

workbook = Workbook()
sheet = workbook.active

sheet["C5"] = "Center Aligned Text"

my_alignment = Alignment(
    horizontal = "center",
    vertical = "top",
    wrapText = True
)

sheet["C5"].alignment = my_alignment

workbook.save("alignment_styles.xlsx")

Source code reference:

horizontal_alignments = (
    "general", "left", "center", "right", "fill", "justify", "centerContinuous",
    "distributed", )
vertical_aligments = (
    "top", "center", "bottom", "justify", "distributed",
)

4.5 Number Formats

Number formate were covered in the previous section (“Cells and Cell Values”).

4.6 Conditional Formatting

Conditional formatting involves applying styles based on cell values or formulas.

openpyxl provides support for this, but it’s more complex; here in openpyxl documentation (https://openpyxl.pages.heptapod.net/openpyxl/formatting.html).

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Color
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

wb = Workbook()
ws = wb.active

# Create fill
redFill = PatternFill(
    start_color = "EE1111",
    end_color = "EE1111",
    fill_type = "solid"
)

# Add a two-color scale
ws.conditional_formatting.add(
    "A1:A10",
    ColorScaleRule(
        start_type = "min",
        start_color = "AA0000",
        end_type = "max",
        end_color = "00AA00"
    )
)

# Add a three-color scale
ws.conditional_formatting.add(
    "B1:B10",
    ColorScaleRule(
        start_type = "percentile",
        start_value = 10,
        start_color = "FF0000",
        mid_type = "percentile",
        mid_value = 50,
        mid_color = "0000FF",
        end_type = "percentile",
        end_value = 90,
        end_color = "00AA00"
    )
)

# Formatting baed on a cell comparison

ws.conditional_formatting.add(
    "C2:C10",
    CellIsRule(
        operator = "between",
        formula = ['1', '5'],
        stopIfTrue  = True,
        fill = redFill
    )
)

myFont = Font()
myBorder = Border()
ws.conditional_formatting.add(
    "D1:D10",
    FormulaRule(
        formula = ["E1=0"],
        font = myFont,
        border = myBorder,
        fill = redFill
    )
)

wb.save("conditional-formatting.xlsx")

Source code reference:

4.7 Styles and Themes

openpyxl allows working with styles and themes, but the specifics are advanced and are best explored in the library’s documentation. Themes govern the overall look and feel, while styles provide more fine-grained control over individual elements.

4.8 Applying Styles to Cells and Ranges

Styles are applied to cells using the appropriate style properties (e.g., cell.font, cell.fill, cell.alignment, cell.number_format, cell.border). To apply styles to ranges, iterate through the cells in the range and apply the styles to each cell individually, or explore using Conditional Formatting which can apply styles to ranges based on conditions. Creating and applying a custom Style object can also be helpful for consistently applying multiple formatting elements.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

workbook = Workbook()
sheet = workbook.active

# Define styles once
bold_red_font = Font(bold = True, color = "FF0000")
yellow_fill = PatternFill(
    start_color = "FFFF00",
    end_color = "FFFF00",
    fill_type = "solid"
)
thin_border = Border(
    left = Side(style = "thin"),
    right = Side(style = "thin"),
    top = Side(style = "thin"),
    bottom = Side(style = "thick")
)
center_alignment = Alignment(horizontal="center")

# Define styles to a range
for row in sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        cell.value = "hello"
        cell.font = bold_red_font
        cell.fill = yellow_fill
        cell.border = thin_border
        cell.alignment = center_alignment

workbook.save("range_styles.xlsx")

Last Updated at: 12/31/2025, 10:01:59 PM

This site is open source. Improve this page.