Profile a Slow Excel Report Script in Python

A report that took ten seconds last year takes four minutes now, and the instinct is to start optimising whatever looks slow. That usually means rewriting the wrong part. A few minutes of measuring tells you where the time actually goes — and in Excel scripts the answer is often surprising: not reading the data, not the pandas transformation, but a loop that sets a font on every cell. This page, part of Testing and Packaging Excel Automation Scripts, measures a typical slow report stage by stage, profiles the worst stage, and fixes it with before-and-after numbers.

Prerequisites

pip install pandas openpyxl xlsxwriter python-calamine

orders.xlsx has 50,000 order lines. The report reads it, adds a revenue column, writes every row to a new workbook with openpyxl, sets a font and alignment on each cell, sizes the columns and saves. Timings below are from one laptop; yours will differ, but the proportions are what matter.

Step 1 — Time each stage

Before any profiler, wrap each stage of the script in a timer. A context manager keeps it to one line per stage and collects the results:

import contextlib
import time
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter

timings = {}

@contextlib.contextmanager
def stage(name):
    start = time.perf_counter()
    yield
    timings[name] = time.perf_counter() - start

def slow_report(out="slow.xlsx"):
    with stage("read"):
        df = pd.read_excel("orders.xlsx", engine="openpyxl")
    with stage("transform"):
        df["Revenue"] = df["Qty"] * df["Price"]
    wb = Workbook(); ws = wb.active
    with stage("write cells"):
        ws.append(list(df.columns))
        for row in df.itertuples(index=False):
            ws.append(list(row))
    with stage("style cells"):
        for row in ws.iter_rows(min_row=2):
            for cell in row:
                cell.font = Font(name="Calibri", size=10)
                cell.alignment = Alignment(horizontal="left")
    with stage("column widths"):
        for col in range(1, ws.max_column + 1):
            letter = get_column_letter(col)
            ws.column_dimensions[letter].width = max(len(str(c.value)) for c in ws[letter]) + 2
    with stage("save"):
        wb.save(out)

slow_report()
total = sum(timings.values())
for name, t in timings.items():
    print(f"{name:14} {t:6.2f} s  {t / total:5.0%}")
print(f"{'total':14} {total:6.2f} s")

One stage dominates: styling the 300,000 cells takes around two thirds of the run, far more than reading, writing and saving combined. The pandas transformation, the part people most often try to optimise, takes no measurable time at all. Stage timers are cheap enough to leave in production code; logging them on every run shows when a report starts to slow down, before anyone complains.

Step 2 — Profile the slowest stage

The timer says which stage; cProfile says which calls inside it. Profile only the styling loop, and sort the statistics by cumulative time — the time spent in a function including everything it calls:

import cProfile
import io
import pstats

wb = Workbook(); ws = wb.active
for row in pd.read_excel("orders.xlsx", engine="calamine").head(10000).itertuples(index=False):
    ws.append(list(row))

profiler = cProfile.Profile()
profiler.enable()
for row in ws.iter_rows(min_row=1):
    for cell in row:
        cell.font = Font(name="Calibri", size=10)
        cell.alignment = Alignment(horizontal="left")
profiler.disable()

out = io.StringIO()
pstats.Stats(profiler, stream=out).strip_dirs().sort_stats("cumulative").print_stats(6)
print("\n".join(line for line in out.getvalue().splitlines() if line.strip())[:1400])

Profiling 10,000 rows is enough to see the pattern and keeps the profile quick. Reading the output: ncalls is how many times each function ran, cumtime the total time inside it. The time is split between assigning styles (__set__ in styleable.py), which looks each one up in the workbook's style table — an IndexedList that hashes the style object every time (add and __hash__) — and constructing a new Font for every cell (__init__ in fonts.py), with the Alignment objects just below the cut-off. strip_dirs() shortens the long site-packages paths so the output is readable.

The profiler's own overhead is large for code like this, which makes hundreds of thousands of tiny calls: the same loop runs several times slower under cProfile than without it. Use its output for proportions, and the stage timers for real durations.

Step 3 — Fix the top cost and measure again

The profile points at two fixes, from least to most change. First, create each style object once and reuse it, so nothing is constructed per cell. Second, register a NamedStyle and assign it by name, which skips the per-assignment lookup of separate font and alignment objects:

def style_timing(apply):
    wb = Workbook(); ws = wb.active
    for row in pd.read_excel("orders.xlsx", engine="calamine").itertuples(index=False):
        ws.append(list(row))
    start = time.perf_counter()
    apply(wb, ws)
    return time.perf_counter() - start

def new_objects(wb, ws):
    for row in ws.iter_rows():
        for c in row:
            c.font = Font(name="Calibri", size=10); c.alignment = Alignment(horizontal="left")

def shared_objects(wb, ws):
    font, align = Font(name="Calibri", size=10), Alignment(horizontal="left")
    for row in ws.iter_rows():
        for c in row:
            c.font = font; c.alignment = align

