User Defined Functions (UDFs)

def func(f=None, *, category: str = None, volatile: bool = False, 
         call_in_wizard: bool = True, macro_type: str = None):
    """
    Decorator to create Excel functions from Python functions.
    
    Args:
        f (callable, optional): Function to decorate.
        category (str, optional): Excel function category for organization.
        volatile (bool): If True, function recalculates on every calculation cycle.
        call_in_wizard (bool): If True, function appears in Excel Function Wizard.
        macro_type (str, optional): Macro type for advanced scenarios.
    
    Returns:
        callable: Decorated function that can be called from Excel.
    
    Platform Support:
        - Windows: COM server integration
        - Web: Office.js via xlwings PRO
    """

def sub(f=None, *, call_in_wizard: bool = True):
    """
    Decorator to create Excel subroutines from Python functions.
    Subroutines are called as macros and don't return values to cells.
    
    Args:
        f (callable, optional): Function to decorate.
        call_in_wizard (bool): If True, appears in Excel macro list.
    
    Returns:
        callable: Decorated function callable as Excel macro.
    
    Platform Support:
        - Windows only (COM server)
    """

def arg(convert=None, *, ndim: int = None, transpose: bool = False):
    """
    Decorator for UDF argument conversion and validation.
    
    Args:
        convert: Converter class or conversion options.
        ndim (int, optional): Force specific number of dimensions.
        transpose (bool): Transpose input data before processing.
    
    Returns:
        callable: Argument converter decorator.
    """

def ret(convert=None, *, transpose: bool = False, expand: str = None):
    """
    Decorator for UDF return value conversion.
    
    Args:
        convert: Converter class or conversion options.
        transpose (bool): Transpose output data.
        expand (str, optional): Dynamic array expansion ('table', 'down', 'right').
    
    Returns:
        callable: Return value converter decorator.
    """

import xlwings as xw
import numpy as np
import pandas as pd

# Basic UDF function
@xw.func
def hello(name):
    """Simple greeting function callable from Excel."""
    return f"Hello {name}!"

# Function with category and options
@xw.func(category="Math", volatile=False)
def multiply_by_two(x):
    """Multiply input by 2."""
    return x * 2

# Array function with converters
@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.ret(np.array)
def matrix_multiply(x, y):
    """Matrix multiplication using NumPy."""
    return np.dot(x, y)

# DataFrame function
@xw.func
@xw.arg('data', pd.DataFrame, index=False, header=True)
@xw.ret(pd.DataFrame, expand='table')
def process_dataframe(data):
    """Process DataFrame and return results."""
    result = data.copy()
    result['total'] = result.sum(axis=1)
    return result

# Subroutine (macro)
@xw.sub
def clear_sheet():
    """Clear active worksheet content."""
    xw.sheets.active.clear_contents()

# Advanced UDF with multiple converters
@xw.func
@xw.arg('prices', np.array, ndim=1)
@xw.arg('volumes', np.array, ndim=1) 
@xw.ret(expand='down')
def calculate_vwap(prices, volumes):
    """Calculate Volume Weighted Average Price."""
    return np.sum(prices * volumes) / np.sum(volumes)

def serve(clsid: str = "{506e67c3-55b5-48c3-a035-eed5deea7d6d}"):
    """
    Start the COM server for UDFs on Windows.
    Must be called to enable UDF functionality.
    
    Args:
        clsid (str): COM class ID for the server. Default is xlwings' standard ID.
    
    Platform: Windows only
    
    Usage:
        if __name__ == '__main__':
            xw.serve()
    """

def import_udfs(module_names: str, xl_workbook):
    """
    Import UDF functions from Python modules.
    
    Args:
        module_names (str): Semicolon-separated module names containing UDF functions.
        xl_workbook: Excel workbook object (internal parameter).
    
    Note: This function is typically called internally by xlwings.
    
    Examples:
        # Import UDFs from multiple modules
        xw.import_udfs('my_udfs;other_udfs', workbook)
    """

def get_udf_module(module_name: str, xl_workbook):
    """
    Get reference to UDF module for introspection.
    
    Args:
        module_name (str): Name of UDF module.
        xl_workbook: Excel workbook object (internal parameter).
    
    Returns:
        dict: Module information including the Python module object.
    """

# Pattern 1: Error handling in UDFs
@xw.func
def safe_divide(a, b):
    """Division with error handling."""
    try:
        if b == 0:
            return "#DIV/0!"
        return a / b
    except Exception as e:
        return f"#ERROR: {str(e)}"

# Pattern 2: Optional parameters
@xw.func
def format_currency(amount, currency="USD", decimals=2):
    """Format number as currency with optional parameters."""
    if currency == "USD":
        symbol = "$"
    elif currency == "EUR":
        symbol = "€"
    else:
        symbol = currency
    
    return f"{symbol}{amount:.{decimals}f}"

