Inspect the XML Inside an .xlsx File with Python

Most of the time openpyxl and pandas hide the file format completely, which is the point of using them. But some questions can only be answered by looking at what is actually in the file: why Excel says a generated workbook needs repairing, whether a formula has a cached result, what number format a cell really has, which tool produced the file, or what changed between two versions that look identical on screen. An .xlsx file is a ZIP archive of XML documents — the Office Open XML format — and the standard library can open it, list its parts and parse any of them. This page is a short field guide to doing that. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

Nothing to install: zipfile and xml.etree.ElementTree are part of Python. The examples inspect orders.xlsx, a small workbook with text, numbers, formulas, booleans, dates and two custom number formats, written by XlsxWriter — which, like Excel, stores text in a shared-strings table.

Step 1 — List the parts

import zipfile

z = zipfile.ZipFile("orders.xlsx")
for info in z.infolist():
    print(f"{info.filename:32} {info.file_size:>6} bytes")

The listing is the map of the package. [Content_Types].xml declares what each part is; _rels/.rels points at the workbook; xl/workbook.xml lists the sheets; xl/worksheets/sheet1.xml and sheet2.xml hold the cells; xl/sharedStrings.xml holds text values (if the writer used it); xl/styles.xml holds fonts, fills, borders and number formats; docProps/app.xml and core.xml hold metadata such as the application name and author; the listing above has a sharedStrings.xml because XlsxWriter wrote the file. Anything else — xl/drawings/, xl/charts/, xl/media/, xl/vbaProject.bin — means charts, images or macros.

Step 2 — Find the file behind a sheet name

Sheet names live in workbook.xml; the file that holds each sheet is found through a relationship ID. Do not assume sheet1.xml is the first tab — after sheets are reordered or deleted, the numbering no longer matches:

import xml.etree.ElementTree as ET

NS = {
    "m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
    "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
    "rel": "http://schemas.openxmlformats.org/package/2006/relationships",
}

def sheet_parts(z):
    wb = ET.fromstring(z.read("xl/workbook.xml"))
    rels = ET.fromstring(z.read("xl/_rels/workbook.xml.rels"))
    targets = {r.get("Id"): r.get("Target") for r in rels.findall("rel:Relationship", NS)}
    out = {}
    for s in wb.find("m:sheets", NS):
        target = targets[s.get(f"{{{NS['r']}}}id")].lstrip("/")
        out[s.get("name")] = target if target.startswith("xl/") else "xl/" + target
    return out

parts = sheet_parts(z)
print(parts)

Every element in these files lives in an XML namespace, so ElementTree searches need the prefixes in NS. The relationship target is usually relative to xl/ (worksheets/sheet1.xml), but some writers use absolute paths starting with /xl/; the helper handles both. This is the same lookup openpyxl performs, and a file where it fails is one where openpyxl reports "0 worksheets found" — see Fix "Worksheet index 0 is invalid, 0 worksheets found".

Step 3 — Read cells and their types

sheet = ET.fromstring(z.read(parts["Orders"]))
for c in sheet.iter(f"{{{NS['m']}}}c"):
    f, v = c.find("m:f", NS), c.find("m:v", NS)
    print(f"{c.get('r'):4} t={c.get('t', 'n'):4} s={c.get('s', '0'):2} "
          f"f={(f.text if f is not None else ''):8} v={v.text if v is not None else ''}")

Each <c> element is one cell. r is its address, s an index into the styles, and t the type: n (the default when absent) for numbers, s for an index into the shared-strings table, str for a formula's text result, inlineStr for text stored in the cell itself, b for booleans (1/0) and e for errors such as #DIV/0!. A <f> child holds a formula and <v> its cached result. The output shows the things openpyxl smooths over: the header and product names are t="s" with small integers as values (indexes into the string table), dates are plain numbers (46266 is 1 September 2026), TRUE is t="b" with value 1, and the formulas in column D carry a cached <v> of 0 — XlsxWriter's placeholder, not the real product.

