xpycode API Reference¶

Overview¶

The xpycode module provides Python access to Excel through an API that mirrors Microsoft's Office.js Excel API. If you're familiar with Office.js, you'll feel right at home.

Key difference from Office.js JavaScript: You don't need to call context.sync() - xpycode handles synchronization automatically.

Module Entry Points¶

import xpycode

xpycode.workbook      # Excel.Workbook - the active workbook
xpycode.worksheets    # Excel.WorksheetCollection - shortcut to workbook.worksheets
xpycode.context       # Excel.RequestContext (rarely needed)
xpycode.win32         # Win32 COM access (Windows only)

Working with Workbooks¶

# Get workbook properties
name = xpycode.workbook.name

# Access worksheets collection
worksheets = xpycode.workbook.worksheets

Working with Worksheets¶

# Get active worksheet
ws = xpycode.workbook.worksheets.getActiveWorksheet()

# Get worksheet by name
ws = xpycode.workbook.worksheets.getItem("Sheet1")

# Get worksheet by index (0-based)
ws = xpycode.workbook.worksheets.getItemAt(0)

# Get worksheet name
print(ws.name)

# Add a new worksheet
new_ws = xpycode.workbook.worksheets.add("NewSheet")

Working with Ranges¶

# Get a range from worksheet
ws = xpycode.workbook.worksheets.getActiveWorksheet()

# Single cell
cell = ws.getRange("A1")

# Range of cells
range_obj = ws.getRange("A1:C10")

# Get range address
print(range_obj.address)

# Read values (returns 2D array)
values = range_obj.values
# Example: [["Header1", "Header2"], [1, 2], [3, 4]]

# Write values (2D array for ranges)
range_obj.values = [["Hello", "World"]]

# Single cell write (can use scalar or 2D array)
cell.values = 42  # Scalar works for single cells
# or: cell.values = [[42]]  # 2D array also works

# Get range dimensions
row_count = range_obj.rowCount
col_count = range_obj.columnCount

Working with Tables¶

# Get tables collection
tables = ws.tables

# Get table by name
table = ws.tables.getItem("MyTable")

# Get table range
table_range = table.getRange()
data = table_range.values

Event Arguments Helper¶

For event handlers, use getEventArgsRange() to get the Range object:

import xpycode

def on_selection_changed(event_args):
    # Get the Range object from event arguments
    selected_range = xpycode.EventManager.getEventArgsRange(event_args)

    # Now use Office.js Range methods
    values = selected_range.values
    address = selected_range.address
    print(f"Selected {address}: {values}")

Utility Functions¶

Object Storage¶

# Save objects between executions
xpycode.Objects.saveObject("my_data", [1, 2, 3])
data = xpycode.Objects.getObject("my_data")
xpycode.Objects.clearObject("my_data")
xpycode.Objects.clearAllObjects()

Message Boxes¶

xpycode.Messages.showMessageBox("Hello!", "Title")
xpycode.Messages.showMessageBoxInfo("Information message")
xpycode.Messages.showMessageBoxWarning("Warning message")
xpycode.Messages.showMessageBoxError("Error message")

Transactions¶

Transactions allow you to batch multiple Excel write operations together for better performance. Instead of syncing with Excel after each operation, all operations within a transaction are deferred until the transaction ends.

transaction()¶

Context manager for batching Excel operations. Use this to improve performance when making multiple writes to Excel.

import xpycode

with xpycode.transaction() as t:
    ws = xpycode.workbook.worksheets.getActiveWorksheet()
    ws.getRange("A1").values = "Hello"
    ws.getRange("B1").values = "World"
    ws.getRange("C1:C100").values = [[i] for i in range(100)]
    # All operations are committed at the end of the block

Parameters:

raise_error (bool, default=True): If True, exceptions are re-raised after the transaction ends. If False, exceptions are suppressed.
error_callback (callable, optional): Function called with exception info if an error occurs.

Important Notes:

No True Rollback

Transactions don't provide rollback functionality. If an error occurs, pending operations are still executed.

