xllify 0.9.10
Python SDK for creating Excel XLL add-ins with xllify
License: MIT
Requires Python: >=3.10
Keywords: excel, xll, udf, add-in, finance

# xllify Python SDK

A Python SDK for creating high-performance Excel add-ins with xllify. Write Python functions and call them from Excel with automatic type conversion, error handling, and real-time updates.

## Installation

You it is strongly recommended that you create a virtual environment and activate it.

```bash
pip install virtualenv # if you don't have it
virtualenv venv
source venv/bin/activate # mac OR
source venv\Scripts\activate # win
```

Then install xllify.

```bash
pip install xllify
xllify-install
```

## Quick Start

### 1. Create your Python functions

```python
# my_functions.py
import xllify

@xllify.fn("xllipy.Hello")
def hello(name: str = "World") -> str:
    return f"Hello, {name}!"

@xllify.fn("xllipy.Add", category="Math")
def add(a: float, b: float) -> float:
    return a + b
```

### 2. Build

```bash
xllify MyAddin.xll my_functions.py
```

### 3. Use in Excel

Functions are immediately available after you open the .xll in Excel.

```
=xllipy.Hello("World")       -> "Hello, World!"
=xllipy.Add(5, 10)           -> 15
```

Functions execute asynchronously - Excel shows #N/A while processing, then updates automatically when complete.

## Workflow

### Build and deployment

For production, Python files are embedded directly into your XLL:

```bash
xllify build MyAddin.xll main.py --requirements requirements.txt
```

When the XLL loads in Excel, Python files are extracted to `%LOCALAPPDATA%\xllify\MyAddin\python\` and the Python process starts automatically.

### Distribution

Just distribute the XLL file. Everything else (Python files, dependencies) is embedded and will be extracted automatically on first load.

## Features

- **Async by default** - Functions run asynchronously so Excel never freezes
- **Auto-reload** - Hot reload functions during development without restarting Excel
- **Matrix support** - Return 2D arrays and pandas DataFrames directly to Excel
- **Type-safe** - Full type hints with `CellValue`, `Matrix`, and `ExcelValue`
- **Simple API** - Just add the `@xllify.fn()` decorator to your existing functions

## Best practices

Our recommended approach is for you to implement your business logic in plain Python modules and call them from short functions wrapped with `@xllify.fn`. This keeps your core logic testable, reusable, and independent of  Excel.

```python
# my_logic.py - Pure Python, no xllify dependencies
def calculate_option_price(spot, strike, time, rate, volatility):
    """Black-Scholes call option pricing - testable business logic"""
    # ... implementation ...
    return price

# excel_functions.py - Thin xllify wrapper
import xllify
from my_logic import calculate_option_price

@xllify.fn("xllipy.BSCall", category="Finance")
def bs_call(s: float, k: float, t: float, r: float, sigma: float) -> float:
    """Excel wrapper for Black-Scholes calculation"""
    return calculate_option_price(s, k, t, r, sigma)
```

Benefits:
- **Testable**: Run `pytest` on `my_logic.py` without Excel or xllify
- **Reusable**: Use the same logic in web apps, CLIs, or other contexts without needing to import xllify
- **Maintainable**: Separate concerns between business logic and Excel integration
- **Debuggable**: Test and debug core logic independently

## Advanced usage

### Process pooling

Configure how many Python processes to spawn for parallel execution:

```python
import xllify

# Spawn 3 Python processes to handle requests in parallel
xllify.configure_spawn_count(3)

@xllify.fn("xllipy.SlowCalc")
def slow_calc(seconds: float) -> str:
    import time
    time.sleep(seconds)
    return f"Done after {seconds}s"
```

**When to use multiple processes:**
- **Blocking operations**: Multiple processes prevent one slow function from blocking others
- **CPU-intensive tasks**: Parallelize across CPU cores for better throughput
- **High concurrency**: Handle many simultaneous Excel formula evaluations

**Guidelines:**
- Default is 1 process (single-threaded)
- Recommended: 2-4 processes for typical workloads
- More processes = more memory overhead but better concurrency
- Each process handles requests independently

### Batching configuration

By default, xllify batches RTD updates for better performance (batch_size=500, batch_timeout_ms=50). You can customize this:

```python
import xllify

# Configure batching before registering functions
xllify.configure_batching(
    enabled=True,
    batch_size=1000,        # Batch up to 1000 updates together
    batch_timeout_ms=100    # Wait up to 100ms before flushing
)

@xllify.fn("xllipy.Hello")
def hello(name: str) -> str:
    return f"Hello, {name}!"
```

**When to adjust batching:**
- **High-volume scenarios**: Increase `batch_size` (1000+) and `batch_timeout_ms` (100+) for better throughput when handling many concurrent calculations
- **Low-latency requirements**: Decrease `batch_timeout_ms` (10-20ms) or disable batching entirely for faster individual responses
- **Balanced performance**: Use defaults (batch_size=500, batch_timeout_ms=50)

**Disable batching:**
```python
xllify.configure_batching(enabled=False)  # Send updates immediately
```

### Parameter metadata

Provide detailed parameter information for better documentation:

```python
from xllify import fn, Parameter

@fn(
    "xllipy.Calculate",
    description="Perform calculation with optional delay",
    category="Math",
    parameters=[
        Parameter("value", type="number", description="Value to process"),
        Parameter("delay", type="number", description="Delay in seconds (optional)")
    ],
    return_type="number"
)
def calculate(value: float, delay: float = 1.0) -> float:
    """Process a value with optional delay"""
    import time
    if delay > 0:
        time.sleep(delay)
    return value * 2
