Python Excel Script Generator

# Generated by the FormulaSurge Python Excel Script Generator
# Setup once:  pip install pandas openpyxl
# Run:         python report.py   (close the output file in Excel first)
import re
from pathlib import Path

import pandas as pd
from openpyxl.styles import Font


def safe(name) -> str:
    """Make a value usable as a sheet or file name (Excel allows 31 characters, no []:*?/\\)."""
    return re.sub(r'[\\/:*?"<>|\[\]]', "_", str(name)).strip()[:31] or "Blank"


def save(sheets: dict, out) -> None:
    """Write each DataFrame to its own sheet: bold frozen header and readable column widths."""
    with pd.ExcelWriter(out, engine="openpyxl") as xl:
        for name, df in sheets.items():
            df.to_excel(xl, sheet_name=safe(name), index=False)
            ws = xl.sheets[safe(name)]
            ws.freeze_panes = "A2"
            for cell in ws[1]:
                cell.font = Font(bold=True)
            for col in ws.columns:
                width = max(len(str(c.value or "")) for c in col)
                ws.column_dimensions[col[0].column_letter].width = min(width + 2, 50)
    print(f"Saved {out}")


FOLDER = Path(r"C:\Reports\Monthly")
OUT = Path(r"C:\Reports\Combined.xlsx")

frames = []
for file in sorted(FOLDER.glob("*.xlsx")):
    if file.name.startswith("~$") or file.resolve() == OUT.resolve():
        continue  # skip Excel lock files and last run's output
    df = pd.read_excel(file, sheet_name=0)
    df["Source file"] = file.name
    frames.append(df)
    print(f"Read {len(df):,} rows from {file.name}")

if not frames:
    raise SystemExit(f"No .xlsx files found in {FOLDER}")

combined = pd.concat(frames, ignore_index=True)
save({"Combined": combined}, OUT)