How to use Worksheet.Evaluate in the xlwings API way

The Evaluate method of the Worksheet object in Excel is a powerful tool that allows you to evaluate a Microsoft Excel expression or a name and return the resulting value. In xlwings, this functionality is exposed through the api property, which provides direct access to the underlying Excel object model. This method is particularly useful for calculating formulas or expressions that are provided as strings, without the need to write them into a cell first. It can handle complex expressions, including those with functions and references, and return the computed result directly to your Python code.

Functionality:
The primary function of Evaluate is to compute the result of an Excel formula or expression given as a string. This is equivalent to typing the formula into the Excel formula bar and pressing Enter, but it is done programmatically. It can evaluate simple arithmetic, Excel functions, named ranges, and cell references. This is efficient for one-off calculations where you do not want to modify the worksheet.

Syntax in xlwings:
In xlwings, you access the Evaluate method through the api property of a Sheet object (which corresponds to a Worksheet). The general syntax is:

result = sheet.api.Evaluate(expression)

• sheet: This is an xlwings Sheet object representing the worksheet where the evaluation context is considered (important for relative references).
• expression (required): A string that contains the Excel formula or expression to be evaluated. This can be any valid Excel formula, such as "SUM(A1:A10)", "2+2", or "A1*B1". The string must be formatted exactly as it would be in Excel, using English function names and comma separators by default (depending on the system’s locale settings).

Parameters:
The Evaluate method takes a single parameter:

• Expression: A string that is a valid Excel formula. It can include:
• Arithmetic operators (e.g., "5*3").
• Excel functions (e.g., "AVERAGE(1,2,3)").
• Cell references (e.g., "A1", "Sheet2!B5"). Relative references are evaluated in the context of the worksheet object used.
• Named ranges (e.g., "MyRange").
• R1C1-style references are also supported if provided as strings (e.g., "R1C1").

Code Examples:
Here are practical examples using xlwings to demonstrate the Evaluate method:

1. Evaluating a simple arithmetic expression:

import xlwings as xw
# Connect to the active workbook and sheet
wb = xw.books.active
sheet = wb.sheets['Sheet1']
# Evaluate 10 + 20
result = sheet.api.Evaluate("10+20")
print(result) # Output: 30

2. Using an Excel function to calculate an average:

import xlwings as xw
wb = xw.books.active
sheet = wb.sheets[0]
# Assume cells A1:A5 contain numbers 1, 2, 3, 4, 5
# Evaluate the AVERAGE function on that range
avg_result = sheet.api.Evaluate("AVERAGE(A1:A5)")
print(avg_result) # Output: 3.0

3. Referencing a cell and performing a calculation:

import xlwings as xw
wb = xlwings.Book('example.xlsx')
sheet = wb.sheets['Data']
# Suppose cell B2 contains the value 100 and C2 contains 0.2
# Calculate B2 * C2
computed = sheet.api.Evaluate("B2*C2")
print(computed) # Output: 20.0

4. Evaluating a more complex formula with a named range:

import xlwings as xw
app = xw.App(visible=False)
wb = app.books.add()
sheet = wb.sheets[0]
# Define a named range for cells D1:D3
wb.api.Names.Add(Name="SalesData", RefersTo="=Sheet1!$D$1:$D$3")
# Assign values to D1:D3
sheet.range('D1').value = [200, 300, 400]
# Use SUM on the named range
total_sales = sheet.api.Evaluate("SUM(SalesData)")
print(total_sales) # Output: 900
wb.close()
app.quit()