Work with Formulas with Aspose.Cells FOSS
Overview
Aspose.Cells FOSS enables Python developers to work with formulas in spreadsheet files using the Cell class. The formula property allows setting or retrieving a cell’s formula string, while the value property provides the computed result. Formula evaluation integrates with workbook-level calculation properties exposed via CalculationProperties.
This page covers reading, writing, and evaluating formulas using core classes: Cell, CalculationProperties, and supporting utilities like CellValueHandler. It aligns with ECMA-376 standards for cell value handling and supports common operations such as parsing and formatting values for XML exchange.
Core Concepts
Aspose.Cells FOSS provides core classes for working with spreadsheet data in Python, including formula handling via the Cell class and calculation control through CalculationProperties. Developers must understand how cells store formulas, how calculation properties affect workbook behavior, and how cell values are processed according to ECMA-376 standards.
Cell Formula Storage
The Cell class stores formulas in its formula property, which accepts and returns formula strings (e.g., =A1+B1). The value property reflects the last calculated result, while data_type indicates whether the cell holds a formula, number, string, or error.
Calculation Control
The CalculationProperties class exposes workbook-level calculation settings such as calc_mode, full_calc_on_load, and ref_mode. These properties determine when and how formulas are recalculated during workbook operations.
Cell Value Handling
The CellValueHandler class provides static methods to parse, format, and validate cell values per ECMA-376, including type detection via get_cell_type(), XML formatting via format_value_for_xml(), and error value checking via is_error_value().
Implementation
Aspose.Cells FOSS enables formula handling in Python via the Cell class. Developers can set and retrieve formulas using the formula property and access computed values through the value property.
Set a Formula in a Cell
Use the formula property to assign a formula string to a cell. The formula is stored as-is and evaluated when the workbook is calculated or saved with calculation enabled.
import aspose.cells_foss
workbook = aspose.cells.Workbook()
worksheet = workbook.worksheets[0]
cell = worksheet.cells.get("A1")
cell.formula = "=10+20"Read a Formula from a Cell
Retrieve the formula string using the formula property of a Cell instance. This returns the raw formula expression as entered by the user.
formula = cell.formula
print(f"Formula: {formula}")Access Computed Cell Value
After setting a formula, access the computed result via the value property. This reflects the evaluated result once the workbook has been calculated.
value = cell.value
print(f"Value: {value}")Code Examples
Aspose.Cells FOSS enables formula handling through the Cell class. You can set formulas using the formula property assignment, and the value property reflects the calculated result after workbook recalculation. The CellValueHandler class supports parsing and formatting cell values per ECMA-376.
import aspose.cells_foss
# Create a new workbook and access the first worksheet
workbook = aspose.cells.Workbook()
worksheet = workbook.worksheets[0]
# Set a formula in cell A1
worksheet.cells.get('A1').formula = '=SUM(10,20,30)'
# Recalculate formulas to update values
workbook.calculate_formula()
# Retrieve the calculated value
result = worksheet.cells.get('A1').value
print(f'Result of formula: {result}')The CellValueHandler class provides static utilities for working with cell values in XML contexts. Use get_cell_type() to determine the type of a value, format_value_for_xml() to prepare values for XML serialization, and parse_value_from_xml() to reconstruct values from XML strings.
import aspose.cells_foss
# Determine cell type for a numeric value
value = 42.5
cell_type = aspose.cells.CellValueHandler.get_cell_type(value)
print(f'Cell type for {value}: {cell_type}')
# Format value for XML output
formatted = aspose.cells.CellValueHandler.format_value_for_xml(value, cell_type)
print(f'Formatted for XML: {formatted}')