How to Build an AI Excel Agent That Reads, Analyses and Updates Spreadsheets

ai-excel-agent/
├── app/
│   ├── __init__.py
│   ├── profiler.py
│   ├── mutator.py
│   └── agent.py
├── tests/
│   └── test_excel_agent.py
└── requirements.txt

pip install openpyxl==3.1.5 pandas==2.2.3 pydantic==2.9.2 openai==1.51.0 pytest==8.3.3

from typing import Dict, Any, List
import openpyxl
from openpyxl.utils import get_column_letter
import pandas as pd

def profile_workbook(file_path: str, max_sample_rows: int = 3) -> Dict[str, Any]:
    """Extracts exact 1-indexed sheet coordinates, column letters, headers, and existing formulas."""
    wb_formulas = openpyxl.load_workbook(file_path, data_only=False)
    wb_values = openpyxl.load_workbook(file_path, data_only=True)
    profile: Dict[str, Any] = {"file_path": file_path, "sheets": {}}

    for sheet_name in wb_formulas.sheetnames:
        ws_f = wb_formulas[sheet_name]
        ws_v = wb_values[sheet_name]
        max_row = ws_f.max_row
        max_col = ws_f.max_column

        headers: List[Dict[str, str]] = []
        for col_idx in range(1, max_col + 1):
            col_letter = get_column_letter(col_idx)
            header_val = ws_v.cell(row=1, column=col_idx).value
            headers.append({
                "column_letter": col_letter,
                "header_cell": f"{col_letter}1",
                "header_name": str(header_val) if header_val is not None else f"Unnamed_{col_letter}",
            })

        sample_rows = []
        for row_idx in range(2, min(max_row + 1, 2 + max_sample_rows)):
            row_dict = {}
            for col_idx in range(1, max_col + 1):
                col_letter = get_column_letter(col_idx)
                f_val = ws_f.cell(row=row_idx, column=col_idx).value
                v_val = ws_v.cell(row=row_idx, column=col_idx).value
                row_dict[f"{col_letter}{row_idx}"] = {
                    "value": v_val,
                    "formula": f_val if isinstance(f_val, str) and f_val.startswith("=") else None,
                }
            sample_rows.append(row_dict)

        profile["sheets"][sheet_name] = {
            "max_row": max_row,
            "max_column": max_col,
            "data_row_range": f"2:{max_row}" if max_row >= 2 else "None",
            "headers": headers,
            "sample_rows": sample_rows,
        }

    wb_formulas.close()
    wb_values.close()
    return profile

def run_pandas_summary(file_path: str, sheet_name: str) -> Dict[str, Any]:
    """Computes descriptive statistics on numeric columns using Pandas without modifying the file."""
    df = pd.read_excel(file_path, sheet_name=sheet_name)
    numeric_summary = df.describe().to_dict()
    missing_counts = df.isna().sum().to_dict()
    return {
        "row_count": int(len(df)),
        "missing_values_per_column": {str(k): int(v) for k, v in missing_counts.items()},
        "numeric_statistics": numeric_summary,
    }

import re
import shutil
from pathlib import Path
from typing import List, Dict, Any, Optional
import openpyxl
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import BarChart, Reference
from pydantic import BaseModel, Field

EXCEL_ERROR_TOKENS = {"#REF!", "#NAME?", "#VALUE!", "#DIV/0!", "#N/A", "#NUM!", "#NULL!"}
ALLOWED_FORMULA_PATTERN = re.compile(r"^=[A-Z0-9_]+\(.*\)$|^=[A-Z]+[0-9]+(\s*[\+\-\*\/]\s*[A-Z0-9\.]+)+$", re.IGNORECASE)

class CellWriteOperation(BaseModel):
    sheet_name: str
    cell_coord: str = Field(..., pattern=r"^[A-Z]{1,3}[1-9][0-9]*$")
    value: Any
    is_formula: bool = False
    number_format: Optional[str] = None
    bold: bool = False

class ChartSpec(BaseModel):
    sheet_name: str
    title: str
    data_col_idx: int
    categories_col_idx: int
    min_row: int
    max_row: int
    anchor_cell: str = "H2"

def validate_formula_syntax(formula_str: str) -> None:
    """Validates balanced parentheses, leading '=', and blocks dangerous DDE injection patterns."""
    if not isinstance(formula_str, str) or not formula_str.startswith("="):
        raise ValueError(f"Formula must start with '=': {formula_str}")
    if any(token in formula_str.upper() for token in ("CMD|", "POWERSHELL", "WEBSERVICE(")):
        raise ValueError(f"Blocked unsafe external/DDE formula: {formula_str}")
    if formula_str.count("(") != formula_str.count(")"):
        raise ValueError(f"Unbalanced parentheses in formula: {formula_str}")
    if not ALLOWED_FORMULA_PATTERN.match(formula_str.strip()):
        raise ValueError(f"Formula does not match valid Excel expression syntax: {formula_str}")

