🎨 Formatting Excel Files with Python: A Guide Using `openpyxl`

Python is not only powerful for reading and writing Excel files, but it also lets you apply styles and formatting to make your spreadsheets look polished and professional. In this post, we’ll explore how to format Excel files using the openpyxl library — from bold headers and colored cells to cell alignment and number formatting.

🔧 Why `openpyxl`?

While pandas is excellent for data manipulation, openpyxl gives you fine-grained control over Excel elements such as:

Fonts
Colors and fills
Borders
Alignments
Number formats

🚀 Getting Started

Install the library (if you haven’t already):

pip install openpyxl

Create a Workbook

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "FormattedSheet"

✍️ Writing and Formatting Cells

1. Bold Headers with Font Style

from openpyxl.styles import Font

ws['A1'] = "Name"
ws['B1'] = "Score"
ws['A1'].font = Font(bold=True)
ws['B1'].font = Font(bold=True)

2. Fill Colors

from openpyxl.styles import PatternFill

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

3. Cell Borders

from openpyxl.styles import Border, Side

border = Border(
    left=Side(border_style="thin"),
    right=Side(border_style="thin"),
    top=Side(border_style="thin"),
    bottom=Side(border_style="thin")
)

ws['A1'].border = border
ws['B1'].border = border

4. Alignment (Centering Text)

from openpyxl.styles import Alignment

center_align = Alignment(horizontal='center', vertical='center')
ws['A1'].alignment = center_align
ws['B1'].alignment = center_align

5. Number Formatting

ws['B2'] = 95.678
ws['B2'].number_format = '0.00'  # Rounds to 2 decimal places

📋 Full Example: Formatted Table

data = [
    ["Name", "Score"],
    ["Alice", 93.5],
    ["Bob", 88.25],
    ["Charlie", 79.0]
]

# Add data
for row in data:
    ws.append(row)

# Style header row
for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = border

# Style data rows
for row in ws.iter_rows(min_row=2, max_row=4, min_col=1, max_col=2):
    for cell in row:
        cell.alignment = center_align
        cell.border = border
        if cell.column == 2:  # Score column
            cell.number_format = '0.0'

Finally, save the workbook:

wb.save("formatted_output.xlsx")

✅ Wrapping Up

With just a few lines of code, you can turn a plain spreadsheet into a neatly formatted Excel report. openpyxl is your go-to tool when you want to go beyond data and make your Excel files visually appealing and ready for presentation.

Search This Blog