Merge machine CSV logs into a formatted Excel report (openpyxl)

"""Merge a folder of per-machine CSV logs into one formatted Excel report.

Each CSV needs the columns: machine, date, parts, scrap  (extras ignored).

Usage:  python weekly_report.py logs/ --out weekly_production.xlsx
"""

import argparse
import csv
import sys
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Font


def main():
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("folder", help="folder containing the .csv logs")
    ap.add_argument("--out", default="weekly_production.xlsx")
    args = ap.parse_args()

    files = sorted(Path(args.folder).glob("*.csv"))
    if not files:
        sys.exit(f"No .csv files found in {args.folder}")

    wb = Workbook()
    ws = wb.active
    ws.title = "Production"

    ws.append(["Machine", "Date", "Parts", "Scrap"])
    for cell in ws[1]:
        cell.font = Font(bold=True)

    rows, skipped = 0, 0
    for log in files:
        with open(log, newline="") as fh:
            for r in csv.DictReader(fh):
                try:
                    ws.append([r["machine"], r["date"],
                               int(r["parts"]), int(r["scrap"])])
                    rows += 1
                except (KeyError, ValueError):
                    skipped += 1   # malformed row: count it, don't die on it

    # Totals row: real Excel formulas, evaluated when the file opens
    last = ws.max_row + 2
    ws[f"A{last}"] = "TOTAL"
    ws[f"A{last}"].font = Font(bold=True)
    ws[f"C{last}"] = f"=SUM(C2:C{last - 2})"
    ws[f"D{last}"] = f"=SUM(D2:D{last - 2})"

    ws.column_dimensions["A"].width = 16
    ws.column_dimensions["B"].width = 12
    ws.freeze_panes = "A2"

    try:
        wb.save(args.out)
    except PermissionError:
        sys.exit(f"Cannot write {args.out} - close it in Excel first.")

    msg = f"{rows} rows from {len(files)} file(s) -> {args.out}"
    if skipped:
        msg += f"  ({skipped} bad rows skipped)"
    print(msg)


if __name__ == "__main__":
    main()