Here’s a bug that doesn’t look like a bug. Ask an AI agent to add one line to an existing
report. It does. The file opens fine in Excel - Excel recalculates every formula the moment
it opens a workbook, so nothing looks wrong. But hand that same file to a second automated
system - a PDF export, a dashboard pipeline, another script reading it with pandas
- and a
number that used to be there is now blank, or zero, or
None
. Nobody touched that cell.
I ran a real, minimal test to find out exactly when and why this happens.
The setup #
A tiny report: three line items and a Total
cell holding =SUM(B2:B4)
, saved with its
correct cached value (450
) already baked in - exactly what a real .xlsx
looks like after someone has opened and saved it in Excel at least once.
Item Amount
Widgets 100
Gadgets 150
Gizmos 200
Total =SUM(B2:B4) [cached: 450]
The raw XML for that cell confirms it, before anything is touched:
<c r="B5"><f>SUM(B2:B4)</f><v>450</v></c>
Both <f>
(the formula) and <v>
(the last-calculated value) are there, as siblings - this
is what makes a .xlsx
formula cell readable two different ways: recalculate it yourself, or
just read <v>
if you don’t have a formula engine at all.
The edit #
A completely unrelated, deliberately harmless change: append one new row below the total. Nothing about the formula, its range, or the cells it reads is touched.
Arm A (openpyxl):
import openpyxl
wb = openpyxl.load_workbook("formula_report.xlsx")
ws = wb["Report"]
ws.append(["Note", "Reviewed"])
wb.save("formula_arm_a_edited.xlsx")
Arm B (Kookerella.FsOpenXmlDsl):
let wb = Workbook.load "formula_report.xlsx"
let sheet = wb.Sheets |> List.find (fun s -> s.Name = "Report")
let editedSheet = { sheet with Cells = sheet.Cells @ [ /* the new row's cells */ ] }
Workbook.save "formula_arm_b_edited.xlsx" { wb with Sheets = [ editedSheet ] }
What’s objectively in each result #
The same B5
cell, in the same file, after the same category of edit:
Original: <c r="B5"><f>SUM(B2:B4)</f><v>450</v></c>
Arm A (openpyxl): <c r="B5"><f>SUM(B2:B4)</f><v /> </c>
Arm B (Kookerella): <c r="B5"><f>SUM(B2:B4)</f><v>450</v></c>
<v>
is still there in Arm A’s result - it’s just empty. The formula survived. The number didn’t.
Why this is worse than it looks #
Excel itself won’t show this. Open either file in real Excel and every formula recalculates
on load - 450
reappears, and you’d never know anything happened. The bug only surfaces when something reads the file without recalculating it. That’s not a rare edge case anymore - it’s exactly what a second automated consumer does:
wb = openpyxl.load_workbook("formula_arm_a_edited.xlsx", data_only=True)
wb["Report"]["B5"].value
data_only=True
is the mode that reads whatever’s cached instead of the formula text - the
same mechanism pandas.read_excel
uses under the hood. Feed this file into a second script,
a dashboard, or any headless pipeline, and the total is silently None
where it used to be
450
. No exception, no warning - just a missing number propagating into whatever runs next.
Doing the identical edit through Kookerella.FsOpenXmlDsl leaves B5
byte-for-byte intact:
data_only=True
still reads back 450
.
Why this happens #
Not a guess - openpyxl’s own Cell
class only has one value slot:
def _bind_value(self, value):
...
self._value = value
There’s no separate field for “the formula” and “its last cached result” at the same time.
When you load a workbook normally (data_only=False
- the mode you need if you want formulas
to stay editable rather than frozen as numbers), a formula cell’s
.value
becomes the formula
text. The cached number is never read into memory at all in that mode - data_only=True
is
a separate, mutually exclusive way of the same file that substitutes the cached value
in place of the formula. So the moment you open a workbook the normal way to edit it, the
cached value has nowhere to live - it’s gone before your edit even happens, and saving just
writes back what’s left: the formula, with an empty <v>
.
This isn’t an openpyxl bug exactly - it’s a structural consequence of a cell model built around “one value per cell,” which is a reasonable design for a library that never evaluates formulas itself. It just means every save silently costs you the one thing a headless reader downstream actually depends on.
Why this matters more now than it used to #
The old safety net was: someone opens this in real Excel eventually, and Excel fixes it. That assumption gets weaker every time more of a pipeline is agent-to-agent rather than human-in-the-loop - one AI produces a report, a second automated system consumes it, and nobody opens Excel in between. That’s the exact shape of workflow AI agents are increasingly used for, and it’s exactly the shape this bug is invisible inside.
Try it #
Kookerella.FsOpenXmlDsl
models a formula cell as Formula(expression, cachedValue: float option)
explicitly, precisely because of this failure mode - see its own README for the reasoning. The MCP server built on it is Kookerella.FsOpenXmlDsl.Mcp:
dotnet tool install -g Kookerella.FsOpenXmlDsl.Mcp
Don’t want a .NET dependency at all? Download a standalone build for your platform from the latest release instead - the runtime is bundled into the executable, so it’s unzip and run, no install required.
{
"mcpServers": {
"fsopenxmldsl": {
"command": "fsopenxmldsl-mcp"
}
}
}
It’s usable entirely through JSON via its create_workbook_from_json
/generate_json
tools too - no .NET required on the calling side. See the product page for the full tool list.