Generating Spreadsheets with Python

pip install polars pyyaml

import polars as pl

# Create data with Polars
budget = pl.DataFrame({
    "Category": ["Talent", "Equipment", "Post-Production"],
    "Budget": [50000, 25000, 15000],
    "Spent": [12000, 8000, 0],
})

# Export to CSV (formulas added separately)
budget.write_csv("data/budget.csv")

import polars as pl
import yaml
from datetime import datetime

def generate_spreadsheet_yaml(
    df: pl.DataFrame,
    output_path: str,
    column_config: dict = None,
    formulas: dict = None
):
    """
    Generate a .spreadsheet.yaml file from a Polars DataFrame.

    Args:
        df: Polars DataFrame with your data
        output_path: Where to save the file
        column_config: Optional dict of column key -> config overrides
        formulas: Optional dict of column key -> formula template
    """
    column_config = column_config or {}
    formulas = formulas or {}

    # Build columns array
    columns = []
    for col_name in df.columns:
        col_def = {
            "key": col_name.lower().replace(" ", "_"),
            "title": col_name,
            "type": _infer_type(df[col_name].dtype),
        }

        # Apply overrides
        if col_name in column_config:
            col_def.update(column_config[col_name])

        # Apply formula
        if col_name in formulas:
            col_def["formula"] = formulas[col_name]

        columns.append(col_def)

    # Build data array
    data = df.to_dicts()

    # Build spreadsheet structure
    spreadsheet = {
        "metadata": {
            "formatVersion": "1.0",
            "generator": "polars",
            "created": datetime.now().isoformat(),
        },
        "columns": columns,
        "data": data,
    }

    # Write YAML
    with open(output_path, "w") as f:
        yaml.dump(spreadsheet, f, default_flow_style=False, allow_unicode=True)

    return spreadsheet


def _infer_type(dtype) -> str:
    """Infer spreadsheet column type from Polars dtype."""
    dtype_str = str(dtype)
    if "Int" in dtype_str or "Float" in dtype_str:
        return "numeric"
    elif "Date" in dtype_str:
        return "date"
    elif "Bool" in dtype_str:
        return "checkbox"
    return "text"


# Example usage
if __name__ == "__main__":
    # Create data
    budget = pl.DataFrame({
        "Category": ["Talent", "Equipment", "Post-Production", "Marketing"],
        "Budget": [50000, 25000, 15000, 10000],
        "Spent": [12000, 8000, 0, 2000],
    })

    # Generate spreadsheet with calculated column
    generate_spreadsheet_yaml(
        df=budget,
        output_path="data/budget.spreadsheet.yaml",
        column_config={
            "Category": {"readOnly": True, "width": 150},
            "Budget": {"type": "currency"},
            "Spent": {"type": "currency"},
        },
        formulas={
            # Add a Remaining column via formula
        }
    )

budget = budget.with_columns([
    (pl.col("Budget") - pl.col("Spent")).alias("Remaining")
])

columns:
  # ... existing columns ...
  - key: remaining
    title: "Remaining"
    type: currency
    formula: "=B{row}-C{row}"

import polars as pl
from pathlib import Path

def process_csv_files(input_dir: str, output_dir: str):
    """Convert all CSVs in a directory to .spreadsheet.yaml"""
    input_path = Path(input_dir)
    output_path = Path(output_dir)
    output_path.mkdir(exist_ok=True)

    for csv_file in input_path.glob("*.csv"):
        df = pl.read_csv(csv_file)
        output_file = output_path / f"{csv_file.stem}.spreadsheet.yaml"
        generate_spreadsheet_yaml(df, str(output_file))
        print(f"Generated: {output_file}")