tessl/pypi-xlwings

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

Overview

Eval results

Files

Data Conversion System

Name: tessl/pypi-xlwings
Author: tessl

Flexible framework for converting data between Python and Excel formats, with built-in support for pandas, NumPy, and custom conversion pipelines. The conversion system handles the complex task of translating between Excel's cell-based data model and Python's rich data types.

Capabilities

Base Converter Framework

The conversion system is built around converter classes that handle reading from and writing to Excel ranges with configurable options.

# Available in xlwings.conversion module
class Converter:
    """Base converter class for custom data transformations."""
    
    def __init__(self, **options):
        """
        Initialize converter with options.
        
        Args:
            **options: Converter-specific configuration options.
        """
    
    def read_value(self, value, options):
        """
        Convert Excel data to Python format.
        
        Args:
            value: Raw data from Excel range.
            options: Conversion options dictionary.
            
        Returns:
            Converted Python data structure.
        """
    
    def write_value(self, value, options):
        """
        Convert Python data to Excel format.
        
        Args:
            value: Python data to convert.
            options: Conversion options dictionary.
            
        Returns:
            Data structure suitable for Excel range.
        """

class RawConverter(Converter):
    """No conversion - pass data through unchanged."""
    
class DictConverter(Converter):
    """Convert between Excel ranges and Python dictionaries."""

class NumpyArrayConverter(Converter):
    """Convert between Excel ranges and NumPy arrays."""

class PandasDataFrameConverter(Converter):
    """Convert between Excel ranges and pandas DataFrames."""

class PandasSeriesConverter(Converter):
    """Convert between Excel ranges and pandas Series."""

class PolarsDataFrameConverter(Converter):
    """Convert between Excel ranges and Polars DataFrames (PRO)."""

class PolarsSeriesConverter(Converter):
    """Convert between Excel ranges and Polars Series (PRO)."""

class OrderedDictConverter(Converter):
    """Convert between Excel ranges and OrderedDict objects."""

class DatetimeConverter(Converter):
    """Convert between Excel dates and Python datetime objects."""

class DateConverter(Converter):
    """Convert between Excel dates and Python date objects."""

Built-in Converters

xlwings provides several built-in converters for common data types and structures:

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

# Raw converter (no conversion)
ws = xw.sheets.active
ws.range('A1:C3').options(convert=None).value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
raw_data = ws.range('A1:C3').options(convert=None).value

# NumPy array converter
array = np.random.rand(5, 3)
ws.range('A1').options(np.array).value = array
read_array = ws.range('A1:C5').options(np.array).value

# pandas DataFrame converter
df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Age': [25, 30, 35],
    'Salary': [50000, 60000, 70000]
})
ws.range('E1').options(pd.DataFrame, header=True, index=False).value = df
read_df = ws.range('E1:G4').options(pd.DataFrame, header=True, index=False).value

# pandas Series converter
series = pd.Series([1, 2, 3, 4, 5], index=['A', 'B', 'C', 'D', 'E'])
ws.range('I1').options(pd.Series, header=True).value = series

Conversion Options

The conversion system supports extensive configuration through options that control data transformation behavior:

# Common conversion options available across converters:

# Dimensional control
ndim: int           # Force specific number of dimensions (1 or 2)
transpose: bool     # Transpose data orientation (swap rows/columns)

# Data type handling  
numbers: int        # How to handle numbers (0=float/int, 1=float)
dates: str          # Date conversion ('datetime', 'date', None)
empty: str          # Empty cell representation ('', None, NaN)
expand: str         # Dynamic expansion ('table', 'down', 'right')

# pandas-specific options
header: bool        # Include column headers
index: bool         # Include row index
dtype: dict         # Column data types
parse_dates: list   # Columns to parse as dates

# NumPy-specific options
dtype: str          # Array data type ('float64', 'int32', etc.)
order: str          # Memory layout ('C', 'F')

Usage examples with options:

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

ws = xw.sheets.active

# DataFrame with custom options
df = pd.DataFrame({
    'Date': pd.date_range('2024-01-01', periods=10),
    'Value': np.random.randn(10),
    'Category': ['A', 'B'] * 5
})

# Write with headers but no index
ws.range('A1').options(pd.DataFrame, header=True, index=False).value = df

# Read with specific data types
df_back = ws.range('A1:C11').options(
    pd.DataFrame, 
    header=True, 
    index=False,
    dtype={'Category': 'category'},
    parse_dates=['Date']
).value

# NumPy array with specific dtype
arr = np.random.randint(0, 100, (5, 5))
ws.range('F1').options(np.array, dtype='int32').value = arr

# Transposed data
ws.range('L1').options(transpose=True).value = [[1, 2, 3], [4, 5, 6]]

# Dynamic expansion
ws.range('P1').options(expand='table').value = df

Custom Converters

Create custom converters for specialized data transformations:

import xlwings as xw
from xlwings.conversion import Converter

class JSONConverter(Converter):
    """Convert between Excel ranges and JSON strings."""
    
    def read_value(self, value, options):
        import json
        if isinstance(value, str):
            try:
                return json.loads(value)
            except json.JSONDecodeError:
                return value
        return value
    
    def write_value(self, value, options):
        import json
        if isinstance(value, (dict, list)):
            return json.dumps(value, indent=2)
        return value

