tessl/pypi-xlwings

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

Overview

Eval results

Files

Collections and Module Functions

Name: tessl/pypi-xlwings
Author: tessl

Collections for managing multiple Excel objects and module-level functions for high-level operations like loading data and viewing in Excel. These provide convenient access patterns and batch operations across Excel objects.

Capabilities

Collections

xlwings provides several collections that manage multiple Excel objects and provide convenient access patterns. All collections support indexing, iteration, and common collection operations.

# Global collections (imported from xlwings module)
apps: Apps          # Collection of all App instances
books: Books        # Collection of all Book instances  
sheets: Sheets      # Collection of all Sheet instances
engines: Engines    # Collection of available Excel engines

class Collection:
    """Base collection class providing common collection functionality."""
    
    def __call__(self, name_or_index):
        """Access item by name or index (1-based for Excel compatibility)."""
    
    def __len__(self) -> int:
        """Number of items in collection."""
    
    def __iter__(self):
        """Iterate over collection items."""
    
    def __getitem__(self, key):
        """Access item by key (supports both string names and integer indices)."""
    
    def __contains__(self, key) -> bool:
        """Check if item exists in collection."""
    
    @property
    def count(self) -> int:
        """Number of items in collection."""
    
    @property
    def api(self):
        """Access to native Excel collection object."""

class Apps(Collection):
    """Collection of Excel application instances."""
    
    def add(self, **kwargs) -> App:
        """
        Create a new Excel application.
        
        Args:
            **kwargs: Arguments passed to App constructor.
            
        Returns:
            App: New Excel application instance.
        """
    
    @property
    def active(self) -> App:
        """Currently active Excel application."""

class Books(Collection):
    """Collection of Excel workbooks across all applications."""
    
    def add(self, **kwargs) -> Book:
        """
        Create a new workbook.
        
        Args:
            **kwargs: Arguments for workbook creation.
            
        Returns:
            Book: New workbook instance.
        """
    
    def open(self, fullname: str, **kwargs) -> Book:
        """
        Open an existing workbook.
        
        Args:
            fullname (str): Full path to workbook file.
            **kwargs: Additional arguments for opening workbook.
            
        Returns:
            Book: Opened workbook instance.
        """
    
    @property
    def active(self) -> Book:
        """Currently active workbook."""

class Sheets(Collection):
    """Collection of worksheets across all workbooks."""
    
    def add(self, name: str = None, before=None, after=None, **kwargs) -> Sheet:
        """
        Add a new worksheet.
        
        Args:
            name (str, optional): Worksheet name.
            before (Sheet, optional): Sheet to insert before.
            after (Sheet, optional): Sheet to insert after.
            **kwargs: Additional arguments.
            
        Returns:
            Sheet: New worksheet instance.
        """
    
    @property  
    def active(self) -> Sheet:
        """Currently active worksheet."""

class Engines(Collection):
    """Collection of available Excel engines."""
    
    def add(self, engine: Engine):
        """
        Add an engine to the collection.
        
        Args:
            engine (Engine): Engine instance to add.
        """
    
    @property
    def active(self) -> Engine:
        """Currently active engine for new Excel operations."""
    
    @active.setter
    def active(self, engine: Engine): ...

Usage examples:

import xlwings as xw

# Working with apps collection
app1 = xw.apps.add()  # Create new app
app2 = xw.apps.add(visible=True)

print(len(xw.apps))  # Number of app instances
for app in xw.apps:  # Iterate over apps
    print(app.version)

# Working with books collection  
wb1 = xw.books.add()  # Create new workbook
wb2 = xw.books.open('/path/to/existing.xlsx')  # Open existing

active_book = xw.books.active  # Get active workbook
book_by_name = xw.books['MyWorkbook.xlsx']  # Access by name

# Working with sheets collection
ws1 = xw.sheets.add('NewSheet')  # Add new sheet
ws2 = xw.sheets.add('DataSheet', after=ws1)  # Add after another sheet

active_sheet = xw.sheets.active  # Get active sheet
sheet_by_name = xw.sheets['Sheet1']  # Access by name

# Working with engines
print([engine.name for engine in xw.engines])  # List available engines
xw.engines.active = xw.engines['excel']  # Set active engine

Module-Level Functions

High-level functions for common Excel operations that work across different Excel objects and provide simplified interfaces for complex operations.

def load(json: dict, sheet: Sheet = None, header: bool = True, index: bool = True):
    """
    Load JSON data into Excel worksheet.
    
    Args:
        json (dict): Data to load into Excel. Can contain nested structures.
        sheet (Sheet, optional): Target worksheet. Uses active sheet if None.
        header (bool): Whether to include header row. Defaults to True.
        index (bool): Whether to include index column. Defaults to True.
    
    Examples:
        # Load simple data
        data = {'A': [1, 2, 3], 'B': [4, 5, 6]}
        xw.load(data)
        
        # Load to specific sheet
        ws = xw.sheets['MySheet']
        xw.load(data, sheet=ws, header=False)
    """

