Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Test That Excel Charts Were Written Correctly

Write pytest tests for the charts a report script produces: since openpyxl does not load charts back, read them straight from the chart XML inside the .xlsx — chart type, title, series names, value and category ranges, axis settings and which sheet and cell each chart sits on — compare them with expectations or a stored snapshot, and catch the regressions that leave a chart empty, pointing at the wrong column, or with hidden axes.
Logging, testing & packagingCharts & images

Tests for a report script usually check the numbers: the right rows, the right totals, the right formats. The charts get looked at once, when the script is first written, and never again — until someone changes the column order and the revenue chart quietly starts plotting costs, or a refactor drops a line and the axes vanish. Charts deserve tests too, and the obstacle is practical: openpyxl can write charts but does not read them back, so load_workbook gives no way to inspect what a saved chart contains. The chart definitions are still in the file, though, as XML parts inside the .xlsx package, and they are easy to read with the standard library. This page builds a small helper that extracts each chart's essentials — type, title, series and their ranges, axis settings, the sheet and cell it is anchored to — and uses it in pytest, both with explicit assertions and with a stored snapshot. It belongs to Testing and Packaging Excel Automation Scripts.

Prerequisites

Bash
pip install openpyxl pytest

The code under test is a small report function that writes a sheet of monthly figures and two charts. The helper reads any .xlsx, whichever library wrote it.

Step 1 — The report being tested

Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

def build_report(path):
    wb = Workbook()
    ws = wb.active
    ws.title = "Monthly"
    ws.append(["Month", "Revenue", "Cost", "Margin %"])
    for i, (rev, cost) in enumerate([(120, 80), (135, 88), (128, 90), (150, 95)], start=2):
        ws.append([f"2026-0{i - 1}", rev, cost, f"=(B{i}-C{i})/B{i}"])

    bars = BarChart()
    bars.type = "col"
    bars.title = "Revenue and cost"
    bars.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=5), titles_from_data=True)
    bars.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
    bars.x_axis.delete = bars.y_axis.delete = False
    ws.add_chart(bars, "F2")

    line = LineChart()
    line.title = "Margin"
    line.add_data(Reference(ws, min_col=4, min_row=1, max_row=5), titles_from_data=True)
    line.set_categories(Reference(ws, min_col=1, min_row=2, max_row=5))
    line.y_axis.numFmt = "0%"
    line.x_axis.delete = line.y_axis.delete = False
    ws.add_chart(line, "F20")
    wb.save(path)

The report writes four months of revenue and cost, a margin formula, a column chart of revenue and cost, and a line chart of the margin. It is ordinary openpyxl code; nothing about it is special for testing.

Step 2 — Read the charts back from the package

Python
import posixpath
import re
import zipfile
import xml.etree.ElementTree as ET

C = "{http://schemas.openxmlformats.org/drawingml/2006/chart}"
A = "{http://schemas.openxmlformats.org/drawingml/2006/main}"
XDR = "{http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing}"
R = "{http://schemas.openxmlformats.org/officeDocument/2006/relationships}"
PR = "{http://schemas.openxmlformats.org/package/2006/relationships}"
M = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"

def _rels(z, part):
    folder, name = posixpath.split(part)
    rels_path = posixpath.join(folder, "_rels", name + ".rels")
    if rels_path not in z.namelist():
        return {}
    root = ET.fromstring(z.read(rels_path))
    # targets may be relative ("../charts/chart1.xml") or absolute ("/xl/worksheets/sheet1.xml")
    return {r.get("Id"): posixpath.normpath(posixpath.join(folder, r.get("Target"))).lstrip("/") for r in root.iter(PR + "Relationship")}

def _text(el):
    return "".join(t.text or "" for t in el.iter(A + "t")) if el is not None else None

