Usage Examples

All the examples on this page live in docs/examples in the repository and are run as part of the test suite, so they always work with the current version.

The two APIs

IronCalc ships two APIs:

• The user API (UserModel): the same high level API used by the IronCalc web application. Every action evaluates the workbook, keeps undo/redo history and produces diffs for collaboration.

• The raw API (Model): a low level API. Nothing is evaluated automatically (call Model.evaluate() yourself), there is no undo/redo and no diffs are produced.

import ironcalc as ic

# The user API: evaluates automatically after every change
model = ic.UserModel("model")
model.set_user_input(0, 1, 1, "=21*2")
assert model.get_formatted_cell_value(0, 1, 1) == "42"

# The raw API: you must evaluate yourself
raw = ic.create("model")
raw.set_user_input(0, 1, 1, "=21*2")
raw.evaluate()
assert raw.get_formatted_cell_value(0, 1, 1) == "42"

A styled report

Building a small sales report: bold headers, background colors, currency formats, borders and column widths, saved as xlsx.

"""Build a small styled sales report and save it as xlsx."""

import os
import tempfile

import ironcalc as ic

model = ic.UserModel("sales-report")

sales = [
    ("Apples", 1500, 0.45),
    ("Oranges", 900, 0.61),
    ("Bananas", 2100, 0.32),
]

# Header row
headers = ["Product", "Units", "Price", "Total"]
for column, header in enumerate(headers, start=1):
    model.set_user_input(0, 1, column, header)

# Style the header: bold, white on dark blue, centered
model.update_range_style(0, 1, 1, 1, 4, "font.b", "true")
model.update_range_style(0, 1, 1, 1, 4, "font.color", "#FFFFFF")
model.update_range_style(0, 1, 1, 1, 4, "fill.color", "#2F5597")
model.update_range_style(0, 1, 1, 1, 4, "alignment.horizontal", "center")

# Data rows with a formula for the total
for row, (product, units, price) in enumerate(sales, start=2):
    model.set_user_input(0, row, 1, product)
    model.set_user_input(0, row, 2, str(units))
    model.set_user_input(0, row, 3, str(price))
    model.set_user_input(0, row, 4, f"=B{row}*C{row}")

last_row = 1 + len(sales)

# A grand total below the data
model.set_user_input(0, last_row + 1, 1, "Total")
model.set_user_input(0, last_row + 1, 4, f"=SUM(D2:D{last_row})")
model.update_range_style(0, last_row + 1, 1, last_row + 1, 4, "font.b", "true")

# Currency formats for the price and total columns
model.update_range_style(0, 2, 3, last_row + 1, 4, "num_fmt", "$#,##0.00")

# Borders around the whole table
border = {"item": {"style": "thin", "color": "#2F5597"}, "type": "All"}
model.set_area_with_border(0, 1, 1, last_row + 1, 4, border)

# Wider first column
model.set_columns_width(0, 1, 1, model.get_column_width(0, 1) * 1.5)

assert model.get_formatted_cell_value(0, last_row + 1, 4) == "$1,896.00"

file = os.path.join(tempfile.mkdtemp(), "sales-report.xlsx")
model.save_to_xlsx(file)
print(f"saved {file}")

Conditional formatting

Color scales and cell-is rules. Rules are plain dictionaries with the same shape used by the IronCalc web application.

"""Highlight interesting values with conditional formatting."""

import os
import tempfile
import random

import ironcalc as ic

model = ic.UserModel("cf-example")

random.seed(42)
for row in range(1, 21):
    model.set_user_input(0, row, 1, str(random.randint(0, 100)))
    model.set_user_input(0, row, 2, str(random.randint(0, 100)))

# A color scale over the first column: red (low) to green (high)
model.add_conditional_formatting(
    0,
    "A1:A20",
    {
        "type": "ColorScale",
        "thresholds": [
            {"cfvo": "Min", "color": "#F8696B"},
            {"cfvo": {"Percentile": 50}, "color": "#FFEB84"},
            {"cfvo": "Max", "color": "#63BE7B"},
        ],
    },
)

