Python openpyxl: Fonts, Fills, Borders, Alignment, and Number Formats

When you need to generate Excel reports programmatically, python openpyxl fonts fills borders alignment and number formats are the core styling tools that determine how readable and professional the output looks. This article explains how to apply each of these styles to individual cells or ranges, how they interact, and where the common mistakes appear in real code.

Applying Fonts with openpyxl

Fonts control the typeface, size, weight, color, and effects of cell text. In openpyxl, you create a Font object and assign it to the font property of a cell. The Font class accepts keyword arguments such as name, size, bold, italic, underline, strike, and color.

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

cell = ws['A1']
cell.value = 'Quarterly Revenue'
cell.font = Font(name='Calibri', size=14, bold=True, color='1F4E78')

The color argument expects an RGB hex string without the leading #. You can also use Color objects if you need alpha transparency, but for most reports a plain hex string is sufficient. The underline argument accepts 'single' or 'double', and strike is a boolean. Fonts are applied per cell, so if you want a whole row or column styled, you must assign the font to each cell individually or use a named style.

Controlling Cell Fills and Background Colors

Fills set the background color of a cell. openpyxl provides PatternFill for solid colors and simple patterns, and GradientFill for gradient effects. The most common use is a solid fill, which you create with PatternFill(start_color='FFC000', end_color='FFC000', fill_type='solid'). The start_color and end_color are the same for a solid fill, and fill_type must be 'solid' to avoid the default none behavior.

from openpyxl.styles import PatternFill

header_fill = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')
ws['A1'].fill = header_fill

If you want a striped or patterned fill, you can set fill_type to values like 'lightGray', 'darkGrid', or 'gray125', but these are rarely used in professional reports. For most data tables, a solid fill for headers and alternating row shading is enough. Be careful: when you copy a cell with a fill, the fill style is copied along with the value unless you explicitly clear it.

Setting Borders on Cells

Borders require two objects: Border and Side. A Side defines the line style and color for one edge, and Border assembles up to four sides (left, right, top, bottom) plus diagonal and diagonalDown/diagonalUp flags. The style argument of Side accepts a string from a fixed set, including 'thin', 'medium', 'thick', 'dashed', 'dotted', and 'double'.

from openpyxl.styles import Border, Side

thin_border = Border(
    left=Side(style='thin', color='000000'),
    right=Side(style='thin', color='000000'),
    top=Side(style='thin', color='000000'),
    bottom=Side(style='thin', color='000000')
)

ws['A1'].border = thin_border

If you only need a bottom border for a header row, you can omit the other sides and openpyxl will leave them unset. The table below lists the common border styles and their visual weight.

Borders do not automatically apply to the entire range when you assign to one cell. To border a range, you must iterate over the cells or use a named style applied to the range.

Aligning Cell Content

Alignment controls the horizontal and vertical positioning of text within a cell, as well as wrapping, indentation, and rotation. The Alignment class takes horizontal, vertical, wrap_text, indent, shrink_to_fit, and text_rotation arguments. Horizontal options include 'left', 'center', 'right', 'fill', 'justify', and 'centerContinuous'. Vertical options are 'top', 'center', 'bottom', and 'justify'.

from openpyxl.styles import Alignment

ws['A1'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

wrap_text=True is essential for long text that would otherwise overflow into adjacent cells. indent is measured in characters and works only when horizontal is 'left' or 'right'. text_rotation accepts an integer between 0 and 180, or 255 for vertical text. When you align a range, the same alignment applies to every cell in that range, so you cannot have different alignments per cell unless you assign them individually.

Formatting Numbers with Number Formats

Number formats control how numeric values are displayed without changing the underlying value. The number_format property of a cell accepts an Excel format string. openpyxl does not validate these strings, so you must use the same syntax as Excel. Common built-in formats include '0' for integers, '0.00' for two decimals, '#,##0' for thousands separators, and '0.00%' for percentages. You can also use custom formats for dates, currency, and scientific notation.

ws['B2'].value = 1234.567
ws['B2'].number_format = '#,##0.00'

ws['B3'].value = 0.25
ws['B3'].number_format = '0.0%'

ws['B4'].value = 45000
ws['B4'].number_format = '$#,##0'

The table below shows a few format strings and their output for the value 1234.5.

Number formats affect only the display; the stored value remains the same. This is important when you later read the file with a library like pandas, which will see the raw numeric value unless you explicitly convert it.

Combining Styles and Managing Performance

Applying styles cell by cell is straightforward but can become slow when you have thousands of cells. Each style assignment creates a new style object and updates the worksheet's style table. For large datasets, the overhead can be noticeable. A better approach is to apply styles to a range in one pass by iterating over the rows and assigning the same style objects to each cell. Even better, define a NamedStyle once and apply it to multiple cells.

from openpyxl.styles import NamedStyle

header_style = NamedStyle(name='header_style')
header_style.font = Font(bold=True, size=12, color='FFFFFF')
header_style.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_style.alignment = Alignment(horizontal='center', vertical='center')
header_style.border = Border(bottom=Side(style='medium', color='1F4E78'))

wb.add_named_style(header_style)

for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=5):
    for cell in row:
        cell.style = 'header_style'

Named styles reduce duplication and make the code easier to maintain. If you need to change the header appearance later, you update the style definition once instead of every assignment. When applying styles to a large range, avoid creating new Font, Fill, or Border objects inside the loop; reuse the same objects or use a named style.

Common Pitfalls and Compatibility Notes

Several issues appear frequently when working with openpyxl styles. First, the color property does not accept a leading #; passing '#FF0000' raises an error. Always use the six-digit hex string without the hash. Second, PatternFill with fill_type='solid' requires both start_color and end_color to be set, otherwise the fill may not render as expected. Third, number formats are not validated, so a typo like '0.00%' instead of '0.0%' will silently produce incorrect output in Excel.

Another subtle issue is that when you copy a cell using copy.copy(), the style is not copied unless you also copy the style objects. If you use ws.cell(row, col).value = other_cell.value and expect the style to follow, you must explicitly assign the style properties. Finally, openpyxl does not support conditional formatting rules that depend on cell values; you can only apply static styles. If you need conditional formatting, you must either compute the style in Python based on the value or use a different library that supports it.

For compatibility, remember that openpyxl writes to the .xlsx format only. Styles applied to cells are saved with the workbook and will be preserved when the file is opened in Excel, LibreOffice, or Google Sheets. However, some advanced features like gradient fills or certain number format codes may not render identically across all spreadsheet applications. Test the output in the target application if the report will be consumed by users on different platforms.