Reading Forces Commit

Accessing values (e.g., range.values, worksheet.name) within a transaction forces an immediate sync. This is because xpycode needs to fetch data from Excel.

No Nested Transactions

Only one transaction can be active at a time. Nested transactions will raise an error.

You can manually commit a transaction before the end of the block using t.commit(), but this will immediately open a new transaction, so use with caution.

blockingTransaction()¶

Similar to transaction(), but attempting to read values (e.g., range.values, range.address) will raise an error instead of forcing a commit. Use this when you're certain you only need to write data.

import xpycode

with xpycode.blockingTransaction() as t:
    ws = xpycode.workbook.worksheets.getActiveWorksheet()
    ws.getRange("A1").values = "Hello"
    ws.getRange("B1").values = "World"
    # This would raise an error:
    # value = ws.getRange("C1").values  # Error!

Parameters:

raise_error (bool, default=True): If True, exceptions are re-raised after the transaction ends. If False, exceptions are suppressed.
error_callback (callable, optional): Function called with exception info if an error occurs.

Use blockingTransaction() when:

You're only performing write operations
You want to catch accidental reads during bulk writes
You want maximum performance without any intermediate syncs

Type Hints¶

xpycode includes type stubs for IDE autocompletion. You can use type hints:

from xpycode import Excel

def process_range(ws: Excel.Worksheet) -> None:
    range_obj: Excel.Range = ws.getRange("A1:B10")
    values = range_obj.values
    # ...

Win32 COM Access¶

For advanced scenarios requiring features not available in Office.js, xpycode provides direct access to Excel's Win32 COM API on Windows.

Entry Point¶

import xpycode

win32 = xpycode.win32  # Win32COMTools object (Windows only)

Platform and Dependencies

Windows only - Raises EnvironmentError on other platforms
Requires pywin32 - Raises ImportError if pywin32 package is not installed
Instance matching - Discovers the correct Excel instance by scanning the Running Object Table (ROT) for a workbook with the xpycode_id custom property

Properties¶

Property	Type	Description
`application`	`xpycode.ExcelComObjects.Application`	The Win32 COM `Application` object representing the running Excel instance
`workbook`	`xpycode.ExcelComObjects.Workbook`	The Win32 COM `Workbook` object for the current workbook
`constants`	`xpycode.ExcelComObjects.constants`	Classic Excel VBA constants (e.g., `xlCenter`, `xlEdgeBottom`, `xlSolid`)

Type Hints¶

For IDE autocompletion, use the xpycode.ExcelComObjects namespace:

import xpycode

# Type hints for Win32 COM objects
app: xpycode.ExcelComObjects.Application = xpycode.win32.application
wb: xpycode.ExcelComObjects.Workbook = xpycode.win32.workbook

Example: Using All Three Properties¶

import xpycode

# Access Win32 COM interface
win32 = xpycode.win32
xl = win32.constants  # VBA constants

# Application-level operations
app = win32.application
app.ScreenUpdating = False  # Improve performance
print(f"Excel version: {app.Version}")

# Workbook-level operations
wb = win32.workbook
print(f"Workbook: {wb.FullName}")
print(f"Sheets: {wb.Worksheets.Count}")

# Use constants for formatting
ws = wb.ActiveSheet
cell = ws.Range("A1")
cell.Value = "Formatted Cell"
cell.HorizontalAlignment = xl.xlCenter
cell.Borders(xl.xlEdgeBottom).Weight = xl.xlMedium

app.ScreenUpdating = True

Office.js vs Win32 COM¶

Key difference: xpycode.workbook is an Office.js proxy object (cross-platform, auto-synced), while xpycode.win32.workbook is a native COM object (Windows only, direct access).

import xpycode

# Office.js approach (preferred for standard operations)
ws = xpycode.workbook.worksheets.getActiveWorksheet()
ws.getRange("A1").values = "Office.js"

