Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

What openpyxl Loses When You Save an Existing Workbook

openpyxl rewrites the whole file when it saves, and features it does not model disappear — sparklines, shapes and text boxes, data-bar and validation extensions, macros, slicers. Tested list, a pre-save risk scan, and safer alternatives.
Errors & fixesWriting Excel filesopenpyxl

openpyxl does not edit an .xlsx file in place. load_workbook parses the parts of the package it understands into Python objects, and save writes a brand-new package from those objects. Anything the library does not model is not in the objects, so it is not in the new file. Usually nothing important is lost. Occasionally a designed dashboard comes back without its sparklines, its text boxes or its macros — and the only warning was a line on stderr nobody read. This guide, part of Using openpyxl for Excel File Manipulation, lists what survives and what does not, based on an actual round trip, and shows how to scan a workbook for risk before you overwrite it.

Why an openpyxl save can drop features The original xlsx package contains parts openpyxl understands and parts it does not. load_workbook builds Python objects only for the understood parts, and save writes a new package from those objects, so the unknown parts are not written back. original.xlsx cells, styles, charts, extensions, shapes load_workbook keeps what it models Python objects unknown parts gone save a brand-new package openpyxl never patches the file — it re-creates it from what it understood

Prerequisites

Bash
pip install openpyxl xlsxwriter

To see the losses for yourself, build a workbook full of features with xlsxwriter — which writes them all — then round-trip it through openpyxl:

Python
import struct
import zlib

import xlsxwriter

def tiny_png(path: str) -> None:
    raw = b"".join(b"\x00" + b"\x43\x38\xca" * 4 for _ in range(4))
    def chunk(tag, data):
        return struct.pack(">I", len(data)) + tag + data + struct.pack(">I", zlib.crc32(tag + data) & 0xFFFFFFFF)
    header = struct.pack(">IIBBBBB", 4, 4, 8, 2, 0, 0, 0)
    with open(path, "wb") as fh:
        fh.write(b"\x89PNG\r\n\x1a\n" + chunk(b"IHDR", header) + chunk(b"IDAT", zlib.compress(raw)) + chunk(b"IEND", b""))

tiny_png("logo.png")
book = xlsxwriter.Workbook("dashboard.xlsx")
ws = book.add_worksheet("Data")
ws.write_row(0, 0, ["name", "a", "b"])
for i in range(1, 6):
    ws.write_row(i, 0, [f"r{i}", i * 10, i * 5])
chart = book.add_chart({"type": "column"})
chart.add_series({"values": "=Data!$B$2:$B$6"})
ws.insert_chart("E2", chart)
ws.insert_image("E18", "logo.png")
ws.add_sparkline("D2", {"range": "Data!B2:C2"})
ws.conditional_format("B2:B6", {"type": "data_bar", "bar_solid": True})
ws.data_validation("A8", {"validate": "list", "source": ["x", "y"]})
ws.write_comment("A1", "a note")
ws.add_table("H10:I12", {"columns": [{"header": "k"}, {"header": "v"}]})
ws.insert_textbox("K2", "Figures are provisional")
book.close()

Step 1 — Round-trip and watch the warnings

openpyxl's only notice that it is about to drop something is a Python warning at load time. Capture them — in a scheduled job they would otherwise vanish into a log nobody reads:

Python
import warnings
from openpyxl import load_workbook

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    wb = load_workbook("dashboard.xlsx")
for w in caught:
    print(w.message)
wb.save("dashboard_resaved.xlsx")

For this workbook openpyxl reports three: an unknown extension, a conditional formatting extension (the solid-fill option on the data bar) and a sparkline group extension. Each of those is a feature that will be missing from dashboard_resaved.xlsx.

Step 2 — Compare what went in with what came out

Warnings cover extensions, not everything. The objective check is to compare the two packages: an .xlsx file is a zip archive, so list its parts and look inside the worksheet and drawing XML for the features you care about.

Python
import zipfile

def features(path: str) -> dict:
    z = zipfile.ZipFile(path)
    sheet = z.read("xl/worksheets/sheet1.xml").decode()
    drawings = "".join(z.read(n).decode() for n in z.namelist() if n.startswith("xl/drawings/drawing"))
    return {
        "chart": any(n.startswith("xl/charts/") for n in z.namelist()),
        "image": any(n.startswith("xl/media/") for n in z.namelist()),
        "sparkline": "sparklineGroup" in sheet,
        "data bar options": "x14:dataBar" in sheet,
        "text box": "<xdr:sp" in drawings,
        "table": any(n.startswith("xl/tables/") for n in z.namelist()),
        "comment": any("comment" in n for n in z.namelist()),
        "validation": "dataValidation" in sheet,
    }

before, after = features("dashboard.xlsx"), features("dashboard_resaved.xlsx")
for name in before:
    print(f"{name:18} {before[name]!s:6} -> {after[name]}")
What survived an openpyxl load and save in this test Charts, images, Excel tables, comments, data validation, hyperlinks, frozen panes and column widths survived. Sparklines, data bar extension options such as solid fills, and text box shapes were lost. Charts were rewritten, so advanced chart formatting can be simplified. Survives the round trip Cell values and styles Charts (rewritten by openpyxl) Images Excel tables Comments and data validation Sparklines Data bar extension options Text boxes and shapes VBA without keep_vba

