tessl/pypi-xlwings

Python library for seamless bidirectional communication between Python and Excel across Windows and macOS platforms.

Overview

Eval results

Files

Utility Functions

Name: tessl/pypi-xlwings
Author: tessl

Helper functions for data type conversion, configuration management, and Excel-specific operations like date/time handling. These utilities support the core xlwings functionality and provide convenient helpers for common operations.

Capabilities

Date and Time Utilities

Functions for converting between Excel's date serial format and Python datetime objects.

def to_datetime(xldate: float, datemode: int = 0):
    """
    Convert Excel serial date to Python datetime.
    
    Args:
        xldate (float): Excel serial date number.
        datemode (int): Date mode (0 for 1900 system, 1 for 1904 system).
    
    Returns:
        datetime: Python datetime object.
        
    Examples:
        # Convert Excel date serial to datetime
        dt = xw.to_datetime(44197)  # 2021-01-01
        
        # Handle 1904 date system (Mac)
        dt_mac = xw.to_datetime(43831, datemode=1)
    """

# Additional date utilities in xlwings.utils module
def np_datetime_to_xl(np_date):
    """
    Convert NumPy datetime to Excel serial format.
    
    Args:
        np_date: NumPy datetime64 object.
        
    Returns:
        float: Excel serial date.
    """

def pd_datetime_to_xl(pd_date):
    """
    Convert pandas datetime to Excel serial format.
    
    Args:  
        pd_date: pandas Timestamp or datetime.
        
    Returns:
        float: Excel serial date.
    """

def datetime_to_xl(dt):
    """
    Convert Python datetime to Excel serial format.
    
    Args:
        dt (datetime): Python datetime object.
        
    Returns:
        float: Excel serial date.
    """

Data Preparation Utilities

Functions for preparing and validating data before Excel operations.

def prepare_xl_data_value(value):
    """
    Prepare Python data for Excel consumption.
    
    Args:
        value: Python data structure to prepare.
        
    Returns:
        Prepared data suitable for Excel ranges.
        
    Description:
        Handles type conversion, None values, and data structure
        normalization for optimal Excel compatibility.
    """

def get_duplicates(seq):
    """
    Find duplicate items in a sequence.
    
    Args:
        seq: Sequence to check for duplicates.
        
    Returns:
        list: List of duplicate items.
        
    Examples:
        dupes = get_duplicates(['a', 'b', 'a', 'c', 'b'])
        # Returns: ['a', 'b']
    """

Performance and Caching Utilities

Utilities for optimizing xlwings performance through caching and call optimization.

def get_cache():
    """
    Get xlwings internal cache for performance optimization.
    
    Returns:
        dict: Cache dictionary for storing computed values.
        
    Description:
        Used internally by xlwings for caching expensive operations
        like engine initialization and object references.
    """

def log_call(func):
    """
    Decorator for logging function calls (debugging utility).
    
    Args:
        func (callable): Function to wrap with logging.
        
    Returns:
        callable: Wrapped function with call logging.
        
    Examples:
        @log_call
        def my_function(x, y):
            return x + y
    """

Configuration and Environment Utilities

Functions for managing xlwings configuration and environment settings.

import xlwings as xw

# Configuration file locations (from xlwings.__init__)
# USER_CONFIG_FILE: Platform-specific user configuration path
# - macOS: ~/Library/Containers/com.microsoft.Excel/Data/xlwings.conf  
# - Other: ~/.xlwings/xlwings.conf

# Usage examples
def setup_xlwings_config():
    """Setup xlwings configuration programmatically."""
    import configparser
    import os
    
    config = configparser.ConfigParser()
    
    # Set default configuration
    config['xlwings'] = {
        'INTERPRETER': 'python',
        'INTERPRETER_MAC': 'python',
        'PYTHONPATH': '',
        'LOG_FILE': '',
        'SHOW_CONSOLE': 'False'
    }
    
    # Write configuration
    config_path = xw.USER_CONFIG_FILE
    os.makedirs(os.path.dirname(config_path), exist_ok=True)
    
    with open(config_path, 'w') as f:
        config.write(f)

def read_xlwings_config():
    """Read current xlwings configuration."""
    import configparser
    
    config = configparser.ConfigParser()
    try:
        config.read(xw.USER_CONFIG_FILE)
        return dict(config['xlwings'])
    except:
        return {}

Data Validation and Type Checking

Utilities for validating data types and structures before Excel operations.

# Custom validation utilities (usage patterns)
def validate_range_data(data):
    """Validate data is suitable for Excel range assignment."""
    if data is None:
        return True
    
    # Check for supported types
    if isinstance(data, (int, float, str, bool)):
        return True
    
    # Check for sequences
    if hasattr(data, '__iter__') and not isinstance(data, str):
        # Validate nested structure
        try:
            # Ensure rectangular structure for 2D data
            if isinstance(data, list) and len(data) > 0:
                if isinstance(data[0], list):
                    first_len = len(data[0])
                    return all(len(row) == first_len for row in data)
            return True
        except:
            return False
    
    # Check pandas/numpy objects
    try:
        import pandas as pd
        import numpy as np
        if isinstance(data, (pd.DataFrame, pd.Series, np.ndarray)):
            return True
    except ImportError:
        pass
    
    return False

