inspect_workbook.py

#!/usr/bin/env python3
"""Inspect an .xlsx/.xlsm workbook without Excel or third-party libraries.

Reports each sheet's visibility, used range, cell/formula/error counts, frozen
panes, merged ranges, Excel tables, data validations, conditional formats and
autofilters, plus workbook-level defined names, external links and macros.
Use it to understand a workbook before answering questions about it, or to
spot structural problems (hidden sheets, broken #REF! names, merged cells in
data ranges, external links).

Usage:
    python inspect_workbook.py report.xlsx
    python inspect_workbook.py report.xlsx --sheet Data --sample 5    # also print the first 5 rows
    python inspect_workbook.py report.xlsx --json

Reads the Office Open XML parts with zipfile/ElementTree. Legacy .xls files
are not supported (save as .xlsx first).
"""

import argparse
import json
import posixpath
import re
import sys
import zipfile
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",
}
R_ID = f"{{{NS['r']}}}id"


def read_xml(z, path):
    try:
        return ET.fromstring(z.read(path))
    except KeyError:
        return None


def rels(z, part):
    folder, name = posixpath.split(part)
    root = read_xml(z, posixpath.join(folder, "_rels", name + ".rels"))
    out = {}
    if root is None:
        return out
    for r in root.findall("rel:Relationship", NS):
        target = r.get("Target")
        if r.get("TargetMode") != "External":
            target = posixpath.normpath(posixpath.join(folder, target)) if not target.startswith("/") else target.lstrip("/")
        out[r.get("Id")] = {"target": target, "type": r.get("Type", "").rsplit("/", 1)[-1]}
    return out


def col_to_num(col):
    n = 0
    for ch in col:
        n = n * 26 + ord(ch) - 64
    return n


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


def cell_value(c, strings):
    t = c.get("t")
    v = c.find("m:v", NS)
    if t == "s" and v is not None:
        return strings[int(v.text)]
    if t == "inlineStr":
        return "".join(x.text or "" for x in c.iter(f"{{{NS['m']}}}t"))
    if t == "b" and v is not None:
        return v.text == "1"
    return v.text if v is not None else None


def inspect(path, sample_sheet=None, sample_rows=0):
    with zipfile.ZipFile(path) as z:
        names = set(z.namelist())
        wb = read_xml(z, "xl/workbook.xml")
        if wb is None:
            raise ValueError("not an Excel workbook (xl/workbook.xml missing)")
        wb_rels = rels(z, "xl/workbook.xml")
        strings = shared_strings(z)

        defined = []
        for dn in wb.findall("m:definedNames/m:definedName", NS):
            ref = dn.text or ""
            defined.append({"name": dn.get("name"), "refers_to": ref, "hidden": dn.get("hidden") == "1",
                            "broken": "#REF!" in ref})

        sheets = []
        for s in wb.findall("m:sheets/m:sheet", NS):
            target = wb_rels.get(s.get(R_ID), {}).get("target")
            info = {"name": s.get("name"), "state": s.get("state", "visible"), "kind": "worksheet"}
            if not target or target not in names or "worksheets" not in target:
                info["kind"] = "chartsheet" if target and "chartsheets" in target else "other"
                sheets.append(info)
                continue
            root = read_xml(z, target)
            dim = root.find("m:dimension", NS)
            cells = root.findall(".//m:sheetData/m:row/m:c", NS)
            pane = root.find(".//m:sheetView/m:pane", NS)
            sheet_rels = rels(z, target)
            tables = []
            for tp in root.findall("m:tableParts/m:tablePart", NS):
                tpath = sheet_rels.get(tp.get(R_ID), {}).get("target")
                troot = read_xml(z, tpath) if tpath else None
                if troot is not None:
                    tables.append({"name": troot.get("displayName") or troot.get("name"), "ref": troot.get("ref"),
                                   "columns": [c.get("name") for c in troot.findall("m:tableColumns/m:tableColumn", NS)]})
            info.update({
                "used_range": dim.get("ref") if dim is not None else None,
                "cells": len(cells),
                "formulas": sum(c.find("m:f", NS) is not None for c in cells),
                "errors": sum(c.get("t") == "e" for c in cells),
                "frozen": pane.get("topLeftCell") if pane is not None and pane.get("state") in ("frozen", "frozenSplit") else None,
                "merged": [m.get("ref") for m in root.findall("m:mergeCells/m:mergeCell", NS)],
                "tables": tables,
                "data_validations": len(root.findall("m:dataValidations/m:dataValidation", NS)),
                "conditional_formats": len(root.findall("m:conditionalFormatting", NS)),
                "autofilter": (root.find("m:autoFilter", NS).get("ref") if root.find("m:autoFilter", NS) is not None else None),
                "protected": root.find("m:sheetProtection", NS) is not None,
            })
            if sample_rows and (sample_sheet in (None, s.get("name"))):
                rows = []
                for row in root.findall("m:sheetData/m:row", NS)[:sample_rows]:
                    vals = {}
                    for c in row.findall("m:c", NS):
                        col = re.match(r"[A-Z]+", c.get("r", "A")).group()
                        f = c.find("m:f", NS)
                        vals[col_to_num(col)] = f"={f.text}" if f is not None and f.text else cell_value(c, strings)
                    width = max(vals, default=0)
                    rows.append([vals.get(i) for i in range(1, width + 1)])
                info["sample"] = rows
            sheets.append(info)

        return {
            "file": path,
            "macros": "xl/vbaProject.bin" in names,
            "external_links": sorted(n for n in names if n.startswith("xl/externalLinks/") and n.endswith(".xml")),
            "defined_names": defined,
            "sheets": sheets,
        }


