Excelの重複行を見つけ、確認用ファイルに分ける

python --version
python -m pip install -r requirements.txt
python example.py

"""Find repeated business rows. Keep the source workbook unchanged."""
from collections import Counter
from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill

BASE = Path(__file__).resolve().parent
HEADERS = ("order_id", "customer", "item", "quantity")


def main():
    destination = BASE / "outputs"
    if destination.exists():
        raise ValueError("outputs already exists; rename it before running again.")
    source = load_workbook(BASE / "orders.xlsx", read_only=True, data_only=False)
    try:
        if "Orders" not in source.sheetnames:
            raise ValueError("Orders sheet is missing.")
        worksheet = source["Orders"]
        rows = list(worksheet.iter_rows(values_only=True))
        if not rows or tuple(rows[0]) != HEADERS:
            raise ValueError(f"Expected headers: {HEADERS}")
        records = []
        for row_number, row in enumerate(rows[1:], start=2):
            if all(value is None for value in row):
                continue
            if any(value is None for value in row):
                raise ValueError(f"Row {row_number}: missing value.")
            if any(isinstance(value, str) and value.startswith("=") for value in row):
                raise ValueError(f"Row {row_number}: formulas are not supported.")
            if not all(isinstance(value, str) and value.strip() for value in row[:3]):
                raise ValueError(f"Row {row_number}: first three fields must be text.")
            if type(row[3]) is not int or row[3] <= 0:
                raise ValueError(f"Row {row_number}: quantity must be a positive integer.")
            records.append((row_number, tuple(row)))
        if not records:
            raise ValueError("No data rows were found.")
    finally:
        source.close()

    counts = Counter(row for _, row in records)
    repeated = [(number, row) for number, row in records if counts[row] > 1]
    unique = [(number, row) for number, row in records if counts[row] == 1]
    book = Workbook()
    book.remove(book.active)
    for name, selected in (("Duplicates", repeated), ("Unique", unique)):
        sheet = book.create_sheet(name)
        sheet.append(("source_row", *HEADERS, "occurrences"))
        for number, row in selected:
            sheet.append((number, *row, counts[row]))
        sheet.freeze_panes = "A2"
        sheet.auto_filter.ref = sheet.dimensions
        sheet.sheet_view.showGridLines = False
        for cell in sheet[1]:
            cell.font = Font(name="Arial", bold=True, color="FFFFFF")
            cell.fill = PatternFill("solid", fgColor="243B53")
        for column, width in zip("ABCDEF", (14, 16, 18, 20, 14, 16)):
            sheet.column_dimensions[column].width = width
    destination.mkdir(exist_ok=False)
    book.save(destination / "duplicate_review.xlsx")
    book.close()
    groups = sum(count > 1 for count in counts.values())
    print(f"Input rows: {len(records)}; duplicate groups: {groups}")
    print(f"Duplicate rows: {len(repeated)}; unique rows: {len(unique)}")
    print("Created outputs/duplicate_review.xlsx (source unchanged).")


if __name__ == "__main__":
    try:
        main()
    except (OSError, ValueError) as error:
        raise SystemExit(f"Stopped: {error}") from error