def normalize_range_address(address):
    """Normalize range address to standard format."""
    import re
    
    # Handle various address formats
    if isinstance(address, tuple):
        # Convert (row, col) to A1 notation
        row, col = address
        col_letter = ''
        while col > 0:
            col -= 1
            col_letter = chr(65 + col % 26) + col_letter
            col //= 26
        return f"{col_letter}{row}"
    
    # Clean up string addresses
    if isinstance(address, str):
        # Remove spaces and normalize
        return re.sub(r'\s+', '', address.upper())
    
    return str(address)

def get_range_dimensions(data):
    """Get dimensions of data for range sizing."""
    if data is None:
        return (1, 1)
    
    # Scalar values
    if isinstance(data, (int, float, str, bool)):
        return (1, 1)
    
    # 2D data (list of lists)
    if isinstance(data, list) and len(data) > 0:
        if isinstance(data[0], list):
            return (len(data), len(data[0]) if data[0] else 0)
        else:
            return (len(data), 1)
    
    # pandas DataFrame
    try:
        import pandas as pd
        if isinstance(data, pd.DataFrame):
            return data.shape
        elif isinstance(data, pd.Series):
            return (len(data), 1)
    except ImportError:
        pass
    
    # NumPy array
    try:
        import numpy as np
        if isinstance(data, np.ndarray):
            if data.ndim == 1:
                return (len(data), 1)
            elif data.ndim == 2:
                return data.shape
    except ImportError:
        pass
    
    # Default for unknown types
    return (1, 1)

Error Handling Utilities

Helper functions for robust error handling in xlwings operations.

def safe_excel_operation(operation, *args, **kwargs):
    """Safely execute Excel operation with error handling."""
    try:
        return operation(*args, **kwargs)
    except Exception as e:
        # Log error and provide meaningful feedback
        error_msg = f"Excel operation failed: {str(e)}"
        print(f"WARNING: {error_msg}")
        return None

def retry_excel_operation(operation, max_retries=3, delay=0.1):
    """Retry Excel operation with exponential backoff."""
    import time
    
    for attempt in range(max_retries):
        try:
            return operation()
        except Exception as e:
            if attempt == max_retries - 1:
                raise e
            time.sleep(delay * (2 ** attempt))
    
def with_excel_error_handling(func):
    """Decorator for comprehensive Excel error handling."""
    from functools import wraps
    
    @wraps(func)
    def wrapper(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except Exception as e:
            # Handle common Excel errors
            error_type = type(e).__name__
            if 'COM' in error_type:
                raise XlwingsError(f"Excel COM error: {str(e)}")
            elif 'AppleScript' in error_type:
                raise XlwingsError(f"Excel AppleScript error: {str(e)}")
            else:
                raise XlwingsError(f"Excel operation error: {str(e)}")
    
    return wrapper

Performance Monitoring Utilities

Tools for monitoring and optimizing xlwings performance.

def measure_excel_performance(func):
    """Decorator to measure Excel operation performance."""
    import time
    from functools import wraps
    
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        
        duration = end_time - start_time
        print(f"{func.__name__} took {duration:.3f} seconds")
        return result
    
    return wrapper

def profile_excel_operations(operations):
    """Profile multiple Excel operations and report performance."""
    import time
    
    results = {}
    
    for name, operation in operations.items():
        start_time = time.time()
        try:
            operation()
            duration = time.time() - start_time
            results[name] = {'success': True, 'duration': duration}
        except Exception as e:
            duration = time.time() - start_time
            results[name] = {'success': False, 'duration': duration, 'error': str(e)}
    
    return results

Usage Patterns

Common utility usage patterns for xlwings applications:

import xlwings as xw
from datetime import datetime

# Pattern 1: Safe data operations
@with_excel_error_handling
def safe_data_write(sheet, address, data):
    """Safely write data with validation."""
    if validate_range_data(data):
        sheet.range(address).value = data
    else:
        raise ValueError("Invalid data format for Excel range")

# Pattern 2: Performance monitoring
@measure_excel_performance
def bulk_data_operation(workbook):
    """Monitor performance of bulk operations."""
    for i, sheet in enumerate(workbook.sheets):
        # Simulated bulk operation
        data = [[j + i for j in range(100)] for _ in range(100)]
        sheet.range('A1').value = data

# Pattern 3: Configuration management
def setup_optimal_excel_config():
    """Configure Excel for optimal performance."""
    for app in xw.apps:
        app.calculation = 'manual'
        app.screen_updating = False
        app.display_alerts = False
    
    # Restore at end of operations
    def restore_config():
        for app in xw.apps:
            app.calculation = 'automatic'
            app.screen_updating = True
            app.display_alerts = True
    
    return restore_config

# Usage
restore = setup_optimal_excel_config()
try:
    # Perform bulk operations
    pass
finally:
    restore()

Types

# Utility function types
DateConversionFunction = Callable[[float, int], datetime]
DataPreparationFunction = Callable[[Any], Any]
ValidationFunction = Callable[[Any], bool]
CacheFunction = Callable[[], dict]
LoggingDecorator = Callable[[Callable], Callable]

# Configuration types
ConfigDict = dict[str, str]
ConfigPath = str

# Performance monitoring types
PerformanceResult = dict[str, Union[bool, float, str]]
OperationDict = dict[str, Callable[[], Any]]

Install with Tessl CLI