spreadsheet-authoring

Spreadsheet Authoring

Write workbooks whose numbers are visible everywhere they are opened.

A formula is stored with the value it produced

An .xlsx cell holding a formula stores two things: the formula text, and the value that formula last produced. Excel fills the value in when it saves. openpyxl writes none, because it has no formula engine.

Anything that does not run a formula engine shows what the file stores. Chat file previews, Quick Look, Google Drive previews, and mail previews all read the stored value, so a workbook written by openpyxl shows =SUM(C2:C7) where the total should be, and a summary sheet of formulas comes out blank.

So: write the computed value next to every formula. Compute it in Python, where the data already is.

Install the two packages first

Both come from PyPI and neither is there by default. Run this once at the start of the task; the import check makes it a no-op when they are already installed.

python3 -c "import xlsxwriter, openpyxl" 2>/dev/null \
  || pip install --user --quiet XlsxWriter==3.2.9 openpyxl==3.1.5 \
  || pip install --user --quiet --break-system-packages XlsxWriter==3.2.9 openpyxl==3.1.5

Use pip, not python3 -m pip. Some environments mark the system Python as externally managed, and the second form is refused there outright. If both attempts fail, build a virtualenv somewhere that persists (python3 -m venv .venv), install into it, and run the scripts below with .venv/bin/python.

Writing a new workbook

Use XlsxWriter. write_formula(row, col, formula, format, value) takes the value as its last argument, which is the whole point.

import datetime

import xlsxwriter

rows = [
    ("Widget", datetime.date(2026, 1, 5), 120, 4.50),
    ("Gadget", datetime.date(2026, 1, 6), 80, 12.00),
    ("Doohickey", datetime.date(2026, 1, 7), 45, 7.25),
]
last = len(rows) + 1

book = xlsxwriter.Workbook("sales.xlsx")
bold = book.add_format({"bold": True})
money = book.add_format({"num_format": "#,##0.00"})
day = book.add_format({"num_format": "yyyy-mm-dd"})

sales = book.add_worksheet("Sales")
sales.write_row(0, 0, ["Item", "Date", "Units", "Unit price", "Total"], bold)
for index, (item, sold_on, units, price) in enumerate(rows, start=1):
    sales.write_string(index, 0, item)
    sales.write_datetime(index, 1, sold_on, day)
    sales.write_number(index, 2, units)
    sales.write_number(index, 3, price, money)
    line = index + 1
    sales.write_formula(index, 4, f"=C{line}*D{line}", money, units * price)

summary = book.add_worksheet("Summary")
summary.write_string(0, 0, "Revenue", bold)
summary.write_formula(
    0, 1, f"=SUM(Sales!E2:E{last})", money, sum(u * p for _, _, u, p in rows)
)
summary.write_string(1, 0, "Items sold", bold)
summary.write_formula(1, 1, f"=COUNTA(Sales!A2:A{last})", None, len(rows))

book.close()