def read_charts(path):
    """Return one dict per chart: sheet, anchor cell, type, title, series and axes."""
    charts = []
    with zipfile.ZipFile(path) as z:
        book = ET.fromstring(z.read("xl/workbook.xml"))
        book_rels = _rels(z, "xl/workbook.xml")
        for sheet in book.iter(M + "sheet"):
            sheet_part = book_rels[sheet.get(R + "id")]
            for drawing in _rels(z, sheet_part).values():
                if "/drawings/" not in drawing:
                    continue
                d_rels = _rels(z, drawing)
                for anchor in ET.fromstring(z.read(drawing)):
                    frm = anchor.find(XDR + "from")
                    chart_ref = anchor.find(f".//{C}chart")
                    if chart_ref is None:
                        continue
                    col, row = int(frm.find(XDR + "col").text), int(frm.find(XDR + "row").text)
                    chart_xml = ET.fromstring(z.read(d_rels[chart_ref.get(R + "id")]))
                    plot = chart_xml.find(f"{C}chart/{C}plotArea")
                    kind = next(el.tag.replace(C, "") for el in plot if el.tag.endswith("Chart"))
                    series = [{"name": _formula(s.find(f"{C}tx")), "values": _formula(s.find(f"{C}val")),
                               "categories": _formula(s.find(f"{C}cat"))} for s in plot.iter(C + "ser")]
                    axes = {ax.tag.replace(C, ""): {"deleted": ax.find(f"{C}delete") is not None and ax.find(f"{C}delete").get("val") == "1",
                                                    "format": ax.find(f"{C}numFmt").get("formatCode") if ax.find(f"{C}numFmt") is not None else None}
                            for ax in plot if ax.tag in (C + "catAx", C + "valAx", C + "dateAx")}
                    charts.append({"sheet": sheet.get("name"), "anchor": _cell(col, row), "type": kind,
                                   "title": _text(chart_xml.find(f"{C}chart/{C}title")), "series": series, "axes": axes})
    return charts

def _formula(el):
    if el is None:
        return None
    f = el.find(f".//{C}f")
    return f.text if f is not None else None

def _cell(col, row):
    letters = ""
    col += 1
    while col:
        col, rem = divmod(col - 1, 26)
        letters = chr(65 + rem) + letters
    return f"{letters}{row + 1}"

The helper follows the same path Excel does. Relationship targets come in two styles — openpyxl writes absolute paths such as /xl/worksheets/sheet1.xml, Excel usually writes relative ones such as ../charts/chart1.xml — so each target is joined to the folder of the part that refers to it, normalised, and stripped of its leading slash, which handles both. The workbook part lists the sheets; each sheet's relationships point to its drawing; each drawing holds one anchor per chart or picture, with the cell the object starts in, and a relationship to the chart part; the chart part holds the type, the title and the series. For each series the useful facts are formulas: the cell that names it, the range of values and the range of categories. For each axis, whether it is deleted — the openpyxl default that hides axes in Excel — and its number format. The result is a plain list of dictionaries, which is exactly what a test wants to compare. Because it reads the XML rather than relying on a library's object model, it works on files written by openpyxl, XlsxWriter or Excel itself.

Finding the charts inside an .xlsx The workbook part lists the sheets. Each sheet's relationships lead to its drawing, the drawing has an anchor per chart with the cell it starts in, and each anchor's relationship leads to the chart part, which holds the type, title, series and axes. xl/workbook.xml sheets in order sheet rels → drawing part drawing anchors cell + chart link chart part type, title, series list of dicts ready to assert

Step 3 — Tests with explicit expectations

Python
import pytest
from chart_inspect import read_charts
from report import build_report

@pytest.fixture(scope="module")
def charts(tmp_path_factory):
    path = tmp_path_factory.mktemp("out") / "report.xlsx"
    build_report(path)
    return {c["title"]: c for c in read_charts(path)}

def test_both_charts_exist(charts):
    assert set(charts) == {"Revenue and cost", "Margin"}

def test_revenue_chart_plots_revenue_and_cost(charts):
    c = charts["Revenue and cost"]
    assert c["type"] == "barChart" and c["anchor"] == "F2"
    assert [s["name"] for s in c["series"]] == ["'Monthly'!B1", "'Monthly'!C1"]
    assert c["series"][0]["values"] == "'Monthly'!$B$2:$B$5"
    assert all(s["categories"] == "'Monthly'!$A$2:$A$5" for s in c["series"])

