📊 Python Data Workflows – 📉 Excel Automation 🐍

from pathlib import Path

import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter


INPUT_FILE = Path("sales_data.csv")
OUTPUT_FILE = Path("sales_report.xlsx")

CURRENCY_COLUMNS = {
    "sales",
    "total_sales",
    "average_sales",
    "revenue_per_item",
}


def load_data(file_path: Path) -> pd.DataFrame:
    """Load the source sales dataset."""
    if not file_path.exists():
        raise FileNotFoundError(
            f"Input file not found: {file_path}"
        )

    df = pd.read_csv(file_path)

    print(
        f"✅ Loaded {len(df)} records "
        f"from {file_path.name}"
    )

    return df


def clean_data(df: pd.DataFrame) -> pd.DataFrame:
    """Clean and prepare the sales dataset."""
    cleaned_df = df.copy()

    required_columns = {
        "order_id",
        "customer_name",
        "category",
        "region",
        "sales",
        "quantity",
        "order_date",
    }

    missing_columns = required_columns.difference(
        cleaned_df.columns
    )

    if missing_columns:
        raise ValueError(
            "Missing required columns: "
            f"{sorted(missing_columns)}"
        )

    cleaned_df["sales"] = pd.to_numeric(
        cleaned_df["sales"],
        errors="coerce"
    )

    cleaned_df["quantity"] = pd.to_numeric(
        cleaned_df["quantity"],
        errors="coerce"
    )

    cleaned_df["order_date"] = pd.to_datetime(
        cleaned_df["order_date"],
        errors="coerce"
    )

    cleaned_df["customer_name"] = (
        cleaned_df["customer_name"]
        .astype("string")
        .str.strip()
    )

    for column in ["category", "region"]:
        cleaned_df[column] = (
            cleaned_df[column]
            .astype("string")
            .str.strip()
            .str.title()
        )

    cleaned_df = cleaned_df.drop_duplicates(
        subset=["order_id"],
        keep="first"
    )

    cleaned_df = cleaned_df.dropna(
        subset=[
            "order_id",
            "category",
            "region",
            "sales",
            "quantity",
            "order_date",
        ]
    )

    cleaned_df = cleaned_df[
        (cleaned_df["sales"] >= 0) &
        (cleaned_df["quantity"] > 0)
    ].copy()

    cleaned_df["revenue_per_item"] = (
        cleaned_df["sales"] /
        cleaned_df["quantity"]
    ).round(2)

    cleaned_df["order_month"] = (
        cleaned_df["order_date"]
        .dt.to_period("M")
        .astype(str)
    )

    cleaned_df = cleaned_df.sort_values(
        "order_date"
    ).reset_index(drop=True)

    print(
        f"✅ Retained {len(cleaned_df)} "
        "valid records"
    )

    return cleaned_df


def create_summaries(
    df: pd.DataFrame
) -> dict[str, pd.DataFrame]:
    """Create summary reports for Excel sheets."""
    category_summary = (
        df.groupby(
            "category",
            observed=True
        )
        .agg(
            total_sales=("sales", "sum"),
            average_sales=("sales", "mean"),
            total_quantity=("quantity", "sum"),
            total_orders=("order_id", "count"),
        )
        .reset_index()
        .sort_values(
            "total_sales",
            ascending=False
        )
    )

    category_summary["average_sales"] = (
        category_summary["average_sales"]
        .round(2)
    )

    region_summary = (
        df.groupby(
            "region",
            observed=True
        )
        .agg(
            total_sales=("sales", "sum"),
            average_sales=("sales", "mean"),
            total_orders=("order_id", "count"),
        )
        .reset_index()
        .sort_values(
            "total_sales",
            ascending=False
        )
    )

    region_summary["average_sales"] = (
        region_summary["average_sales"]
        .round(2)
    )

    monthly_summary = (
        df.groupby(
            "order_month",
            observed=True
        )
        .agg(
            total_sales=("sales", "sum"),
            average_sales=("sales", "mean"),
            total_quantity=("quantity", "sum"),
            total_orders=("order_id", "count"),
        )
        .reset_index()
        .sort_values("order_month")
    )

    monthly_summary["average_sales"] = (
        monthly_summary["average_sales"]
        .round(2)
    )

    print("✅ Summary reports created")

    return {
        "Sales Data": df,
        "Category Summary": category_summary,
        "Region Summary": region_summary,
        "Monthly Summary": monthly_summary,
    }


