How to Confirm XLSX Metadata Cleanup Using Python

Sharing Excel workbooks often means dealing with hidden metadata that can compromise privacy or compliance. Even after using a cleanup tool, it's not always obvious if sensitive fields are gone or if the workbook's structure stayed the same. This guide shows how to use Python to check that metadata has been removed from an XLSX file and that the rest of the workbook hasn't changed.

Understanding XLSX Metadata and File Structure

An XLSX file works as a ZIP archive with several XML files inside. Important metadata fields like author, last editor, and company appear in docProps/core.xml and docProps/app.xml. Custom properties, such as a ProjectCode, are stored in docProps/custom.xml. The workbook's actual content—sheets, formulas, and hidden sheets—lives under the xl/ directory.

Microsoft's documentation points out that Open XML workbooks can hold document-level custom properties along with regular workbook content. The Excel.DocumentProperties.custom property holds user-defined key-value pairs, like a project ID, which are kept separate from worksheets and formulas. These custom properties differ from custom XML parts, which can also store structured XML data in an .xlsx package and stay with the file even if no add-in is present. Because of this, a metadata-cleanup check that looks only at visible workbook cells or standard author fields might miss some data stores tied to the document. Microsoft Learn — Office Add-ins

Before running any checks, make a copy of your original workbook and a cleaned version. The script here compares these two files to see if the expected metadata fields are gone and if the rest of the workbook is unchanged.

Python Script for Metadata Verification

The script uses only Python's standard library. It reads both the original and cleaned XLSX files, pulls out the relevant property XML files, and calculates SHA-256 hashes for all other parts of the archive. This method checks that only the allowed metadata fields changed, while everything else in the workbook stays byte-for-byte the same.

Here's the core logic of the script:

import argparse
import hashlib
import json
from collections import Counter
from xml.etree import ElementTree as ET
from zipfile import BadZipFile, ZipFile

PROPERTY_PARTS = {"docProps/core.xml", "docProps/app.xml", "docProps/custom.xml"}
DC = "http://purl.org/dc/elements/1.1/"
CP = "http://schemas.openxmlformats.org/package/2006/metadata/core-properties"
APP = "http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"
CUSTOM = "http://schemas.openxmlformats.org/officeDocument/2006/custom-properties"
ROOTS = {
    "docProps/core.xml": f"{{{CP}}}coreProperties",
    "docProps/app.xml": f"{{{APP}}}Properties",
    "docProps/custom.xml": f"{{{CUSTOM}}}Properties",
}

def xml_part(archive, path):
    if path not in archive.namelist():
        return None
    root = ET.fromstring(archive.read(path))
    if root.tag != ROOTS[path]:
        raise ValueError("Unexpected property XML root: " + path)
    return root

def text_at(root, name):
    node = None if root is None else root.find(name)
    return "" if node is None else "".join(node.itertext()).strip()

def snapshot(path):
    with ZipFile(path) as archive:
        names = [item.filename for item in archive.infolist() if not item.is_dir()]
        if any(count > 1 for count in Counter(names).values()):
            raise ValueError("Duplicate ZIP member names")
        if "xl/workbook.xml" not in names:
            raise ValueError("Missing xl/workbook.xml")
        core = xml_part(archive, "docProps/core.xml")
        app = xml_part(archive, "docProps/app.xml")
        custom = xml_part(archive, "docProps/custom.xml")
        project_codes = [] if custom is None else [
            "".join(node.itertext()).strip() for node in custom.findall(f"{{{CUSTOM}}}property")
            if node.get("name") == "ProjectCode"
        ]
        properties = {
            "creator": text_at(core, f"{{{DC}}}creator"),
            "lastModifiedBy": text_at(core, f"{{{CP}}}lastModifiedBy"),
            "company": text_at(app, f"{{{APP}}}Company"),
            "ProjectCode": project_codes,
        }
        other_parts = {name: hashlib.sha256(archive.read(name)).hexdigest() for name in names if name not in PROPERTY_PARTS}
        return properties, other_parts