Writers differ here, and the XML is where you see it. The same sheet saved by openpyxl stores text as t="inlineStr" inside each cell, has no sharedStrings.xml at all, and writes formulas with an empty <v>:

from openpyxl import load_workbook

load_workbook("orders.xlsx").save("orders_openpyxl.xlsx")
zo = zipfile.ZipFile("orders_openpyxl.xlsx")
print("sharedStrings.xml present:", "xl/sharedStrings.xml" in zo.namelist())
cells = {c.get("r"): c for c in ET.fromstring(zo.read("xl/worksheets/sheet1.xml")).iter(f"{{{NS['m']}}}c")}
print("A2:", cells["A2"].get("t"), "| D2 cached value:", repr(cells["D2"].find("m:v", NS).text))

That empty <v> is why data_only=True and pandas read formula cells from openpyxl-written files as None or NaN, while XlsxWriter files read as 0.

Step 4 — Resolve shared strings and number formats

Text cells with t="s" store an index; the text is in sharedStrings.xml. Formats are two hops away: the cell's s indexes the cellXfs list in styles.xml, whose numFmtId either names a built-in format or one defined in numFmts:

def shared_strings(z):
    if "xl/sharedStrings.xml" not in z.namelist():
        return []
    root = ET.fromstring(z.read("xl/sharedStrings.xml"))
    return ["".join(t.text or "" for t in si.iter(f"{{{NS['m']}}}t")) for si in root.findall("m:si", NS)]

def number_formats(z):
    styles = ET.fromstring(z.read("xl/styles.xml"))
    custom = {n.get("numFmtId"): n.get("formatCode") for n in styles.iterfind("m:numFmts/m:numFmt", NS)}
    builtin = {"0": "General", "14": "mm-dd-yy", "22": "m/d/yy h:mm"}
    return [custom.get(x.get("numFmtId"), builtin.get(x.get("numFmtId"), "built-in " + x.get("numFmtId")))
            for x in styles.iterfind("m:cellXfs/m:xf", NS)]

strings, fmts = shared_strings(z), number_formats(z)
print("shared strings:", strings)
for c in sheet.iter(f"{{{NS['m']}}}c"):
    if c.get("r") in ("A2", "C2", "F2"):
        v = c.find("m:v", NS).text
        shown = strings[int(v)] if c.get("t") == "s" else v
        print(c.get("r"), repr(shown), "format:", fmts[int(c.get("s", 0))])

Joining every <t> inside an <si> handles rich-text strings, which split one cell's text into several runs. The number-format lookup shows #,##0.00 [$EUR] on the price and dd mmm yyyy on the date — exactly what Read Cell Values as Displayed in Excel needs to reproduce the on-screen text.

Step 5 — Pretty-print a part

Writers save XML on one long line. Indenting it makes it readable in a terminal or a diff:

import xml.dom.minidom

def pretty(z, part, limit=900):
    return xml.dom.minidom.parseString(z.read(part)).toprettyxml(indent="  ")[:limit]

print(pretty(z, "xl/worksheets/sheet1.xml", 700))
for f in ("orders.xlsx", "orders_openpyxl.xlsx"):
    app = ET.fromstring(zipfile.ZipFile(f).read("docProps/app.xml"))
    print(f, "->", app.find("{http://schemas.openxmlformats.org/officeDocument/2006/extended-properties}Application").text)

docProps/app.xml has an Application element, but treat it as a hint rather than proof: XlsxWriter writes Microsoft Excel there, so its files claim to come from Excel, while openpyxl writes Microsoft Excel Compatible / Openpyxl 3.1.5. The structure of the parts is a better fingerprint — inline strings and empty cached values point to openpyxl, a shared-string table with cached zeros to XlsxWriter, and a calcChain.xml part to a workbook that Excel itself has calculated and saved. For large sheet parts, print a slice; a sheet with 100,000 rows is many megabytes of XML.

Step 6 — Diff two workbooks part by part

