How To Evaluate Excel Formula In Python

Evaluating Excel formulas is a common task when working with spreadsheet data. Many tasks may require you to extract data from an Excel file and process it using Python. In this tutorial, we’ll learn how to evaluate Excel formulas in Python using the openpyxl library, which allows you to read and modify Excel files.

Step 1: Install openpyxl

First, you need to install the openpyxl library if you don’t already have it. To do this, you can use the following command:
bash
pip install openpyxl
Now that you’ve installed openpyxl, you’re ready to start evaluating Excel formulas in Python.

Step 2: Load the Excel File

Before you can evaluate Excel formulas, you need to load the Excel file. To do this, use the load_workbook() method provided by openpyxl.

Here’s an example of how to load an Excel file:

1
2
3
4
from openpyxl import load_workbook
 
file_path = "example.xlsx"
workbook = load_workbook(file_path, data_only=False)

Step 3: Access the Worksheet and Cell with the Formula

You’ll need to access the worksheet and cell containing the formula you want to evaluate. To do this, you can use the following code:

1
2
sheet = workbook["Sheet1"]  # Replace "Sheet1" with the name of the sheet containing the formula
cell = sheet["A1"]  # Replace "A1" with the address of the cell containing the formula

Step 4: Evaluate the Formula

Now that you have a reference to the cell containing the formula, you can evaluate it using openpyxl’s evaluate() method.

1
2
3
4
if cell.data_type == "f":
    formula_result = sheet[cell.coordinate].value
else:
    formula_result = cell.value

In this code, we are checking the data_type attribute of the cell object. The data_type attribute indicates the type of data stored in the cell.

If data_type is equal to “f”, it means that the cell contains a formula. In this case, we want to retrieve the result of the formula. The result of a formula is stored in the value attribute of the cell object in the sheet object. So, we access sheet[cell.coordinate].value to get the formula result and assign it to the variable formula_result.

If data_type is not equal to “f”, it means that the cell does not contain a formula. In this case, we can directly use the value stored in the cell object, which can be accessed using cell.value. We assign this value to the variable formula_result.

By using this conditional check, we ensure that formula_result will have the correct value based on whether the cell contains a formula or not.

Step 5: Print the Result

Finally, you can print the result using the following code:

1
print(f"Evaluation of the formula in cell {cell.coordinate} is: {cell_value}")

Now, let’s put it all together:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from openpyxl import load_workbook
 
file_path = "example.xlsx"
workbook = load_workbook(file_path, data_only=False)
 
sheet = workbook["Sheet1"]
cell = sheet["A1"]
 
formula_result = cell.parent.formula_attributes[cell.coordinate]
 
if formula_result == 'auto':
    cell_value = cell.value
else:
    cell_value = sheet.formula_attributes[cell.coordinate]
 
print(f"Evaluation of the formula in cell {cell.coordinate} is: {cell_value}")

This code will print the result of the formula in cell A1 to the console. If you want to evaluate other cells’ formulas, simply change the cell address in the code.

Conclusion

In this tutorial, we’ve learned how to evaluate Excel formulas in Python using the openpyxl library. Evaluating Excel formulas programmatically can be particularly useful when working with large datasets, automating tasks, or building Python applications that handle spreadsheet data.

Keep in mind that the openpyxl library supports only simple Excel formulas. If you need to work with more complex formulas or functions, consider using other Python libraries such as pandas and numpy to process the data.