Fix openpyxl data_only Formula Returns None
Reading a workbook that a script produced returns nothing where the totals should be:
>>> wb = load_workbook("out/report.xlsx", data_only=True)
>>> wb["Summary"]["D2"].value
None
>>> load_workbook("out/report.xlsx")["Summary"]["D2"].value
'=SUM(Data!C2:C500)'
pandas shows the same gap — pd.read_excel returns NaN for every formula column — and opening the file in Excel displays the correct numbers immediately.
Root Cause
An .xlsx file stores two things per formula cell: the formula text, and optionally the cached result of the last calculation. Excel writes both when it saves. openpyxl only writes the formula text — it has no formula engine, so it has nothing to cache — and data_only=True asks for exactly the value that is not there, returning None. Excel shows the right numbers because it calculates on open. The same happens with any file written by a library rather than by a spreadsheet application, and with files where calculation was set to manual and never run. A subtler version bites when a file is read, modified and saved with openpyxl: every cached value in the workbook is lost, so a file that worked as an input yesterday returns None today, and a formula cell read with data_only=False gives the formula string where downstream code expected a number.
Minimal Diagnostic
Check whether the workbook has cached values at all, per sheet, and how many formula cells are affected.
# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook
SOURCE = Path("out/report.xlsx")
def formula_report(path: Path) -> None:
formulas = load_workbook(path, data_only=False)
cached = load_workbook(path, data_only=True)
try:
props = formulas.calculation
print(f"calculation mode: {getattr(props, 'calcMode', '?')}, "
f"fullCalcOnLoad: {getattr(props, 'fullCalcOnLoad', False)}")
for ws in formulas.worksheets:
cached_ws = cached[ws.title]
total = with_cache = 0
example = None
for row in ws.iter_rows():
for cell in row:
if isinstance(cell.value, str) and cell.value.startswith("="):
total += 1
value = cached_ws[cell.coordinate].value
if value is not None:
with_cache += 1
elif example is None:
example = (cell.coordinate, cell.value)
if total:
print(f" {ws.title}: {total} formula cell(s), {with_cache} with a cached value"
+ (f"; first empty: {example[0]} = {example[1]}" if example else ""))
finally:
formulas.close()
cached.close()
if __name__ == "__main__":
formula_report(SOURCE)
calculation mode: auto, fullCalcOnLoad: False
Summary: 24 formula cell(s), 0 with a cached value; first empty: D2 = =SUM(Data!C2:C500)
Data: 500 formula cell(s), 0 with a cached value; first empty: E2 = =C2*D2
Zero cached values everywhere: the file has never been calculated by a spreadsheet application. A file saved by Excel would show the two counts equal.
Fix: Decide Whether the Value or the Formula Is the Contract
Two clean answers, depending on who consumes the file. When another program reads it, write values, not formulas. When a person edits it, keep formulas and recalculate before anything reads it back. Changed lines carry comments.
# pip install "pandas>=2.2" openpyxl xlsxwriter
from pathlib import Path
import pandas as pd
def write_values_for_machines(data: pd.DataFrame, dest: Path) -> Path:
summary = (data.groupby("region", observed=True)
.agg(orders=("amount", "size"), revenue=("amount", "sum"))
.reset_index())
summary["avg_order"] = (summary["revenue"] / summary["orders"]).round(2) # changed: computed here
dest.parent.mkdir(parents=True, exist_ok=True)
with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
data.to_excel(writer, sheet_name="Data", index=False)
summary.to_excel(writer, sheet_name="Summary", index=False) # changed: numbers, not formulas
book, ws = writer.book, writer.sheets["Summary"]
money = book.add_format({"num_format": "#,##0.00"})
ws.set_column(1, 3, 14, money)
return dest
Computing in pandas and writing numbers removes the problem entirely: any reader — openpyxl, pandas, another system — sees the same values, and nothing depends on a spreadsheet application having opened the file. This is the right default for report files that feed other automation.
When the workbook is a live model that people edit, keep the formulas and ask Excel to recalculate on open, so the first human to open it produces the cached values:
# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook
def formulas_with_recalc(dest: Path, last_row: int) -> None:
wb = load_workbook(dest)
try:
ws = wb["Summary"]
ws["D2"] = f"=SUM(Data!C2:C{last_row})" # formulas stay live for the reader
wb.calculation.fullCalcOnLoad = True # changed: Excel recalculates on open
wb.save(dest)
finally:
wb.close()
fullCalcOnLoad does not create cached values in the file — it instructs the application to recalculate when it opens. It is the right setting for human-facing workbooks, and it does nothing for a script that reads the file directly.
Variant Fix 1: Recalculate a Third-Party Workbook Headlessly
Files that arrive from elsewhere with formulas and no cached values — often because they were produced by another script — can be recalculated by LibreOffice on a server:
# system: sudo apt-get install -y libreoffice-calc
import shutil
import subprocess
import tempfile
from pathlib import Path
from openpyxl import load_workbook
def recalculate(path: Path, timeout: int = 180) -> Path:
if shutil.which("soffice") is None:
raise SystemExit("LibreOffice is not installed; cannot recalculate formulas")
with tempfile.TemporaryDirectory() as tmp:
result = subprocess.run(
["soffice", "--headless", "--norestore",
"--convert-to", "xlsx:Calc MS Excel 2007 XML", "--outdir", tmp, str(path)],
capture_output=True, text=True, timeout=timeout,
)
produced = Path(tmp) / (path.stem + ".xlsx")
if result.returncode != 0 or not produced.exists():
raise RuntimeError(f"recalculation failed: {result.stderr[:300]}")
dest = path.with_name(path.stem + "-calculated.xlsx")
shutil.copy2(produced, dest)
return dest
def read_calculated(path: Path, sheet: str, cell: str):
calculated = recalculate(path)
wb = load_workbook(calculated, data_only=True)
try:
return wb[sheet][cell].value
finally:
wb.close()
LibreOffice recalculates on load by default for foreign formats and writes the results into the converted file, so data_only=True then returns numbers. Two caveats: its function coverage is very good but not identical to Excel's — check any workbook using recent dynamic-array functions — and the conversion is slow enough (seconds per file) that it belongs in an intake step rather than inside a loop. The timeout matters, since a corrupt file can hang the process, as covered in fix LibreOffice headless conversion timeout.
Variant Fix 2: Compute the Formulas in Python Instead
For a handful of well-known formulas, re-implementing them is faster and needs no extra software:
# pip install openpyxl "pandas>=2.2"
import re
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries
SUM_RANGE = re.compile(r"^=SUM\((?:'?([^'!]+)'?!)?(\$?[A-Z]+\$?\d+:\$?[A-Z]+\$?\d+)\)$", re.I)
def evaluate_simple_sums(path: Path) -> dict[str, float]:
wb = load_workbook(path, data_only=False)
values: dict[str, float] = {}
try:
for ws in wb.worksheets:
for row in ws.iter_rows():
for cell in row:
if not isinstance(cell.value, str):
continue
match = SUM_RANGE.match(cell.value.replace(" ", ""))
if not match:
continue
sheet_name, ref = match.group(1) or ws.title, match.group(2).replace("$", "")
target = wb[sheet_name]
min_col, min_row, max_col, max_row = range_boundaries(ref)
total = 0.0
for r in target.iter_rows(min_row=min_row, max_row=max_row,
min_col=min_col, max_col=max_col, values_only=True):
total += sum(v for v in r if isinstance(v, (int, float)))
values[f"{ws.title}!{cell.coordinate}"] = total
return values
finally:
wb.close()
This works for simple aggregates over literal ranges and stops there: nested formulas, lookups and references to other files need a real engine. Libraries such as formulas and pycel implement one in Python and are worth evaluating when many workbooks must be evaluated without LibreOffice — with the caveat that any independent implementation differs from Excel somewhere, so critical figures deserve a spot check against the application.
Keeping Both: Values for Machines, Formulas for People
When a workbook must satisfy both audiences, write the numbers and put the formula alongside as a comment or in a hidden column, rather than hoping every reader recalculates:
# pip install openpyxl
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.comments import Comment
def annotate_with_formula(path: Path, sheet: str, cell: str, formula: str, value: float) -> None:
wb = load_workbook(path)
try:
ws = wb[sheet]
ws[cell] = value # the number every reader can rely on
ws[cell].comment = Comment(f"Computed as {formula}", "report job")
wb.save(path)
finally:
wb.close()
The cell holds a number, so pandas and openpyxl read it directly, while a person hovering sees how it was derived. For models where readers genuinely need to change inputs and see totals update, keep the formulas — but then treat the workbook as a human artefact and do not read it back in automation without a recalculation step.
Verification
Assert that the file a downstream job will read actually yields numbers, not None, and that they match what the pipeline computed.
# pip install openpyxl "pandas>=2.2"
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
def verify_readable_values(path: Path, expected: dict[str, float], sheet: str = "Summary") -> None:
wb = load_workbook(path, data_only=True)
try:
ws = wb[sheet]
for coordinate, want in expected.items():
got = ws[coordinate].value
assert got is not None, f"{sheet}!{coordinate} has no readable value (formula without cache)"
assert isinstance(got, (int, float)), f"{sheet}!{coordinate} is {type(got).__name__}, not a number"
assert abs(float(got) - want) < 0.01, f"{sheet}!{coordinate} = {got}, expected {want}"
finally:
wb.close()
frame = pd.read_excel(path, sheet_name=sheet)
assert not frame.isna().all().any(), "a column reads as entirely empty through pandas"
print(f"{path.name}: {len(expected)} value(s) readable without recalculation")
if __name__ == "__main__":
verify_readable_values(Path("out/report.xlsx"), {"B2": 4012.0, "C2": 18904221.55})
Running this against the file the job ships — not against an in-memory frame — is what catches the regression where someone converts a computed column back into a formula for tidiness and silently breaks every downstream reader.
FAQ
Can openpyxl calculate formulas? No. It reads and writes them as text; calculation is out of scope for the library.
Why did my previously working input start returning None? Something re-saved it with a library instead of Excel, discarding the cached values. Check the file's producer.
Does fullCalcOnLoad help my script?
No — it only affects applications that calculate. Scripts need values or a recalculation step.
Is there a way to tell whether a value is cached or computed?
Compare data_only=False and data_only=True for the same cell, as the diagnostic does: a formula string in one and a number in the other means the value is cached.
Related
- Writing Excel Formulas and Charts with openpyxl — building workbooks with live formulas
- Fix openpyxl Formulas Not Calculating — formulas that show as blank in Excel
- Add Subtotals to Excel Reports with pandas — static values versus SUBTOTAL formulas
- Reading Excel Files with Python — how readers see the resulting workbook