def render(r):
    print(f"{r['file']}: {len(r['sheets'])} sheet(s)"
          + (", contains macros" if r["macros"] else "")
          + (f", {len(r['external_links'])} external link(s)" if r["external_links"] else ""))
    for s in r["sheets"]:
        flag = "" if s["state"] == "visible" else f" [{s['state']}]"
        if s["kind"] != "worksheet":
            print(f"\n- {s['name']}{flag} ({s['kind']})")
            continue
        print(f"\n- {s['name']}{flag}: range {s['used_range']}, {s['cells']} cells, {s['formulas']} formulas"
              + (f", {s['errors']} error values" if s["errors"] else ""))
        details = []
        if s["frozen"]:
            details.append(f"frozen at {s['frozen']}")
        if s["autofilter"]:
            details.append(f"autofilter {s['autofilter']}")
        if s["merged"]:
            details.append(f"{len(s['merged'])} merged range(s): {', '.join(s['merged'][:5])}")
        if s["data_validations"]:
            details.append(f"{s['data_validations']} data validation rule(s)")
        if s["conditional_formats"]:
            details.append(f"{s['conditional_formats']} conditional format(s)")
        if s["protected"]:
            details.append("sheet protected")
        for d in details:
            print(f"    {d}")
        for t in s["tables"]:
            print(f"    table {t['name']} {t['ref']}: {', '.join(t['columns'])}")
        for row in s.get("sample", []):
            print("    | " + " | ".join("" if v is None else str(v) for v in row))
    if r["defined_names"]:
        print("\nDefined names:")
        for d in r["defined_names"]:
            print(f"    {d['name']} = {d['refers_to']}" + ("  <-- broken" if d["broken"] else "") + ("  (hidden)" if d["hidden"] else ""))


def main():
    ap = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    ap.add_argument("file", help=".xlsx or .xlsm file")
    ap.add_argument("--sheet", help="limit --sample to this sheet")
    ap.add_argument("--sample", type=int, default=0, help="print the first N rows of each (or --sheet) sheet")
    ap.add_argument("--json", action="store_true", help="emit machine-readable JSON")
    args = ap.parse_args()
    try:
        report = inspect(args.file, args.sheet, args.sample)
    except (zipfile.BadZipFile, ValueError, OSError) as e:
        print(f"Error: {e}", file=sys.stderr)
        sys.exit(1)
    if args.json:
        print(json.dumps(report, indent=2, default=str))
    else:
        render(report)


if __name__ == "__main__":
    main()