```

### Working with arrays and matrices

Excel ranges are passed as 2D lists. You can also **return** 1D arrays or 2D matrices to Excel:

```python
from xllify import Matrix

# Input: Accept ranges as 2D lists
@xllify.fn("xllipy.SumArray", description="Sum all numbers in a range")
def sum_array(numbers: list) -> float:
    """Sum a 2D array from Excel range"""
    total = 0.0
    for row in numbers:
        for cell in row:
            if isinstance(cell, (int, float)):
                total += cell
    return total

# Output: Return 1D arrays (displayed as single row)
@xllify.fn("xllipy.GetRow")
def get_row() -> list:
    """Return a 1D array to Excel"""
    return [1, 2, 3, 4, 5]  # Spills as 1x5 row in Excel

# Output: Return 2D matrices
@xllify.fn("xllipy.GetData")
def get_data() -> Matrix:
    """Return a 2D array to Excel"""
    return [
        [1.0, True, "hello"],
        [2.0, False, None],    # None displays as empty cell
        [3.0, None, "world"]
    ]
```

In Excel:
```
=xllipy.SumArray(A1:C10)           -> Sum of all numbers
=xllipy.GetRow()                   -> Spills 1x5 array (single row)
=xllipy.GetData()                  -> Spills 3x3 array into cells
```

### Pandas DataFrames

Return DataFrames directly - automatically converted to Excel ranges with headers:

```python
import pandas as pd

@xllify.fn("xllipy.GetDataFrame")
def get_dataframe() -> pd.DataFrame:
    """Return pandas DataFrame to Excel"""
    return pd.DataFrame({
        'Name': ['Alice', 'Bob', 'Charlie'],
        'Age': [25, 30, 35],
        'Score': [95.5, 87.3, 92.1]
    })
```

In Excel, `=xllipy.GetDataFrame()` spills as:
```
Name      Age    Score
Alice     25     95.5
Bob       30     87.3
Charlie   35     92.1
```

### Type mapping

| Excel Type | Python Type |
|------------|-------------|
| Number | `float` |
| String | `str` |
| Boolean | `bool` |
| Range | `List[List]` (2D array) |
| Empty | `None` |

## API reference

### Type definitions

```python
from xllify import CellValue, Matrix, ExcelValue

CellValue = Union[float, bool, str, None]
Matrix = List[List[CellValue]]
ExcelValue = Union[CellValue, Matrix, Any]
```

- **CellValue**: A single Excel cell value (number, boolean, string, or None for empty cells)
- **Matrix**: A 2D array of cell values
- **ExcelValue**: Any value that can be returned to Excel (scalar, matrix, or pandas DataFrame)

### Decorators

#### `@xllify.fn(name, description="", category="", parameters=None, return_type="")`

Register a Python function as an Excel function.

**Arguments:**
- `name` (str): Excel function name (e.g., "xllipy.MyFunc")
- `description` (str, optional): Function description (defaults to docstring)
- `category` (str, optional): Excel function category
- `parameters` (List[Parameter], optional): Parameter metadata
- `return_type` (str, optional): Return type override

**Example:**
```python
@xllify.fn("xllipy.Add", description="Add numbers", category="Math")
def add(a: float, b: float) -> float:
    return a + b
```

### CLI commands

```bash
# Install the xllify tool and xllify-lua
xllify-install

# Clear async function cache
xllify-clear-cache
```

## Error handling

Python exceptions are caught and returned to Excel as error strings:

```python
@xllify.fn("xllipy.Divide")
def divide(a, b):
    if b == 0:
        raise ValueError("Division by zero")
    return a / b
```

In Excel:
```
=xllipy.Divide(10, 0)    -> #ERROR: Division by zero
```

## More examples

### Slow operations (async)

Functions run asynchronously - Excel never freezes:

```python
@xllify.fn("xllipy.SlowCalc")
def slow_calc(seconds: float) -> str:
    import time
    time.sleep(seconds)
    return f"Done after {seconds}s"
```

Excel shows #N/A while waiting, then updates automatically.
> **Note:** Blocking operations will block your Python process. Use `xllify.configure_spawn_count(N)` to run multiple processes for parallel execution. See [Process pooling](#process-pooling) for details.

### Black-Scholes option pricing

```python
from math import log, sqrt, exp, erf

@xllify.fn("xllipy.BSCall", category="Finance")
def black_scholes_call(s: float, k: float, t: float, r: float, sigma: float) -> float:
    """Black-Scholes call option price"""
    if t <= 0:
        return max(s - k, 0)

    d1 = (log(s / k) + (r + 0.5 * sigma ** 2) * t) / (sigma * sqrt(t))
    d2 = d1 - sigma * sqrt(t)

    def norm_cdf(x):
        return 0.5 * (1 + erf(x / sqrt(2)))

    return s * norm_cdf(d1) - k * exp(-r * t) * norm_cdf(d2)
```

Usage: `=xllipy.BSCall(100, 95, 0.25, 0.05, 0.2)`

### HTTP requests

```python
@xllify.fn("xllipy.FetchPrice")
def fetch_price(symbol: str) -> float:
    import requests
    resp = requests.get(f"https://api.example.com/price/{symbol}")
    return resp.json()["price"]
```

### System info

```python
@xllify.fn("xllipy.GetInfo")
def get_info() -> str:
    import sys
    import platform
    return f"Python {sys.version.split()[0]} on {platform.system()}"
```

## Development

### Running tests

```bash
pip install -e ".[dev]"
pytest tests/ -v
```

### Code formatting

```bash
black xllify/ tests/ examples/
```

## License

MIT

## Support

- Issues: https://github.com/xllifycom/xllify-python/issues