Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Keep Charts and Images When Filling an Excel Template

openpyxl drops charts, images and pivot tables on re-save. Why it happens, which objects survive, and three ways to fill a template without losing its visuals.

You fill a template with openpyxl, save it, and the chart your colleague spent an afternoon on has vanished. Nothing errored. The cause is structural: openpyxl parses a workbook into an object model and writes a new file from that model, so anything it does not represent has nothing to write back. This guide explains exactly what survives, then gives three practical ways to fill a template without losing its visuals — ranked by how much control you have over the template itself. It belongs to Generating Excel Reports from Templates.

Why a re-save loses objects openpyxl parses the file into an object model and writes a new file from it, so any part of the original that the model does not represent is simply absent from the output. The output is rebuilt, not edited template.xlsx cells, styles chart, image, pivot openpyxl model cells, styles ✓ chart, pivot — not held output.xlsx cells, styles visuals gone Nothing errors — the objects simply never reach the writer

Prerequisites

Bash
pip install openpyxl pandas xlsxwriter

Establish what your template loses

Before choosing a strategy, measure. Fill nothing, save, and compare the parts inside the two zip files:

Python
"""Diff the internal parts of a template and its re-saved copy."""
import zipfile

from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
wb.save("resaved.xlsx")

def parts(path: str) -> set[str]:
    with zipfile.ZipFile(path) as zf:
        return {n for n in zf.namelist() if not n.startswith("docProps/")}

lost = parts("template.xlsx") - parts("resaved.xlsx")
for name in sorted(lost):
    print("lost:", name)
Text
lost: xl/charts/chart1.xml
lost: xl/drawings/drawing1.xml
lost: xl/pivotCache/pivotCacheDefinition1.xml

That list tells you exactly which strategy you need. A template that loses nothing can be filled directly, and many simple templates do survive intact.

Strategy 1: fill only the cells, let Excel redraw

The best outcome is a template whose chart points at a range that grows, so writing data is all your code has to do. Define the source as a dynamic named range in the template — using OFFSET/COUNTA or an Excel table — and the chart follows the data without any code touching the chart.

Python
from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
ws = wb["Data"]

for r, (region, revenue) in enumerate(rows, start=2):
    ws.cell(row=r, column=1, value=region)
    ws.cell(row=r, column=2, value=float(revenue))

wb.save("filled.xlsx")

This works only if the chart survives the round trip, so run the diff above first. Where it does survive — common for charts openpyxl can model — this is by far the cheapest option, and the template's owner keeps control of the visual.

Strategy 2: rebuild the chart after filling

If the chart does not survive, recreate it in code. You lose the designer's fine-tuning, but the output is reliable and reproducible:

Python
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

wb = load_workbook("template.xlsx")
ws = wb["Data"]

# … write the data rows as above …

chart = BarChart()
chart.type = "col"
chart.title = "Revenue by region"
chart.y_axis.title = "Revenue (GBP)"
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
cats = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.height, chart.width = 8, 15

ws.add_chart(chart, "E2")
wb.save("filled.xlsx")

Keep the chart-building code beside the template so the two stay in step, and treat the template as owning the layout and styling while the code owns the chart. The chart options are covered in Creating Charts in Excel with openpyxl.

Three strategies for filling a template with visuals Write only cells when the objects survive, rebuild the visuals in code when they do not, or drive a real application when the template is too elaborate to reproduce. Cheapest first — try them in this order 1. cells only dynamic ranges in the template needs objects to survive simplest by far 2. rebuild in code chart defined in Python template keeps layout loses hand tuning reproducible 3. drive an app xlwings or LibreOffice everything preserved heavy dependency last resort

Strategy 3: drive an application that understands the file

When the template is too elaborate to reproduce — a formatted dashboard with pivots, slicers and hand-placed shapes — let something that fully understands the format do the writing. xlwings edits through a real Excel:

Python
import xlwings as xw

with xw.App(visible=False, add_book=False) as app:
    app.display_alerts = False
    book = app.books.open("template.xlsx")
    try:
        sheet = book.sheets["Data"]
        sheet.range("A2").value = rows        # a list of lists
        book.api.RefreshAll()
        book.save("filled.xlsx")
    finally:
        book.close()

Everything is preserved because nothing was rebuilt. The cost is a Windows or macOS machine with Excel and an interactive session, which rules it out for most servers — the trade-offs are set out in Refresh Excel pivot tables and queries with Python.

Keep images by re-adding them

An image that does not survive is easy to restore, because it is just a file plus an anchor:

Python
from openpyxl.drawing.image import Image

logo = Image("assets/logo.png")
logo.width, logo.height = 180, 48        # pixels
ws.add_image(logo, "A1")

Keep the source image beside the template in version control rather than extracting it from the workbook each run — the extraction is fragile and the file is small. Placement details are in Add a logo image to an Excel report with openpyxl.