# Win32 COM approach (for advanced features)
ws_com = xpycode.win32.workbook.ActiveSheet
ws_com.Range("A1").Value = "Win32 COM"
ws_com.Range("A1").Font.Bold = True

For more details and examples, see the Excel Objects Guide.

Code Examples¶

Read and Write¶

import xpycode

def read_and_write():
    # Get active sheet
    ws = xpycode.workbook.worksheets.getActiveWorksheet()

    # Read single cell (returns 2D array)
    value = ws.getRange("A1").values[0][0]

    # Write single cell (can use scalar or 2D array)
    ws.getRange("B1").values = "Hello, Excel!"  # Scalar for single cell
    # or: ws.getRange("B1").values = [["Hello, Excel!"]]  # 2D array also works

    # Read range (returns 2D array)
    data = ws.getRange("A1:C10").values

    # Write range (must be 2D array)
    ws.getRange("E1").values = [[1, 2], [3, 4], [5, 6]]

Work with DataFrames¶

import pandas as pd
import xpycode

def work_with_dataframe():
    # Read from Excel
    ws = xpycode.workbook.worksheets.getActiveWorksheet()
    data = ws.getRange("A1:D100").values

    # Create DataFrame (first row is headers)
    df = pd.DataFrame(data[1:], columns=data[0])

    # Process data
    df_filtered = df[df['Score'] > 80]

    # Write back to Excel
    ws.getRange(f"F1:I{len(output_data)}").getResizedRange(len(df_filtered.index),len(df_filtered.columns)-1).values = df_filtered

Event Handlers¶

import xpycode

def auto_update(event):
    """Update summary when data changes."""
    # Get the Range object from event arguments
    selected_range = xpycode.EventManager.getEventArgsRange(event)

    # Get worksheet
    ws = xpycode.workbook.worksheets.getItem(event.worksheet)

    # Get changed cell values
    cell_values = selected_range.values

    # Update summary
    ws.getRange("Z1").values = [[f"Last changed: {event.address} = {cell_values}"]]

Common Patterns¶

Iterate Over Range¶

# Read range (returns 2D array)
ws = xpycode.workbook.worksheets.getActiveWorksheet()
data = ws.getRange("A1:A10").values

# Iterate
for row in data:
    cell_value = row[0]
    print(cell_value)

Find Last Row¶

ws = xpycode.workbook.worksheets.getActiveWorksheet()

# Simple approach: check until empty
row = 1
while ws.getRange(f"A{row}").values[0][0] is not None:
    row += 1
last_row = row - 1

print(f"Last row with data: {last_row}")

Bulk Update¶

ws = xpycode.workbook.worksheets.getActiveWorksheet()

# Prepare data (2D array)
data = [[i, i*2, i*3] for i in range(1, 101)]

# Write in one operation (faster than cell-by-cell)
ws.getRange("A1").getResizedRange(len(data)-1,len(data[0])-1).values = data

Office.js Reference¶

For complete API documentation, refer to: - Excel JavaScript API Reference - Office.js Overview

The xpycode API mirrors the Office.js Excel API, so methods and properties documented there apply to xpycode.

Remember: Unlike Office.js JavaScript, you don't need to call context.sync() - xpycode handles synchronization automatically!

Excel Objects Guide - Detailed object usage
Custom Functions - Using xpycode in UDFs
Events - Event handling patterns

xpycode API Reference¶

Overview¶

Module Entry Points¶

Working with Workbooks¶

Working with Worksheets¶

Working with Ranges¶

Working with Tables¶

Event Arguments Helper¶

Utility Functions¶

Object Storage¶

Message Boxes¶

Transactions¶

transaction()¶

blockingTransaction()¶

Type Hints¶

Win32 COM Access¶

Entry Point¶

Properties¶

Type Hints¶

Example: Using All Three Properties¶

Office.js vs Win32 COM¶

Code Examples¶

Read and Write¶

Work with DataFrames¶

Event Handlers¶

Common Patterns¶

Iterate Over Range¶

Find Last Row¶

Bulk Update¶

Office.js Reference¶

Related¶