3. Excel automation with openpyxl

Crash reporting rarely ends with a single analysis. A typical engineering workflow produces dozens of workbooks per project: one per intersection, one per scenario, one per review cycle. Manually opening each file to copy counts, apply header formatting, and build a summary tab is exactly the kind of repetitive work that invites transcription errors. openpyxl is the standard Python library for reading and writing .xlsx files, and it is the right tool when you need to preserve workbook structure, apply cell-level formatting, and generate derived sheets programmatically.

This chapter builds a small but complete automation pipeline: load a crash report workbook, read its data rows, write computed results into a new summary sheet, and apply consistent styling so the output is presentation-ready. The example uses a fictional but realistic workbook layout — a Collisions sheet with columns for Crash_ID, Date, Severity, Collision_Type, Vehicles, and Injuries — and produces a Summary sheet with counts by severity and by collision type.

Reading an Existing Workbook

Always open workbooks with load_workbook() and read values through worksheet objects. The data_only=True flag is important when the source file contains formulas: without it, openpyxl returns the formula string (e.g. =SUM(D2:D50)) rather than the cached result. Use read_only=True for large files to reduce memory use, but note that read-only mode disables cell writing.

from openpyxl import load_workbook

wb = load_workbook("CR-2024-014_collisions.xlsx", data_only=True)
ws = wb["Collisions"]

# Locate the header row and map column names to indices
headers = [cell.value for cell in ws[1]]
col = {name: idx + 1 for idx, name in enumerate(headers)}

rows = []
for r in range(2, ws.max_row + 1):
    crash_id = ws.cell(row=r, column=col["Crash_ID"]).value
    if crash_id is None:
        continue  # skip blank trailing rows
    rows.append({
        "id": crash_id,
        "severity": ws.cell(row=r, column=col["Severity"]).value,
        "type": ws.cell(row=r, column=col["Collision_Type"]).value,
        "vehicles": ws.cell(row=r, column=col["Vehicles"]).value,
        "injuries": ws.cell(row=r, column=col["Injuries"]).value,
    })

Mapping headers to indices rather than hard-coding column letters makes the script resilient to column reordering. The blank-row guard matters in practice: ws.max_row often extends past the last real record because of stray formatting, and a None crash ID is a reliable sentinel for "no data here."

Computing Aggregates and Writing a Summary Sheet

With the rows loaded into plain Python dictionaries, aggregation is straightforward. Use collections.Counter for frequency counts, then write results into a new worksheet created with wb.create_sheet(). Writing cell by cell via ws.cell(row=..., column=..., value=...) is explicit and easy to debug; ws.append() is convenient for whole rows.

from collections import Counter

sev_counts = Counter(r["severity"] for r in rows)
type_counts = Counter(r["type"] for r in rows)
total_injuries = sum(r["injuries"] or 0 for r in rows)

if "Summary" in wb.sheetnames:
    del wb["Summary"]
summary = wb.create_sheet("Summary", 0)  # insert as first tab

summary["A1"] = "Crash Summary — CR-2024-014"
summary["A3"] = "Severity"
summary["B3"] = "Count"
for i, (sev, n) in enumerate(sorted(sev_counts.items()), start=4):
    summary.cell(row=i, column=1, value=sev)
    summary.cell(row=i, column=2, value=n)

start = 4 + len(sev_counts) + 1
summary.cell(row=start, column=1, value="Collision Type")
summary.cell(row=start, column=2, value="Count")
for i, (ctype, n) in enumerate(type_counts.most_common(), start=start + 1):
    summary.cell(row=i, column=1, value=ctype)
    summary.cell(row=i, column=2, value=n)

summary.cell(row=start + len(type_counts) + 2, column=1, value="Total Injuries")
summary.cell(row=start + len(type_counts) + 2, column=2, value=total_injuries)

Deleting an existing Summary sheet before recreating it makes the script idempotent — rerunning it on the same workbook will not raise a duplicate-sheet error or leave stale data behind. Inserting at index 0 places the summary as the first tab, which is the conventional position for a reader-facing overview.

Applying Cell Styling

Styling in openpyxl is applied through Font, PatternFill, Alignment, and Border objects imported from openpyxl.styles. A small helper function keeps formatting consistent across the workbook and avoids repeating style definitions at every call site.

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

HEADER_FILL = PatternFill("solid", fgColor="1F4E79")
HEADER_FONT = Font(bold=True, color="FFFFFF", size=11)
THIN = Side(style="thin", color="BFBFBF")
BOX = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

def style_header(cell):
    cell.fill = HEADER_FILL
    cell.font = HEADER_FONT
    cell.alignment = Alignment(horizontal="center", vertical="center")
    cell.border = BOX

for cell in summary[3]:
    if cell.value is not None:
        style_header(cell)
for cell in summary[start]:
    if cell.value is not None:
        style_header(cell)

summary.column_dimensions["A"].width = 28
summary.column_dimensions["B"].width = 12
summary.freeze_panes = "A4"

Two practical notes. First, PatternFill("solid", fgColor=...) requires the "solid" pattern type; omitting it produces a fill that renders inconsistently across viewers. Second, column widths and freeze_panes are worksheet-level properties, not cell properties — set them once on the sheet object. Freezing at A4 keeps the title and header rows visible while scrolling long summary tables.

Number Formats and Conditional Emphasis

Counts should be right-aligned and integers should not display decimals. Apply a number format to the count column rather than relying on default formatting:

for row in summary.iter_rows(min_row=4, min_col=2, max_col=2):
    for cell in row:
        if isinstance(cell.value, (int, float)):
            cell.number_format = "0"
            cell.alignment = Alignment(horizontal="right")

For emphasis, a light fill on the total row draws the eye without competing with the header. Use a distinct but muted color so the summary remains readable when printed in grayscale:

TOTAL_FILL = PatternFill("solid", fgColor="DDEBF7")
total_row = start + len(type_counts) + 2
for c in (1, 2):
    summary.cell(row=total_row, column=c).fill = TOTAL_FILL
    summary.cell(row=total_row, column=c).font = Font(bold=True)

Verification and Common Failure Modes

After running the script, open the output workbook and confirm three things: the Summary tab appears first, the severity counts sum to the number of data rows read, and the total injuries figure matches a manual spot-check of a few records. A mismatch in the first check usually means the sheet was appended rather than inserted at index 0; a mismatch in the second usually means blank rows were counted or a severity label was misspelled in the source (e.g. "Fatal " with a trailing space), which Counter treats as a distinct key. Normalizing with .strip().title() before counting is a cheap safeguard.

Finally, save with wb.save("CR-2024-014_collisions_summary.xlsx"). Saving to a new filename preserves the source workbook as an audit trail, which matters in crash reporting where the original data file may be referenced in a review. The complete script — load, parse, aggregate, write, style, save — is short enough to run interactively but structured enough to reuse across projects by parameterizing the input path and sheet names.