def compare(before_path, after_path):
    before, old_parts = snapshot(before_path)
    after, new_parts = snapshot(after_path)
    changed = sorted(
        name for name in old_parts.keys() | new_parts.keys() if old_parts.get(name) != new_parts.get(name)
    )
    remaining = sorted(key for key, value in after.items() if value)
    print("Before properties:", json.dumps(before, ensure_ascii=False))
    print("After properties:", json.dumps(after, ensure_ascii=False))
    print("Remaining checked properties:", remaining)
    print("Changed/added/removed other parts:", changed)
    print("Other parts in output:", len(new_parts))
    if remaining or changed:
        print("REVIEW: one or more checks failed.")
        return 1
    print("OK: checked properties empty/absent; other part hashes match.")
    return 0

def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("before")
    parser.add_argument("after")
    args = parser.parse_args()
    try:
        return compare(args.before, args.after)
    except (BadZipFile, ET.ParseError, OSError, RuntimeError, ValueError) as error:
        print("ERROR:", error)
        return 2

if __name__ == "__main__":
    raise SystemExit(main())

To run the script, use the command line with the original and cleaned workbook files as arguments:

python3 verify_xlsx_cleanup.py excel-metadata-demo.xlsx excel-metadata-demo-clean.xlsx

Interpreting the Results

After running the script, you'll get a summary of the properties before and after cleanup, a list of any remaining metadata, and a report on whether any other workbook parts changed. If all targeted metadata fields are empty or missing and the rest of the file matches, the script confirms the cleanup worked. If any properties remain or unexpected changes show up, the script flags them for review.

This method helps confirm that only the intended metadata fields were removed, while workbook content, formulas, and hidden sheets stay the same. For more on Python code verification and template-based workflows, see the earlier breakdown.

Best Practices for Sharing Cleaned Workbooks

Always check for sensitive information in cells, comments, hidden sheets, and external connections before sharing a cleaned workbook. While this script checks metadata in the main property files, it doesn't look at cell content or embedded objects. For a broader workflow on Excel metadata removal, the AI MetaClean project offers more documentation and guidance. Their Excel metadata cleanup guide covers built-in inspection steps and workbook sharing practices.

Microsoft's Office Add-ins documentation notes that the Open XML .xlsx format can hold custom XML data embedded in the workbook package. This data stays with the file and doesn't depend on the add-in that created it, so it's relevant for privacy review and forensic comparison. Excel exposes these embedded data stores through a CustomXmlPartCollection, which gives access to XML strings and identifiers. A verification workflow should separate document properties from custom XML parts, since clearing only the standard properties files doesn't remove all possible identifying data. Microsoft Learn — Office Add-ins

Custom XML can hold structured information that's not shown as regular worksheet content. Microsoft explains that custom XML parts are available through workbook APIs and can be used when data needs structure or must stay accessible across add-in sessions. Worksheet-level custom properties may also exist and can reuse the same key across different worksheets, creating another spot a document-level-only audit could miss. So, byte-level verification of selected metadata files is helpful but should be paired with an inventory of custom XML parts, worksheet custom properties, relationships, and other package components.

Microsoft also documents that settings created through the Office Settings object are stored in the spreadsheet and remain available to the add-in that created them. Because of this, a cleanup claim should define its scope clearly—such as "standard document properties removed"—unless the verification process checks the entire package and confirms that no prohibited data-bearing parts remain. The documentation shows that Excel workbooks can expose document properties, custom properties, custom XML parts, and worksheet-level custom properties through separate interfaces, setting a broader verification baseline than just checking author, last-editor, company, or custom-property XML files.

Comparing hashes of unchanged ZIP members can show that workbook components outside an allowed metadata set didn't change. This evidence is strongest when the verifier also checks ZIP member names, duplicate entries, compression, XML relationships, and package content types, since an archive can stay valid while gaining a new data-bearing part. A solid audit should report both "allowed differences" and "unexpected additions, deletions, or modifications," instead of just saying the workbook opens without errors.