def write_workbook(
    reports: dict[str, pd.DataFrame],
    output_file: Path
) -> None:
    """Write all DataFrames into one Excel workbook."""
    with pd.ExcelWriter(
        output_file,
        engine="openpyxl",
        date_format="DD-MM-YYYY",
        datetime_format="DD-MM-YYYY",
    ) as writer:
        for sheet_name, report_df in reports.items():
            report_df.to_excel(
                writer,
                sheet_name=sheet_name,
                index=False
            )

    print(
        f"✅ Workbook created: "
        f"{output_file.name}"
    )


def format_headers(worksheet) -> None:
    """Apply consistent formatting to worksheet headers."""
    header_fill = PatternFill(
        fill_type="solid",
        fgColor="1F4E78"
    )

    header_font = Font(
        color="FFFFFF",
        bold=True
    )

    for cell in worksheet[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(
            horizontal="center",
            vertical="center"
        )

    worksheet.row_dimensions[1].height = 24


def adjust_column_widths(worksheet) -> None:
    """Automatically resize worksheet columns."""
    for column_cells in worksheet.columns:
        max_length = 0

        for cell in column_cells:
            if cell.value is not None:
                max_length = max(
                    max_length,
                    len(str(cell.value))
                )

        column_letter = get_column_letter(
            column_cells[0].column
        )

        worksheet.column_dimensions[
            column_letter
        ].width = min(max_length + 3, 32)


def apply_number_formats(worksheet) -> None:
    """Apply currency and date formatting."""
    headers = {
        cell.value: cell.column
        for cell in worksheet[1]
    }

    for column_name in CURRENCY_COLUMNS:
        column_index = headers.get(column_name)

        if not column_index:
            continue

        for row_number in range(
            2,
            worksheet.max_row + 1
        ):
            worksheet.cell(
                row=row_number,
                column=column_index
            ).number_format = '£#,##0.00'

    date_column = headers.get("order_date")

    if date_column:
        for row_number in range(
            2,
            worksheet.max_row + 1
        ):
            worksheet.cell(
                row=row_number,
                column=date_column
            ).number_format = "DD-MM-YYYY"


def apply_conditional_formatting(
    worksheet
) -> None:
    """Highlight low-to-high sales values."""
    headers = {
        cell.value: cell.column
        for cell in worksheet[1]
    }

    sales_column = (
        headers.get("total_sales") or
        headers.get("sales")
    )

    if not sales_column or worksheet.max_row < 2:
        return

    column_letter = get_column_letter(
        sales_column
    )

    cell_range = (
        f"{column_letter}2:"
        f"{column_letter}{worksheet.max_row}"
    )

    worksheet.conditional_formatting.add(
        cell_range,
        ColorScaleRule(
            start_type="min",
            start_color="F8696B",
            mid_type="percentile",
            mid_value=50,
            mid_color="FFEB84",
            end_type="max",
            end_color="63BE7B",
        )
    )


def format_workbook(
    output_file: Path
) -> None:
    """Apply formatting across all worksheets."""
    workbook = load_workbook(output_file)

    for worksheet in workbook.worksheets:
        worksheet.freeze_panes = "A2"
        worksheet.auto_filter.ref = (
            worksheet.dimensions
        )

        format_headers(worksheet)
        adjust_column_widths(worksheet)
        apply_number_formats(worksheet)
        apply_conditional_formatting(
            worksheet
        )

    workbook.save(output_file)

    print("✅ Workbook formatting completed")


def display_summary(
    df: pd.DataFrame,
    output_file: Path
) -> None:
    """Display a concise workflow summary."""
    print("\n📊 Excel Automation Summary")
    print(f"Processed records: {len(df)}")
    print(
        f"Total sales: "
        f"£{df['sales'].sum():,.2f}"
    )
    print(
        f"Average order value: "
        f"£{df['sales'].mean():,.2f}"
    )
    print(f"Workbook: {output_file}")


def main() -> None:
    """Run the complete Excel automation workflow."""
    print("🚀 Starting Excel Automation\n")

    try:
        raw_df = load_data(INPUT_FILE)
        cleaned_df = clean_data(raw_df)

        if cleaned_df.empty:
            raise ValueError(
                "No valid records remain after cleaning."
            )

        reports = create_summaries(cleaned_df)

        write_workbook(
            reports,
            OUTPUT_FILE
        )

        format_workbook(OUTPUT_FILE)

        display_summary(
            cleaned_df,
            OUTPUT_FILE
        )

        print(
            "\n✅ Excel report generated "
            "successfully"
        )

    except (
        FileNotFoundError,
        ValueError,
        KeyError,
        pd.errors.ParserError,
        PermissionError,
    ) as error:
        print(
            f"\n❌ Excel automation failed: "
            f"{error}"
        )


if __name__ == "__main__":
    main()