How to use Worksheet.TransitionExpEval in the xlwings API way

The TransitionExpEval property of a Worksheet object in Excel is a legacy feature primarily designed for compatibility with older spreadsheet applications, such as Lotus 1-2-3. When this property is set to True, it instructs Excel to evaluate formulas using the expression evaluation rules of Lotus 1-2-3 during the transition period when a file is being converted or used. This can affect how certain operators (like the string concatenation operator & or the exponentiation operator ^) and functions are interpreted. In modern Excel usage, this property is rarely needed but can be accessed and controlled via xlwings for automation scripts that interact with legacy systems or files.

Functionality:
The property serves as a switch. When enabled (True), Excel temporarily uses Lotus 1-2-3 formula evaluation rules for the specific worksheet. When disabled (False), which is the default, Excel uses its standard evaluation rules. This is typically only relevant when opening or working with files originally created in Lotus 1-2-3 to ensure formula results remain consistent during the transition.

Syntax in xlwings:
In xlwings, you access this property through the api property of a Sheet object, which exposes the underlying Excel VBA object model. The property is a Boolean (True/False).

# To get the current value
current_setting = sheet.api.TransitionExpEval

# To set a new value
sheet.api.TransitionExpEval = True # or False

• sheet: This is your xlwings Sheet object (e.g., wb.sheets['Sheet1']).
• .api: This provides direct access to the Excel VBA object, here a Worksheet object.
• .TransitionExpEval: The property being accessed or modified. It expects a Boolean value.

Code Examples:

1. Checking the Current Setting:
   This script prints the current TransitionExpEval status for all worksheets in an open workbook.

import xlwings as xw

# Connect to the active Excel instance
app = xw.apps.active
wb = app.books.active

for sheet in wb.sheets:
    status = sheet.api.TransitionExpEval
    print(f"Sheet '{sheet.name}' has TransitionExpEval = {status}")

2. Enabling Transition Evaluation for a Specific Sheet:
   This script opens a workbook and enables Lotus 1-2-3 formula evaluation for a sheet named “LegacyData”.

import xlwings as xw

# Open a specific workbook
wb = xw.Book(r'C:\Path\To\Legacy_File.xlsx')

# Target a specific sheet
target_sheet = wb.sheets['LegacyData']

# Enable Transition Formula Evaluation
target_sheet.api.TransitionExpEval = True
print(f"TransitionExpEval enabled for '{target_sheet.name}'.")

# Save the workbook if needed
wb.save()
# wb.close()

3. Conditional Workflow Based on Setting:
   This example demonstrates a check: if transition evaluation is already on, it performs a recalculation; otherwise, it turns it on and then calculates.

import xlwings as xw

wb = xw.Book.active
sheet = wb.sheets[0] # First sheet

if sheet.api.TransitionExpEval:
    print("Transition mode active. Forcing a full calculation.")
    sheet.api.Calculate() # Calculates the worksheet
else:
    print("Enabling transition mode and calculating.")
    sheet.api.TransitionExpEval = True
    sheet.api.Calculate()

# Note: The .Calculate() method is used here via .api to force a manual recalculation under the active evaluation rules.