Style Cells and Fonts in Excel with Python openpyxl

Excel formatting makes data readable and professional. Python openpyxl helps automate this styling. You can change fonts, colors, and borders programmatically.

This guide covers essential formatting techniques. We will explore fonts, fills, borders, and alignment. Each section includes practical code examples.

Getting Started with Openpyxl

First, ensure openpyxl is installed. Use pip to install the library. Check our Install Openpyxl in Python Step by Step guide if needed.

Import openpyxl in your Python script. Create or load a workbook. Then access the active worksheet to begin styling.

# Import openpyxl and create a workbook
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# Create a new workbook and select active sheet
wb = Workbook()
ws = wb.active
ws.title = "StyledSheet"

Basic Font Styling

Font styling changes text appearance. You can modify size, color, and style. Use the Font class for these changes.

Create a Font object with desired properties. Then apply it to specific cells. This makes headers and important data stand out.

# Create different font styles
header_font = Font(name='Arial', size=14, bold=True, color='FF0000')
italic_font = Font(italic=True, size=12)
bold_font = Font(bold=True)

# Apply fonts to cells
ws['A1'] = "Sales Report"
ws['A1'].font = header_font

ws['A3'] = "Quarterly Data"
ws['A3'].font = italic_font

ws['B5'] = "Important Note"
ws['B5'].font = bold_font

Cell Background Colors

Cell fills add background colors. Use PatternFill for solid colors. This highlights important cells or creates color coding.

Choose fill types and colors. Apply them to cell ranges. This improves data visualization and organization.

# Create different fill patterns
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')

# Apply fills to cells
ws['A1'].fill = yellow_fill

for row in ws['B2:B10']:
    for cell in row:
        cell.fill = green_fill

Adding Borders to Cells

Borders define cell boundaries clearly. Create border sides with specific styles. Then build complete borders from these sides.

Apply borders to individual cells or ranges. This creates professional-looking tables and data sections.

# Define border styles
thin_border = Side(border_style="thin", color="000000")
thick_border = Side(border_style="thick", color="FF0000")

# Create border objects
cell_border = Border(left=thin_border, right=thin_border, top=thin_border, bottom=thin_border)
header_border = Border(bottom=thick_border)

# Apply borders
ws['A1'].border = header_border

for row in ws['A3:C10']:
    for cell in row:
        cell.border = cell_border

Text Alignment Options

Alignment controls text positioning within cells. Use horizontal and vertical alignment. Also control text wrapping and indentation.

Proper alignment improves readability. Center headers and align numbers consistently. This creates polished spreadsheets.

# Create alignment objects
center_align = Alignment(horizontal='center', vertical='center')
right_align = Alignment(horizontal='right')
wrap_align = Alignment(wrap_text=True)

# Apply alignments
ws['A1'].alignment = center_align
ws['B5'].alignment = right_align
ws['C10'].alignment = wrap_align
ws['C10'] = "This is a long text that will wrap within the cell boundaries"

Working with Multiple Sheets

Real projects often use multiple sheets. Learn to iterate through sheets efficiently. Apply consistent styling across all sheets.

Loop through workbook sheets. Apply formatting patterns to each. This ensures uniform appearance throughout.

# Apply styling to multiple sheets
standard_font = Font(name='Calibri', size=11)
standard_fill = PatternFill(start_color='F0F0F0', fill_type='solid')

for sheet in wb.sheetnames:
    current_sheet = wb[sheet]
    current_sheet['A1'].font = standard_font
    current_sheet['A1'].fill = standard_fill

Complete Styling Example

Combine all styling techniques in one example. Create a professional sales report. Use fonts, colors, borders, and alignment together.

This demonstrates real-world formatting. You can adapt this pattern for your projects. The result is a publication-ready spreadsheet.

# Complete styling example
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

wb = Workbook()
ws = wb.active

# Define styles
title_font = Font(name='Arial', size=16, bold=True, color='FFFFFF')
header_font = Font(bold=True)
data_fill = PatternFill(start_color='E6E6FA', fill_type='solid')
border_side = Side(border_style="thin")
table_border = Border(left=border_side, right=border_side, top=border_side, bottom=border_side)

# Apply title
ws['A1'] = "Quarterly Sales Report"
ws['A1'].font = title_font
ws['A1'].fill = PatternFill(start_color='3366FF', fill_type='solid')
ws['A1'].alignment = Alignment(horizontal='center')
ws.merge_cells('A1:D1')

# Apply headers
headers = ['Product', 'Q1 Sales', 'Q2 Sales', 'Q3 Sales']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=3, column=col)
    cell.value = header
    cell.font = header_font
    cell.border = table_border

# Add sample data
data = [
    ['Laptops', 15000, 18000, 22000],
    ['Phones', 12000, 15000, 19000],
    ['Tablets', 8000, 9500, 11000]
]

for row_num, row_data in enumerate(data, 4):
    for col_num, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_num, column=col_num)
        cell.value = value
        cell.border = table_border
        if col_num > 1:  # Format numbers
            cell.number_format = '#,##0'
            cell.fill = data_fill

# Adjust column widths
ws.column_dimensions['A'].width = 15
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12

# Save the workbook
wb.save('styled_report.xlsx')

Common Issues and Solutions

Sometimes you might encounter import errors. If you get ModuleNotFoundError: No module named 'openpyxl', reinstall the library.

For basic openpyxl operations, check our Python Openpyxl Tutorial: Read Write Excel XLSX Files. It covers fundamental workbook operations.

Remember to save your workbook after styling. Otherwise, changes will be lost. Use the save method with your filename.

Conclusion

Python openpyxl provides powerful Excel styling capabilities. You can automate font formatting, cell colors, and borders. This saves time and ensures consistency.

Combine these techniques for professional reports. Experiment with different style combinations. Create templates for repeated use.

Mastering openpyxl styling makes your Python-Excel workflows efficient. Your spreadsheets will look professional and be more readable.