tools/spreadsheet_reader.py

from __future__ import annotations

from pathlib import Path
from typing import Any

import pandas as pd


def inspect_spreadsheet(file_path: str, sheet_name: str | int | None = None) -> dict[str, Any]:
    path = Path(file_path)
    if path.suffix.lower() == ".csv":
        frame = pd.read_csv(path)
        name = "csv"
    else:
        selected = 0 if sheet_name is None else sheet_name
        frame = pd.read_excel(path, sheet_name=selected)
        name = str(selected)
    return {
        "ok": True,
        "source": str(path),
        "content": frame.head(100).to_csv(index=False),
        "metadata": {
            "sheet": name,
            "rows": int(frame.shape[0]),
            "columns": [str(column) for column in frame.columns],
            "dtypes": {str(column): str(dtype) for column, dtype in frame.dtypes.items()},
        },
    }

def calculate_food_sales(file_path: str) -> str:
    df = pd.read_excel(file_path)

    excluded_columns = {
        "Location",
        "Soda",
    }

    food_columns = [
        column
        for column in df.columns
        if column not in excluded_columns
    ]

    total = (
        df[food_columns]
        .select_dtypes(include="number")
        .sum()
        .sum()
    )

    return f"${total:,.2f}"