Last active
October 17, 2025 23:58
-
-
Save schneider8357/89af000736514020a569351f0446ae53 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # pip install openpyxl olefile | |
| from pathlib import Path | |
| from zipfile import ZipFile | |
| from typing import Dict, List, Union | |
| from openpyxl import load_workbook | |
| import olefile | |
| import sys | |
| import os | |
| from io import BytesIO | |
| from datetime import datetime | |
| EXCEL_EXTS = {".xlsx", ".xlsm", ".xltx", ".xltm"} | |
| def inspect_excel_file(file_path: Union[str, Path]) -> Dict: | |
| p = Path(file_path) | |
| info: Dict = { | |
| "file": str(p), | |
| "exists": p.exists(), | |
| "error": None, | |
| "workbook_structure_locked": False, | |
| "workbook_windows_locked": False, | |
| "workbook_password_hash": False, | |
| "any_sheet_protected": False, | |
| "any_sheet_password_hash": False, | |
| "any_protection": False, | |
| "has_macros": False, | |
| "macros_password_protected": False, | |
| "sheets": [], | |
| "protected_sheets": [], | |
| } | |
| if not info["exists"]: | |
| info["error"] = "File not found" | |
| return info | |
| if p.suffix.lower() not in EXCEL_EXTS: | |
| info["error"] = "Not an Excel (xlsx/xlsm/xltx/xltm) file" | |
| return info | |
| if p.name.startswith("~"): # exclude temp/lock files like '~$Workbook.xlsm' | |
| info["error"] = "Skipped temp/lock file (tilde-prefixed)" | |
| return info | |
| wb = None | |
| try: | |
| # read_only=False so ws.protection is available | |
| wb = load_workbook(filename=str(p), read_only=False, data_only=False, keep_vba=False, keep_links=False) | |
| wb_sec = getattr(wb, "security", None) | |
| info["workbook_structure_locked"] = bool(getattr(wb_sec, "lockStructure", False)) if wb_sec else False | |
| info["workbook_windows_locked"] = bool(getattr(wb_sec, "lockWindows", False)) if wb_sec else False | |
| info["workbook_password_hash"] = bool(getattr(wb_sec, "workbookPassword", None)) if wb_sec else False | |
| sheets = [] | |
| protected_sheets = [] | |
| any_sheet_protected = False | |
| any_sheet_password_hash = False | |
| for ws in wb.worksheets: | |
| title = ws.title | |
| sheets.append(title) | |
| prot = getattr(ws, "protection", None) | |
| prot_on = bool(getattr(prot, "sheet", False)) if prot else False | |
| pass_hash = bool(getattr(prot, "password", None)) if prot else False | |
| if prot_on: | |
| protected_sheets.append(title) | |
| any_sheet_protected = any_sheet_protected or prot_on | |
| any_sheet_password_hash = any_sheet_password_hash or pass_hash | |
| info["sheets"] = sheets | |
| info["protected_sheets"] = protected_sheets | |
| info["any_sheet_protected"] = any_sheet_protected | |
| info["any_sheet_password_hash"] = any_sheet_password_hash | |
| info["any_protection"] = ( | |
| info["workbook_structure_locked"] | |
| or info["workbook_windows_locked"] | |
| or info["any_sheet_protected"] | |
| ) | |
| except Exception as e: | |
| info["error"] = f"openpyxl: {type(e).__name__}: {e}" | |
| finally: | |
| # ensure close of internal zip handles | |
| if wb is not None: | |
| try: | |
| wb.close() | |
| finally: | |
| arch = getattr(wb, "_archive", None) | |
| if arch is not None: | |
| try: | |
| arch.close() | |
| except Exception: | |
| pass | |
| del wb | |
| # Macros / VBA password (read directly from OOXML; isolate with BytesIO) | |
| try: | |
| with ZipFile(p, "r") as zf: | |
| info["has_macros"] = ("xl/vbaProject.bin" in zf.namelist()) | |
| if info["has_macros"]: | |
| with zf.open("xl/vbaProject.bin") as vb: | |
| data = vb.read() | |
| with olefile.OleFileIO(BytesIO(data)) as ole: | |
| if ole.exists("PROJECT"): | |
| with ole.openstream("PROJECT") as s: | |
| proj_txt = s.read().decode("latin-1", errors="ignore") | |
| info["macros_password_protected"] = any(t in proj_txt for t in ("DPB=", "DPX=", "Password=")) | |
| else: | |
| info["macros_password_protected"] = False | |
| else: | |
| info["macros_password_protected"] = False | |
| except Exception as e: | |
| info["error"] = (info["error"] + " | " if info["error"] else "") + f"zip/ole: {type(e).__name__}: {e}" | |
| return info | |
| def scan_directory_for_excels(root_dir: Union[str, Path]) -> List[Dict]: | |
| root = Path(root_dir) | |
| results: List[Dict] = [] | |
| for dirpath, _, filenames in os.walk(root): | |
| for name in filenames: | |
| if name.startswith("~"): # exclude tilde-prefixed temp/lock files | |
| continue | |
| if Path(name).suffix.lower() in EXCEL_EXTS: | |
| results.append(inspect_excel_file(Path(dirpath) / name)) | |
| return results | |
| def _bool_icon(v: bool) -> str: | |
| return "✔" if v else "✘" | |
| def build_structured_report(rows: List[Dict]) -> str: | |
| now = datetime.now() | |
| now_str = now.strftime("%Y-%m-%d %H:%M:%S") | |
| total = len(rows) | |
| ok = sum(1 for r in rows if not r.get("error")) | |
| errs = total - ok | |
| prot = sum(1 for r in rows if r.get("any_protection")) | |
| macros = sum(1 for r in rows if r.get("has_macros")) | |
| macros_locked = sum(1 for r in rows if r.get("macros_password_protected")) | |
| lines: List[str] = [] | |
| lines.append("=" * 80) | |
| lines.append(f"EXCEL PROTECTION REPORT".ljust(60) + f"Generated: {now_str}") | |
| lines.append("=" * 80) | |
| lines.append(f"Files scanned: {total} | OK: {ok} | Errors: {errs} | Any protection: {prot}") | |
| lines.append(f"Has macros: {macros} | VBA password protected: {macros_locked}") | |
| lines.append("-" * 80) | |
| for i, r in enumerate(sorted(rows, key=lambda x: x.get("file", "")), 1): | |
| lines.append(f"[{i}] File: {r.get('file','')}") | |
| if r.get("error"): | |
| lines.append(f" Status : ERROR") | |
| lines.append(f" Error : {r['error']}") | |
| lines.append("-" * 80) | |
| continue | |
| lines.append(f" Status : OK") | |
| lines.append( | |
| " Protection : " | |
| f"any={_bool_icon(r.get('any_protection', False))} " | |
| f"workbook(structure={_bool_icon(r.get('workbook_structure_locked', False))}, " | |
| f"windows={_bool_icon(r.get('workbook_windows_locked', False))}, " | |
| f"password_hash={_bool_icon(r.get('workbook_password_hash', False))}) " | |
| f"sheets(any_protected={_bool_icon(r.get('any_sheet_protected', False))}, " | |
| f"any_passhash={_bool_icon(r.get('any_sheet_password_hash', False))})" | |
| ) | |
| lines.append( | |
| " Macros : " | |
| f"present={_bool_icon(r.get('has_macros', False))} " | |
| f"vba_password_protected={_bool_icon(r.get('macros_password_protected', False))}" | |
| ) | |
| sheets = r.get("sheets", []) or [] | |
| psheets = set(r.get("protected_sheets", []) or []) | |
| lines.append(f" Sheets : total={len(sheets)} | protected={len(psheets)}") | |
| if sheets: | |
| for s in sheets: | |
| tag = " [protected]" if s in psheets else "" | |
| lines.append(f" - {s}{tag}") | |
| else: | |
| lines.append(f" - (no sheets found)") | |
| lines.append("-" * 80) | |
| return "\n".join(lines) | |
| if __name__ == "__main__": | |
| # USAGE: | |
| # python check_excel_protection.py <path-to-file-or-directory> | |
| target = sys.argv[1] if len(sys.argv) > 1 else "." | |
| path = Path(target) | |
| if path.is_file(): | |
| if path.name.startswith("~"): | |
| results = [] # skip single tilde-prefixed file | |
| else: | |
| results = [inspect_excel_file(path)] | |
| else: | |
| results = scan_directory_for_excels(path) | |
| report_text = build_structured_report(results) | |
| print(report_text) | |
| ts = datetime.now().strftime("%Y%m%d_%H%M%S") # date + hhmmss | |
| log_name = f"excel_protection_report_{ts}.log" | |
| with open(log_name, "w", encoding="utf-8") as f: | |
| f.write(report_text) | |
| print(f"\nSaved report to: {log_name}") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment