How to use Worksheet.Scenarios in the xlwings API way
In Excel, a Scenario is a set of input values (called changing cells) that you can save and later substitute into a worksheet to see different outcomes. The Scenarios collection of a Worksheet object in Excel’s object model allows you to manage these saved scenarios. Through xlwings, you can programmatically access, create, modify, and apply these scenarios, enabling powerful what-if analysis automation within your Python scripts.
Functionality
The Scenarios member provides a way to interact with all scenarios defined on a specific worksheet. You can add new scenarios, retrieve existing ones, change their values, show (apply) a particular scenario, and delete scenarios. This is particularly useful for building financial models, project plans, or any analysis where you need to quickly switch between different sets of assumptions.
Syntax and Key Members
In xlwings, you access the Scenarios collection via the api property of a Sheet object (which corresponds to a Worksheet). The primary properties and methods include:
- Accessing the Collection:
sheet.api.Scenarios - Count Property:
sheet.api.Scenarios.Countreturns the number of scenarios on the sheet. - Item Method:
sheet.api.Scenarios(Index)orsheet.api.Scenarios(Name)retrieves a specificScenarioobject.Indexcan be the scenario’s number (1-based) or name. - Add Method: Used to create a new scenario.
sheet.api.Scenarios.Add(Name, ChangingCells, Values, Comment, Locked, Hidden)Name(String, Required): The name for the new scenario.ChangingCells(Object, Required): An xlwings Range object (e.g.,sheet.range("B2:B3")), specifying the cells that will change.Values(Variant, Optional): An array of values to be entered into the changing cells. If omitted, the current values in the cells are used.Comment(String, Optional): A comment describing the scenario (up to 255 characters).Locked(Boolean, Optional):Trueto prevent modifications when the sheet is protected.Hidden(Boolean, Optional):Trueto hide the scenario when the sheet is protected.- A
Scenarioobject itself has key methods like: Show(): Applies the scenario’s values to the worksheet.ChangeScenario(ChangingCells, Values): Modifies the scenario’s changing cells or values.Delete(): Removes the scenario.
Code Examples
- Adding a New Scenario:
import xlwings as xw
wb = xw.Book("Analysis.xlsx")
sheet = wb.sheets["Sheet1"]
# Define changing cells and values
changing_cells = sheet.range("B2, B4") # Assumptions for Price and Units
scenario_values = [29.99, 1200]
# Add a "Best Case" scenario
sheet.api.Scenarios.Add(Name="Best Case",
ChangingCells=changing_cells,
Values=scenario_values,
Comment="Optimistic sales forecast")- Applying (Showing) an Existing Scenario:
# Apply the "Worst Case" scenario to see its impact
try:
sheet.api.Scenarios("Worst Case").Show()
print("Applied 'Worst Case' scenario.")
except Exception as e:
print(f"Scenario not found: {e}")- Iterating Through and Managing Scenarios:
# List all scenarios and delete a specific one
scenarios = sheet.api.Scenarios
print(f"Number of scenarios: {scenarios.Count}")
for i in range(1, scenarios.Count + 1):
scen = scenarios(i)
print(f"{i}: {scen.Name} - {scen.Comment}")
# Delete the "Obsolete" scenario if it exists
if scenarios.Count > 0:
for scen in scenarios:
if scen.Name == "Obsolete":
scen.Delete()
print("Deleted 'Obsolete' scenario.")
break- Modifying a Scenario’s Values:
# Update the values for the "Base Case" scenario
target_scenario = sheet.api.Scenarios("Base Case")
new_changing_cells = sheet.range("B2:B3")
new_values = [25.50, 950]
target_scenario.ChangeScenario(ChangingCells=new_changing_cells, Values=new_values)