The chart survives, but openpyxl rebuilt it from its own chart model — the chart part shrank from 1,624 to 1,372 bytes in this test — so styling it does not model, such as some custom colours or label layouts on a chart drawn in Excel, can be simplified. Treat "partial" as "check it by eye". Macros are covered separately in Work with Macro-Enabled .xlsm Files in openpyxl: without keep_vba=True the VBA project is discarded. Slicers, timelines, form controls and Power Pivot data models belong to the same category as shapes — features openpyxl does not model — and should be assumed lost.

Step 3 — Scan a workbook before you overwrite it

Knowing the list is useful; enforcing it is better. Before a job saves over a workbook it did not create, scan the package for parts and markup openpyxl is known to drop, and refuse to overwrite if any are present:

Python
RISKY_MARKERS = {
    "sparklines": ("xl/worksheets/", "sparklineGroup"),
    "extension formats": ("xl/worksheets/", "<x14:"),
    "shapes or text boxes": ("xl/drawings/", "<xdr:sp"),
}
RISKY_PARTS = {
    "VBA project": "xl/vbaProject.bin",
    "slicers": "xl/slicers/",
    "timelines": "xl/timelines/",
    "data model": "xl/model/",
    "form controls": "xl/ctrlProps/",
}

def at_risk(path: str) -> list[str]:
    found = []
    with zipfile.ZipFile(path) as z:
        names = z.namelist()
        for label, prefix in RISKY_PARTS.items():
            if any(n.startswith(prefix) for n in names):
                found.append(label)
        for label, (folder, marker) in RISKY_MARKERS.items():
            if any(n.startswith(folder) and n.endswith(".xml") and marker in z.read(n).decode("utf-8", "ignore")
                   for n in names):
                found.append(label)
    return found

print(at_risk("dashboard.xlsx"))            # ['sparklines', 'extension formats', 'shapes or text boxes']
print(at_risk("dashboard_resaved.xlsx"))    # []

A job can call at_risk on its input and either stop with a clear message or switch to one of the safer strategies below. The empty result for the re-saved file is the loss made visible: the features are simply no longer there to detect.

Step 4 — Safer ways to update a designed workbook

How to update a workbook that contains features openpyxl would drop If only data changes, write it to a separate data workbook or sheet that the designed workbook references. If Excel is available, let Excel open and save the file through xlwings or COM. If neither is possible, save a copy with openpyxl and keep the original, reviewing what changed. The workbook has features openpyxl drops. What changes? only the data separate data file designed file reads from it Excel is available let Excel save it xlwings or COM neither save a copy never overwrite the original

The cleanest pattern separates data from design. The designed workbook — sparklines, shapes, slicers and all — is edited only in Excel, and it reads its numbers from a plain data sheet or a linked data workbook that your script regenerates in full every run. openpyxl or xlsxwriter never touches the designed file, so nothing can be lost. The template-filling workflow in Generating Excel Reports from Templates follows the same principle.

When the designed file itself must change and Excel is installed, let Excel do the saving: xlwings or pywin32 open the workbook in the real application, write values, and save through Excel, which preserves everything Excel understands. See Read and Write a Live Excel Workbook with xlwings.

If neither is possible, never overwrite the original: save to a new file name, run at_risk on the input, and put its findings in the job's log so the loss is at least recorded.

Common pitfalls

  • Treating a clean load as a safe save. Shapes and some other features are dropped without any warning at all. Scan the package rather than relying on warnings.
  • Suppressing warnings globally. warnings.filterwarnings("ignore") at the top of a script hides exactly the messages this guide depends on.
  • Testing with your own files only. Workbooks from colleagues accumulate features over time. A job that has saved a file safely for months can lose a sparkline the week someone adds one.
  • Assuming read-only is harmless because it cannot save. It cannot, which is the point: for jobs that only read, read_only=True guarantees nothing is lost because nothing is written.

Performance and scale notes

The risk scan opens the zip and reads only the worksheet and drawing XML, which is fast even for large workbooks — far faster than load_workbook itself. Run it on every input, not just on the ones you expect to be complex. The round-trip cost is the other consideration: load and save parse and rewrite every cell, so an openpyxl edit of a large workbook takes seconds where writing a small separate data file takes milliseconds — another reason to keep bulk data and design apart.

Conclusion

An openpyxl save is a rewrite, not an edit. Cell values, styles, tables, comments, validation, images and charts come through; sparklines, extension formats, shapes and text boxes do not, charts can lose formatting, and macros need keep_vba=True. Capture the load-time warnings, scan the package for features openpyxl drops before overwriting anything, and keep designed workbooks away from the save path by writing data into a separate file or letting Excel itself do the saving.

Frequently asked questions

Does openpyxl keep charts and images when it saves an existing file? In current versions it reads existing charts and images and writes them back, so they usually survive. Charts are rebuilt from openpyxl's own model, though, so formatting it does not support can be simplified on the way through.

What are the warnings about extensions not being supported? Excel stores newer features — sparklines, data-bar options, some data validation and conditional formatting — in extension blocks. openpyxl warns that it does not support them and drops them when saving. The warning is the only notice you get.

How do I keep VBA macros in an .xlsm file? Load with keep_vba=True and save with the .xlsm extension. Without keep_vba, the macro project is discarded.

What should I use if the workbook contains features openpyxl drops? Write your data into a separate, plain workbook or sheet and let the designed workbook pull it in, or drive Excel itself with xlwings or COM so Excel does the saving. For values only, some tools patch the XML directly, but that is fragile.

More on errors & fixes

More on errors & fixes →