Consider building the whole workbook instead

Templates exist so a non-developer can own the design. When that is not actually true — when the template is a file someone made once and nobody maintains — building from code with xlsxwriter is more robust: nothing to preserve, nothing to lose, and the layout is in version control with the rest of the job. The reasonable rule is to keep a template when a business owner genuinely edits it, and to generate from code otherwise. The generation route is covered in Write a formatted Excel report with xlsxwriter.

Check the output before delivering it

Whichever strategy you choose, assert that the visuals are present rather than trusting they are:

Python
import zipfile

def assert_visuals(path: str, expect_charts: int = 1) -> None:
    with zipfile.ZipFile(path) as zf:
        charts = [n for n in zf.namelist() if n.startswith("xl/charts/chart")]
        drawings = [n for n in zf.namelist() if n.startswith("xl/drawings/drawing")]
    if len(charts) < expect_charts:
        raise ValueError(f"{path}: expected {expect_charts} chart(s), found {len(charts)}")
    print(f"{path}: {len(charts)} chart(s), {len(drawings)} drawing(s)")

assert_visuals("filled.xlsx", expect_charts=1)

Add it to the pre-delivery checks so a library upgrade that changes what survives is caught by the job rather than by the recipient — the same idea as Validate an Excel report before sending it.

Split the template into a data sheet and a presentation sheet

The structural fix that makes strategy 1 work reliably is to stop mixing the two concerns. Give the template a plain Data sheet that code writes to, and a Report sheet that holds every chart, formula and piece of formatting, referencing Data by range. Code then touches only cells on a sheet with nothing to lose.

Python
from openpyxl import load_workbook

wb = load_workbook("template.xlsx")
data = wb["Data"]

# clear last run's rows without touching anything else
if data.max_row > 1:
    data.delete_rows(2, data.max_row - 1)

for r, (region, revenue) in enumerate(rows, start=2):
    data.cell(row=r, column=1, value=region)
    data.cell(row=r, column=2, value=float(revenue))

wb.save("filled.xlsx")

delete_rows keeps the sheet clean between runs, so a month with fewer rows does not leave last month's tail behind the new data — a subtle bug that shows up as a chart with a flat, wrong final segment.

Separating the sheet code writes from the sheet people see Code writes plain values to a data sheet, while the presentation sheet holds the charts and formatting and references the data sheet by range. Nothing fragile lives where code writes Data sheet values only, written by code safe to clear and refill refs Report sheet charts, formulas, styling code never writes here owner edits this one

That separation also settles ownership: the business owner edits the report sheet freely, and the only contract between them and the code is the column layout of the data sheet.

Common pitfalls and gotchas

  • Assuming an error would have told you. The loss is silent; only a check catches it.
  • keep_vba as a fix. It preserves macros in .xlsm, not charts or pivots.
  • Renaming a sheet the chart references. That breaks the chart even when the object survives.
  • Writing over the template. Always write to a new path, so a bad run does not destroy the source.
  • A chart range that does not grow. Filling more rows than the template's fixed range leaves the extra data out of the chart.

Performance and scale notes

Strategies 1 and 2 are pure Python and run in milliseconds per report, which matters when generating one workbook per region in a loop. Strategy 3 costs an application start of seconds and serialises — one Excel instance at a time — so a hundred reports become a batch job rather than a loop. That difference is usually decisive: if the report set is large, invest in reproducing the template in code once rather than paying for an application launch on every run. Where a template must be filled per recipient, the loop pattern is in Generate one Excel report per region in a loop.

Conclusion

openpyxl rewrites the workbook rather than editing it, so anything outside its object model disappears on save. Diff the zip parts to learn what your template actually loses, then pick the cheapest strategy that works: write only cells when the visuals survive, rebuild charts in code when they do not, and drive Excel only when the template is genuinely too elaborate to reproduce. Whichever you choose, assert that the charts and drawings are present in the output before it goes out.

Frequently asked questions

Why does openpyxl delete my chart? openpyxl reads a workbook into its own object model and writes a new file from it. Anything it does not model — most charts it did not create, some images, pivot tables, slicers, form controls — has nothing to write back and disappears.

Which objects survive a round trip? Cell values, formulas, most styles, conditional formatting, merged cells, defined names and print settings generally survive. Charts, pivot caches, slicers, form controls and some drawings often do not.

What is the safest way to fill a template with a chart? Point the chart at a dynamic range and write only the data cells, then have Excel redraw the chart on open. Where a chart must be redrawn server-side, rebuild it with openpyxl after filling.

Does keep_vba help? It preserves macros in an .xlsm, which is a different problem. It does not preserve charts or pivot tables.

Can I avoid templates entirely? Often yes, and it is usually the more robust choice — build the whole workbook from code with xlsxwriter, so nothing depends on preserving objects a library cannot model.