def test_axes_are_visible(charts):
    for c in charts.values():
        assert not any(ax["deleted"] for ax in c["axes"].values()), c["title"]

def test_margin_axis_is_percent(charts):
    assert charts["Margin"]["axes"]["valAx"]["format"] == "0%"
Bash
python -m pytest -q test_report_charts.py 2>&1 | tail -3

The fixture builds the report once into a temporary folder and indexes its charts by title. Each test then states one thing that must stay true: both charts exist; the revenue chart is a column chart at F2 whose two series are named from B1 and C1, plot B2:B5 and share the month categories; no axis is deleted; the margin axis is formatted as a percentage. These are exactly the properties that break silently. If someone inserts a column before Revenue, the series reference test fails with the new range in the message; if the delete = False lines are lost in a refactor, the axis test fails and names the chart. All four tests pass against the current report.

What the helper reads from the two charts The first chart is a bar chart at F2 titled Revenue and cost with two series from columns B and C and month categories from column A. The second is a line chart at F20 titled Margin with one series from column D and a percent-formatted value axis. Neither has a deleted axis. A B C D E 1 Title Type Anchor Series Axes 2 Revenue and cost barChart F2 B1 → B2:B5, C1 → C2:C5 visible 3 Margin lineChart F20 D1 → D2:D5 visible, value 0%

Step 4 — A snapshot for everything else

Python
import json
from pathlib import Path
from chart_inspect import read_charts
from report import build_report

build_report("report.xlsx")
current = read_charts("report.xlsx")
snapshot = Path("charts_snapshot.json")
if not snapshot.exists():
    snapshot.write_text(json.dumps(current, indent=2))           # first run: record it, review it, commit it
assert json.loads(snapshot.read_text()) == current, "chart definitions changed — review and update the snapshot"
print(json.dumps(current[0], indent=2)[:400])

Explicit assertions are best for the properties that matter most. For everything else — the exact set of series, every axis, every anchor — a snapshot is less work: record the helper's output as JSON once, review it, commit it next to the tests, and compare future runs against it. Any change to any chart then fails the test with both versions available to diff; if the change was intended, the snapshot is regenerated as part of the same commit. Because the helper returns plain data, the snapshot is readable and reviews well in a pull request, unlike a binary .xlsx. The same idea for whole workbooks is covered in Compare Excel Output to a Golden File in Tests.

Explicit assertions or a snapshot Explicit assertions check a few named properties, give clear failure messages and survive harmless changes. A snapshot checks every property of every chart with little effort, but fails on any change and has to be reviewed and updated deliberately. Explicit assertions a few properties that matter clear failure messages ignore harmless changes JSON snapshot every chart, every property little code to write fails on any change review when updating

Common pitfalls

  • Expecting openpyxl to load charts. It does not; read the chart XML from the package.
  • Testing only that a chart exists. Assert its series ranges; the wrong column is the common failure.
  • Ignoring deleted axes. openpyxl hides axes unless told otherwise; test for it.
  • Matching on file names like chart1.xml. Follow the relationships so tests survive reordering.
  • Snapshots nobody reviews. Read the first snapshot before committing it.

Conclusion

To test the charts a script writes, read them from the .xlsx package: follow the workbook's relationships to each sheet's drawing, each drawing anchor to its chart part, and pull out the chart type, title, series name, value and category formulas, axis settings and anchor cell. Assert the properties that matter in pytest, and keep a reviewed JSON snapshot for the rest, so a chart that starts plotting the wrong column or loses its axes fails a test instead of reaching a reader.

Frequently asked questions

Can openpyxl read charts from an existing workbook? No; charts are not loaded, and saving a loaded workbook drops them. Read the chart XML from the file directly.

Where are charts stored in an .xlsx file? In xl/charts/chartN.xml, linked from a drawing part that the sheet refers to; the drawing anchors give each chart's position.

What should a chart test check? At least the chart type, the series value and category ranges, the series names and that the axes are not deleted.

How do I snapshot-test Excel charts? Convert each chart's definition to a dictionary, save it as JSON once, and compare later runs with the stored file.

More on logging, testing & packaging

More on logging, testing & packaging →