How to use Worksheet.CustomProperties in the xlwings API way

The CustomProperties member of the Worksheet object in Excel’s object model provides a powerful way to store and retrieve custom metadata associated with a specific worksheet. These properties are stored directly within the Excel file and persist with it, making them ideal for attaching auxiliary information like configuration settings, version numbers, author notes, or any custom identifiers that your automation scripts might need. Unlike cell values, they are not directly visible on the grid, offering a clean way to embed data for programmatic use.

In xlwings, you access this collection through the api property of a Sheet object, which grants direct access to the underlying Excel VBA object model. The CustomProperties collection itself has methods to Add, Item (for retrieval), and Count, and each CustomProperty object has Name and Value properties.

Key xlwings API Syntax:

• Accessing the Collection: sheet.api.CustomProperties
• Adding a Property: sheet.api.CustomProperties.Add(Name, Value)
• Name: A required String that is the unique identifier for the property.
• Value: A required Variant that can be a string, number, or boolean. This is the data stored.
• Retrieving a Property by Name: sheet.api.CustomProperties.Item(Name)
• Returns a CustomProperty object.
• Getting/Setting a Property’s Value: cp.Value (where cp is a CustomProperty object).
• Getting the Count: sheet.api.CustomProperties.Count

Code Examples:

1. Adding and Reading a Custom Property:

import xlwings as xw

# Connect to an open workbook or open a new one
wb = xw.Book(r'C:\path\to\your\file.xlsx')
sheet = wb.sheets['Sheet1']

# Add a custom property
sheet.api.CustomProperties.Add("DataVersion", "2.5")
sheet.api.CustomProperties.Add("Processed", True)

# Read a specific property's value
try:
    version_prop = sheet.api.CustomProperties.Item("DataVersion")
    print(f"Data Version: {version_prop.Value}") # Output: Data Version: 2.5
except:
    print("Property not found.")

# Iterate through all custom properties
for i in range(1, sheet.api.CustomProperties.Count + 1):
    cp = sheet.api.CustomProperties.Item(i) # Can also index by position
    print(f"{cp.Name}: {cp.Value}")
    # Output might be:
    # DataVersion: 2.5
    # Processed: True

2. Updating an Existing Property:

# Check if a property exists and update it
prop_name = "LastRefresh"
try:
    last_refresh_prop = sheet.api.CustomProperties.Item(prop_name)
    last_refresh_prop.Value = "2024-05-27 14:30" # Update the value
except:
    # If it doesn't exist, create it
    sheet.api.CustomProperties.Add(prop_name, "2024-05-27 14:30")

3. Using Properties for Workflow Control:

# A common use case is to mark if a sheet has been initialized or processed
if sheet.api.CustomProperties.Count > 0:
    status_prop = sheet.api.CustomProperties.Item("InitializationStatus")
    if status_prop.Value == "Complete":
        print("Sheet is ready for analysis.")
    else:
        print("Running setup macro...")
    # ... run setup code ...
    status_prop.Value = "Complete"
else:
    print("First-time setup required.")
    sheet.api.CustomProperties.Add("InitializationStatus", "Complete")
# Save the workbook to persist the properties
wb.save()