def apply_workbook_operations(
    source_path: str,
    target_path: str,
    operations: List[CellWriteOperation],
    chart_spec: Optional[ChartSpec] = None,
    allow_overwrite_formulas: bool = False,
) -> Dict[str, Any]:
    """Clones source_path to target_path and applies audited cell/formula/chart operations."""
    shutil.copy2(source_path, target_path)
    wb = openpyxl.load_workbook(target_path)
    audit_log: List[Dict[str, Any]] = []

    for op in operations:
        if op.sheet_name not in wb.sheetnames:
            raise ValueError(f"Sheet '{op.sheet_name}' does not exist in workbook.")
        ws = wb[op.sheet_name]
        existing_val = ws[op.cell_coord].value

        if (
            isinstance(existing_val, str)
            and existing_val.startswith("=")
            and not allow_overwrite_formulas
        ):
            raise PermissionError(
                f"Refusing to overwrite existing formula '{existing_val}' in {op.sheet_name}!{op.cell_coord}."
            )

        if op.is_formula:
            validate_formula_syntax(str(op.value))
            write_val = str(op.value)
        else:
            # Prevent accidental CSV/DDE injection in text cells
            if isinstance(op.value, str) and op.value.startswith(("=", "+", "-", "@")):
                write_val = "'" + op.value
            else:
                write_val = op.value

        ws[op.cell_coord].value = write_val
        if op.number_format:
            ws[op.cell_coord].number_format = op.number_format
        if op.bold:
            ws[op.cell_coord].font = Font(bold=True)

        audit_log.append({
            "sheet": op.sheet_name,
            "cell": op.cell_coord,
            "previous_value": existing_val,
            "new_value": write_val,
        })

    if chart_spec:
        ws = wb[chart_spec.sheet_name]
        chart = BarChart()
        chart.title = chart_spec.title
        chart.style = 10
        data_ref = Reference(
            ws,
            min_col=chart_spec.data_col_idx,
            min_row=chart_spec.min_row,
            max_row=chart_spec.max_row,
        )
        cats_ref = Reference(
            ws,
            min_col=chart_spec.categories_col_idx,
            min_row=chart_spec.min_row + 1,
            max_row=chart_spec.max_row,
        )
        chart.add_data(data_ref, titles_from_data=True)
        chart.set_categories(cats_ref)
        ws.add_chart(chart, chart_spec.anchor_cell)

    wb.save(target_path)
    wb.close()
    return {"target_path": target_path, "changes_applied": len(audit_log), "audit_log": audit_log}

import os
import openpyxl
import pytest
from app.profiler import profile_workbook, run_pandas_summary
from app.mutator import (
    CellWriteOperation,
    ChartSpec,
    apply_workbook_operations,
    validate_formula_syntax,
)

def create_sample_workbook(path: str):
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "RegionalSales"
    ws.append(["Region", "Revenue", "COGS"])
    ws.append(["North", 120000, 70000])
    ws.append(["South", 95000, 52000])
    ws.append(["West", 150000, 85000])
    wb.save(path)
    wb.close()

def test_excel_agent_profile_and_safe_mutation(tmp_path):
    src = str(tmp_path / "source.xlsx")
    dst = str(tmp_path / "updated.xlsx")
    create_sample_workbook(src)

    profile = profile_workbook(src)
    assert "RegionalSales" in profile["sheets"]
    assert profile["sheets"]["RegionalSales"]["max_row"] == 4

    stats = run_pandas_summary(src, "RegionalSales")
    assert stats["row_count"] == 3

    ops = [
        CellWriteOperation(sheet_name="RegionalSales", cell_coord="D1", value="Gross Profit", bold=True),
        CellWriteOperation(sheet_name="RegionalSales", cell_coord="D2", value="=B2-C2", is_formula=True, number_format="$#,##0"),
        CellWriteOperation(sheet_name="RegionalSales", cell_coord="D3", value="=B3-C3", is_formula=True, number_format="$#,##0"),
        CellWriteOperation(sheet_name="RegionalSales", cell_coord="D4", value="=B4-C4", is_formula=True, number_format="$#,##0"),
        CellWriteOperation(sheet_name="RegionalSales", cell_coord="A5", value="Total", bold=True),
        CellWriteOperation(sheet_name="RegionalSales", cell_coord="D5", value="=SUM(D2:D4)", is_formula=True, number_format="$#,##0", bold=True),
    ]
    chart = ChartSpec(
        sheet_name="RegionalSales",
        title="Gross Profit by Region",
        data_col_idx=4,
        categories_col_idx=1,
        min_row=1,
        max_row=4,
        anchor_cell="F2",
    )
    result = apply_workbook_operations(src, dst, ops, chart_spec=chart)
    assert result["changes_applied"] == 6

    # Verify formula protection blocks accidental overwrite of D5
    with pytest.raises(PermissionError):
        apply_workbook_operations(
            dst,
            str(tmp_path / "fail.xlsx"),
            [CellWriteOperation(sheet_name="RegionalSales", cell_coord="D5", value=999)],
            allow_overwrite_formulas=False,
        )

def test_formula_validation_blocks_dde_and_unbalanced_parens():
    with pytest.raises(ValueError):
        validate_formula_syntax("=SUM(B2:B5")
    with pytest.raises(ValueError):
        validate_formula_syntax("=CMD|'/C calc'!A0")