Decrease the Calculation Time of Cell.Calculate Method with Python.NET

Possible Usage Scenarios

Normally, we recommend users to call workbook.calculate_formula() method once and then get the calculated values of individual cells. When working with single cell calculations, you can use calculation_options.recursive property to significantly reduce computation time. Setting this property to False prevents recalculating dependent cells on subsequent calls.

Optimizing Cell Calculation Performance

The following sample demonstrates using the recursive property. Use the provided sample Excel file to test the performance difference. The code shows how setting recursive=False reduces calculation time by avoiding redundant dependent cell recalculations.

# For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
# Test calculation time after setting recursive true
test_calc_time_recursive(True)

# Test calculation time after setting recursive false
test_calc_time_recursive(False)

import os
import time
from aspose.cells import Workbook, CalculationOptions

def test_calc_time_recursive(rec):
    """
    Tests calculation time with recursive option and prints elapsed seconds
    """
    # The path to the documents directory
    current_dir = os.path.dirname(os.path.abspath(__file__))
    data_dir = os.path.join(current_dir, "data")
    
    # Load sample workbook
    wb = Workbook(os.path.join(data_dir, "sample.xlsx"))
    
    # Access first worksheet
    ws = wb.worksheets[0]
    
    # Set calculation options
    opts = CalculationOptions()
    opts.recursive = rec
    
    # Start timing
    start_time = time.perf_counter()
    
    # Calculate cell A1 one million times
    for _ in range(1000000):
        ws.cells.get("A1").calculate(opts)
    
    # Calculate elapsed time
    elapsed_time = int(time.perf_counter() - start_time)
    
    # Print results
    print(f"Recursive {rec}: {elapsed_time} seconds")

Performance Benchmark Results

Typical output when running the optimized code with the sample file shows significant time reduction:

Recursive True: 96 seconds
Recursive False: 42 seconds