class DateTimeRangeConverter(Converter):
    """Convert date ranges to formatted strings."""
    
    def read_value(self, value, options):
        from datetime import datetime
        if isinstance(value, list) and len(value) == 2:
            start, end = value
            if isinstance(start, datetime) and isinstance(end, datetime):
                return f"{start.strftime('%Y-%m-%d')} to {end.strftime('%Y-%m-%d')}"
        return value
    
    def write_value(self, value, options):
        if isinstance(value, str) and ' to ' in value:
            from datetime import datetime
            start_str, end_str = value.split(' to ')
            start = datetime.strptime(start_str.strip(), '%Y-%m-%d')
            end = datetime.strptime(end_str.strip(), '%Y-%m-%d')
            return [start, end]
        return value

# Register and use custom converters
ws = xw.sheets.active

# Use JSON converter
json_data = {"name": "John", "age": 30, "city": "New York"}
ws.range('A1').options(JSONConverter()).value = json_data
read_json = ws.range('A1').options(JSONConverter()).value

# Use date range converter
date_range = "2024-01-01 to 2024-12-31"
ws.range('C1:D1').options(DateTimeRangeConverter()).value = date_range

Advanced Conversion Patterns

Complex conversion scenarios and patterns for handling specialized data:

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

# Pattern 1: Multi-sheet DataFrame operations
def write_dataframe_sheets(dataframes_dict, workbook):
    """Write multiple DataFrames to separate sheets."""
    for sheet_name, df in dataframes_dict.items():
        if sheet_name in [s.name for s in workbook.sheets]:
            ws = workbook.sheets[sheet_name]
        else:
            ws = workbook.sheets.add(sheet_name)
        
        ws.range('A1').options(
            pd.DataFrame, 
            header=True, 
            index=False
        ).value = df

# Pattern 2: Incremental data loading
def append_dataframe_data(df, sheet, start_row=None):
    """Append DataFrame to existing Excel data."""
    if start_row is None:
        # Find last row with data
        last_row = sheet.used_range.last_cell.row
        start_row = last_row + 1
    
    # Write data without headers
    sheet.range(f'A{start_row}').options(
        pd.DataFrame,
        header=False,
        index=False
    ).value = df

# Pattern 3: Chunked data processing  
def process_large_dataset(sheet, chunk_size=1000):
    """Process large Excel dataset in chunks."""
    used_range = sheet.used_range
    total_rows = used_range.shape[0]
    
    results = []
    for start_row in range(1, total_rows + 1, chunk_size):
        end_row = min(start_row + chunk_size - 1, total_rows)
        
        # Read chunk
        chunk_range = sheet.range(f'A{start_row}:Z{end_row}')
        chunk_df = chunk_range.options(pd.DataFrame).value
        
        # Process chunk
        processed = chunk_df.apply(lambda x: x * 2)  # Example operation
        results.append(processed)
    
    return pd.concat(results, ignore_index=True)

# Pattern 4: Mixed data type handling
def handle_mixed_data_types(range_obj):
    """Handle ranges with mixed data types."""
    raw_data = range_obj.options(convert=None).value
    
    processed_data = []
    for row in raw_data:
        processed_row = []
        for cell in row:
            if isinstance(cell, str):
                # Try to parse as number
                try:
                    processed_row.append(float(cell))
                except ValueError:
                    processed_row.append(cell)
            elif cell is None:
                processed_row.append(np.nan)
            else:
                processed_row.append(cell)
        processed_data.append(processed_row)
    
    return processed_data

Performance Optimization

Conversion best practices for optimal performance with large datasets:

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

# Best practice 1: Batch operations
def efficient_multi_range_read(sheet, ranges):
    """Read multiple ranges efficiently."""
    # Read all ranges in one operation when possible
    data = {}
    for name, address in ranges.items():
        data[name] = sheet.range(address).options(np.array).value
    return data

# Best practice 2: Minimize Excel interactions
def efficient_dataframe_write(df, sheet, start_cell='A1'):
    """Efficiently write DataFrame with minimal Excel calls."""
    # Single write operation instead of cell-by-cell
    sheet.range(start_cell).options(
        pd.DataFrame,
        header=True,
        index=False,
        expand='table'
    ).value = df

# Best practice 3: Use appropriate converters
def choose_optimal_converter(data):
    """Choose the most efficient converter for data type."""
    if isinstance(data, pd.DataFrame):
        return pd.DataFrame
    elif isinstance(data, np.ndarray):
        return np.array
    elif isinstance(data, list):
        return None  # Raw converter for lists
    else:
        return None

# Best practice 4: Memory-efficient large data handling
def memory_efficient_processing(sheet, output_sheet):
    """Process large data without loading everything into memory."""
    chunk_size = 10000
    used_range = sheet.used_range
    total_rows = used_range.shape[0]
    
    for i in range(0, total_rows, chunk_size):
        # Process in chunks to manage memory
        start_row = i + 1
        end_row = min(i + chunk_size, total_rows)
        
        chunk = sheet.range(f'A{start_row}:Z{end_row}').options(np.array).value
        processed_chunk = np.square(chunk)  # Example processing
        
        # Write processed chunk
        output_sheet.range(f'A{start_row}').options(np.array).value = processed_chunk

Types

# Converter type definitions
ConverterClass = type[Converter]
ConversionOptions = dict[str, Any]

# Built-in converter types
RawConverterType = type[RawConverter]
DictConverterType = type[DictConverter]
NumpyConverterType = type[NumpyArrayConverter]
PandasDFConverterType = type[PandasDataFrameConverter]
PandasSeriesConverterType = type[PandasSeriesConverter]

# Option type definitions
ConvertOption = Union[ConverterClass, None]
TransposeOption = bool
NDimOption = Literal[1, 2]
NumbersOption = Literal[0, 1]
DatesOption = Union[Literal['datetime', 'date'], None]
EmptyOption = Union[str, None, float]
ExpandOption = Literal['table', 'down', 'right']

Install with Tessl CLI