Validate an Excel Report Before Sending It

pip install pandas openpyxl

from pathlib import Path

from openpyxl import load_workbook

def check_file(path, min_bytes=5_000):
    path = Path(path)
    problems = []
    if not path.exists():
        return [f"{path} was not created"]
    size = path.stat().st_size
    if size < min_bytes:
        problems.append(f"file is only {size:,} bytes (expected at least {min_bytes:,})")
    try:
        wb = load_workbook(path, read_only=True)
        wb.close()
    except Exception as exc:                     # zipfile.BadZipFile, InvalidFileException…
        problems.append(f"file cannot be opened as a workbook: {type(exc).__name__}")
    return problems

print(check_file("reports/march.xlsx"))

import pandas as pd

EXPECTED = {
    "Summary": {"min_rows": 1, "columns": {"Region", "Revenue"}},
    "Detail": {"min_rows": 10, "columns": {"Order_ID", "Region", "Revenue"}},
}

def check_content(path, expected=EXPECTED):
    problems = []
    book = pd.ExcelFile(path)
    for sheet, rules in expected.items():
        if sheet not in book.sheet_names:
            problems.append(f"missing sheet {sheet!r}")
            continue
        frame = book.parse(sheet)
        if len(frame) < rules["min_rows"]:
            problems.append(
                f"{sheet}: {len(frame)} row(s), expected at least {rules['min_rows']}"
            )
        missing = rules["columns"] - set(frame.columns)
        if missing:
            problems.append(f"{sheet}: missing column(s) {sorted(missing)}")
        blank = [c for c in rules["columns"] & set(frame.columns) if frame[c].isna().all()]
        if blank:
            problems.append(f"{sheet}: column(s) entirely empty {sorted(blank)}")
    return problems

import pandas as pd

def check_numbers(path):
    problems = []
    summary = pd.read_excel(path, sheet_name="Summary")
    detail = pd.read_excel(path, sheet_name="Detail")

    if summary["Revenue"].sum() <= 0:
        problems.append(f"total revenue is {summary['Revenue'].sum():,.2f}")

    if (summary["Revenue"] < 0).any():
        bad = summary.loc[summary["Revenue"] < 0, "Region"].tolist()
        problems.append(f"negative revenue for {bad}")

    # The summary must reconcile with the detail it claims to summarise
    gap = abs(summary["Revenue"].sum() - detail["Revenue"].sum())
    if gap > 0.01:
        problems.append(f"summary and detail differ by {gap:,.2f}")

    return problems

import csv
import statistics
from pathlib import Path

HISTORY = Path("state/report_history.csv")

def record_run(rows, total, path=HISTORY):
    path.parent.mkdir(parents=True, exist_ok=True)
    new = not path.exists()
    with open(path, "a", newline="", encoding="utf-8") as handle:
        writer = csv.writer(handle)
        if new:
            writer.writerow(["rows", "total"])
        writer.writerow([rows, round(total, 2)])

def check_against_history(rows, total, path=HISTORY, low=0.5, high=1.5, keep=6):
    if not path.exists():
        return []
    with open(path, newline="", encoding="utf-8") as handle:
        past = [(int(r["rows"]), float(r["total"])) for r in csv.DictReader(handle)][-keep:]
    if len(past) < 3:
        return []                                   # not enough history to judge

    problems = []
    median_rows = statistics.median(r for r, _ in past)
    median_total = statistics.median(t for _, t in past)
    if not (median_rows * low <= rows <= median_rows * high):
        problems.append(f"row count {rows:,} is outside the usual range (~{median_rows:,.0f})")
    if not (median_total * low <= total <= median_total * high):
        problems.append(f"total {total:,.0f} is outside the usual range (~{median_total:,.0f})")
    return problems

import sys

def publish(path, rows, total, to="finance@example.com"):
    problems = (
        check_file(path)
        or check_content(path) + check_numbers(path) + check_against_history(rows, total)
    )
    if problems:
        log.error("output validation failed for %s: %s", path, "; ".join(problems))
        alert(f"Report NOT sent — {len(problems)} check(s) failed",
              f"{path}\n\n" + "\n".join(f"• {p}" for p in problems))
        return 1
    deliver(path, to)
    record_run(rows, total)
    log.info("delivered %s rows=%d total=%.2f", path, rows, total)
    return 0

sys.exit(publish("reports/march.xlsx", rows=48_211, total=1_284_600.00))

from openpyxl import load_workbook

ERROR_VALUES = {"#REF!", "#DIV/0!", "#VALUE!", "#N/A", "#NAME?", "#NUM!"}

def check_presentation(path):
    problems = []
    wb = load_workbook(path, data_only=True)

    for ws in wb.worksheets:
        errors = [
            f"{ws.title}!{cell.coordinate}"
            for row in ws.iter_rows()
            for cell in row
            if isinstance(cell.value, str) and cell.value in ERROR_VALUES
        ]
        if errors:
            problems.append(f"{len(errors)} formula error(s), first at {errors[0]}")

        # Columns narrower than their header text show as ####
        for column, dim in ws.column_dimensions.items():
            if dim.width and dim.width < 6:
                problems.append(f"{ws.title}: column {column} is only {dim.width:.0f} wide")

    if wb.active is None or wb.active.title != wb.sheetnames[0]:
        problems.append("the workbook does not open on the first sheet")

    return problems

import shutil
from pathlib import Path

def publish_with_history(new_file, published="share/monthly_report.xlsx", archive="share/archive"):
    new_file, published = Path(new_file), Path(published)
    Path(archive).mkdir(parents=True, exist_ok=True)

    if published.exists():                       # keep what readers currently have
        shutil.copy2(published, Path(archive) / f"{published.stem}_previous{published.suffix}")

    shutil.copy2(new_file, published)
    shutil.copy2(new_file, Path(archive) / new_file.name)
    return published