# Highlight values greater than 90 in the second column
model.add_conditional_formatting(
    0,
    "B1:B20",
    {
        "type": "CellIs",
        "operator": "GreaterThan",
        "formula": "90",
        "formula2": None,
        "format": {"fill": {"color": "#FFC7CE"}, "font": {"b": True}},
        "stop_if_true": False,
    },
)

rules = model.get_conditional_formatting_list(0)
assert len(rules) == 2

file = os.path.join(tempfile.mkdtemp(), "conditional-formatting.xlsx")
model.save_to_xlsx(file)
print(f"saved {file}")

Reading an xlsx file

Values come back as native Python types (None, str, float or bool).

"""Read an existing xlsx file, inspect it and recalculate it."""

import os
import tempfile

import ironcalc as ic

# First create a workbook to read (in real life you already have one)
folder = tempfile.mkdtemp()
file = os.path.join(folder, "input.xlsx")

writer = ic.UserModel("input")
writer.set_user_input(0, 1, 1, "Price")
writer.set_user_input(0, 1, 2, "120")
writer.set_user_input(0, 2, 1, "Tax")
writer.set_user_input(0, 2, 2, "=B1*0.21")
writer.save_to_xlsx(file)

# Read it back
model = ic.load_from_xlsx(file)
model.evaluate()

# Walk all non-empty cells
(min_row, max_row, min_column, max_column) = model.get_sheet_dimensions(0)
for row in range(min_row, max_row + 1):
    for column in range(min_column, max_column + 1):
        value = model.get_cell_value(0, row, column)
        if value is not None:
            name = ic.column_name_from_number(column)
            print(f"{name}{row}: {value!r}")

# Values come back as native Python types
assert model.get_cell_value(0, 2, 2) == 25.2

# Change an input and recalculate
model.set_user_input(0, 1, 2, "200")
model.evaluate()
assert model.get_cell_value(0, 2, 2) == 42.0

Importing CSV data

"""Import tab separated data and add formulas over it."""

import ironcalc as ic

model = ic.UserModel("csv-import")

rows = [
    "month\trevenue\tcosts",
    "January\t10000\t8000",
    "February\t12000\t8500",
    "March\t14000\t9000",
]
model.paste_csv_string(0, 1, 1, len(rows), 3, "\n".join(rows))

# Add a profit column
model.set_user_input(0, 1, 4, "profit")
for row in range(2, len(rows) + 1):
    model.set_user_input(0, row, 4, f"=B{row}-C{row}")

assert model.get_formatted_cell_value(0, 2, 4) == "2000"
assert model.get_formatted_cell_value(0, 4, 4) == "5000"
print(model.get_formatted_cell_value(0, 4, 4))

Collaboration with diffs

Two models can be kept in sync by exchanging binary diffs, the same mechanism the IronCalc web application uses for real time collaboration.

"""Keep two models in sync exchanging diffs (the collaboration mechanism)."""

import ironcalc as ic

alice = ic.UserModel("shared")
bob = ic.UserModel("shared")

# Alice makes some edits
alice.set_user_input(0, 1, 1, "100")
alice.set_user_input(0, 2, 1, "=A1*2")
alice.update_range_style(0, 1, 1, 2, 1, "font.b", "true")

# ... and sends the resulting diffs to Bob
diffs = alice.flush_send_queue()
bob.apply_external_diffs(diffs)

assert bob.get_formatted_cell_value(0, 2, 1) == "200"
assert bob.get_cell_style(0, 1, 1)["font"]["b"] is True

# Edits flow in both directions
bob.set_user_input(0, 3, 1, "=SUM(A1:A2)")
alice.apply_external_diffs(bob.flush_send_queue())

assert alice.get_formatted_cell_value(0, 3, 1) == "300"
print("both models in sync")