Learning Goals
3 minBy the end of this lesson you can:
- Create a workbook, write cells, and save it.
- Append rows of data and write Excel formulas from Python.
- Style cells:
Font,PatternFill,Alignment, number formats. - Set column widths and freeze the header row for a polished result.
Warm-Up · Why Not Just CSV?
5 minYou could dump results to CSV — but a CSV opened by a manager is a grey wall of unformatted text: no totals, no highlighting, columns too narrow to read. A styled .xlsx says "a professional made this."
Writing Excel is the mirror of reading it: build a Workbook, grab a worksheet, assign ws["A1"] = value or ws.append([...]), then attach style objects to cells. Formulas are just strings starting with = — Excel evaluates them when the file opens. A few style touches turn raw data into a report people trust.
New Concept · Building a Workbook
14 minCreate, write, save
from openpyxl import Workbook wb = Workbook() # comes with one sheet ws = wb.active ws.title = "Sales" ws["A1"] = "Region" # write by coordinate ws["B1"] = "Amount" ws.append(["North", 28010]) # append a whole row ws.append(["South", 25000]) wb.save("report.xlsx") # writes the file
ws.append(list) is the workhorse — it adds a row at the next free line. Mix it with coordinate writes for headers and totals.
Formulas are strings
ws["B5"] = "=SUM(B2:B4)" # Excel computes this on open ws["C2"] = "=B2*1.06" # 6% tax ws["A6"] = "=COUNTA(A2:A4)"
Write any Excel formula as a string beginning with =. openpyxl stores it; Excel (or LibreOffice) evaluates it. The cell shows the formula until opened in a spreadsheet app.
Styling cells
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side bold_white = Font(bold=True, color="FFFFFF", size=12) blue_fill = PatternFill("solid", fgColor="2563EB") centre = Alignment(horizontal="center", vertical="center") for cell in ws[1]: # ws[1] = the whole first row cell.font = bold_white cell.fill = blue_fill cell.alignment = centre
Font(bold=, color=, size=)— colours are 6-digit hex strings (no#).PatternFill("solid", fgColor=...)— the cell background.Alignment(horizontal=, vertical=)— text positioning.- Assign a style object to a cell's
.font,.fill, etc.
Number formats
ws["B2"].number_format = "#,##0.00" # 28,010.00 ws["C2"].number_format = "0.0%" # 6.0% ws["D2"].number_format = "yyyy-mm-dd" # dates
The number stays the same; only its display changes — exactly like the Format Cells dialog in Excel.
Column widths & frozen header
ws.column_dimensions["A"].width = 18 # wide enough to read ws.column_dimensions["B"].width = 14 ws.freeze_panes = "A2" # header stays visible when scrolling
freeze_panes = "A2" freezes everything above row 2 — the classic "keep the header on screen" trick that makes long reports usable.
openpyxl has no true auto-fit, but you can fake it: for each column, find the longest value's length and set the width to a bit more. A small loop over ws.columns does it — handy for reports with unpredictable content.
Worked Example · A Styled Sales Report
12 minGoal: turn a list of records into a polished workbook — styled header, currency formatting, a totals row with a live formula, frozen header, and tidy widths.
from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment def build_report(records: list[dict], out: str) -> None: wb = Workbook() ws = wb.active ws.title = "Sales" headers = ["Region", "Product", "Amount"] ws.append(headers) # style the header row bold_white = Font(bold=True, color="FFFFFF") blue = PatternFill("solid", fgColor="2563EB") for cell in ws[1]: cell.font = bold_white cell.fill = blue cell.alignment = Alignment(horizontal="center") # data rows for r in records: ws.append([r["region"], r["product"], r["amount"]]) # currency format on the Amount column for row in ws.iter_rows(min_row=2, min_col=3, max_col=3): for cell in row: cell.number_format = "#,##0.00" # totals row with a live SUM formula last = ws.max_row total_row = last + 1 ws.cell(row=total_row, column=2, value="TOTAL").font = Font(bold=True) total_cell = ws.cell(row=total_row, column=3, value=f"=SUM(C2:C{last})") total_cell.font = Font(bold=True) total_cell.number_format = "#,##0.00" # polish ws.column_dimensions["A"].width = 14 ws.column_dimensions["B"].width = 20 ws.column_dimensions["C"].width = 14 ws.freeze_panes = "A2" wb.save(out) print(f"wrote {out} with {len(records)} rows") data = [ {"region": "North", "product": "Widget", "amount": 1200.0}, {"region": "South", "product": "Gadget", "amount": 950.5}, {"region": "North", "product": "Gizmo", "amount": 430.0}, ] build_report(data, "sales_report.xlsx")
wrote sales_report.xlsx with 3 rows # Open it: blue bold header, currency-formatted amounts, # a bold TOTAL row showing 2,580.50 (a live =SUM formula), # frozen header, readable column widths.
Read the code
Notice the flow: append data, then layer style on top by iterating the cells you want to format. The totals row uses a real =SUM(C2:Cn) formula built with an f-string, so if someone edits a number in Excel the total updates live. Combine this with Lesson 18's reading and Lesson 15's CSV merging and you can ingest raw data and emit a boardroom-ready workbook — which is exactly the Lesson 22 project.
Try It Yourself
13 minCreate a workbook with a bold title in A1, three column headers in row 2, and five data rows. Save and open it to confirm.
Write a sheet of Name, Score rows and fill any cell with a score below 50 in red. Use PatternFill and a per-cell check.
Hint
from openpyxl.styles import PatternFill red = PatternFill("solid", fgColor="FCA5A5") for row in ws.iter_rows(min_row=2, min_col=2, max_col=2): for cell in row: if isinstance(cell.value, (int, float)) and cell.value < 50: cell.fill = red
Write autofit(ws) that sets each column's width to the length of its longest value (plus padding). Apply it to a report and confirm columns are readable.
Hint
from openpyxl.utils import get_column_letter def autofit(ws): for col in ws.columns: longest = max((len(str(c.value)) for c in col if c.value), default=0) letter = get_column_letter(col[0].column) ws.column_dimensions[letter].width = longest + 2
Mini-Challenge · CSV → Styled Excel
8 minWrite csv_to_xlsx(csv_path, out) that reads a CSV (Lesson 15), writes it to a styled workbook with a coloured frozen header, auto-fit columns, and — if a column is named amount or price — currency formatting plus a SUM total row. The bridge from plain data to a presentable report.
Show a sample solution
import csv from openpyxl import Workbook from openpyxl.styles import Font, PatternFill from openpyxl.utils import get_column_letter def csv_to_xlsx(csv_path, out): with open(csv_path, newline="", encoding="utf-8") as f: rows = list(csv.reader(f)) headers, data = rows[0], rows[1:] wb = Workbook(); ws = wb.active ws.append(headers) for cell in ws[1]: cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="2563EB") money_cols = [i for i, h in enumerate(headers) if h.lower() in ("amount", "price")] for r in data: ws.append([float(v) if i in money_cols else v for i, v in enumerate(r)]) for i in money_cols: letter = get_column_letter(i + 1) for c in ws[letter][1:]: c.number_format = "#,##0.00" total = ws.max_row + 1 ws.cell(row=total, column=i + 1, value=f"=SUM({letter}2:{letter}{ws.max_row})") ws.freeze_panes = "A2" wb.save(out) print("wrote", out) csv_to_xlsx("sales.csv", "sales.xlsx")
Non-negotiables: styled frozen header, money-column detection, currency format + SUM total.
Recap
3 minBuild a report by creating a Workbook, writing cells with ws["A1"] = v or ws.append([...]), and saving with wb.save. Formulas are strings starting with = that Excel evaluates on open. Layer style on top with Font, PatternFill, and Alignment objects (colours are hex strings), set display with number_format, widen columns via column_dimensions, and freeze_panes the header. Together these turn raw rows into a report people are happy to receive — the payoff for everything in this data-files arc.
Vocabulary Card
- ws.append
- Adds a list of values as the next row in a worksheet.
- Font / PatternFill
- Style objects for text appearance and cell background colour.
- number_format
- Controls how a cell's value is displayed (currency, %, dates).
- freeze_panes
- Keeps rows/columns visible while the rest scrolls.
Homework
4 minBuild gradebook.py that takes a list of students with subject scores and produces a styled .xlsx: a frozen, coloured header; one row per student; a per-student average column using an =AVERAGE(...) formula; conditional red fill for any failing average; and a bold class-average row at the bottom. Auto-fit the columns.
Sample · gradebook.py (core)
from openpyxl import Workbook from openpyxl.styles import Font, PatternFill students = [ {"name": "Aisha", "math": 88, "science": 92, "english": 79}, {"name": "Ben", "math": 45, "science": 51, "english": 40}, ] subjects = ["math", "science", "english"] wb = Workbook(); ws = wb.active; ws.title = "Grades" ws.append(["Name", *subjects, "Average"]) for cell in ws[1]: cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="2563EB") red = PatternFill("solid", fgColor="FCA5A5") for s in students: row = [s["name"], *(s[sub] for sub in subjects)] ws.append(row) r = ws.max_row avg = ws.cell(row=r, column=len(subjects) + 2, value=f"=AVERAGE(B{r}:{chr(65+len(subjects))}{r})") plain_avg = sum(s[sub] for sub in subjects) / len(subjects) if plain_avg < 50: avg.fill = red # class-average row last = ws.max_row crow = last + 1 ws.cell(row=crow, column=1, value="Class avg").font = Font(bold=True) for col in range(2, len(subjects) + 3): letter = chr(64 + col) ws.cell(row=crow, column=col, value=f"=AVERAGE({letter}2:{letter}{last})").font = Font(bold=True) ws.freeze_panes = "A2" wb.save("gradebook.xlsx")
Non-negotiables: styled frozen header, per-student AVERAGE formula, red fail fill, bold class-average row.