xlsx

1#!/usr/bin/env python32"""Read an .xlsx workbook: inventory, JSON/CSV dumps, formula listing.3 4Modes (pick one):5  --sheets     JSON inventory: sheet names, dimensions, row/col counts6  --json       dump one sheet's rows as a JSON array of arrays7  --csv        dump one sheet as CSV to stdout or --out8  --formulas   JSON list of formula cells {"cell", "formula", "cached"}9  --notes      JSON list of cell notes/comments across sheets10  --names      JSON map of workbook defined names11 12Options:13  --sheet NAME     sheet to dump (default: active sheet)14  --data-only      load cached formula RESULTS instead of formula strings.15                   Caveat: openpyxl never computes formulas; cached values16                   exist only if the file was last saved by Excel/LibreOffice.17  --encoding ENC   encoding for --csv --out files (default utf-8)18  --out PATH       write --csv output to a file instead of stdout19 20Usage:21  xlsx_read.py book.xlsx --sheets22  xlsx_read.py book.xlsx --json --sheet Data23  xlsx_read.py book.xlsx --csv --sheet Data --out data.csv24  xlsx_read.py book.xlsx --formulas25  xlsx_read.py book.xlsx --notes26  xlsx_read.py book.xlsx --names27"""28from __future__ import annotations29 30import argparse31import csv32import json33import sys34from datetime import date, datetime, time35 36from openpyxl import load_workbook37 38 39def jsonable(value):40    if isinstance(value, (datetime, date, time)):41        return value.isoformat()42    return value43 44 45def sheet_rows(ws):46    return [[jsonable(c) for c in row] for row in ws.iter_rows(values_only=True)]47 48 49def cmd_sheets(wb):50    info = []51    for ws in wb.worksheets:52        info.append({53            "name": ws.title,54            "dimensions": ws.dimensions,55            "max_row": ws.max_row,56            "max_col": ws.max_column,57            "merged": [str(r) for r in ws.merged_cells.ranges],58            "charts": len(getattr(ws, "_charts", [])),59            "freeze_panes": ws.freeze_panes,60            "autofilter": ws.auto_filter.ref,61            "tables": {t.displayName: t.ref for t in ws.tables.values()},62            "protected": bool(ws.protection.sheet),63        })64    names = {name: dn.attr_text for name, dn in wb.defined_names.items()}65    print(json.dumps({"sheets": info, "defined_names": names},66                     ensure_ascii=False, indent=2))67 68 69def cmd_notes(wb, sheet):70    out = []71    sheets = [sheet] if sheet else wb.sheetnames72    for name in sheets:73        for row in wb[name].iter_rows():74            for cell in row:75                if cell.comment is not None:76                    out.append({"sheet": name, "cell": cell.coordinate,77                                "text": cell.comment.text,78                                "author": cell.comment.author})79    print(json.dumps({"notes": out}, ensure_ascii=False, indent=2))80 81 82def cmd_names(wb):83    names = {name: dn.attr_text for name, dn in wb.defined_names.items()}84    print(json.dumps({"defined_names": names}, ensure_ascii=False, indent=2))85 86 87def cmd_formulas(path, sheet):88    wb_f = load_workbook(path, data_only=False)89    wb_v = load_workbook(path, data_only=True)90    out = []91    sheets = [sheet] if sheet else wb_f.sheetnames92    for name in sheets:93        ws_f, ws_v = wb_f[name], wb_v[name]94        for row in ws_f.iter_rows():95            for cell in row:96                if isinstance(cell.value, str) and cell.value.startswith("="):97                    out.append({98                        "sheet": name,99                        "cell": cell.coordinate,100                        "formula": cell.value,101                        "cached": jsonable(ws_v[cell.coordinate].value),102                    })103    print(json.dumps({"formulas": out}, ensure_ascii=False, indent=2))104 105 106def main(argv=None):107    ap = argparse.ArgumentParser(description="Read/inspect an .xlsx workbook.")108    ap.add_argument("file", help="path to .xlsx file")109    mode = ap.add_mutually_exclusive_group(required=True)110    mode.add_argument("--sheets", action="store_true")111    mode.add_argument("--json", action="store_true")112    mode.add_argument("--csv", action="store_true")113    mode.add_argument("--formulas", action="store_true")114    mode.add_argument("--notes", action="store_true")115    mode.add_argument("--names", action="store_true")116    ap.add_argument("--sheet", help="sheet name (default: active)")117    ap.add_argument("--data-only", action="store_true",118                    help="return cached formula results (see module docstring)")119    ap.add_argument("--encoding", default="utf-8")120    ap.add_argument("--out", help="output file for --csv")121    args = ap.parse_args(argv)122 123    if args.formulas:124        cmd_formulas(args.file, args.sheet)125        return 0126 127    wb = load_workbook(args.file, data_only=args.data_only)128    if args.sheets:129        cmd_sheets(wb)130        return 0131    if args.notes:132        cmd_notes(wb, args.sheet)133        return 0134    if args.names:135        cmd_names(wb)136        return 0137 138    ws = wb[args.sheet] if args.sheet else wb.active139    rows = sheet_rows(ws)140    if args.json:141        print(json.dumps({"sheet": ws.title, "rows": rows}, ensure_ascii=False))142    else:  # --csv143        if args.out:144            with open(args.out, "w", newline="", encoding=args.encoding) as fh:145                csv.writer(fh).writerows(146                    [["" if v is None else v for v in r] for r in rows])147            print(json.dumps({"ok": True, "out": args.out, "rows": len(rows)}))148        else:149            w = csv.writer(sys.stdout)150            for r in rows:151                w.writerow(["" if v is None else v for v in r])152    return 0153 154 155if __name__ == "__main__":156    try:157        sys.exit(main())158    except Exception as exc:  # noqa: BLE001159        print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)160        sys.exit(1)161