# Pattern 3: Range processing
@xw.func  
@xw.arg('data', np.array, ndim=2)
@xw.ret(expand='table')
def process_range(data, operation="sum"):
    """Process 2D range with specified operation."""
    if operation == "sum":
        return np.sum(data, axis=1).reshape(-1, 1)
    elif operation == "mean":
        return np.mean(data, axis=1).reshape(-1, 1)
    elif operation == "std":
        return np.std(data, axis=1).reshape(-1, 1)
    else:
        return data

# Pattern 4: Caching expensive operations
_cache = {}

@xw.func(volatile=False)
def expensive_calculation(input_value):
    """Cache expensive calculations."""
    if input_value in _cache:
        return _cache[input_value]
    
    # Simulate expensive operation
    import time
    time.sleep(0.1)
    result = input_value ** 2
    
    _cache[input_value] = result
    return result

# Pattern 5: Database integration
@xw.func
def query_database(sql_query, connection_string):
    """Execute SQL query and return results."""
    import sqlite3
    
    try:
        conn = sqlite3.connect(connection_string)
        cursor = conn.cursor()
        cursor.execute(sql_query)
        results = cursor.fetchall()
        conn.close()
        return results
    except Exception as e:
        return f"#ERROR: {str(e)}"

# Async UDF (Windows only)
@xw.func
async def async_web_request(url):
    """Asynchronous web request UDF."""
    import aiohttp
    
    async with aiohttp.ClientSession() as session:
        async with session.get(url) as response:
            return await response.text()

# Caller context UDF
@xw.func
def get_caller_info():
    """Get information about the calling cell."""
    caller = xw.caller()  # Special function to get caller context
    return f"Called from {caller.address}"

# Dynamic array UDF (Excel 365/2021)
@xw.func
@xw.ret(expand='table')
def generate_multiplication_table(size):
    """Generate multiplication table as dynamic array."""
    import numpy as np
    
    if not isinstance(size, (int, float)) or size <= 0:
        return "#ERROR: Size must be positive number"
    
    size = int(size)
    table = np.outer(np.arange(1, size + 1), np.arange(1, size + 1))
    return table

# Real-time data UDF
@xw.func(volatile=True)
def get_stock_price(symbol):
    """Get real-time stock price (volatile function)."""
    import requests
    
    try:
        # Simulated API call
        url = f"https://api.example.com/stock/{symbol}"
        response = requests.get(url, timeout=5)
        data = response.json()
        return data.get('price', '#N/A')
    except:
        return '#N/A'

# udfs.py - Main UDF module
import xlwings as xw
import numpy as np
import pandas as pd

@xw.func
def my_function(x):
    return x * 2

# Server startup
if __name__ == '__main__':
    # Start COM server for UDFs
    xw.serve()

# setup.py for UDF package distribution
from setuptools import setup

setup(
    name='my-excel-udfs',
    version='1.0.0',
    py_modules=['udfs'],
    install_requires=['xlwings', 'numpy', 'pandas'],
    entry_points={
        'console_scripts': [
            'start-udfs=udfs:main',
        ],
    },
)

' In Excel VBA (ThisWorkbook or Module)
Sub ImportPythonUDFs()
    ' Import UDFs from Python module
    Application.Run "xlwings.import_udfs", "udfs"
End Sub

Sub StartUDFServer()
    ' Start Python UDF server
    Shell "python -c ""import udfs; udfs.serve()"""
End Sub

# Windows-specific UDF features
@xw.func
def windows_specific_function():
    """Function available only on Windows."""
    import win32api
    return win32api.GetComputerName()

# COM server configuration
if __name__ == '__main__':
    # Register COM server
    import sys
    if len(sys.argv) > 1 and sys.argv[1] == '--register':
        import win32com.server.register
        win32com.server.register.UseCommandLine()
    else:
        xw.serve()

# Office.js UDFs (PRO version)
from xlwings.server import func, arg, ret

@func
@arg('data', convert='dataframe')
@ret(expand='table')
def web_compatible_function(data):
    """Function that works in both desktop and web Excel."""
    return data.describe()

# UDF decorator types
UDFDecorator = Callable[[Callable], Callable]
ArgDecorator = Callable[[Callable], Callable]  
RetDecorator = Callable[[Callable], Callable]

# UDF function signatures
UDFFunction = Callable[..., Any]
SubroutineFunction = Callable[..., None]

# Server management
ServerFunction = Callable[[], None]
ImportFunction = Callable[[str], None]
GetModuleFunction = Callable[[str], Any]