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 as plain text. Formulas are stored verbatim in the XLSX file and are evaluated by Excel or LibreOffice when the file is opened — the library itself does not evaluate formula results at runtime.
This page covers reading and writing formulas using the Cell class.
Core Concepts
Aspose.Cells FOSS stores Excel-compatible formulas as plain strings in the XLSX file. When the saved file is opened in Excel or LibreOffice, those applications evaluate the formulas and display computed results. The library does not evaluate formulas at runtime in Python.
Cell Formula Storage
The Cell class stores formulas in its formula property, which accepts and returns formula strings (e.g., =A1+B1). The data_type property indicates whether the cell holds a formula, number, string, or error.
Important:
worksheet.calculate_formula()is a compatibility stub and does not evaluate formulas. Formula evaluation occurs in Excel or LibreOffice when the XLSX file is opened.
Implementation
Aspose.Cells FOSS enables formula handling in Python via the Cell class. Developers can set and retrieve formulas using the formula property.
Set a Formula in a Cell
Use the formula property to assign a formula string to a cell. The formula is stored verbatim and will be evaluated by Excel or LibreOffice when the file is opened.
from aspose.cells_foss import Workbook
workbook = Workbook()
worksheet = workbook.worksheets[0]
cell = worksheet.cells.get_cell_by_name("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}")Code Examples
The following example writes a formula string to a cell and saves the workbook. The formula will be evaluated by Excel or LibreOffice when the file is opened.
from aspose.cells_foss import Workbook
# Create a new workbook and access the first worksheet
workbook = Workbook()
worksheet = workbook.worksheets[0]
# Set a value in A1 and a SUM formula in A2
worksheet.cells.get_cell_by_name('A1').value = 42
worksheet.cells.get_cell_by_name('A2').formula = '=SUM(A1, 10)'
# Save — Excel will compute =SUM(A1, 10) = 52 when it opens the file
workbook.save('formulas.xlsx')
print('Saved formulas.xlsx')