def named_style(wb, ws):
    from openpyxl.styles import NamedStyle
    wb.add_named_style(NamedStyle(name="body", font=Font(name="Calibri", size=10), alignment=Alignment(horizontal="left")))
    for row in ws.iter_rows():
        for c in row:
            c.style = "body"

for fn in (new_objects, shared_objects, named_style):
    print(f"{fn.__name__:15} {style_timing(fn):5.2f} s")

Reusing the objects cuts the styling time about four-fold; the named style cuts it by more than half again. Both are small code changes that keep the script on openpyxl. Styling Excel Cells with openpyxl covers named styles alongside the other styling tools.

Step 4 — Rethink the approach when the fix is not enough

Per-cell styling is only needed when cells differ. When a whole column shares a format, the fastest option is not to style cells at all: write with xlsxwriter and attach the format to the column. Combine that with a faster reader and widths computed from the DataFrame instead of by scanning the sheet:

timings = {}

def fast_report(out="fast.xlsx"):
    with stage("read"):
        df = pd.read_excel("orders.xlsx", engine="calamine")
    with stage("transform"):
        df["Revenue"] = df["Qty"] * df["Price"]
    with stage("write, format, save"):
        with pd.ExcelWriter(out, engine="xlsxwriter") as writer:
            df.to_excel(writer, index=False, sheet_name="Orders")
            ws = writer.sheets["Orders"]
            body = writer.book.add_format({"font_name": "Calibri", "font_size": 10, "align": "left"})
            for i, col in enumerate(df.columns):
                width = max(len(col), int(df[col].astype(str).str.len().max())) + 2
                ws.set_column(i, i, width, body)

fast_report()
for name, t in timings.items():
    print(f"{name:20} {t:6.2f} s")
print(f"{'total':20} {sum(timings.values()):6.2f} s")
print(pd.read_excel("fast.xlsx").shape, pd.read_excel("slow.xlsx").shape)

The whole report now runs in a fraction of the original time, and both files hold the same 50,000 rows and six columns. The calamine engine reads several times faster than openpyxl; see Speed Up pandas Excel Reads with the calamine Engine. A column format applies to every cell in the column that has no format of its own, so the data looks the same as with per-cell styling. The trade-off is that xlsxwriter only creates new files; when the report must edit an existing template, stay with openpyxl and use the fixes from Step 3.

Where Excel scripts usually lose time

Across many report scripts the same few causes come up, roughly in this order: styling or formatting cells one at a time; reading large files with the default engine; writing cells in Python loops instead of letting pandas write whole frames; repeated work inside loops — recomputing a lookup, reopening a workbook, rescanning a column — that could be done once; and saving the same workbook several times during a run. Measuring first tells you which of them applies before you change anything.

Common pitfalls

• Optimising before measuring. Time the stages first; the slow part is rarely where you guess.
• Trusting profiled durations. cProfile adds heavy overhead to many small calls; use it for proportions.
• Profiling the whole run at full size. Profile the slowest stage on a sample to keep it fast and readable.
• A new style object per cell. Create styles once, or use a NamedStyle or column formats.
• Changing several things at once. Make one change, measure, then the next, so you know what helped.

Performance notes

Keep the stage timer in the production script and log its results: a report whose styling time doubles after a data change points straight at the cause. For memory rather than time, tracemalloc reports which lines allocate the most, which is useful when a large workbook runs out of memory rather than time.

Conclusion

Time each stage of the report with a perf_counter context manager, profile the slowest stage on a sample with cProfile sorted by cumulative time, and read the output for proportions rather than durations. In openpyxl, reuse style objects or assign a NamedStyle instead of creating styles per cell; when the output is a new file, read with calamine and write with xlsxwriter using column formats. Change one thing at a time and measure after each change.

Frequently asked questions

How do I find out why my Python Excel script is slow? Time each stage — read, transform, write, style, save — with time.perf_counter, find the slowest, then run that part under cProfile and sort the statistics by cumulative time to see which calls inside it cost the most.

Why is cProfile so much slower than the normal run? cProfile records every function call, which adds overhead to each one. Code that makes millions of small calls, like openpyxl styling, can run several times slower under the profiler. Use it to see where time goes relatively, and stage timers without the profiler for real durations.

Why is styling cells with openpyxl slow? Each assignment of a Font, Fill or Alignment is looked up in the workbook's style table, and creating a new style object for every cell makes that lookup expensive. Create style objects once and reuse them, use a NamedStyle, or write with xlsxwriter and apply formats per column.

What usually makes an Excel report script slow? In order of how often it happens: styling cells one at a time, reading large files with the default engine, writing rows one by one in Python loops, and recalculating things such as column widths by scanning every cell repeatedly.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — making report code dependable.
• Speed Up openpyxl with Read-Only Mode — faster reads of big workbooks.
• Write Large DataFrames to Excel with Write-Only Mode — faster writes.
• Log Python Excel Script Output to a File — keeping the stage timings.