def view(data, sheet_name: str = None):
    """
    Display data in Excel viewer for inspection and analysis.
    
    Args:
        data: Data to display. Supports pandas DataFrames, NumPy arrays, 
              lists, dictionaries, and other common Python data structures.
        sheet_name (str, optional): Name for the viewer sheet.
    
    Examples:
        import pandas as pd
        import numpy as np
        
        # View DataFrame
        df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
        xw.view(df)
        
        # View NumPy array
        arr = np.random.rand(10, 5)
        xw.view(arr, sheet_name='RandomData')
        
        # View nested list
        matrix = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
        xw.view(matrix)
    """

Usage examples:

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

# Using load() function
sales_data = {
    'Product': ['A', 'B', 'C'],
    'Q1': [100, 150, 200],
    'Q2': [120, 160, 180],
    'Q3': [110, 170, 220],
    'Q4': [130, 155, 210]
}

# Load into active sheet
wb = xw.books.add()
xw.load(sales_data)

# Load into specific sheet without headers
ws = wb.sheets.add('RawData')
xw.load(sales_data, sheet=ws, header=False)

# Using view() function for data exploration
df = pd.DataFrame({
    'Date': pd.date_range('2024-01-01', periods=100),
    'Value': np.random.randn(100).cumsum(),
    'Category': np.random.choice(['A', 'B', 'C'], 100)
})

# Quick view of DataFrame
xw.view(df, sheet_name='TimeSeries')

# View correlation matrix
correlation = df[['Value']].corr()
xw.view(correlation, sheet_name='Correlation')

# View summary statistics
summary = df.describe()
xw.view(summary, sheet_name='Statistics')

Collection Patterns and Best Practices

Common patterns for working with xlwings collections effectively:

# Pattern 1: Safe collection access
def get_or_create_book(name):
    """Get existing book or create new one."""
    try:
        return xw.books[name]
    except KeyError:
        return xw.books.add().save(name)

# Pattern 2: Batch operations on collections
def close_all_books():
    """Close all open workbooks."""
    for book in xw.books:
        book.close()

# Pattern 3: Finding objects by criteria
def find_sheets_by_pattern(pattern):
    """Find sheets matching name pattern."""
    import re
    matching_sheets = []
    for sheet in xw.sheets:
        if re.match(pattern, sheet.name):
            matching_sheets.append(sheet)
    return matching_sheets

# Pattern 4: Engine management
def with_engine(engine_name):
    """Context manager for temporary engine switching."""
    from contextlib import contextmanager
    
    @contextmanager
    def engine_context():
        original = xw.engines.active
        try:
            xw.engines.active = xw.engines[engine_name]
            yield
        finally:
            xw.engines.active = original
    
    return engine_context()

# Usage
with with_engine('calamine'):
    # Operations using Calamine engine
    wb = xw.books.open('large_file.xlsx')
    data = wb.sheets[0].used_range.value

Advanced Collection Operations

# Advanced collection methods and properties
class Books(Collection):
    @property
    def active(self) -> Book:
        """Currently active workbook."""
    
    def add(self, template: str = None) -> Book:
        """
        Add new workbook, optionally from template.
        
        Args:
            template (str, optional): Template file path.
        """

class Sheets(Collection):  
    def add(self, name: str = None, before=None, after=None) -> Sheet:
        """Add worksheet with positioning control."""
    
    def copy(self, before=None, after=None) -> Sheet:
        """Copy active sheet to new position."""

# Engine-specific collections
class Engines(Collection):
    def __getitem__(self, key: str) -> Engine:
        """Get engine by name ('excel', 'calamine', 'remote', etc.)."""
    
    @property  
    def available(self) -> list:
        """List of available engine names."""

Usage examples:

# Advanced workbook operations
template_wb = xw.books.add(template='/path/to/template.xlsx')

# Advanced sheet operations  
data_sheet = xw.sheets.add('Data', before=xw.sheets[0])
copy_sheet = data_sheet.copy(after=data_sheet)

# Engine inspection and management
print(f"Available engines: {xw.engines.available}")
for engine in xw.engines:
    print(f"Engine: {engine.name}, Type: {engine.type}")

# Conditional engine usage
if 'calamine' in xw.engines.available:
    fast_engine = xw.engines['calamine']
    fast_engine.activate()

Types

# Collection type aliases
Apps = Collection[App]
Books = Collection[Book]
Sheets = Collection[Sheet]  
Engines = Collection[Engine]

# Function signatures for type checking
LoadFunction = Callable[[dict, Optional[Sheet], bool, bool], None]
ViewFunction = Callable[[Any, Optional[str]], None]

Install with Tessl CLI