When a tool "changes nothing" but the file looks different, compare the packages. The re-save from Step 3 is a good example — same data, different writer:

import difflib

def diff_packages(path_a, path_b):
    a, b = zipfile.ZipFile(path_a), zipfile.ZipFile(path_b)
    print("only in", path_a, sorted(set(a.namelist()) - set(b.namelist())))
    print("only in", path_b, sorted(set(b.namelist()) - set(a.namelist())))
    for part in sorted(set(a.namelist()) & set(b.namelist())):
        if part.endswith(".xml") and a.read(part) != b.read(part):
            left, right = pretty(a, part, 10**7).splitlines(), pretty(b, part, 10**7).splitlines()
            changed = sum(1 for l in difflib.unified_diff(left, right, lineterm="", n=0)
                          if l[:1] in "+-" and l[:3] not in ("+++", "---"))
            print(f"  {part:28} {changed} changed lines")

diff_packages("orders.xlsx", "orders_openpyxl.xlsx")

The report lists the shared-strings part as present only in the original and counts changed lines per part. Between two different writers almost every part differs, because each orders attributes and styles its own way; the useful comparison is between two saves by the same tool — yesterday's and today's report, or a workbook before and after one script step — where only the parts that really changed show up. Print the diff lines themselves for those parts. On a workbook saved by Excel, the same comparison after an openpyxl round trip shows exactly what openpyxl drops or rewrites, the evidence behind What openpyxl Loses When Saving an Existing Workbook. For diffs in version control, the same idea powers Diff Excel Files in Git with a textconv Driver.

Common pitfalls

• Forgetting namespaces. root.find("sheets") finds nothing; use the {namespace}tag form or a prefix map.
• Assuming sheet1.xml is the first tab. Resolve through workbook.xml.rels.
• Assuming shared strings exist. Some writers store text inline (t="inlineStr") and have no sharedStrings.xml.
• Editing the XML by hand. Inspecting is safe; changing a part means keeping indexes, relationships and content types consistent — use a library for edits.
• Huge parts. ET.fromstring loads the whole part; for very large sheets use ET.iterparse on z.open(part) and clear elements as you go.

Conclusion

Open the .xlsx with zipfile, list its parts, and follow workbook.xml and its relationships to the sheet you want. Read each <c> element's t, s, <f> and <v> to see types, styles, formulas and cached results; resolve text through sharedStrings.xml and formats through styles.xml; pretty-print parts to read them and diff two packages part by part to see what a tool changed. It is the quickest way to answer "what is actually in this file?"

Frequently asked questions

How do I see the XML inside an Excel file? Open it with zipfile.ZipFile("file.xlsx"), list the parts with namelist(), and read any part, such as xl/worksheets/sheet1.xml, with z.read(). Parse it with xml.etree.ElementTree or pretty-print it with xml.dom.minidom.

What does t="s" mean in an xlsx cell? The cell's value is an index into the shared strings table in xl/sharedStrings.xml. Other types are t="str" for a formula's text result, t="inlineStr" for inline text, t="b" for booleans and t="e" for errors; numbers and dates have no t attribute or t="n".

How do I find which XML file holds a given sheet? Look up the sheet's r:id in xl/workbook.xml, then find the relationship with that Id in xl/_rels/workbook.xml.rels; its Target is the worksheet part. The file numbering does not necessarily follow the tab order.

How can I tell which program last saved an Excel file? Read the Application element in docProps/app.xml, but treat it as a hint: XlsxWriter, for example, writes Microsoft Excel there. The structure is more telling — inline strings suggest openpyxl, a shared-string table with cached zero formula results suggests XlsxWriter, and a calcChain.xml part suggests Excel itself.

Related

• Up one level: Handling Excel File Formats and Conversions — every format and how to move between them.
• Fix "Excel Found Unreadable Content" After Writing with Python — where inspecting the XML pays off.
• How openpyxl Stores Python Values in Cells — the library view of the same cell types.
• Read Excel Files Inside a ZIP Archive with Python — when the workbook itself is inside another ZIP.