Working with Formulas

Overview

Aspose.Cells FOSS stores formulas as Excel-compatible strings. The library does not evaluate formulas internally — it preserves the expression verbatim in the file, and the spreadsheet application (Excel, LibreOffice Calc, or any compatible reader) computes the result when the file is opened.

There are two ways to place a formula in a cell:

1. Cell constructor — ws.cells["A4"] = Cell(None, "=SUM(A1:A3)")
2. .formula property — ws.cells["A4"].formula = "=SUM(A1:A3)"

Both approaches store the same data. The constructor form is compact when you are building a sheet from scratch; the property form is more natural when you already have a reference to an existing cell and want to attach a formula to it.

Setting a Formula via the Cell Constructor

The Cell constructor signature is Cell(value, formula). To create a pure formula cell, pass None as the value and the expression string as the second argument:

from aspose.cells_foss import Workbook, Cell

workbook = Workbook()
ws = workbook.worksheets[0]

##Input values
ws.cells["A1"] = Cell(10)
ws.cells["A2"] = Cell(25)
ws.cells["A3"] = Cell(15)

##Formula cell: value=None, formula="=SUM(A1:A3)"
ws.cells["A4"] = Cell(None, "=SUM(A1:A3)")

workbook.save("formula_constructor.xlsx")
print("Saved formula_constructor.xlsx")

Setting value=None makes it explicit that the cell has no static value — its displayed content comes entirely from the formula. Passing a non-None value alongside a formula is allowed but unusual; most spreadsheet readers will display the formula result and ignore the stored value.

Setting a Formula via the .formula Property

Assign directly to cell.formula when you already hold a cell reference or when you want to add a formula to a cell that was previously written with a value:

from aspose.cells_foss import Workbook

workbook = Workbook()
ws = workbook.worksheets[0]

##Write input data
for i, val in enumerate([4, 8, 15, 16, 23], start=1):
    ws.cells[f"A{i}"].value = val

##Attach a formula to an existing cell reference
cell = ws.cells["B1"]
cell.formula = "=AVERAGE(A1:A5)"

##Or assign directly by address
ws.cells["B2"].formula = "=MAX(A1:A5)"
ws.cells["B3"].formula = "=MIN(A1:A5)"

workbook.save("formula_property.xlsx")
print("Saved formula_property.xlsx")

Reading a Formula Back

After writing a formula cell, use .formula to retrieve the expression string and .value to retrieve whatever static value (if any) was stored alongside it.

from aspose.cells_foss import Workbook, Cell

workbook = Workbook()
ws = workbook.worksheets[0]

ws.cells["A1"] = Cell(None, "=SUM(B1:B5)")

cell = ws.cells["A1"]

print(cell.formula)   # =SUM(B1:B5)
print(cell.value)     # None  (no static value was set)

In practice, .value is None for any cell created with Cell(None, formula). If you load an existing workbook that was previously saved with calculated values cached alongside formulas, .value may return the last cached result — but this behaviour depends on how the originating application saved the file and should not be relied upon for fresh calculations.

To distinguish a formula cell from a plain-value cell at runtime:

cell = ws.cells["A1"]
if cell.formula:
    print(f"Formula cell: {cell.formula}")
else:
    print(f"Value cell: {cell.value}")

Common Excel Formulas

The following table lists frequently used Excel functions with example formula strings you can pass directly to Aspose.Cells FOSS.

All formula strings must begin with =. Function names are case-insensitive in Excel-compatible files, but the conventional uppercase form is shown above for readability.

Complete Example

The following example creates a five-row dataset of numeric values in column A, then writes SUM, AVERAGE, MAX, and MIN formulas into column B, and saves the result to an XLSX file.

from aspose.cells_foss import Workbook, Cell

workbook = Workbook()
ws = workbook.worksheets[0]

##--- Headers ---
ws.cells["A1"].value = "Value"
ws.cells["B1"].value = "Formula"
ws.cells["C1"].value = "Result (evaluated by Excel)"

##--- Input data in A2:A6 ---
input_values = [14, 28, 7, 35, 21]
for i, val in enumerate(input_values, start=2):
    ws.cells[f"A{i}"].value = val

##--- Formula labels in B column ---
ws.cells["B2"].value = "SUM"
ws.cells["B3"].value = "AVERAGE"
ws.cells["B4"].value = "MAX"
ws.cells["B5"].value = "MIN"

##--- Formulas in C column ---
ws.cells["C2"] = Cell(None, "=SUM(A2:A6)")
ws.cells["C3"] = Cell(None, "=AVERAGE(A2:A6)")
ws.cells["C4"] = Cell(None, "=MAX(A2:A6)")
ws.cells["C5"] = Cell(None, "=MIN(A2:A6)")

##--- Save ---
workbook.save("formulas_demo.xlsx")
print("Saved formulas_demo.xlsx")

When this file is opened in Excel or LibreOffice Calc, column C will display:

Notes

Formula strings must start with =

The library stores the string as-is. Omitting the leading = causes the spreadsheet reader to treat the text as a literal string rather than a formula.

##Correct — starts with =
ws.cells["A1"].formula = "=SUM(B1:B5)"

##Wrong — treated as the literal text "SUM(B1:B5)", not a formula
ws.cells["A1"].formula = "SUM(B1:B5)"

Range references use standard Excel A1 notation

Row-column ranges are written as FirstCell:LastCell using uppercase column letters and one-based row numbers: "A1:A10", "B2:D5", "C3:C3". The FOSS library does not translate or validate the range string — an invalid range will be stored verbatim and cause an error in the spreadsheet reader at open time.

Formulas are stored as strings, not evaluated

Aspose.Cells FOSS does not include a formula engine. If you need the computed result of a formula in Python (for example, to perform further calculations), compute the value yourself and write it as a static value with .value. Use formulas only when the end-user will open the file in a spreadsheet application that can evaluate them.

See Also

• API Reference: Full class and method documentation for aspose.cells_foss
• Knowledge Base: Task-oriented how-to guides
• Product Overview: Features and capabilities summary
• Getting Started / Installation: pip install and setup
• Blog: Introducing Aspose.Cells FOSS: Library overview and quick start